Using the SQL Server Profiler

The version of Profiler than comes with SQL Server 7.0 may not collect every event that SQL Server experiences. As events occur in SQL Server, they are first stored in a queue, then these events are moved to the Profiler for capture. If there are many events occurring, it is possible that the queue can fill up and events lost before they can be moved to Profiler. Although this is not a good thing, in practice it generally does not present too much of a problem because capturing every event is not always necessary (but sometimes it is).

In SQL Server 2000, this problem has been fixed, assuming you select the "Server processes SQL Server trace data" option, which is available from the "General" tab of the trace's property sheet. If you don't select this option, then you might lose trace events, just as you do in SQL Server 7.0. But if this option is selected, you will be guaranteed that no events will be lost. But, if you do choose this option, SQL Server will incur a heavier than normal load, which might affect the performance of your production applications. So only turn this option on if you need to guarantee no lost trace data. [7.0, 2000] Updated 8-7-2006

*****

Don’t run the Profiler on the same server you are monitoring, this can noticeably and negatively affect the server’s performance. Instead, run it on another server or workstation and collect the data there. [7.0, 2000, 2005] Updated 8-7-2006

*****

Whenever you start a trace using Profiler, producers (special code within SQL Server) are used to collect specific sets of events and pass them to a queue in SQL Server. The queue is a temporary holding area for captured events where they are held before being passed to the Profiler. To move the events from the queue to the Profiler, SQL Server creates a special thread to perform this task. If the producers increase the rate of captured events, causing the queue to fill up, SQL Server will automatically boost the priority of the threads, speeding the movement of events from the queue to the Profiler. This helps to prevent the queue from filling up.

But sometimes the producers send too many events to the queue (when the server you are profiling is very busy, and/or when you select too many events to capture), and the queue fills up. When this happens, the producers begin a short time-out period, allowing the queue to empty out. During this time-out period, the producers continue to collect events, but they are not written to the queue, instead, these events are lost.

To help prevent events from being lost under heavy load conditions, you can change the default queue settings. There are four different queue settings you can change:

• Number of rows to buffer: Default value: 1000. Increasing this value will allocate more memory to the queue and help prevent data loss, but doing so takes memory away from SQL Server's other activities.
• Server timeout (seconds): Default value: 5. This is the length of the time-out period that the producers use when the queue is full. By decreasing this amount, you reduce the amount of time that events are lost.
• Boost Priority (%): Default value: 95. Indicates the point at which the thread moving events from the queue to the Profiler is increased. The higher this percentage, the more the queue fills before the thread priority is increased.
• Reduce Priority (%): Default value: 90. Indicates the point at which the thread moving events from the queue to the Profiler is decreased. The lower this percentage, the emptier the queue before the thread priority is decreased.

Under normal circumstances you will not have to change these settings. But if you are profiling a lot of events from a very busy server, you may find that you are not capturing all events. If this is the case, you may want to experiment with some of these settings in order to reduce data loss. If you have extra memory in your server that is not being used by SQL Server, consider increasing the "Number of rows to buffer" setting. If you don't have the extra memory, you may want to consider changing the values for the "Boost Priority" and "Reduce Priority" settings. Microsoft recommends setting the "Boost Priority" to 2 and the "Reduce Priority" to 1 for heavy loads.

Like many settings, you will probably need to carefully experiment with various settings until you find the best for your particular environment and situation. Also, you may only want to use special settings for heavy user situations, not for use under normal loads.

To change these settings, go to the "Trace Properties" tab and click on the icon that looks like a server next to the "SQL Server" drop-down box. This brings up a dialog box where you can change these settings. [7.0, 2000] Updated 8-7-2006

*****

Many times, the output of a Profiler trace can be overwhelming. This can make it difficult to find what you are looking for within a trace. One way to help overcome this problem is to write the trace to a SQL Server table, then query the table directly using Query Analyzer or Management Studio. Assuming you know what you are looking for, this method can greatly speed up finding the data in the trace you want to find. [7.0, 2000, 2005] Updated 8-7-2006

*****

Since it is often impossible to manually tune every index and table in your database, the best way to spend your available "tuning time" is to identify those tables and indexes that your application uses the most. If you identify these, then you can focus your limited resources on hand tuning these heavily used objects.

So how to you identify these heavily used tables and indexes? One of the easiest ways to do this is to use the SQL Server Profiler. There is an event class called "Object: Opened." If you select this event class, you will get a trace of every object opened by SQL Server.

To make the trace easier to read, group the "text" data column. This way, you can easily see how many events have occurred for each object access. Alternately, you can store the trace in a SQL Server table, and then apply the appropriate GROUP BY query to get the results you want. [7.0, 2000, 2005] Updated 8-7-2006