Now switch to the Pubs database, and run a few queries against its tables. Make sure you use the same connection that called the stored procedure, because in our test, we retrieve the SPID of the current connection with the system function @@SPID and use that SPID as the trace filter. When you're finished, execute the following statement, replacing 14 with the queue handle you received in the printed and email messages:
sp_stop_mytrace 14
You can now open the generated file in SQL Profiler for review.
Setting a Trace to Autostart
You can manually use these stored procedures to start and stop a trace, or you can set them to run automatically when SQL Server starts or in response to certain events. To set a trace to autostart, you first save its definition by using extended stored procedure xp_trace_savequeuedefinition, then set the trace to autostart by using extended stored procedure xp_trace_setqueueautostart.
Savequeuedefinition requires two parameters in addition to the queue handle. Queue_name identifies the trace. (Savequeuedefinition will save the trace definition under the Registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ MSSQLServer\SQLServerProfiler\Server\Queues\<queue_name>. You load the trace definition later by using extended stored procedure xp_trace_loadqueuedefinition.) And is_shared specifies whether the queue definition is shared (1) or private (0).
Setqueueautostart also requires the queue name and a Boolean value that specifies whether to enable (1) or disable (0) autostarting.
Listing 3 shows the rest of the procedure to set a trace to autostart (note that this code doesn't show the steps already covered). To disable autostarting, run
EXEC xp_trace_setqueueautostart
'My Sample',
0 - auto-start? 1 - enable, 0 - disable
In the Trenches
Now that you understand Profiler's tracing functions, you can use them to investigate many database situations. Take long-running queries, for example. To find out why they're taking so long, just define a trace with the relevant completion events, depending on whether you run the queries inside stored procedures or in batches. You usually need to trace the SQL:StmtCompleted and SP:StmtCompleted events. Group the queries by duration or CPU data columns, depending on whether the queries are slower than you'd like or are using more CPU resources than they should. To make sure you don't waste tracing resources on fast queries, you can filter the trace by a minimum number of milliseconds for duration or a minimum number of milliseconds for CPU usage.
You can also use Profiler to find out what's going on behind the scenes in SQL Server Enterprise Manager. Suppose you want to see what Enterprise Manager does when you add the file 'file5' to a filegroup called 'fg5' in the testdb database. First, open testdb's database properties dialog box and add the file. But before clicking OK, set up a trace in Profiler using the default events and data columns and a specific-value filter of 1 (master) on database ID. Start the trace; click OK in Enterprise Manager, then stop the trace. Profiler will capture statements like these:
use [master]
ALTER DATABASE [testdb] ADD FILEGROUP [fg5]
ALTER DATABASE [testdb] ADD FILE(NAME = N'file5', FILENAME = N'd:\MSSQL7\DATA\file5_Data.NDF' , SIZE = 1, FILEGROWTH = 10%) TO FILEGROUP [fg5]
In addition, Profiler can help solve problems such as how to get automatic notification when an auto-grow parameter changes. This problem is tricky because you can't place a trigger on a system table. One way to get this information is to use Profiler to define a trace with the following events, data columns, and filters:
- Events: TSQL: SQL:StmtCompleted and SP:StmtCompleted
- Data Columns: EventClass, TextData, NTUserName, Appli-cationName, SQLUserName, and StartTime
- Filters: Specific-value filter on Database ID for the database you want to filter on and inclusion filter on Text (%alter%database%modify%file%)
You can then create an output table structured as the statement in Listing 4. Note that in this case, you must build a table instead of letting Profiler create it because you need to retrieve the TextData column from a trigger. The problem is that in the Profiler-created table, TextData is the ntext datatype, which is inaccessible from a trigger. To make the column in your table accessible from a trigger, just specify its datatype as nvarchar.
Next, you define a trigger to alert you when an auto-grow parameter changes, as Listing 5 shows. To test the trigger call, change a file's properties:
ALTER DATABASE testdb MODIFY FILE (NAME = 'testdb_dat', MAXSIZE = 30MB)
You'll receive the following email message:
File properties changed:
Statement: ALTER DATABASE testdb MODIFY FILE (NAME = 'testdb_dat', MAXSIZE = 30MB)
NT User Name: Gandalf
Application Name: MS SQL Query Analyzer
SQL User Name: NA
Time: 1999-12-22 14:15:28
Finding out which events led to a deadlock can be difficult, but Profiler provides some specific events to help. For example, you can trace the Lock:Deadlock event, which tells you a deadlock occurred and specifies the SPID and transaction ID deadlocked, the time the deadlock occurred, and the application and login names. The handy Lock: Deadlock Chain event, generated by each event leading to a deadlock, supplies the SPIDs and transaction IDs involved.
You can record the transaction IDs involved in the deadlock, then group the output by transaction ID and expand only the transactions involved. Or you might consider sending the trace output to a table so you can run queries that filter on a list of SPIDs or transaction IDs.
To generate a deadlock situation, create two tables—t1 and t2—each with one integer column, then in each table, insert a row with a value of 1. Define a trace with the following events: Lock:Deadlock, Lock: Deadlock Chain, and relevant starting and completion statement events (RPC, SP, SQL), depending on the source you suspect. In this example, we need only SQL: StmtStarting and SQL:StmtCompleted.
Besides the default data columns, add the transaction ID and any other data columns you might want. Filter the trace on the database ID of the database you're working on, then open two connections to your server from Query Analyzer. From connection 1, execute:
BEGIN TRANSACTION
UPDATE t1 SET col1 = 1
From connection 2, execute:
BEGIN TRANSACTION
UPDATE t2 SET col1 = 1
SELECT * FROM t1
COMMIT TRANSACTION
And from connection 1 again, execute:
SELECT * FROM t2
COMMIT TRANSACTION
Stop the trace, and open the trace file. Find the Lock:Deadlock Chain events, and record the transaction IDs involved. Group your output by transaction ID, and expand the relevant transactions. The output should look similar to that in Screen 1.
SQL Server Enterprise Manager provides a wizard that helps you set up traces, including one that helps you identify the cause of a deadlock. You can use the Create Trace Wizard to set up a trace by choosing Wizards from Enterprise Manager's Tools menu. Open the Management category, and choose Create Trace Wizard.
On the Case
SQL Profiler's trace replay capabilities along with SQL Server 7.0's extended stored procedures for tracing provide a wealth of power and flexibility for debugging database problems. Whether you want to simply monitor your SQL Server environment or need to solve some pressing performance mysteries, it's time to put your knowledge into action.