Home > Database Administration, Extended Events, Performance, SQL Server 2012 > SQL Server 2012: Detecting Sort Warnings with Extended Events (sort_warning event)

SQL Server 2012: Detecting Sort Warnings with Extended Events (sort_warning event)

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
GO 

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.

 

Advertisements
  1. No comments yet.
  1. 03/05/2014 at 4:54 AM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: