Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

After noticing 100's of sort_warning errors in a recent analysis of the default trace output, I am trying get the text of the queries causing those warnings.

enter image description here

SQL Server Profiler has an event called Sort Warnings, but it doesn't have the TextData column.

Using Extended Events is not possible for 2008 R2, because the sort_warning event isn't available in Extended Events for 2008 R2.

I thought about placing a trigger on the table I'm using to store profiler's results to capture the output from DBCC INPUTBUFFER.

-- table to store the results of dbcc inputbuffer
CREATE TABLE Trace_Results(EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(255))

CREATE TRIGGER [dbo].[Trace_Trigger]
  ON [dbo].Trace_Table
  AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL VARCHAR(MAX)

    SELECT @SQL = 'DBCC INPUTBUFFER(' + CONVERT(VARCHAR(10), ins.SPID) + ')'
    FROM   inserted ins

    --PRINT @SQL

  INSERT INTO Trace_Results (EventType, Parameters, EventInfo)
    EXEC(@SQL)


END

I can start the trace from profiler without any errors, but when I run a query that causes a sort warning, I get the following error:

enter image description here

Which causes the trace to stop.

I also just tried inserting directly into the Trace_Table table with the following command

INSERT INTO trace_table (eventclass) values(1)

, but nothing shows up in the Trace_Results table.

If I place the same trigger on any other user table in my database, the results from DBCC INPUTBUFFER are stored as expected in Trace_Results.

What would prevent a trigger on a table created in SQL Server Profiler from inserting data into another table?

UPDATE 24/01

Trying @Kin's solution, but still getting NULL in the SQLText columns

Query used to cause sort warnings

-- 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

Screen shot of sort_warnings_log table

enter image description here

share|improve this question
    
Well, seems like some kind of internal permissions thing or something perhaps, have you tried with a server-side trace instead of using Profiler directly? –  Aaron Bertrand Jan 21 at 15:53
    
Hello @AaronBertrand, I didn't think it was possible to store to a table with a server-side trace. –  Craig Efrein Jan 21 at 15:55
    
You should avoid using Profiler against production at all costs (see this and this). Also, if the plan is cached, you can always figure out the queries that contain sort warnings by combing through the plan cache, instead of trying to catch individual queries in the act (and making your trace have even more performance impact). –  Aaron Bertrand Jan 21 at 16:00
    
(Oh, I might be remembering poorly - were sort warnings not in the plan XML in 2008 R2?) And of course you can always automate pulling in trace data to a table from a file once a trace is up and running - I suspect such a table would have the ability to utilize such a trigger when Profiler is not involved. In any case, note that in either scenario, multiple rows with different SPIDs may be inserted, so your trigger currently won't capture them all, only an arbitrary single row. –  Aaron Bertrand Jan 21 at 16:04
2  

2 Answers 2

up vote 2 down vote accepted

These are the messages that get logged:

16943 Could not complete cursor operation because the table schema changed after the cursor was declared.
19052 The active trace must be stopped before modification.

So it seems like this is an inherent issue with creating a trigger on the trace table (and I did not crack open a debugger to see exactly what Profiler is trying to do in this case). This has nothing to do with the fact that you're pulling spid values in the trigger, or using DBCC INPUTBUFFER, or using dynamic SQL. A simple trigger that just does PRINT N'hi'; also causes the same symptom: those two exceptions, the error dialog in Profiler, and the trace stops logging to that table altogether.

Perhaps you can just set up a trace that collects both the sort warning events and SQL:StmtCompleted and try to manually correlate them using sequence / transaction info or simple intuition. Or you could try my idea from above:

You can always automate pulling in trace data to a table from a file once a server-side trace is up and running - I suspect such a table would have the ability to utilize such a trigger when Profiler is not involved.

It's not exactly as simple as it sounds, and it would have to poll quite frequently in order for the trigger to rely on the timeliness of subsequent inputbuffer data. I don't want to get too deep into the implementation details of that, it's just an off-the-cuff idea.

The other idea is to upgrade. :-)

share|improve this answer

You can use Event notification with Service broker as described in Trace Event Groups for Use with Event Notifications.

Below is the script that will help you : (and apologies that I misunderstood your crust of the question. For 2012, it is much better with XEvents)

Note that the script source is Identifying and Solving Sort Warnings Problems in SQL Server. I have modified it for as per my environment as we need both batch and sql statements that caused it.

--Create table to log data for sort warnings ....
IF OBJECT_ID (N'dbo.sort_warnings_log', N'U') IS NOT NULL
  DROP TABLE dbo.sort_warnings_log;
GO
CREATE TABLE dbo.sort_warnings_log
(
    MessageBody XML,
    LoggedTime DATETIME,
    Spid INT,
    BatchSqlText NVARCHAR(MAX),
    statementsqltext NVARCHAR(MAX)
);
GO

--Create a procedure to run when a message enters the queue
IF OBJECT_ID (N'dbo.usp_process_sort_warning_event', N'P') IS NOT NULL
    DROP PROCEDURE dbo.usp_process_sort_warning_event;
GO
CREATE PROCEDURE dbo.usp_process_sort_warning_event
WITH EXECUTE AS OWNER
AS
BEGIN
    DECLARE @messageTypeName NVARCHAR(256),
            @messageBody XML;
    RECEIVE TOP(1) -----------------> Change here for the messages.
            @messageTypeName = message_type_name,
            @messageBody = message_body
    FROM dbo.SortWarningsQueue;

    IF @@ROWCOUNT = 0
        RETURN;

    DECLARE @LoggedTime NVARCHAR(24);
    DECLARE @spid INT;
    DECLARE @sql NVARCHAR(MAX);
    DECLARE @sql1 NVARCHAR(MAX);

    SET @LoggedTime = CONVERT(DATETIME,CONVERT(NVARCHAR(24),@messagebody.query('data(//PostTime)')));
    SET @spid = CONVERT(INT,CONVERT(NVARCHAR(6),@messagebody.query('data(//SPID)')));

    SELECT  @sql = COALESCE(txt.text,'not found')   
    FROM sys.dm_exec_connections con
    CROSS APPLY sys.dm_exec_sql_text (con.most_recent_sql_handle) AS txt
    WHERE session_id = @spid;

    SELECT  @sql1 = SUBSTRING(txt.text, (req.statement_start_offset/2)+1, 
                (
            (CASE req.statement_end_offset WHEN -1 THEN DATALENGTH(txt.text) ELSE req.statement_end_offset END - req.statement_start_offset)/2)
                + 1
                ) 

    FROM sys.dm_exec_requests req
    CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) AS txt
    WHERE session_id = @spid;


    INSERT INTO dbo.sort_warnings_log(MessageBody, LoggedTime, Spid, BatchSqlText, statementsqltext)
    VALUES(@messageBody, @LoggedTime, @spid, @sql, @sql1);
END;
GO


--Create a queue to collect events
CREATE QUEUE dbo.SortWarningsQueue
WITH STATUS=ON, 
ACTIVATION 
            (
                PROCEDURE_NAME = dbo.usp_process_sort_warning_event,
                MAX_QUEUE_READERS = 5,
                EXECUTE AS OWNER
            );
GO

--Create a service on the queue that references the Event Notifications contract
CREATE SERVICE SortWarningsService
ON QUEUE dbo.SortWarningsQueue
(
    [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
);
GO

--Create the database event notification
CREATE EVENT NOTIFICATION NotifySort_Warnings
ON SERVER
FOR SORT_WARNINGS
TO SERVICE 'SortWarningsService','current database';
GO

--Cleanup Script
DROP EVENT NOTIFICATION NotifySort_Warnings ON SERVER;
DROP SERVICE SortWarningsService;
DROP QUEUE dbo.SortWarningsQueue;
DROP PROCEDURE dbo.usp_process_sort_warning_event;
DROP TABLE dbo.sort_warnings_log;
GO

Below is the screenshot :

enter image description here

Found some good articles :

share|improve this answer
    
Hello, I was able to setup the queue,service and event notification, but I am still not seeing the query text. I will update my question with the query I'm using to cause sort warnings. –  Craig Efrein Jan 24 at 10:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.