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

to be meaningful. As an example, you could consider the following scenario to represent a low
memory situation:

Available memory is less than 10 MB

❑

❑ Page life expectancy less than 60 seconds

❑ Buffer cache hit ratio less than 90 percent

You'd be fairly confident that a server exhibiting all three of these symptoms was experiencing a low
memory condition. You should be able to write a fairly simple query to determine whether the server
you're monitoring has these symptoms. Although your columns may be defined with different names,
a query such as this should provide this information:

SELECT *
FROM subset
WHERE Mem_Avail_Bytes < 100000
AND Buff_Mgr_Page_Life_Expect < 60
AND Buff_Cache_Hit_Ratio < 90

There are as many useful examples as there are counter combinations, and making the data available
within a database means limiting searches by columns, date range, and pre-defined or template
conditions is trivial. Additionally, with a little extra effort, Reporting Services could be used to present
and deliver performance analysis findings.

Combining Performance Monitor Logs and
SQL Profiler Trace
Troubleshooting performance related problems in SQL Server 2000 often required gathering
Performance Monitor logs, SQL Profiler traces, and activity details from system tables such as
sysprocesses. There was no capability provided to combine the log and trace data to provide any kind
of unified view. Interpreting this data often required system administrators to undertake the arduous
and pain-staking process of manually stepping through SQL Profiler traces and then switching to the
same point in time of the Performance Monitor logs to gain an understanding of resource status while a
specific query or stored procedure was executed. While laborious and time consuming, this method did
allow system administrators to correlate SQL Server activity (Profiler) with resource utilization
(Performance Monitor).

Fortunately, analysis of performance data has been significantly improved in SQL Server 2005 with a
feature allowing importing of Performance Monitor log files into a SQL Profiler trace. The process is
quite straight forward, and necessitates both Perfmon and Profiler traces be captured to file. Of course
you'll need to configure each Profiler and Perfmon separately, although these should run simultaneously.
Once the log and trace files have been created, you should open the trace file in SQL Profiler and from
the file menu select Import Performance Data and select the Perfmon log file. Further details, including
a screen capture of the end result (shown in Figure 2.4), can be found in Chapter 2, ''Monitoring Server
Resources with System Monitor.''