Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Collect SQL Server Performance Counters and Build Reports with SSRS


By:   |   Read Comments (4)   |   Related Tips: More > Monitoring

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

I would like to monitor some performance metrics and I don't have the budget to purchase a third party monitoring product. Is there a way to capture performance metrics and view the trends of data in a graphical format using SQL Server Reporting Services?

Solution

Third party tools are awesome for capturing performance metrics, but some small shops (and even large shops) don't budget for this type of software leaving it up to the DBA to create their own monitoring solution.

There are a few different ways to capture certain performance metrics, but in this tip we'll focus on using the sys.dm_os_performance_counters DMV and how to view this data in a more readable form using SQL Server Reporting Services graphs. Tim Ford explains this DMV in this tip, but basically it's similar to Windows Performance Monitor, or PerfMon, in that it captures live performance data. The DMV doesn't include all the counters as Performance Monitor, but it does show the SQL Server related counters. (Note that some of the counters in this DMV are of cumulative values since the last reboot.)

You can query this DMV using the following query:

SELECT * FROM sys.dm_os_performance_counters


SELECT * FROM sys.dm_os_performance_counters

As you can see from the screenshot above, this can be hard to read. Also, it only shows the current values at the time the query is executed so you don't know anything about the past and it makes it hard to see how the data fluctuates during the business day.

Collecting the SQL Server Monitoring Report Data

For the purpose of simplicity, this tip will focus on one counter, Page Life Expectancy (PLE). I'll show you how to capture data and create a graph for analysis.

First, we'll need to create a table that will store our metrics:

CREATE TABLE [dbo].[CounterCollections](
[ID] [int] IDENTITY(1,1) NOT NULL,
[object_name] [varchar](128) NOT NULL,
[counter_name] [varchar](128) NOT NULL,
[cntr_value] [bigint] NOT NULL,
[collection_datetime] [datetime] NOT NULL )

Next, we'll need to create a script that will insert our data into the table we created above:

INSERT INTO CounterCollections
SELECT object_name, counter_name, cntr_value, GETDATE() collection_datetime
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Page life expectancy'

Finally, we'll need to create a SQL Server Agent Job that will run the script above on a specified schedule:

create a SQL Agent job that will run the script

I'll run this job every 5 minutes:

run this job every 5 minutes

Creating the SQL Server Monitoring Report

While the table gathers data we can switch over to Business Intelligence Development Studio (BIDS) or the SQL Server Data Tools (SSDT) and create a SSRS Report that will display our data.

In this example, I'm using SSDT. Choose File, New Project. On the New Project dialog choose Report Server Project and name the Project:

Choose File, New Project. On the New Project dialog choose Report Server Project and name the Project

Right click Shared Data Sources and Add New Data Source:

Right click Shared Data Sources and Add New Data Source

For this example, I'll use localhost:

I'll use localhost

Next right click Shared Dataset and Add New Dataset:

Next right click Shared Dataset and Add New Dataset

This is where you will build the query that you want to use in the report. I will use the following query where the WHERE clause creates the Start and End time parameters:

This is where you will build the query that you want to use in the report

Next click Reports, Add, New Item:

click Reports, Add, New Item

Click Report and specify a name:

Click Report and specify a name

A fresh, clean report should appear. In the Report Data tab, right click Datasets and Add Dataset:

 In the Report Data tab, right click Datasets and Add Dataset

Click on the Dataset that we created earlier and click OK:

Click on the Dataset that we created earlier and click OK:

Expand Parameters and double click each parameter and set the Data type as Date/Time:

Expand Parameters and double click each parameter and set the Data type as Date/Time

From the Toolbox, click on Chart and drag it into the Design View:

From the Toolbox, click on Chart and drag it into the Design View

Choose a Line Graph:

Choose a Line Graph

Click on the Chart and you should see a Chart Data box appear. Add cntr_value to Values and collection_datetime to Category groups:

Click on the Chart and you should see a Chart Data box appear

We can now click on Preview to see the trends that relate to Page Life Expectancy:

click on Preview to see the trends that relate to Page Life Expectancy

Once you have the data showing correctly, you can format the chart to look more user friendly:

Once you have the data showing correctly, you can format the chart to look more user friendly

This was just an example using the Page Life Expectancy counter. You can create this type of report using any counter that SQL Server offers. This type of report works great as a monitoring solution or to troubleshoot certain bottlenecks within SQL Server.

Another SSRS Monitoring Report Example with Multiple Counters

Here is another report example that you can create that has multiple counters and graphs.

You can create this type of report using any counter that SQL Server offers
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, September 06, 2017 - 8:20:18 PM - Ricardo Ramirez Back To Top

 

Hi Brad,

can u please provide sample for mutiple graphs inside one report.

Is just what I need for my thesis and I can not find the development.

Thanks.

Saludos desde Chile.


Wednesday, June 14, 2017 - 2:21:11 PM - Isaiah A Back To Top

Hi Brad:

This is an excellent article. I tried it, and it worked like a dream. May God bless you and  your family.

Sincerely,

Isaiah. 


Tuesday, June 14, 2016 - 9:11:07 AM - Dorival Santos Back To Top

Hi Brad,

Could you show how you got the CPU Usage and Available Memory Counters?
I would like to test these two counters but I not found in sys.dm_os_performance_counters.

Thanks in advance!


Wednesday, May 21, 2014 - 11:52:08 AM - ashwin Back To Top

 

 

can u please provide sample for mutiple graphs inside one report.

 

Thanks.


Learn more about SQL Server tools