Databases Reference
In-Depth Information
OTHER PERFMON LOG ANALYSIS TOOLS
This section evaluates common tools for managing, manipulating, and interpreting PerfMon logs.
Because PerfMon logs can be saved or converted to comma-separated value (CSV) i les, there are
many options for data analysis, including loading the i le into SQL Server, or analyzing it with
Microsoft Excel or almost any other data manipulation tool.
Using SQL Server to Analyze PerfMon Logs
Analyzing large quantities of performance logs with SQL Server can be a useful solution when data
analysis through other methods could be cumbersome and labor intensive. The data load process
from CSV i les could simply make use of the ad hoc Import/Export Wizard launched from SQL
Server Management Studio, or alternately this process could be automated and scheduled.
SQL Server can't read the native binary log i le (BLG) i le type, so you should either write PerfMon
logs to a log i le as a CSV i le type or use the Relog utility to convert the i le post-capture (more
detail to follow) from BLG to CSV. It is also possible for PerfMon to log directly to a SQL Server
database through a DSN, although there is additional overhead with this process, which can be
avoided by logging to i le.
Analyzing PerfMon logs from within a database has the benei t of data access through the familiar
language of T-SQL, which means problems should be easier to identify, and you can write queries
looking for specii c problem conditions. Here's an example where three counters could be used to
identify a low-memory condition:
➤
Available memory less than 100MB
➤
Page life expectancy less than 60 seconds
➤
Buffer cache hit ratio less than 98%
If the PerfMon logs have already been imported into SQL Server, the following query could be used
to identify any instance during the data capture window when the low memory condition existed:
SELECT *
FROM subset
WHERE Mem_Avail_Bytes < 1000000
AND Buff_Mgr_PLE < 60
AND Buff_Cache_Hit_Ratio < 98
This example should be modii ed to rel ect the table and column names specii ed during the data
import, but the concept could be adapted for any number of scenarios. Additionally, this method could
be used to manage performance data across a number of servers, and Reporting Services could be used
to present the data.
Combining PerfMon Logs and SQL Profi ler Traces
A feature i rst available in SQL Server 2005 was the capability to combine PerfMon logs with SQL
Proi ler traces. Using Proi ler to combine logs in this way enables the viewing of T-SQL code that's
running on the server, combined with the hardware impact of running the code, such as high CPU
or low memory.
Search WWH ::
Custom Search