Have you ever wonder how SQL Server keeps track of what you’re doing when you’re using a database application? One way is through session state capture, which is a tool that collects data on what you’re doing in a SQL Server session. This data can include things like the queries you run, the time it takes for those queries to run, and how much CPU and memory they use. By capturing this data, you can figure out which queries are slowing things down and optimize your database performance. In this blog, I’ll walk you through the process of setting up a session state capture in SQL Server, step-by-step. So let’s get started and learn about session state capture in SQL Server!
First off, let’s create the database to store our objects and set the db to ‘simple recovery’ and authorization for the ‘sa’ account.
--Script to create the database where you can save all your sessions captured. You can name the database whatever you like to call it.
CREATE DATABASE [DBATools]
GO
--Set the DBATools DB to simple recovery and set sa authorization
USE [master]
GO
ALTER DATABASE [DBATools]
SET RECOVERY SIMPLE WITH NO_WAIT
GO
USE [DBATools]
GO
ALTER AUTHORIZATION ON DATABASE::[DBATools] TO [sa]
GO
Now, let’s create the tables to store our data and set the ‘DateInserted’ to default.
USE [DBATools]
GO
--Script to create a session state capture table
CREATE TABLE [dbo].[SessionStateCapture](
[SessionStateCaptureID] [INT] IDENTITY(1,1) NOT NULL,
[SessionId] [INT] NULL,
[HostName] [NVARCHAR](128) NULL,
[ProgramName] [NVARCHAR](128) NULL,
[LocalNetAddress] [VARCHAR](48) NULL,
[LocalTcpPort] [INT] NULL,
[LoginName] [NVARCHAR](128) NOT NULL,
[DatabaseName] [NVARCHAR](128) NULL,
[ClientNetAddress] [VARCHAR](48) NULL,
[DateInserted] [DATETIME] NOT NULL
) ON [PRIMARY]
GO
--Script to create a session state capture exceptions table
CREATE TABLE [dbo].[SessionStateCaptureExceptions](
[SessionStateCaptureExceptionsID] [int] IDENTITY(1,1) NOT NULL,
[HostName] [NVARCHAR](128) NOT NULL,
[ProgramName] [NVARCHAR](128) NOT NULL,
[LoginName] [NVARCHAR](128) NOT NULL,
[DatabaseName] [NVARCHAR](128) NOT NULL,
[Comments] [NVARCHAR](2000) NULL,
[DateInserted] [DATETIME] NOT NULL
) ON [PRIMARY]
GO
--Script to create a session state capture staging table
CREATE TABLE [dbo].[SessionStateCaptureStaging](
[HostName] [NVARCHAR](128) NOT NULL,
[ProgramName] [NVARCHAR](128) NOT NULL,
[LoginName] [NVARCHAR](128) NOT NULL,
[DatabaseName] [NVARCHAR](128) NOT NULL,
[Comments] [NVARCHAR](2000) NULL
) ON [PRIMARY]
GO
--This is to make the DateInsert column auto populated by default with the current datetime
ALTER TABLE [dbo].[SessionStateCapture] ADD DEFAULT (GETDATE()) FOR [DateInserted]
GO
--This is to make the DateInsert column auto populated by default with the current datetime
ALTER TABLE [dbo].[SessionStateCaptureExceptions] ADD DEFAULT (GETDATE()) FOR [DateInserted]
GO
This stored procedure will capture the sessions and save in the SessionStateCapture table.
USE [DBATools]
GO
--Create a SP to collect the sessions and save it in the SessionStateCapture table
CREATE PROC [dbo].[usp_SessionStateCapture]
AS
INSERT INTO [dbo].[SessionStateCapture]
( [SessionId]
, [HostName]
, [ProgramName]
, [LocalNetAddress]
, [LocalTcpPort]
, [LoginName]
, [DatabaseName]
, [ClientNetAddress]
)
SELECT ec.session_id
, es.host_name
, es.program_name
, local_net_address
, local_tcp_port
, es.login_name
, DB_NAME(es.database_id) AS [database_name]
, ec.[client_net_address]
FROM sys.dm_exec_connections ec
INNER JOIN sys.dm_exec_sessions es
ON ec.session_id = es.session_id
LEFT JOIN [dbo].[SessionStateCaptureExceptions] ssce
ON es.host_name = ssce.HostName
AND es.login_name = ssce.LoginName
AND DB_NAME(es.database_id) = ssce.[DatabaseName]
WHERE ssce.HostName IS NULL
AND ssce.ProgramName IS NULL
AND ssce.LoginName IS NULL
AND ssce.[DatabaseName] IS NULL;
GO
--Create a SP to collect the log exceptions and save it in the SessionStateCaptureExceptions table
CREATE PROC [dbo].[usp_SessionStateCapture_LogExceptions]
AS
TRUNCATE TABLE [dbo].[SessionStateCaptureStaging];
INSERT INTO [dbo].[SessionStateCaptureStaging]
( [HostName]
, [ProgramName]
, [LoginName]
, [DatabaseName]
, [Comments]
)
SELECT [HostName]
, [ProgramName]
, [LoginName]
, [DatabaseName]
, [ProgramName]
FROM [dbo].[SessionStateCapture]
WHERE [DateInserted] > dateadd(hh, -1, getdate());
INSERT INTO [dbo].[SessionStateCaptureExceptions]
( [HostName]
, [ProgramName]
, [LoginName]
, [DatabaseName]
, [Comments]
)
SELECT sscs.[HostName]
, sscs.[ProgramName]
, sscs.[LoginName]
, sscs.[DatabaseName]
, sscs.[ProgramName]
FROM [dbo].[SessionStateCaptureStaging] sscs
LEFT JOIN [dbo].[SessionStateCaptureExceptions] ssce
ON sscs.[HostName] = ssce.[HostName]
AND sscs.[ProgramName] = ssce.[ProgramName]
AND sscs.[LoginName] = ssce.[LoginName]
AND sscs.[DatabaseName] = ssce.[DatabaseName]
WHERE ssce.[HostName] IS NULL
GROUP BY sscs.[HostName]
, sscs.[ProgramName]
, sscs.[LoginName]
, sscs.[DatabaseName]
, sscs.[ProgramName];
TRUNCATE TABLE [dbo].[SessionStateCaptureStaging];
GO
This script will create the Job with steps and schedule.
--This script is to create a job with steps and schedule
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class = 1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB', @type = N'LOCAL', @name = N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
--This line is to create a sql job
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N'_DBA - SessionStateCapture',
@enabled = 0,
@notify_level_eventlog = 2,
@notify_level_email = 2,
@notify_level_netsend = 0,
@notify_level_page = 0,
@delete_level = 0,
@description = N'Session State Capture',
@category_name = N'Database Maintenance',
@owner_login_name = N'sa',
@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
--This line is to create a job step1
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId, @step_name = N'Run Capture Procedure',
@step_id = 1,
@cmdexec_success_code = 0,
@on_success_action = 3,
@on_success_step_id = 0,
@on_fail_action = 2,
@on_fail_step_id = 0,
@retry_attempts = 0,
@retry_interval = 0,
@os_run_priority = 0,
@subsystem = N'TSQL',
@command = N'EXEC [dbo].[usp_SessionStateCapture]',
@database_name = N'DBATools',
@flags = 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
--This line is to create a job step2
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId, @step_name = N'Log Exceptions',
@step_id = 2,
@cmdexec_success_code = 0,
@on_success_action = 1,
@on_success_step_id = 0,
@on_fail_action = 2,
@on_fail_step_id = 0,
@retry_attempts = 0,
@retry_interval = 0,
@os_run_priority = 0,
@subsystem = N'TSQL',
@command = N'EXEC [dbo].[usp_SessionStateCapture_LogExceptions];',
@database_name = N'DBATools',
@flags = 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
--This line is create a schedule for the job
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId, @name = N'_DBA - Session State Capture - Every Minute',
@enabled = 1,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_start_date = 20210708,
@active_end_date = 99991231,
@active_start_time = 0,
@active_end_time = 235959,
@schedule_uid = N'32f5049b-d697-4fa8-9811-700dec02124c'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO