Archive for category Diagnostics
In SQL Server 2012 by setting up an extended event session based on the degree_of_parallelism event you can identify queries that are using large workspace memory grants.
As an example I ran the following test query and captured the memory grant using the actual execution plan, performance monitor, sys.dm_exec_memory_grants and the extended event session:
All four diagnostic tools show the same value for the granted memory.
You can change the definition of the extended event session to filter on the workspace memory to identify those queries requiring a large grant.
The sort_statistics_tracing extended event can be used to reveal details about the sorts being performed on your SQL Server. Adding the sql_text action and sending the output to a live data window we see this:
It’s possible to filter on the max buffers or bobs (big output buffers) to eliminate all but the larger sorts. In combination with the sort warning extended event we can identify the big sorts that are spilling to tempdb.
In SQL 2008 R2 and before there are very few ways to identify transactions that have been rolled back. A server side trace capturing the SQLTransaction events is one way. You’d filter on EventSubClass = 2 to capture just the rollbacks. Or you could try reading the transaction log using fn_dblog() filtering on Operation = LOP_ABORT_XACT. Yet another way, which provides very limited data, is to periodically query the syscacheobjects sql column for any text which is like ‘%rollback%’. Each of these methods has their own issues and overheads. Ideally we’d like a light-weight solution.
Fortunately, in SQL Server 2012 extended events comes to the rescue. You can either use extended events to map to a trace definition for SQLTransaction events or you could roll your own event session. Here’s one possible roll your own solution:
-- Create the event session to catch rollbacks CREATE EVENT SESSION evtsRollback ON SERVER ADD EVENT sqlserver.sql_transaction ( ACTION (sqlserver.session_id,sqlserver.database_id,sqlserver.sql_text) -- Could add more actions WHERE transaction_state = 2 -- Rollbacks only AND sqlserver.database_id = 8 -- Just this one database ) ADD TARGET package0.ring_buffer; GO -- Start the sesssion ALTER EVENT SESSION evtsRollback ON SERVER STATE=START; -- Begin a transaction, make a change, then rollback the transaction -- Drop the event ALTER EVENT SESSION evtsRollback ON SERVER DROP EVENT sqlserver.sql_transaction; -- Query the results SELECT event.value('(event/@name)','varchar(50)') AS event, DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),CURRENT_TIMESTAMP),event.value('(event/@timestamp)','datetime2')) AS [timestamp], event.value('(event/action[@name="session_id"])','int') AS session_id, event.value('(event/action[@name="database_id"])','int') AS database_id, event.value('(event/data[@name="duration"])','bigint') AS duration_microseconds, event.value('(event/data[@name="transaction_id"])','bigint') AS transaction_id, event.value('(event/data[@name="transaction_state"]/text)','nvarchar(max)') AS transaction_state, event.value('(event/data[@name="transaction_type"]/text)','nvarchar(max)') AS transaction_type, event.value('(event/action[@name="sql_text"])','nvarchar(max)') AS sql_text FROM ( SELECT n.query('.') AS event FROM ( SELECT CAST(target_data as XML) AS target_data FROM sys.dm_xe_sessions AS s JOIN sys.dm_xe_session_targets AS t ON s.address = t.event_session_address WHERE s.name = 'evtsRollback' AND t.target_name = 'ring_buffer' ) AS s CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(n) ) AS t; -- Tidy up DROP EVENT SESSION evtsRollback ON SERVER;
The results of the select statement for my simple test case were as follows:
One scenario in which you’d want to run such a diagnostic is when you have thousands of transactions a minute. For the vast majority of the week they all succeed but you encounter few occasions when the front end application deliberately rolls back a transaction, for example, when thresholds for acceptable levels of performance have been exceeded. Without such a light-weight diagnostic tool identifying infrequent rollbacks becomes problematic.