SQL Server Session Logging with sp_session_logger
Introduction
Why SQL Server sessions logging and monitoring is Important
Setting Up sp_session_logger
Using sp_session_logger
Understanding the Logged Data
Conclusion
Introduction
SQL Server sessions logging and monitoring is crucial for maintaining optimal database performance and identifying issues before they become problematic.
This blog post explores the importance of session monitoring, the insights it can provide, and details a stored procedure,
sp_session_logger,
designed for logging session information in SQL Server.
Why SQL Server sessions logging and monitoring is Important
SQL Server sessions logging and monitoring is essential for several reasons:
- Identifying Long-Running Queries: Monitoring sessions helps in pinpointing queries that consume excessive resources, enabling optimizations.
- Resource Utilization: Tracking CPU, memory, and I/O statistics ensures efficient resource use and helps in identifying performance bottlenecks.
- Detecting Deadlocks: Real-time monitoring can identify deadlocks, allowing for prompt resolution to minimize disruption.
- Diagnosing Slowdowns: Session data can reveal the causes of performance slowdowns, such as specific user activities or problematic applications.
- Tracking User Activity: Monitoring user sessions helps in detecting unauthorized access and ensuring compliance with security policies.
- Audit Trails: Keeping logs of session activities provides a comprehensive audit trail for security audits and forensic investigations.
Setting Up sp_session_logger
The
sp_query_logger stored procedure logs session information into a table named
session_logger. Here's how to set it up:
Create the Stored Procedure: This step creates or alters the
sp_session_logger procedure, which captures and logs session performance data.
if object_id('dbo.sp_session_logger') is null
exec ('create procedure dbo.sp_session_logger as return 0;');
go
alter procedure dbo.sp_session_logger
@output_database_name nvarchar(256) = null,
@output_schema_name nvarchar(256) = null,
@output_table_name nvarchar(256) = null
with
recompile
as
set nocount on;
set transaction isolation level read uncommitted;
begin
declare
@valid_output_location bit,
@create_table_string nvarchar(4000),
@insert_string nvarchar(4000);
select
@valid_output_location = 0,
@output_database_name = quotename(@output_database_name),
@output_schema_name = quotename(@output_schema_name),
@output_table_name = quotename(@output_table_name);
begin
if @output_database_name is not null
and @output_schema_name is not null
and @output_table_name is not null
and exists (select *
from sys.databases
where quotename(name) = @output_database_name)
begin
set @valid_output_location = 1;
end;
else if @output_database_name is not null
and @output_schema_name is not null
and @output_table_name is not null
and not exists (select *
from sys.databases
where quotename(name) = @output_database_name)
begin
raiserror('The specified output database was not found on this server', 16, 0);
end;
else
begin
set @valid_output_location = 0;
end;
end;
if @valid_output_location = 1
begin
set @create_table_string = 'use '
+ @output_database_name
+ '; if exists(select * from '
+ @output_database_name
+ '.information_schema.schemata where quotename(schema_name) = '''
+ @output_schema_name
+ ''') and not exists (select * from '
+ @output_database_name
+ '.information_schema.tables where quotename(table_schema) = '''
+ @output_schema_name + ''' and quotename(table_name) = '''
+ @output_table_name + ''') create table '
+ @output_schema_name + '.'
+ @output_table_name
+ ' (
id int identity(1,1) not null,
log_datetime datetime not null,
db_name nvarchar(128),
session_id smallint not null,
login_time datetime not null,
host_name nvarchar(128),
program_name nvarchar(128),
host_process_id int,
client_version int,
client_interface_name nvarchar(32),
security_id varbinary(85) not null,
login_name nvarchar(128) not null,
nt_domain nvarchar(128),
nt_user_name nvarchar(128),
status nvarchar(30) not null,
context_info varbinary(128),
cpu_time int not null,
memory_usage int not null,
total_scheduled_time int not null,
total_elapsed_time int not null,
endpoint_id int not null,
last_request_start_time datetime not null,
last_request_end_time datetime,
reads bigint not null,
writes bigint not null,
logical_reads bigint not null,
is_user_process bit not null,
text_size int not null,
language nvarchar(128),
date_format nvarchar(3),
date_first smallint not null,
quoted_identifier bit not null,
arithabort bit not null,
ansi_null_dflt_on bit not null,
ansi_defaults bit not null,
ansi_warnings bit not null,
ansi_padding bit not null,
ansi_nulls bit not null,
concat_null_yields_null bit not null,
transaction_isolation_level smallint not null,
lock_timeout int not null,
deadlock_priority int not null,
row_count bigint not null,
prev_error int not null,
original_security_id varbinary(85) not null,
original_login_name nvarchar(128) not null,
last_successful_logon datetime,
last_unsuccessful_logon datetime,
unsuccessful_logons bigint,
group_id int not null,
database_id smallint not null,
authenticating_database_id int,
open_transaction_count int
constraint [pk_' + cast(newid() as char(36)) + '] primary key clustered (id asc)); ';
set @insert_string = 'insert into '
+ @output_schema_name + '.' + @output_table_name
+ '(log_datetime, db_name, session_id, login_time, host_name, program_name, host_process_id, '
+ 'client_version, client_interface_name, security_id, login_name, nt_domain, nt_user_name, '
+ 'status, context_info, cpu_time, memory_usage, total_scheduled_time, total_elapsed_time, '
+ 'endpoint_id, last_request_start_time, last_request_end_time, reads, writes, logical_reads, '
+ 'is_user_process, text_size, language, date_format, date_first, quoted_identifier, arithabort, '
+ 'ansi_null_dflt_on, ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null, '
+ 'transaction_isolation_level, lock_timeout, deadlock_priority, row_count, prev_error, '
+ 'original_security_id, original_login_name, last_successful_logon, last_unsuccessful_logon, '
+ 'unsuccessful_logons, group_id, database_id, authenticating_database_id, open_transaction_count) '
+ 'select '
+ 'sysdatetimeoffset(), db_name(database_id), session_id, login_time, host_name, program_name, host_process_id, '
+ 'client_version, client_interface_name, security_id, login_name, nt_domain, nt_user_name, '
+ 'status, context_info, cpu_time, memory_usage, total_scheduled_time, total_elapsed_time, '
+ 'endpoint_id, last_request_start_time, last_request_end_time, reads, writes, logical_reads, '
+ 'is_user_process, text_size, language, date_format, date_first, quoted_identifier, arithabort, '
+ 'ansi_null_dflt_on, ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null, '
+ 'transaction_isolation_level, lock_timeout, deadlock_priority, row_count, prev_error, '
+ 'original_security_id, original_login_name, last_successful_logon, last_unsuccessful_logon, '
+ 'unsuccessful_logons, group_id, database_id, authenticating_database_id, open_transaction_count '
+ 'from sys.dm_exec_sessions order by session_id;';
if @valid_output_location = 1
begin
set @create_table_string = replace(@create_table_string, ''''+@output_schema_name+'''', ''''+@output_schema_name+'''');
set @create_table_string = replace(@create_table_string, ''''+@output_table_name+'''', ''''+@output_table_name+'''');
exec(@create_table_string);
set @insert_string = replace(@insert_string, ''''+@output_schema_name+'''', ''''+@output_schema_name+'''');
set @insert_string = replace(@insert_string, ''''+@output_table_name+'''', ''''+@output_table_name+'''');
exec(@insert_string);
end;
end;
end;
set nocount off;
go
Using sp_session_logger
To use
sp_session_logger, execute it with the appropriate parameters.
For example, to log session information into the
session_logger table of the
dbdocs database:
execute dbo.sp_session_logger
@output_database_name = 'dbdocs',
@output_schema_name = 'dbo',
@output_table_name = 'session_logger';
This setup logs detailed statistics for sessions, allowing you to analyze performance issues and optimize the query or index usage.
The
sp_session_logger procedure can be scheduled to run a few times a day to record the insights about the SQL Server sessions.
select * from session_logger;
Understanding the Logged Data
The
session_logger table captures extensive information about each sessions:
Column | Description
----------------------------------- | ----------------------------------------------
id Integer | identity column.
log_datetime | Datetime indicating the time of logging.
db_name | Name of the database.
session_id | Smallint indicating the session ID.
login_time | Datetime indicating the login time.
host_name | Name of the host.
program_name | Name of the program.
host_process_id | Integer indicating the host process ID.
client_version | Integer indicating the client version.
client_interface_name | Name of the client interface.
security_id | Varbinary containing security ID.
login_name | Name of the login.
nt_domain | Name of the NT domain.
nt_user_name | Name of the NT user.
status | Status information.
context_info | Varbinary containing context information.
cpu_time | Integer indicating CPU time.
memory_usage | Integer indicating memory usage.
total_scheduled_time | Integer indicating total scheduled time.
total_elapsed_time | Integer indicating total elapsed time.
endpoint_id | Integer indicating the endpoint ID.
last_request_start_time | Datetime indicating the last request start time.
last_request_end_time | Datetime indicating the last request end time.
reads | Bigint indicating the number of reads.
writes | Bigint indicating the number of writes.
logical_reads | Bigint indicating the number of logical reads.
is_user_process | Bit indicating if it's a user process.
text_size | Integer indicating text size.
language | Name of the language.
date_format | Date format.
date_first | Smallint indicating the date first.
quoted_identifier | Bit indicating if quoted identifier is on.
arithabort | Bit indicating if arithabort is on.
ansi_null_dflt_on | Bit indicating if ANSI NULL default is on.
ansi_defaults | Bit indicating if ANSI defaults are on.
ansi_warnings | Bit indicating if ANSI warnings are on.
ansi_padding | Bit indicating if ANSI padding is on.
ansi_nulls | Bit indicating if ANSI NULLs are on.
concat_null_yields_null | Bit indicating if concatenating NULL yields NULL.
transaction_isolation_level | Smallint indicating the transaction isolation level.
lock_timeout | Integer indicating lock timeout.
deadlock_priority | Integer indicating deadlock priority.
row_count | Bigint indicating row count.
prev_error | Integer indicating previous error.
original_security_id | Varbinary containing original security ID.
original_login_name | Name of the original login.
last_successful_logon | Datetime indicating last successful logon.
last_unsuccessful_logon | Datetime indicating last unsuccessful logon.
unsuccessful_logons | Bigint indicating unsuccessful logons.
group_id | Integer indicating group ID.
database_id | Smallint indicating database ID.
authenticating_database_id | Integer indicating authenticating database ID.
open_transaction_count | Integer indicating open transaction count.
Conclusion
In conclusion, the stored procedure
sp_session_logger serves as a comprehensive logging mechanism for tracking
session-related information within a SQL Server environment. By capturing essential details such as
login time, host
information, resource usage metrics, and transactional data, this procedure provides valuable insights into database activity and user interactions.
With its structured approach to logging and inclusion of various system parameters,
sp_session_logger offers
administrators and analysts a robust tool for monitoring and analyzing database sessions. This enables better performance tuning, troubleshooting, and
auditing of database activity, ultimately contributing to enhanced security, efficiency, and reliability of SQL Server environments.
Related content