Profiler, SQL Server's built-in tracing feature, is an invaluable tool for performance tuning and debugging SQL Server applications. With it, you can trace every request a database application makes to SQL Server and use that data to improve performance. For example, if you run a Profiler trace during a long-running process, you might find that SQL Server is using a table scan, encountering table locks, or running into other performance-robbing events. In most cases, I run Profiler at the request of a developer who's trying to debug some tricky database code. Occasionally, if time permits, I go on a preemptive strike and run my favorite trace, the duration trace. This Profiler trace looks for processes accessing SQL Server that take more than 1 second. One second seems to be a good limit, letting you see the processes that are consuming the most resources without trapping everything.
If I run this trace only when I have a little free time, the data collection is sporadic and inconsistent. But what if I could run the trace on a regular schedule, gathering this performance data routinely throughout the day? I could find out whether certain processes take longer at different times of day and which databases most of the slower processes are accessing. All I have to do is schedule a SQL Server job to run my duration trace. By bringing together two great SQL Server tools—the scheduler and Profiler's trace functionality—you can easily set up a job to gather performance metrics, load this data into a table, then analyze it. And by combining Profiler traces with the job scheduler, you can run a trace on a set schedule and for limited periods without having to think about it.
This article assumes that you have some working knowledge of Profiler and that you've built and run traces before. If you've never used the Profiler interface to build traces, I recommend running a few built-in traces against a test SQL Server to familiarize yourself with the technology. For Profiler tips, perform a keyword search on Profiler at http://www.sqlmag.com. The examples I show use Profiler to build a trace that you can run through any SQL Server command-line tool, such as Query Analyzer. Although a trace could affect the performance of the server you're tracing if you choose too many events to watch, the trace functionality just observes the running processes, so you can't harm any data, transactions, or other processes.
Now let's look at the nine steps you can follow to set up a Profiler trace to run at set intervals and gather data about what's happening on your SQL Server. Before you begin, I recommend creating a separate database to hold the tables and stored procedure the examples create. In the scripts that create the scheduled jobs, I call the database TracerDB. If you use another name, be sure to adjust the example scripts. The T-SQL script to create both jobs is included in Web Listing 1.
Step 1: Use Profiler to Create the Trace Definition
To create the Profiler trace definition, I recommend using one of the built-in trace templates as a starting point. From the Profiler File menu, select Open, Trace Template, and select SQLProfilerTSQL_Duration. Before you forget, click Save As, and give your new template file a name.
Next, add a few data columns and filters to the trace. To exclude SQL Serverrun background processes from your trace, add the NTUserName filter and set the Not Like value to the account that runs your SQL Server and SQL Agent services. You might need to experiment with this; if your scheduled jobs run under a service account, you'll miss any processes that run through the job scheduler. I added the data columns StartTime and EndTime because I need to know when each process ran. To get a better picture of what's running, I added the data columns DatabaseID, Reads, and Writes. (I didn't include DatabaseName because DatabaseID consistently has a value, whereas DatabaseName doesn't.)
I set the Duration filter to at least 1000 milliseconds (1 second) so that I'd capture only the most attention-worthy processes. Depending on your environment, you can adjust that value according to how many processes you have time to analyze. SQL Server can do an amazing amount of work in less than 1 second, so setting Duration to 1000 eliminates most of the processes SQL Server runs. Click Save, and you're all set.
Step 2: Run the Trace
Now that you've saved the trace template, you need to run the trace so that you can create the trace output file. You'll use this file later to create the tables in SQL Server to capture your trace results. Select File, New, Trace from the Profiler menu to load the trace template you created. From the resulting Database Connection dialog box, you can connect to your server, then select the template you created from the Template Name drop-down box. Click Run. Let the trace run for a few minutes to collect some results in the trace window, then stop the trace.
Step 3: Save Files for Trace Definition and Trace Output
Now, save the T-SQL script that created the trace you just ran. From the File menu, select Script Trace, For SQL Server 2000. In the Save As dialog box, save the file with a .sql extension. You'll use this script to create a stored procedure to build the trace. Next, you need to create a file that contains the results of the trace you just ran. From the File menu, select Save As, Trace File, and save the file with a .trc extension. You'll use this file to create a table in SQL Server to save future trace results in. For this example, I saved the file to D:\sqlbackup\trace_dump_00.trc.
Prev. page
[1]
2
3
next page 
