Monitoring SQL Server can be a complex task, especially when trying to capture all SQL queries and stored procedure calls for detailed analysis. Extended Events (XEvents) provide a robust way to capture this data with minimal performance impact. In this post, we'll set up an Extended Events session to capture all SQL and stored procedure calls, including important system events.
Setting Up an Extended Events Session
To monitor and capture SQL Server activity, you can create an Extended Events session using the script below. This session, named "CaptureAllSQLAndStoredProcCalls," will track various events including errors, connections, logins, logouts, RPC completions, and SQL batch completions.
Script Breakdown
Here’s a detailed script for creating the Extended Events session:
-- Create an Extended Events session to capture all SQL and stored procedure calls
CREATE EVENT SESSION [CaptureAllSQLAndStoredProcCalls]
ON SERVER
-- Capture errors with severity 20 and above, or specific error numbers
ADD EVENT sqlserver.error_reported
(ACTION
(
package0.callstack,
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name,
sqlserver.nt_username,
sqlserver.session_id,
sqlserver.session_nt_username,
sqlserver.sql_text,
sqlserver.tsql_stack,
sqlserver.username
)
WHERE
(
[severity] >= (20)
OR
(
[error_number] IN (17803, 701, 802, 8645, 8651, 8657, 8902, 41354, 41355, 41367, 41384, 41336, 41309, 41312, 41313)
)
)
),
-- Capture existing connections
ADD EVENT sqlserver.existing_connection
(ACTION
(
package0.event_sequence,
sqlserver.client_hostname,
sqlserver.session_id
)
),
-- Capture login events
ADD EVENT sqlserver.login
(SET collect_options_text = (1)
ACTION
(
package0.event_sequence,
sqlserver.client_hostname,
sqlserver.session_id
)
),
-- Capture logout events
ADD EVENT sqlserver.logout
(ACTION
(
package0.event_sequence,
sqlserver.session_id
)
),
-- Capture RPC completions
ADD EVENT sqlserver.rpc_completed
(SET collect_statement = (1)
ACTION
(
package0.event_sequence,
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name,
sqlserver.nt_username,
sqlserver.session_id,
sqlserver.session_nt_username,
sqlserver.sql_text,
sqlserver.tsql_stack,
sqlserver.username
)
WHERE ([package0].[equal_boolean]([sqlserver].[is_system], (0)))
),
-- Capture SQL batch completions
ADD EVENT sqlserver.sql_batch_completed
(ACTION
(
package0.event_sequence,
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name,
sqlserver.nt_username,
sqlserver.session_id,
sqlserver.session_nt_username,
sqlserver.sql_text,
sqlserver.tsql_stack,
sqlserver.username
)
WHERE ([package0].[equal_boolean]([sqlserver].[is_system], (0)))
)
-- Set the target to save the event data to a file
ADD TARGET package0.event_file
(SET filename = N'c:\temp\CaptureAllSQLAndStoredProcCalls.xel')
WITH
(
MAX_MEMORY = 16384KB, -- Maximum memory for the session
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, -- Event retention mode
MAX_DISPATCH_LATENCY = 5 SECONDS, -- Maximum dispatch latency
MAX_EVENT_SIZE = 0KB, -- Maximum event size
MEMORY_PARTITION_MODE = PER_CPU, -- Memory partitioning mode
TRACK_CAUSALITY = ON, -- Track causality
STARTUP_STATE = OFF -- Session is off by default on server restart
);
GO
Explanation of Key Components
Events Captured: This session captures a variety of events, including errors (severity 20 and above), existing connections, logins, logouts, RPC completions, and SQL batch completions. The specific error numbers are included to help diagnose common critical issues.
Actions and Filters: Each event includes actions to collect relevant data such as SQL text, session ID, and client application details. Filters are applied to capture non-system events and to ensure only critical errors are logged.
Target and Settings: The
event_filetarget is used to save captured events to a file for later analysis. Settings likeMAX_MEMORY,EVENT_RETENTION_MODE, andMAX_DISPATCH_LATENCYcontrol the performance and behavior of the session.Conclusion
Using Extended Events to capture all SQL and stored procedure calls provides valuable insights into SQL Server activity and performance. The script above helps set up a comprehensive monitoring session to track various server events and troubleshoot issues effectively.
For more SQL Server tips and tools, check out the SQL Server DBA Toolbox.
Feel free to customize this script to fit your specific monitoring needs and explore additional events and actions as required.
No comments:
Post a Comment