Go Back

SQL Servers

KPIs & Performance

Current Version: 1

Release Date: 18th September 2015

Download - 3 KB

What now?



If you are using Squared Up < 2.11.11 then you'll need to manually import the dashboard via the server

Screenshot

Notes

SQL Servers Overview

1. SQLServer: Buffer Manager: Buffer cache hit ratio
The buffer cache hit ratio counter represents how often SQL Server is able to find data pages in its buffer cache when a query needs a data page. The higher this number the better, because it means SQL Server was able to get data for queries out of memory instead of reading from disk. You want this number to be as close to 100 as possible. Having this counter at 100 means that 100% of the time SQL Server has found the needed data pages in memory. A low buffer cache hit ratio could indicate a memory problem.

2. SQLServer: Buffer Manager: Page life expectancy
The page life expectancy counter measures how long pages stay in the buffer cache in seconds. The longer a page stays in memory, the more likely SQL Server will not need to read from disk to resolve a query. You should watch this counter over time to determine a baseline for what is normal in your database environment. Some say anything below 300 (or 5 minutes) means you might need additional memory.

3. SQLServer: SQL Statistics: Batch Requests/Sec
Batch Requests/Sec measures the number of batches SQL Server is receiving per second. This counter is a good indicator of how much activity is being processed by your SQL Server box. The higher the number, the more queries are being executed on your box. Like many counters, there is no single number that can be used universally to indicate your machine is too busy. Today’s machines are getting more and more powerful all the time and therefore can process more batch requests per second. You should review this counter over time to determine a baseline number for your environment.

4. SQLServer: SQL Statistics: SQL Compilations/Sec
The SQL Compilations/Sec measure the number of times SQL Server compiles an execution plan per second. Compiling an execution plan is a resource-intensive operation. Compilations/Sec should be compared with the number of Batch Requests/Sec to get an indication of whether or not complications might be hurting your performance. To do that, divide the number of batch requests by the number of compiles per second to give you a ratio of the number of batches executed per compile. Ideally you want to have one compile per every 10 batch requests.

5. SQLServer: SQL Statistics: SQL Re-Compilations/Sec
When the execution plan is invalidated due to some significant event, SQL Server will re-compile it. The Re-compilations/Sec counter measures the number of time a re-compile event was triggered per second. Re-compiles, like compiles, are expensive operations so you want to minimize the number of re-compiles. Ideally you want to keep this counter less than 10% of the number of Compilations/Sec.

6. SQLServer: General Statistics: User Connections
The user connections counter identifies the number of different users that are connected to SQL Server at the time the sample was taken. You need to watch this counter over time to understand your baseline user connection numbers. Once you have some idea of your high and low water marks during normal usage of your system, you can then look for times when this counter exceeds the high and low marks. If the value of this counter goes down and the load on the system is the same, then you might have a bottleneck that is not allowing your server to handle the normal load. Keep in mind though that this counter value might go down just because less people are using your SQL Server instance.

7. SQLServer: Locks: Lock Waits / Sec: _Total
In order for SQL Server to manage concurrent users on the system, SQL Server needs to lock resources from time to time. The lock waits per second counter tracks the number of times per second that SQL Server is not able to retain a lock right away for a resource. Ideally you don’t want any request to wait for a lock. Therefore you want to keep this counter at zero, or close to zero at all times.

8. SQLServer: Buffer Manager: Checkpoint Pages / Sec
The checkpoint pages per second counter measures the number of pages written to disk by a checkpoint operation. You should watch this counter over time to establish a baseline for your systems. Once a baseline value has been established you can watch this value to see if it is climbing. If this counter is climbing, it might mean you are running into memory pressures that are causing dirty pages to be flushed to disk more frequently than normal.

9. SQLServer – Thread count
Usually SQL Server opens system thread for each query request, but if amount of threads exceeds specified max worker threads value, SQL Server pools the worker threads. When all worker threads are active with long running queries, SQL Server may appear unresponsive until a worker thread completes and becomes available. Though not a defect, this can sometimes be undesirable. The monitor analyzes amount of free threads and notifies if the amount is low.

