Performance Monitor is an oldie but goldie tool to analyze performance problems. In this article, we will learn how we can write Perfmon counter data to a SQL database through the ODBC connection.
Perfmon is a monitoring tool that helps to track resource usage and system performance metrics in real-time and it also allows us to write counters data into various file types. So that, we can analyze and troubleshoot the hardware and application issues. Perfmon includes various counters and these counters can be used to measure applications or system performances for the different resource types (CPU, Memory, DISK, etc.). However real-time counter monitoring is not a convenient option to figure out the performance issues because this method wastes time and does not help to figure out the big picture. For this reason, we need to collect the counter data for the long-periods and then analyze. Perfmon allows us to store counter data for the following file types:
- Binary file
- Text file (comma-separated)
- Text file (tab-separated)
Along with these file types, we can also write Perfmon counters data to the SQL database. With this option, we can query and analyze the collected counter data more easily with T-SQL. Besides, we can visualize this data with SQL Server Reporting Service (SSRS), Qlikview, or PowerBI.
To write Perfmon counters data into the SQL Server database we need to complete the following two steps.
- Create an ODBC connection
- Create a Data Collector Set
Firstly, we will look at some essential counters that can be used to troubleshoot SQL Server performance problems.
Useful Performance Monitor counters for SQL Server
Perfmon can be very helpful when we are diagnosing or troubleshooting SQL Server problems. The following counters can use to measure the performance of the SQL Server and every counter indicates different resource utilization related to the SQL Server or can indicate problems about the SQL Server
Processors: % Processor Time
This counter is used for tracking the percentage of the total processor utilization that works on non-idle threads. In general, we don’t expect this value to exceed %85. Otherwise, we should suspect a CPU problem.
System: Processor Queue Length
This counter shows the number of the threads that wait in the CPU queue and if this number is greater than two, it indicates a CPU bottleneck.
SQL Server: SQL Statistics\Batch Requests/sec
This counter shows the number of the SQL batches which are being performed per second. This counter is very useful to detect busy and unbusy times of the database server. Thus, we can schedule maintenance tasks in unbusy periods.
SQL Server: SQL Statistics\Compilations/sec
This counter shows the number of T-SQL queries compiled or re-compiled per second before they are executed. We should compare this counter value to the Batch Requests/sec metric to make the right decision. Compilations/sec should be %10 of the Batch Requests/sec or less than.
Network Interface: Bytes Total/Sec
This counter measures the total number of bytes that sent and received over the network adapter. It is a good indicator to analyze the utilization on the network adapter and this metric can be compared to the capacity of the network adapter.
PhysicalDisk: Avg. Disk sec/Transfer
This counter shows the total latency in milliseconds and it can be useful to interpret the performance of the storage systems.
PhysicalDisk: Disk Bytes/sec
This counter shows the total throughput of the disk subsystems in bytes.
Memory: Available Mbytes
This counter shows the amount of physical memory in megabytes that can be used by the operating system immediately for any application or process. On the ideal conditions, this value can be above 300MB.
SQL Server: Buffer Manager\Page Life Expectancy
This counter measures how long the data pages are kept in the buffer pool.
SQL Server: Buffer Manager\Buffer cache hit ratio
This counter measures the percentage of the pages found in the buffer pool.
SQL Server: Buffer Manager\Lazy writes/sec
This counter measures how many times the Lazy Writer process flushes dirty pages to the disk. The ideal value is zero and low values can be acceptable but if this value is greater than 20, we can consider a memory bottleneck.
Create an ODBC connection
To writing Performance Monitor counters data to the database, we require an ODBC data source. Over this connection, Perfmon will write counter data to the SQL database. At first, let’s launch the Administrative Tools.
On the Administrative Tools window, we will click the ODBC Data Sources.
On the ODBC Data Source Administrator screen, we navigate the System DSN tab and click the Add button.
After launching the Create New Data Source dialog window, we select the SQL Server driver and click the Finish button.
As a first step, on the Create a New Data Source to SQL Server window, we give a Name to the ODBC connection and set the SQL Server instance name or IP and click Next.
On this screen, we set the credentials for the database connection and click Next.
On this step, we change the default database which we want to store Performance Monitor counter data.
As a final step, we click finish and test the ODBC connection.
The created ODBC connection will be seen on the ODBC data source list.
Writing Performance Monitor data into the SQL database
After creating the ODBC data source, we launch the Perfmon to create a Data Collector Set.
We navigate to Data Collector Sets and right-click on the User Defined node and select New Collector Set.
On the new window, we give a name to the new Data Collector Set, chose the Create manually (Advanced), and click Next.
We select the Create data logs option and check the Performance counter.
We click the Add to select the counters which we want to store data to SQL Server.
We click the Add and choose the counters which we want to track.
We don’t change the Sample Interval setting and click Next.
We don’t change the default Root directory and click Next.
On the last screen, we choose the Save and close option and click Finish.
After creating the data collector it will be shown on the Performance Monitor management console. We will right-click on the created data collector and select the Properties menu.
On the data collector properties dialog, we will change the Log format option as SQL and choose the data source which we created in the previous section. Finally, hit the OK button to save the ODBC settings.
As the last step, we start the data collector and it starts to write counter data into the database.
Analyzing Performance Monitor data
After starting the data collector, Perfmon will create three tables and the counter data will be stored in these tables. These tables are:
CounterData table stores the collected data of the selected counters
CounterDetails table stores detailed information about the selected counters
DisplayToID table stores information about the data collector. The following database diagram illustrates the relationship between these 3 tables
We can use the following query to retrieve data for a particular counter.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT MachineName, CounterName, InstanceName, CounterValue, CounterDateTime, DisplayString FROM dbo.CounterDetails CDetails INNER JOIN dbo.CounterData CData ON CData.CounterID = CDetails.CounterID INNER JOIN DisplayToID DToID ON DToID.GUID = CData.GUID WHERE ObjectName = 'Processor' AND CDetails.CounterName = '% Processor Time' ORDER BY CounterDateTime; |
Also, we can use the following query to retrieve data for the maximum, minimum, and average value of counters.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT MachineName, CounterName, MIN(CounterValue) AS MinimumValue , MAX(CounterValue) AS MaximumValue , AVG(CounterValue) AS AverageValue FROM dbo.CounterDetails CDetails INNER JOIN dbo.CounterData CData ON CData.CounterID = CDetails.CounterID INNER JOIN DisplayToID DToID ON DToID.GUID = CData.GUID GROUP BY MachineName, CounterName, InstanceName , DisplayString |
As we stated, we can virtualize this data with BI reporting tools. For example, the following image illustrates straightforward SSRS reports that have been created using this data.
Conclusion
In this article, we learned how to import Performance Monitor data to the SQL database through the ODBC connection. This option is an effective method to collect performance counter data with Perfmon for long periods. At the same time, we can store our performance metrics data warehouse for our servers and analyze this data with different BI tools.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023