Using the SQL Server Profiler

The Profiler can store the data it captures in a text file or in a SQL Server table. For fastest performance and to use the fewest resources, you should store your data in a text file, not in a SQL Server table, on a computer other than the SQL Server being traced.

If you do decide to store the Profiler data in a SQL Server table, don't store it in a database on the same server you are profiling, as it could negatively affect the performance of the that server. Instead, store the trace in a database on another SQL Server that you have created especially for this purpose. [7.0, 2000, 2005] Updated 1-6-2006

*****

When running the Profiler, do not select more data than you need to collect. The more you collect, the more resources you will use, slowing down performance. This includes only selecting those events and data columns you really need. In addition, if you don't need to collect all events, you can use filters to reduce the amount of events you collect.

For example, one of the best ways to exclude unwanted data from your Profiler traces is to exclude the Enterprise Manager, Profiler, SQL Agent, and Query Analyzer events using the "ApplicationName" Not Like filter, such as:

• ApplicationName Not Like: SQL Profiler
• ApplicationName Not Like: MS SQLEM
• ApplicationName Not Like: SQLAgent - Alert Engine
• ApplicationName Not Like: SQLAgent - Job Manager
• ApplicationName Not Like: SQL Query Analyzer

Of course, you may want to trace these, but in most cases you won't, and getting rid of them will reduce the load placed on your server by Profiler. [7.0, 2000, 2005] Updated 1-6-2006

*****

These SQL Server Profiler event classes can be used to help you find out if SQL Server is running as efficiently as it can. They include:

• Execution Warnings: For very busy servers, it is possible that a query may have to wait a short time before there are available resources for it to run. This event class can be watched to find out if and how long queries have to wait for needed resources. This event class has two possible values. "Query Wait" is used to indicate how often a query has to wait. "Query Time-Out" is used to indicate how often a query has timed out waiting for the necessary resources. Obviously, you don't want to see either of these events. If you do, and they occur regularly, then you need to reduce the load on your server, beef up the server hardware, rewrite the queries, select more appropriate indexes to resolve the problem, or some combination of all these actions.
• Hash Warning: This event is used to measure hash recursions or hash bails that have occurred on your server. A hash recursion (event 0) happens when the input of the query does not fit entirely into memory, forcing SQL Server to split the input into multiple parts, which are then processed individually. A hash bail (event 1) is even worse for performance. It occurs when a hashing operation reaches its maximum recursion depth, which forces the query to run under an alternative query plan, one that is much less than optimal. Think of a hash bail as a hash recursion's worst nightmare. As you can imagine, these two events can really slow down a query. Some options to correct these problem events include: insure that the Index Statistics are up-to-date, rewrite the query, experiment with optimizer hints, or add more RAM to the server.
  
  While the purpose of this warning is to let you know when hash operations go awry, it also serves another purpose. If you want optimum performance, you want to avoid hash operations of any type, even ones that work properly. So if you identify any hash warnings, you might want to try to find out why a hash operation is being performed in the first place, and try to fix this problem. This of course, will also fix your hash warning problem.
• Missing Column Statistics: Tells you which columns in your query are missing statistics, such as the Index Statistics used by the Query Optimizer to help it evaluate the most efficient query plan. If one or more columns of statistics are missing, the Query Optimizer may not select the best query plan, hurting performance. To help prevent this problem, consider the following: Be sure you have "auto create statistics" turned on for your database, use the CREATE STATISTICS statement to manually create the statistics on the missing columns, or use the Index Tuning Wizard or Database Engine Tuning Advisor to identify and automatically create the needed statistics.
• Missing Join Predicate: Indicates whether or not the query in question has a join predicate. If not, this can cause the Query Optimizer to produce a less than optimized query plan. To fix this, add a join predicate.
• Sort Warnings: Tells you if sort operations can be done completely in memory or if the sorting operation has to be divided into two or more steps in order to complete. Obviously, a one step operation will be much faster. Potential solutions to this problem include reducing the number of rows to be returned and sorted, reducing the number of columns being sorted, getting rid of the sort, or adding more RAM to the server.

If you haven't done so already, trace these events on one of your busier databases and see what's going on. You may be surprised. [7.0, 2000, 2005] Updated 1-6-2006

*****

You don't have to run the Profiler from the GUI interface. If you like, you can control it by using a number of available extended stored procedures, such as xp_sqltrace in SQL Server 7.0; or any number of system stored procedures in SQL 2000/2005, such as sp_trace_create. (Note: SQL Server 7.0 uses extended stored procedures and SQL 2000 uses system stored procedures.) One of the advantages of using the Profiler's extended stored procedures instead of the GUI is that you can easily schedule the Profiler to automatically collect data at regular intervals throughout the day. In addition, these procedures use less server resources than does the Profiler tool itself.

For example, say you are wanting to collect trace data to feed the Index Tuning Wizard or Database Engine Tuning Advisor. In order for these tools to do a good job, it is important that the trace file it uses as its data be representative of the types of queries run throughout the day. Given this scenario, you might want to use the SQL Server Agent to schedule the appropriate Profiler extended or system stored procedures to run at regular intervals throughout the day, collecting a good representation of trace data to feed to the Index Tuning Wizard. Check the SQL Server Books Online for a listing of the various Profiler system and extended stored procedures.

Because Profiler traces can be called from extended or stored procedures, you can also include them in your applications, scripts, and stored procedures. If you find that you run a particular trace very often, you might be better off in the long run to create a custom stored procedure that runs the trace for you, instead of using the Profiler GUI each time. [7.0, 2000, 2005] Updated 1-6-2006