Esat Erkec
Perfmon and SSRS

Importing Performance Monitor data into SQL databases

August 21, 2020 by

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.

Launch Administrative Tools

On the Administrative Tools window, we will click the ODBC Data Sources.

Open ODBC Data Sources

On the ODBC Data Source Administrator screen, we navigate the System DSN tab and click the Add button.

Creating a System DSN

After launching the Create New Data Source dialog window, we select the SQL Server driver and click the Finish button.

Creating a SQL Server data source

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.

Giving a name to the ODBC data source

On this screen, we set the credentials for the database connection and click Next.

Setting ODBC connection database credentials.

On this step, we change the default database which we want to store Performance Monitor counter data.

Changing the default database option of the ODBC connection

As a final step, we click finish and test the ODBC connection.

Test ODBC data source connection

The created ODBC connection will be seen on the ODBC data source list.

ODBC connection 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.

Launching the Performance Monitor

We navigate to Data Collector Sets and right-click on the User Defined node and select New Collector Set.

Create a new data collector set for Performance Monitor

On the new window, we give a name to the new Data Collector Set, chose the Create manually (Advanced), and click Next.

Giving a name to Data Collector Set

We select the Create data logs option and check the Performance counter.

Selecting counters data types of Performance Monitor

We click the Add to select the counters which we want to store data to SQL Server.

Adding counters to Performance Monitor

We click the Add and choose the counters which we want to track.

Selecting performance counters

We don’t change the Sample Interval setting and click Next.

Setting the sample interval

We don’t change the default Root directory and click Next.

Choosing the root directory

On the last screen, we choose the Save and close option and click Finish.

Saving the Data Collector Set

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.

Changing Data Collector Set property

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.

Set the Data Collector Set data source name

As the last step, we start the data collector and it starts to write counter data into the database.

Starting the data collector set

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

Perfmon tables

We can use the following query to retrieve data for a particular counter.

Data collector query

Also, we can use the following query to retrieve data for the maximum, minimum, and average value of counters.

Data collector result set

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.

Perfmon and SSRS

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.

Esat Erkec
168 Views