Monitoring SQL Server Resources with System Monitor - Professional SQL Server 2005 Performance Tuning - page 60

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

LoggingDirectlytoSQLServer
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.

As a second option, system monitor could be configured to populate a database on a remote server. This
would reduce the monitoring overhead on the monitored server. However, it could affect the integrity of
the results because these will include network time. Logging to a remote server, whether to the console,
a flat file, or a SQL Server database, will include time for the data to travel across the network to the
logging server. This isn't necessarily a problem if any network latency is consistent. However, due to the
nature of networks, this can't usually be guaranteed.

Additionally, it is worth noting that Performance Monitor is supported when used with DSNs that use
the MDAC SQLODBC driver. The SQL Native Client cannot be used as a data logging destination with
Performance Monitor.

ImportingPerformanceLogintoSQLServer
The lowest monitoring overhead can usually be obtained by logging to a file saved locally (or
perhaps remotely). Once the log has been created, it may be copied to a different computer for analysis
or interrogation. SQL Server doesn't support directly reading or importing the .BLG (system monitor)
file type, so one of the easiest ways is to re-save the data as a comma-separated values (CSV) file that
can be readily imported into SQL Server. The import operation is driven by a wizard and will create the
destination table ready for analysis with TSQL. The following procedure assumes you've already created
and saved a .BLG file, and includes the operations required to import the data to SQL Server:

1.

Prepare the log file.
a.

Open the saved .BLG file in System Monitor.

b.

Add interesting counters (potentially all objects/counters, since they'll then be
available within the database for inclusion/exclusion within queries).

2.

Save the log as a CSV file.
a.

Right-click anywhere on the performance chart.

b.

Click Save Data As.

c.

Choose name and location for the file.

d.

Select the Save as Type: Text File (comma delimited) (*.csv).