10. SQLServer: General Statistic: Processes Blocked (Not in dashboard, instructions below)
The processes blocked counter identifies the number of blocked processes. When one process is blocking another process, the blocked process cannot move forward with its execution plan until the resource that is causing it to wait is freed up. Ideally you don’t want to see any blocked processes. When processes are being blocked you should investigate.

http://www.databasejournal.com/features/mssql/article.php/3932406/Top-10-SQL-Server-Counters-for-Monitoring-SQL-Server-Performance.htm


If you need to have processes blocked as a Performance Graph in your dashboard you will need to setup a custom perfmon using the Process Blocked counter here. To see how to add existing counters as perfmons see this blog by Abdul Karim for Process Performance Monitoring in SCOM.

I used these preferences:
  • Rule name: Processes Blocked
  • Management Pack: <Any custom MP>
  • Rule Category: Performance Collection
  • Rule target: SQL DB Engine.
The performance counter is-
  • Object: SQLServer:General Statistics
  • Counter: Processes blocked
  • Instance: blank
  • Interval: 1 minute.
  • I did not choose optimization. Create!
Then create a new Performance Section within the Squared Up Dashboard, choose graph, scope to page scope, set metric to SQLServer:General Statistics – Processes blocked and set fit to data.
This dashboard uses the KPI plugin which you may need to download separately.
If you think I should have different/more performance counters please comment below!

Pre-requisites

Management Packs

  • Microsoft SQL Server Core Library - Microsoft SQL Server Core Library: This Management Pack is the core library for all versions of SQL Server. It defines all SQL Server base classes and relationships
  • Microsoft SQL Server Generic Presentation - This Management Pack defines common folder structure and views.

Squared Up Plugins

  • Performance 2.2
  • KPI 2.2

1006 downloads

7 comments

3 kilobytes


Category: Version 2 Dashboards

Release Date: 18th September 2015

Last Updated: 6th September 2016

Current Version: 1


Contributors

  • Craig Allardyce


Discussion

Login to join the discussion.

  1. Craig Allardyce |
  2. Is anybody else having the issue where processes blocked is not filling with data? I’m not sure if it is a counter that I have to turn on, I have the blocked sessions monitors on, or if it’s a bug I’m experiencing. I walked through the troubleshooting steps, making sure scope is correct and turning the time period to all, and still nothing shows. This is one of the more important bits of data from this dashboard so I would really like to get it working.

    Christopher Walker |
    • Hi Christopher,
      Can you tell me what version of SQL you are monitoring? The processes blocked metric works for me on SQL 2012 (http://i.imgur.com/iiOKnzQ.png). There may be an issue with the management pack for SQL 2014. Also, could you try re-downloading the dashboard and importing it again? I have updated it to include SQL 2014 instances in the page scope.

      Craig Allardyce |
      • I tried re downloading it and still nothing in that section. When I go to edit the metric, it’s not auto filling with that option (almost as if squaredup doesn’t see it as an option). Maybe I don’t have my MP’s configured properly? I followed all the instructions for all the MP’s as I imported them so I don’t think that’s the issue. I have SQL 2008/2012/2014 installed and a few monitored instances of each version.

        Christopher Walker |
  3. adding to the above the message generated when importing “This management pack cannot be imported.

    : XSD verification failed for the management pack. [Line: 2, Position: 2]
    The ‘Manifest’ element is not declared.”

    Georges Deaibess |
  4. Hi, we are not abel to import the dashboards to SCOM 2012 r2

    Georges Deaibess |
    • Hi Georges. Thanks for visiting Community Dashboards. This site contains dashboards created by users of Squared Up which is a HTML5 dashboard and web console solution for SCOM. Squared Up allows you to marry the unparalleled breadth and depth of the SCOM data collection platform with a stunning HTML5 user experience to deliver outstanding IT monitoring for your users. If you’re interested in Squared Up, head over to http://www.squaredup.com to find out more, try our demo lab, and download a free 30 day trial. If you’ve got any other questions, feel free to let me know 🙂

      Squared Up |

Login to start a discussion.