Collecting performance counters and using SQL Server to analyze the data
来源:互联网 发布:推广软件有哪些 编辑:程序博客网 时间:2024/05/12 07:59
Collecting performance counters and using SQL Server to analyze the data
Written By: Hilary Cotter -- 4/3/2009 -- 2 comments
Stay informed - get the MSSQLTips.com newsletter and win - click here
Problem
Quite frequently I find myself in situation where I need to get detailed information on performance monitor counters. For example I need to determine which processes are consuming all CPU at certain times. I find it handy to push the performance monitor counters into SQL Server where I can query it or perhaps display it in Reporting Services.
Solution
The following will explain how to select counters, how to collect data, how to load the data to SQL Server and how to query the data that has been saved.
Select Counters
I first need to collect my data.
I use TypePerf to display a list of performance monitor counters on the machine I wish to collect from. TypePerf is installed by default on all machines after Windows 2000.
I use the following query to get a list of all the counters that can be used on the machine I am monitoring. Although the counters are generally the same they may be different from machine to machine.. This is run in a DOS command window.
typeperf -q
You can return the entire list and store this list in a text file by using the following command.
typeperf -q > counterstxt
Once you have the counters from the machine you want to monitor you need to edit the list to keep only the counters you wish to collect.
I normally collect all counters for the following objects. The * after the counter group specifies that all counters should be collected. After you have the list of counters you want to collect save this as counterstxt. This is a list of counters that were available on the machine that I wanted to analyze again not all of these counters may exist on your machine that you are monitoring.
/.NET CLR Data(*)/SqlClient: (for connection pooling)/SQLServer:SSIS Pipeline/*/SQLServer:SSIS Service /*/MSFTESQLServer/*/MSOLAPServer/*/LogicalDisk(*)/*/Memory/*/Network Interface(*)/*/Paging File(*)/*/PhysicalDisk(*)/*/Process(*)/*/Processor(*)/*/Server/*/SQLServer:Access Methods/*/SQLServer:Buffer Manager/*/SQLServer:General Statistics/*/SQLServer:Latches/*/SQLServer:Locks(*)/*/SQLServer:Memory Manager/*/SQLServer:SQL Statistics/*/System/*
Collect Data
I then use logman to create my collection
logman create counter MyCollection -s %computername% -cf counterstxt
I then start the collection like this:
logman MyCollection start
Once I have collected a representative sample I stop the collection as follows:
logman MyCollection stop
By default on Vista and Windows 2008 servers, your performance monitor counters will be stored in %systemdrive%/PerfLogs/Admin and will be named after your collection name (in our case they will be called MyCollection.blg (blg is the extension for perfmon counters). On Windows 2000, 2003 and XP machines they will be stored by default in %systemdrive%/PerfLogs.
Load to SQL Server
Now that I have collected my perfmon counters I am ready to push them into SQL Server. To do this I use relog.
To use relog to input your performance monitor counters into SQL Server you must first select a database you wish to push them into and then create a system DSN to this SQL Server database (any version of SQL Server will work from SQL 2000 to SQL 2008). I use Windows Authentication as I don’t want to worry about saving the account and password in the DSN.
- Open up the Data Sources (ODBC) (In the Control Panel applet in the Administrative Tools section)
- Under "User DSN" click "Add" and select SQL Server for your driver and click "Finish"
- Give your System DSN a name – I call it "relog", and then point to a SQL Server in the drop down list or type in the server name and click "Next"
- Select Windows Authentication (ensure that your windows login has dbo rights in the database that you wish to write your performance monitor counters to). and click "Next"
- Select your database from the dropdown and click "Next"
- Click "Finish"
- Click "Test Data Source..." to test your data source
- If the test was successful click "OK" and click "OK" again and then close this applet
Now push your performance monitor counters to your SQL Server database by using the following command. ServerName is the name of the server which I collected the data on. This name will be written to the SQL Server table DisplayToID that is created and I can query on it when I want to look at my counters.
You will want to run this command in the folder that has the "blg" file that was created or you will need to specify the path too. Also, you need to make sure the filename that was created is what is used for the command.
relog MyCollection.blg -f SQL -o SQL:relog!ServerName
Analyze the Data
Now that the data has been loaded it is time to query the data.
The collection tables that are created when the data is loaded are the following:
- DisplayToID - table containing information about your collection
- CounterDetails - contains details about your perfmon counters
- CounterData - contains the actual counter data
Here is a sample query illustrating how to access your perfmon counter data. Here we are looking for context switches (Context Switches/sec). This will group the data in one minute intervals.
SELECT MachineName, CONVERT(DATETIME, CONVERT(VARCHAR(16), CounterDateTime)) as [Date], AVG(CounterValue) as Average, MIN(CounterValue) as Minimum, MAX(CounterValue) as MaximumFROM CounterDetails JOIN CounterData ON CounterData.CounterID = CounterDetails.CounterID JOIN DisplayToID ON DisplayToID.GUID = CounterData.GUIDWHERE CounterName = 'Context Switches/sec'GROUP BY MachineName, CONVERT(DATETIME, CONVERT(VARCHAR(16), CounterDateTime))
Here is a sample result set. (note the gap in time is because there were
Next Steps
- As I am interested in long term trending I am getting averages by minute. In your case it may make more sense not to aggregate.
- Take a look at these other performance counter tips
- Collecting Performance Monitor Statistics for SQL Server Agent Scheduled Jobs
- How To Collect Performance Data With TYPEPERF.EXE
- Creating SQL Server performance based reports using Excel
- Free Microsoft Tools to Help Setup and Maintain PerfMon for SQL Server
- Collecting performance counters and using SQL Server to analyze the data
- HOW TO Analyze ASP.NET Web Application Performance by Using the Performance Administration Tool
- Using the LDF File and Old Backup File to Restore the Data Base in SQL SERVER 2005
- Top 10 SQL Server Counters for Monitoring SQL Server Performance
- Key Performance Counters and their thresholds for Windows Server
- [High-Performance]Using ROW_NUMBER() to paginate your data with SQL Server 2005(数亿级海量数据瞬间闪现)
- Only Using the MDF File to Restore the Data Base in SQL SERVER 2005
- ARE YOU USING THE RIGHT SQL SERVER PERFORMANCE METRICS?
- Using the LND tool to analyze IBM Lotus Notes and Domino hangs and crashes
- SQL Server Index Data Structure and Performance Tuning
- A Hadoop data pipeline to analyze applicaction performance
- Using ROW_NUMBER() to paginate your data with SQL Server 2005 and ASP.NET
- Using Data Dictionary and Dynamic Performance Views
- Analyze ISO and TP4 communications to the Microsoft Exchange Server on the network
- SQL Server 2014 and the Data Platform
- Using MERGE in SQL Server to insert, update and delete at the same time
- Using R to read and plot the csv data
- Using the !analyze Extension
- 抓拍MSN中国办公室
- Auto generate change scripts in SQL Server Management Studio (SSMS) for tables
- 如何解决超出显示范围
- 装饰模式
- 供应西门子6ES5系列PLC 陈淑(武汉凯凯)
- Collecting performance counters and using SQL Server to analyze the data
- SQL Server Worst Practices
- NSN sprint904 总结回顾
- link error 2001错误及解决
- IE六与IE七
- Deploying Office Solutions with a Setup Project
- 性能测试工具之研究【转】
- 图片上传前的预览与检测
- 将Quartz.NET集成到 Castle中