Using Perfmon with SQL Server – Part Two

Rate This
  • Comments 1

Yesterday I posted a header from a script that shows you some links for how to use “Logman”, a Windows command-line utility that can automate your collection of Windows Performance Monitor counters. Towards the bottom, you can see the logman command I use, and you can see that it points to a counter file for the objects and counters I collect on a regular basis.

In the script that follows, you’ll see the counters I collect. There are a LOT of caveats to this script – For one, it assumes a Database Named PERF, that you have the same counters I do, and that you’ve collected them into a Comma-Separated Values (CSV) file in the C:\TEMP directory.

In fact, it’s best if you just read through this script, and understand what is going on rather than trying to run it. Once you understand it, you can create your own structure using the those concepts.

As always, this is on a test system, know what you’re doing, don’t run with scissors:

/*    Perfmon Example.sql
Purpose:  Imports Perfmon Data into SQL Server
Author:   Buck Woody
Last Edited: 09/30/2009
Instructions: Proper Counters must be tracked. See table definition
    for that structure.
References:
You can also automate this: http://www.mssqltips.com/tip.asp?tip=1776
Another way: http://www.mssqltips.com/tip.asp?tip=1722
Counters to include: http://sqlblog.com/blogs/kevin_kline/archive/2008/03/28/the-most-important-performance-monitor-counters-for-sql-server.aspx
Interpretations: http://www.extremeexperts.com/SQL/Articles/SQLCounters.aspx
More Interpretations: http://technet.microsoft.com/en-us/magazine/2008.08.pulse.aspx
*/

/*
Logman automation - Comes with Windows OS:
logman /?
logman create /?
logman start /?
logman stop /?
logman start "SQL Server Counters"
*/

USE PERF;
GO

/* Clean up if desired
DROP TABLE PerfmonCounters
End Clean Up */

CREATE TABLE PerfmonCounters (
DateCollected VARCHAR (55)
, Processor_Total_Percent_Processor_Time varchar (100)
, PhysicalDisk_Total_Average_Disk_Bytes_Write varchar (100)
, PhysicalDisk_Total_Average_Disk_Bytes_Read varchar (100)
, PhysicalDisk_Total_Average_Disk_Queue_Length varchar (100)
, MSSQL_SQL2K8_Transactions_Longest_Transaction_Running_Time varchar (100)
, MSSQL_SQL2K8_SQL_Statistics_SQL_ReCompilations_sec varchar (100)
, MSSQL_SQL2K8_SQL_Statistics_SQL_Compilations_sec varchar (100)
, MSSQL_SQL2K8_Memory_Manager_Total_Server_Memory_KB varchar (100)
, MSSQL_SQL2K8_Memory_Manager_Target_Server_Memory_KB varchar (100)
, MSSQL_SQL2K8_General_Statistics_Logouts_sec varchar (100)
, MSSQL_SQL2K8_General_Statistics_Transactions varchar (100)
, MSSQL_SQL2K8_General_Statistics_User_Connections varchar (100)
, MSSQL_SQL2K8_General_Statistics_Active_Temp_Tables varchar (100)
, MSSQL_SQL2K8_General_Statistics_Logins_sec varchar (100)
, MSSQL_SQL2K8_General_Statistics_Processes_blocked varchar (100)
, MSSQL_SQL2K8_Databases_Total_Transactions_sec varchar (100)
, MSSQL_SQL2K8_Databases_Total_Data_File_Size_KB varchar (100) 
, MSSQL_SQL2K8_Databases_Total_Backup_Restore_Throughput_sec varchar (100)
, MSSQL_SQL2K8_Databases_Total_Active_Transactions varchar (100)
, MSSQL_SQL2K8_Buffer_Manager_Buffer_cache_hit_ratio varchar (100)
, MSSQL_SQL2K8_Access_Methods_Page_Splits_sec varchar (100)
, MSSQL_SQL2K8_Access_Methods_Full_Scans_sec varchar (100)
, Memory_Percent_Committed_Bytes_In_Use varchar (100)
, Memory_Available_MBytes varchar (100)
, Memory_Cache_Faults_sec varchar (100)
)

/* Import */
BULK INSERT PerfmonCounters
FROM 'C:\TEMP\\ImportMe.csv'
WITH (FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n')
GO

/* Analysis */
SELECT  CONVERT(DATETIME, [DateCollected]) AS 'DateAndTimeCollected'
      , CONVERT (NUMERIC(20,10), [Processor_Total_Percent_Processor_Time]) AS 'Processor_Total_Percent_Processor_Time'
      ,[PhysicalDisk_Total_Average_Disk_Bytes_Write]
      ,[PhysicalDisk_Total_Average_Disk_Bytes_Read]
      ,[PhysicalDisk_Total_Average_Disk_Queue_Length]
      ,[MSSQL_SQL2K8_Transactions_Longest_Transaction_Running_Time]
      ,[MSSQL_SQL2K8_SQL_Statistics_SQL_ReCompilations_sec]
      ,[MSSQL_SQL2K8_SQL_Statistics_SQL_Compilations_sec]
      ,[MSSQL_SQL2K8_Memory_Manager_Total_Server_Memory_KB]
      ,[MSSQL_SQL2K8_Memory_Manager_Target_Server_Memory_KB]
      ,[MSSQL_SQL2K8_General_Statistics_Logouts_sec]
      ,[MSSQL_SQL2K8_General_Statistics_Transactions]
      ,[MSSQL_SQL2K8_General_Statistics_User_Connections]
      ,[MSSQL_SQL2K8_General_Statistics_Active_Temp_Tables]
      ,[MSSQL_SQL2K8_General_Statistics_Logins_sec]
      ,[MSSQL_SQL2K8_General_Statistics_Processes_blocked]
      ,[MSSQL_SQL2K8_Databases_Total_Transactions_sec]
      ,[MSSQL_SQL2K8_Databases_Total_Data_File_Size_KB]
      ,[MSSQL_SQL2K8_Databases_Total_Backup_Restore_Throughput_sec]
      ,[MSSQL_SQL2K8_Databases_Total_Active_Transactions]
      ,[MSSQL_SQL2K8_Buffer_Manager_Buffer_cache_hit_ratio]
      ,[MSSQL_SQL2K8_Access_Methods_Page_Splits_sec]
      ,[MSSQL_SQL2K8_Access_Methods_Full_Scans_sec]
      ,[Memory_Percent_Committed_Bytes_In_Use]
      ,[Memory_Available_MBytes]
      ,[Memory_Cache_Faults_sec]
  FROM [MDW].[dbo].[PerfmonCounters]
GO

-- Specific Analysis

SELECT
  AVG(CONVERT (NUMERIC(20,10), [Processor_Total_Percent_Processor_Time])) AS 'Average'
, STDEV(CONVERT (NUMERIC(20,10), [Processor_Total_Percent_Processor_Time])) AS 'Standard_Deviation'
, MAX(CONVERT (NUMERIC(20,10), [Processor_Total_Percent_Processor_Time])) AS 'Maximum_Processor'
, MIN(CONVERT (NUMERIC(20,10), [Processor_Total_Percent_Processor_Time])) AS 'Minimum_Processor'
FROM [MDW].[dbo].[PerfmonCounters]
GO

/* End Perfmon Example.sql */

Leave a Comment
  • Please add 6 and 6 and type the answer here:
  • Post
Page 1 of 1 (1 items)