Using the SQL Server Profiler
The Auto Update Statistics database option is a great feature of SQL Server, at least in most cases. In some very busy SQL Servers, this feature can interfere with normal daily activity. This is because this feature can “kick in” at times when the server is already very busy, degrading overall server performance. In these cases, it is sometimes better to turn this feature off and to manually or schedule an UPDATE STATISTICS during lulls in database usage. So how do you find out if your SQL Server is performing excessive Auto Updates? One of the best ways is to use the SQL Server Profiler. How you do this depends on which version of SQL Server you are using.
To use Profiler in SQL Server 7.0 to find how often Auto Update statistics occurs, first you must go to the Profiler’s menu and click on Tools, then Options. On the General tab, select “All Event Classes”. You must do this in order to see the Auto-Update Stats sub-event when you create the trace. Once you have selected “All Event Classes”, create a new trace. Then under the Events tab, open the Misc. event class, and then select the Auto-Update Stats event.
To use Profiler in SQL Server 2000/2005 to find how often auto update statistics occurs, all you have to do is to select the “Auto Stats” event, which is located under the “Objects” event class.
Run this trace for a day and see how many Auto Update statistics events you have. If you find that they occur often, and/or if they take a long time to run (the time it takes for the update to run is listed under the Duration column in the Profiler window), and/or if it runs during very busy times of the day, then you may want to consider updating statistics manually at a more opportune time, instead of automatically using the Auto Update Statistics database option. [7.0, 2000, 2005] Updated 2-11-2005
In SQL Server 2000/2005, the Profiler has an option on the “General” tab of the “Trace Properties” window called, “Enable Trace Stop Time.” It allows you to specify when a trace you start should automatically stop. This can be handy if you want to perform a trace for a specific period of time, but don’t want to be around to turn it off when you are done with the trace. [2000, 2005] Updated 2-11-2005
The SQL Server 2000 Profiler offers predefined Trace Templates. These are just default Profiler templates you can use to collect a variety of performance-related data. You can choose to use them as is, to modify them, or ignore them and create your own. If you are new to using the Profiler, you may want to carefully examine the templates in order to see how they are created. Here are the Trace Templates included with SQL Server 2000:
- TSQL: This template collects Transact-SQL statements run on SQL Server in the order that they are run. This gives you a “historical” snapshot of what is happening on your server, useful to help you see exactly what is going on in your SQL Server from a very broad perspective.
- TSQL (Grouped): This template also captures Transact-SQL statements, but it groups them by application name, Windows NT user name, login name, and the client process ID.
- SP Count: Tracks which stored procedures have been run, and how many times. The more often a SP runs, the more likely a candidate it is for tuning.
- TSQL SP: Traces the SPs that run, including the Transact-SQL that ran from them. Results are sorted by the time of the event.
- TSQL by Duration: Tracks the Transact-SQL statements that have run, along with how long they took to execute. This template is useful for identifying which queries take the longest time to complete.
- TSQL For Replay: Captures all the detail needed to replay trace on the same SQL Server, or another SQL Server. This may be useful for diagnosing and debugging problems.
- Tuning: This trace captures detailed information on SP and Transact-SQL statements, including performance related information, such as duration time.
- Standard: This trace captures a wide multitude of data, including logins and logoffs, SPs and Transact-SQL statements.
(2000) Updated 2-11-2005
SQL Server 2000/2005 offers a feature in Profiler called file rollover. What this feature does is to allow trace files to create multiple physical files instead of a single, very large trace file. To turn this feature on, first create a new trace and then open up the Property Sheet for the trace and view the “General” tab.
To use the file rollover feature, you must check the “Save to file” option and designate a file name for the trace to be stored. Next, you need to specify how large each file size should be. And last, you need to check the “Enable file rollover” option. Once you begin this trace, as the trace file reaches the specified size, then that file will be closed and a new file opened. This will continue until you stop the trace. If you don’t specify the “Enable file rollover” option, then once the file reaches its specified size, the trace will stop. [2000, 2005] Updated 2-11-2005
The version of Profiler that comes with SQL Server 2000/2005 collects more events classes than did the previous version of Profiler. Some of these are grouped under the “Database” events class group and include:
- Data File Auto Grow
- Data File Auto Shrink
- Log File Auto Grow
- Log File Auto Shrink
In other performance tips on this website we recommend that the database “Auto Shrink” function not be turned on because it takes up too much overhead. We also recommend that you manually size production databases and transaction logs to a large enough size so that the “Auto Grow” feature doesn’t have to “kick-in” very often, also because of the overhead involved.
Now, with the events classes available listed above, you can use the Profiler to find out if easily any of your databases have “Auto Shrink” turned on (not recommended) or if your databases are experiencing too many auto grows (and you need to manually grow your databases to large enough sizes instead). [2000, 2005] Updated 5-3-2005