Sort warnings events will rise when sort operation is performed in a query and it doesn’t fit into memory. In this case, SQL Server needs to spill the sort operation into tempdb which can result in a very slow physical operation especially if multiple passes to tempdb is needed for sorting the data. Ideally, there shouldn’t be any sort warnings on a server.

How to detect Sort Warnings in SQL Server 2008 (R2)?

In SQL Server 2008 (R2), sort warning event can be detected using SQL Profiler. SQL Server default trace has this event included by default. But using Sort Warning event in SQL Profiler we can’t capture what statements are causing these warnings because TextData column isn’t available.  Solomon Rutzky presented a way of finding statements by using profiler trace, trigger and DBCC INPUTBUFFER.

SQL Server 2008 (R2) doesn’t provide an event through Extended Events to track sort warnings. In SQL Server 2012 this will change.

How to detect Sort Warnings in SQL Server 2012 RC0?

In SQL Server 2012 RC0, the number of event has expanded to 616 and one of these new events is sort_warning event. Extended Events Sort_warning event can be used to detect what statements are causing sort warnings.  I will show how to use sort_warning event to detect sort warnings in database. Let’s create a new database with a simple table and stored procedures for testing.

-- Create TestSortWarnings database 
CREATE DATABASE TestSortWarnings 
GO 
USE TestSortWarnings 
GO 
-- Create TestTable 
CREATE TABLE dbo.TestTable (
	Col1 int IDENTITY(1,1), 
	Col2 varchar(8000)
) 
GO 
-- Insert data into table 
INSERT INTO dbo.TestTable (Col2) 
	SELECT REPLICATE('A','8000') 
GO 200 
CREATE PROCEDURE dbo.Test 
AS SET NOCOUNT ON 
-- Declare table variable 
DECLARE @T1 TABLE (
	Col1 int, 
	Col2 varchar(8000)
) 
-- Insert data to table variable 
INSERT INTO @T1 SELECT * FROM dbo.TestTable 
-- Return data sorted on Col2 column 
SELECT * FROM @T1 
ORDER BY Col2 ASC 
GO 
CREATE PROCEDURE dbo.Test1 
AS SET NOCOUNT ON 
-- Declare table variable 
DECLARE @T1 TABLE (
	Col1 int, 
	Col2 varchar(8000)
) 
-- Insert data to table variable 
INSERT INTO @T1 SELECT * FROM dbo.TestTable 
-- Return data sorted on Col2 column 
SELECT * FROM @T1 
ORDER BY Col2 ASC OPTION (RECOMPILE) 
-- Example with DINSTINCT 
SELECT DISTINCT Col2 FROM @T1 
-- Example with GROUP BY 
SELECT Col2, COUNT(*) FROM @T1 GROUP BY Col2

Table TestTable has 2 columns, no indexes and has 200 rows. Two stored procedures will generate sort warnings during execution. I will use this database, table and stored procedures in all my examples.

Detecting Sort Warnings with Extended Events

SQL Server 2012 management studio (SSMS) has UI for creating and managing Extended Events and it will be much easier to use it. Great thing is that SSMS includes an event session data viewer for all targets except the ETW file target. If you want to learn more about it you can read a post “Introducing the Extended Events User Interface” written by SQL Team. Post is focused on the mechanisms for creating event sessions and displaying event session data.

In this post I will use T-SQL but everything from this post can be done with UI. Next script will create event session SortWarning with ring_buffer target. It will capture sqlserver.sort_warning event with sqlserver.sql_text action. Event session is configured with a predicate on the sqlserver.database_id to track sort warnings only on TestSortWarnings database. Before you execute the script, you need to replace a value for database_id with a value returned by SELECT DB_ID(‘TestSortWarnings’) statement.

CREATE EVENT SESSION SortWarning 
ON SERVER 
ADD EVENT sqlserver.sort_warning( 
	ACTION(sqlserver.sql_text) 
	WHERE (sqlserver.database_id=(7))
) 
-- Replace database_id 
ADD TARGET package0.ring_buffer 
GO 
-- Start the Event Session 
ALTER EVENT SESSION SortWarning 
ON SERVER STATE=START 
GO

I will use prepared test data to simulate sort warnings. I will also include Actual Execution Plan to demonstrate new warnings information in it.

USE TestSortWarnings 
GO 
DECLARE @T1 TABLE (
	Col1 int, 
	Col2 varchar(8000)
) 
-- Insert data to table variable 
INSERT INTO @T1 SELECT * FROM dbo.TestTable 
-- Return data sorted on Col2 column 
SELECT * FROM @T1 ORDER BY Col2 ASC 
GO

If we look at Active Execution Plan we can see a warning on sort operator.

Let’s execute the stored procedure Test:

EXEC dbo.Test

GO

Let’s retrieve the event data from the event session target:

DECLARE @XEvent XML 
SELECT @XEvent = CAST(target_data AS XML) 
FROM sys.dm_xe_sessions s 
INNER JOIN sys.dm_xe_session_targets t 
ON s.address = t.event_session_address 
WHERE s.name = 'SortWarning' AND t.target_name = 'ring_buffer' 
SELECT 
	event_data.value('(./@name)[1]', 'varchar(20)') AS event_name, 
	DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_data.value('(./@timestamp)[1]', 'datetime')) AS timestamp, 
	event_data.value('(./data[@name="sort_warning_type"]/text)[1]', 'varchar(20)') AS sort_warning_type, 
	event_data.value('(./action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text 
FROM @XEvent.nodes('RingBufferTarget[1]/event') XE(event_data)

Results:

Sort_warning event is raised 2 times. We see that singe pass over the data are required to sort the data and statements which caused spill to tempdb.  Let’s now execute stored procedure dbo.Test1 (with included Actual Execution plan).

EXEC dbo.Test1

If we look at Actual Execution Plan we can see that stored procedure Test1 caused 2 sort warnings. (1) Second statement in stored procedure with ORDER BY didn’t cause sort warnings because of OPTION (RECOMPILE). Statements with DISTINCT (2) and GROUP BY (3) caused sort warnings.

If we retrieve the event data from the event session target again we will see 2 new records.

As we can see, by capturing only sqlserver.sort_warning event and sqlserver.sql_text action in event session we have information about the statement sent from a client to an SQL Server. Sometimes, that can be enough, but in some situations we need more information. If we have a stored procedure, which can be nested for example, with more statements that use sort operator (like Test1 stored procedure from our example), we will not know exactly which statements inside a stored procedure are causing sort warning events.

My next post will show how we can track sort warnings to the statement level inside a stored procedure.