SQL Server Profiler is a powerful tool for troubleshooting and performance tuning. However, there are situations where it may not provide all the necessary information to identify the root cause of certain problems. One such scenario is when the ObjectId, ObjectName, and TextData fields are empty or NULL. In such cases, you are left with only the knowledge that the event occurred, which may not be sufficient to diagnose sporadic issues in a production environment.

One common problem that DBAs often encounter is when the tempdb database grows at an alarming rate, consuming more space than usual. The challenge here is to determine what is causing the excessive growth. It is important to note that tempdb space is not only occupied by temporary tables, but also by various other operations such as cursors, DBCC CHECKDB, DISTINCT, ORDER BY, GROUP BY, triggers, table variables, table-valued functions, hash joins, hash aggregate operations, and index creations with the SORT_IN_TEMPDB option. In addition, new features introduced in SQL Server 2005, such as snapshot isolation level transactions and Multiple Active Result Sets (MARS), also utilize tempdb.

To identify the source of the problem, you can start by checking if the data is stored in tables or indexes. This can be done by querying the reserved column of the tempdb.dbo.sysindexes table (remember to divide the value by 128.0 to get the size in megabytes). However, it is possible that only a small portion of the data causing the growth is stored in tables, given the numerous operations that utilize tempdb.

In order to determine what is using tempdb, you can use SQL Profiler to capture events such as Sort Warnings (indicating a sort operation that couldn’t fit in memory) and Data File Auto Grow. Sort Warnings provide insights into the operations that are using tempdb in general, while Data File Auto Grow indicates which operations are consuming significant amounts of data, leading to the need for tempdb to increase in size. Unfortunately, the SQL statement or object that caused these events is not recorded in the Profiler output, as both the TextData and ObjectId fields are empty.

However, there is a workaround to augment the Profiler results and capture additional information. By adding a trigger to the table where the Profiler results are saved, you can store relevant information in the TextData field. It is important to select certain fields, such as EventClass, DatabaseID, SPID, and TextData, and optionally include EventSubClass and ApplicationName. The trigger can be created using the following script:

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

Once the trigger is in place, you can view the captured data from the trace using the following code:

SELECT * FROM working.dbo.TraceTable

It is important to note a couple of minor issues with this approach. Firstly, the trigger needs to be created each time the trace is run, even if you are saving the results to the same table. This is because the table is dropped and recreated each time, which in turn drops the trigger. Secondly, the output from DBCC INPUTBUFFER is not always guaranteed to be accurate, as it may reflect the buffer contents after the event was caught by SQL Profiler and inserted into the table. However, for operations such as Sort Warnings and Auto Grow, it is generally safe to assume that the process triggering the event will still be running when DBCC INPUTBUFFER is executed.

While this example focuses on identifying tempdb and file growth issues, the same method can be applied to any Profiler event that does not provide data in the TextData field. For events that do return TextData values, you can use a WHERE condition in the UPDATE statement to ensure that the trigger does not overwrite the existing data captured by Profiler.

By leveraging SQL Profiler and adding a trigger to capture additional information, you can gain deeper insights into the operations utilizing tempdb and identify the root cause of excessive growth. This approach can be a valuable tool in troubleshooting and optimizing the performance of your SQL Server environment.