Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Capturing Missing Information From SQL Profiler

By Solomon Rutzky,

While SQL Profiler is a very helpful (and even invaluable) tool, it does not always provide all of the information needed to find the root cause of certain problems. If ObjectId, ObjectName, and TextData are all empty / NULL, then all you are left with is the knowledge that the event you are looking for occurred. Knowing only that the event occurred will only help in a controlled environment where you caused the event but not in production where something is happening sporadically.

Take for instance the particularly squirrelly issue of having tempdb grow at an alarming rate, far more than usual and taking up a lot more space than usual. The problem you are dealing with here is determining what is taking up the space. This can be a difficult task since since tempdb space is not always taken up only by temp tables. Many operations use tempdb as work space explicitly, such as Static and Keyset Cursors, as well as DBCC CHECKDB. Other operations use tempdb when needed (typically when there is not enough space in memory to complete the operation or store the data), such as when using DISTINCT, ORDER BY, GROUP BY, AFTER triggers, table variables, table returned in table-valued-functions, hash joins and hash aggregate operations, and when (re)creating Indexes with the SORT_IN_TEMPB option. Additionally, in SQL Server 2005, new features will also use tempdb, such as snapshot isolation level transactions and Multiple Active Results Sets (MARS). And there may be even a few more. That's a lot of possibilities of what can be contributing to tempdb growth!

So first we check to see if that data is in tables or indexes (as that might point to a source of the problem) via the reserved column of tempdb.dbo.sysindexes (be sure to divide the value in that field by 128.0 to see how much it is in Megabytes). Of course, it is very possible that very little of the data that is causing the problem is in tables given how many operations use tempdb. If this is the case we have to find out what is using tempdb. For this we can run SQL Profiler and trap events such as: Sort Warnings (indicates that a sort could not fit in memory) and Data File Auto Grow. Sort Warnings will show us many of the operations that are using tempdb in general and the Data File Auto Grow will indicate which operations are using so much data that the database has to increase the size of tempdb. Unfortunately, the SQL (statement or object) that caused either event is not recorded in the Profiler output; both TextData and ObjectId fields are empty. How unhelpful is that?

Triggers, Temp Tables, and Input Buffers, Oh My!

All is not lost, though, as we can augment the Profiler results saved to a table; we will just add a trigger to store information in the TextData field. While it doesn't matter entirely which fields you choose to report on, you must at least pick: EventClass, DatabaseID, SPID, and TextData. You should also select: EventSubClass and ApplicationName. When you run the trace, be sure to save the results to a table. After you click on "Run" and the trace starts, execute the following (assuming that you have replaced {db_name_here} in the USE statement with whatever database you are storing the trace results in). The script assumes a table name of TraceTable to store the results in. Just change the name in the ON and FROM clauses if you choose a different table name.

Please note that this has only been tested on SQL Server 2000.

USE [{db_name_here}]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[TraceTableExtraInfo]
  ON [dbo].[TraceTable]
  AFTER INSERT
AS
BEGIN
  SET NOCOUNT ON;


  DECLARE @SQL VARCHAR(40)

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

  CREATE TABLE #DBCCInfo (EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(255))

  INSERT
  INTO #DBCCInfo (EventType, Parameters, EventInfo)
    EXEC(@SQL)


  UPDATE tt
  SET    tt.TextData = info.EventInfo
  FROM   dbo.TraceTable tt
  INNER JOIN  inserted ins
         ON   ins.RowNumber = tt.RowNumber
  CROSS JOIN  #DBCCInfo info
  WHERE  tt.TextData IS NULL

  DROP TABLE #DBCCInfo
END



You can then see the data from the trace with the following code:

SELECT * FROM working.dbo.TraceTable

Now there are two minor issues with this approach:

  1. You have to create this trigger each time you run the trace. This includes each time you re-run the trace, even when saving to the same table, as it will drop and recreate the table each time. The dropping of the table in turn drops the trigger.
  2. The output from DBCC INPUTBUFFER is not guaranteed to be accurate; it might be what is in the buffer after the event was caught by SQL Profiler and the row was inserted into the table. For the operations of Sort Warnings and Auto Grow it seems safe enough to assume that in the majority of cases the process that triggered that event will still be running by the time DBCC INPUTBUFFER runs since the processes needed the resource (either tempdb in the case of a Sort Warning or more File space in the case of an Auto Grow). But for the most part, if there is a real problem then you will clearly see a pattern between the event and what is in the buffer when the event is triggered.

Please note that while the example situation shown here is looking for tempdb and file growth issues, this method can be used on any Profiler event that does not return data in the TextData field. For those events that do return TextData values there is the WHERE condition in the UPDATE statement that ensures that if the TextData field is populated by Profiler, it will not be over-written by this trigger.

Total article views: 9393 | Views in the last 30 days: 4
 
Related Articles
FORUM

Profiler Event: SP:StmtStarting with TextData but empty ObjectName

Profiler Event: SP:StmtStarting with TextData but empty ObjectName

FORUM

Trigger to capture TSQL statement

like "Textdata" column in Profiler

ARTICLE

Mastering SQL Server Profiler - Part 4: Profiler Events

The fourth module of the training course "Becoming a Profiler Master", takes a detailed look at the ...

FORUM

PROFILER: What events should i check..

PROFILER: What events should i check...

FORUM

SQL profiler - textdata NULL

Hi, Have scheduled a trace in sql server job . but when i try to read the trace textdata column is ...

Tags
monitoring    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones