Professional SQL Server® 2005 Performance Tuning

3.10. Using SQL Server to Analyze Performance Logs

As someone interested in SQL Server, you'll probably be comfortable with Transact-SQL and more interested in knowing how to analyze logs using TSQL than learning a specialist scripting tool for analyzing logs such as LogParser and so on. If this is true, then you'll be interested to learn there are a couple of ways to get performance data into SQL Server to enable you to manipulate and analyze the data in an environment you're already familiar with and making patterns or trends easier to identify.

If you think analyzing performance data in a SQL Server database sounds useful, there are two possible methods to achieve this:

• Configure System Monitor to log in live and direct to SQL Server

• Import performance data to SQL Server post data capture

3.10.1.

3.10.1.1. Logging Directly to SQL Server

Using a regular ODBC DSN, you can configure System Monitor to log directly to a database. When monitoring is complete, performance data is immediately available for analysis directly within SQL Server. The CPU, memory, and disk resources required to administer monitoring and having a server logging directly to a SQL Server database will usually incur a fair amount of overhead. This could affect the server being monitored and thus the results of the monitoring. At the very least, monitoring overhead should be taken into account when recording performance baseline data and when making any recommendations to alter a configuration or schema.