Archive for category Diagnostics

Large workspace memory grant queries

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.


Leave a comment

Sort statistics tracing

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.

Leave a comment

Identifying rolled back transactions

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

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;

-- Start the sesssion

-- 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
event.value('(event/@name)[1]','varchar(50)') AS event,
DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),CURRENT_TIMESTAMP),event.value('(event/@timestamp)[1]','datetime2')) AS [timestamp],
event.value('(event/action[@name="session_id"])[1]','int') AS session_id,
event.value('(event/action[@name="database_id"])[1]','int') AS database_id,
event.value('(event/data[@name="duration"])[1]','bigint') AS duration_microseconds,
event.value('(event/data[@name="transaction_id"])[1]','bigint') AS transaction_id,
event.value('(event/data[@name="transaction_state"]/text)[1]','nvarchar(max)') AS transaction_state,
event.value('(event/data[@name="transaction_type"]/text)[1]','nvarchar(max)') AS transaction_type,
event.value('(event/action[@name="sql_text"])[1]','nvarchar(max)') AS sql_text
SELECT n.query('.') AS event
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 = 'evtsRollback'
AND t.target_name = 'ring_buffer'
) AS s
CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(n)
) AS t;

-- Tidy up

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.



Leave a comment