Monitoring SQL Server

This will be the third in our blog series about monitoring. The first covered monitoring IIS and the second, monitoring ASP.NET. Please take a look at these first to get some basic information about how to monitor different Windows products. In today’s article we will focus on how to use monitoring software to visualize, troubleshoot and check Microsoft SQL Server performance counters. We will start talking a little about MS SQL in general and then we’ll dig more into describing performance counters, services and ideal values that are needed to consider when monitoring Microsoft’s SQL Server.

 

What is MS SQL Server?

MS SQL Server 2012MS SQL Server is a product offered by Microsoft that is used in database environments. Unlike MySQL, which is the Linux version of a database system, MSSQL comes only with a licensed version. This means that you will have to pay for each of your MSSQL products. MSSQL is known as a relational database management system (RDMS) whose main responsibility is to store and retrieve data that are requested by different software applications. You’ve probably heard a lot about database requests or queries that are intended to retrieve information hosted in databases. The main query languages used with MSSQL are T-SQL and ANSI SQL. Almost all products ranging from websites, software applications, games, etc. are using some sort of relational database.

Monitoring Disk Usage of an SQL Server

We will start discussing about monitoring disk usage of an SQL server. MSSQL uses Microsoft Windows operating system input/output (I/O) calls to write and read data on disks. The Server’s disks can be either local attached storage or shared storage from a NAS device. The SQL server is responsible for how and when I/O operations are made. With that being said, Disk I/O operations and Excess Paging must be monitored. It’s best to isolate disk operations caused by the SQL Server and monitor them carefully. Disk performance counters that can be monitored include:

  • Physical Disk – Avg. Disk sec/Read
  • Physical Disk – Avg. Disk sec/Write
  • Physical Disk – Disk Reads/sec
  • Physical Disk – Disk Writes/sec

Monitoring Memory and CPU Usage

Two of the most important elements that must be monitored on a SQL server are memory and CPU usage. This means that activity made by the MSSQL server that has direct impact on the hardware devices should be isolated from the rest. Microsoft uses a dynamic memory allocation system meaning that the SQL server will request extra memory from the system if needed or will release it if there are no requirements. This allocation is made by querying the OS for the available physical memory. Memory allocation can be configured manually so that a certain amount of memory is permanently reserved for the SQL server. 

In order to analyze and troubleshoot memory activity on your Microsoft SQL Server, make sure the following performance counters are added to the monitoring tool:

  • Process: Working Set – memory used by a single process
  • SQL Server: Memory Manager: Total Server Memory (KB)
  • SQL Server: Buffer Manager: Database Pages
  • SQL Server: Buffer Manager: Buffer Cache Hit Ratio – percentage of requests resolved by the information stored in the cache.

In terms of CPU usage, it all comes down to purpose of the SQL Server. A CPU is an important hardware component that has to be monitored permanently. With that being said, consider the number of CPUs and cores that will be used on the SQL server. Any high usage can indicate that the hardware equipment is not appropriate for the Server’s operation. In terms of CPU usage, the following performance counters should be monitored:Performance Counters

  • Processor: % Privileged Time – percentage of time CPU takes to execute kernel commands. This can also refer to the time spend to execute commands that are related to the SQL Server such as I/O requests.
  • Processor: %User Time – percentage of time CPU takes to execute user processes such as SQL Server
  • Processor: % Processor Time – percentage of time CPU requires to execute threads. You can also add  %Total Processor Time to check the performance of all processors

System: Processor Queue Length – this performance counter measures the number of threads that are waiting in the processor’s queue to be executed. A process can have one or multiple threads and if the CPU cannot execute requests as they are sent, they will be stacked in the queue. If threads require more CPU cycles than are available it could end in a processor bottleneck.

It is recommended that the server run a dedicated instance of the server if your SQL Server processes many calculations. By configuring MS SQL Server as such, you can rest assured that the hardware resources will be used only by the SQL instance and not shared with other intense usage processes. Bottlenecks can also indicate that the CPU is not fast enough and requests are stacked because the processor cannot execute them in the allocated time. In this case it’s best to change the hardware or add additional CPUs.

Tools to Troubleshoot MS SQL Performance

Tools to Troubleshoot MS SQL PerformanceThere are three main tools that can help you troubleshoot MS SQL performance issues:

Using the SQL Profiler trace log – this powerful utility is one of the best to use for troubleshooting MS SQL Server’s performance. SQL Profiler is used to monitor all activity running on the Server. With this tool you can analyze data before bottlenecks are made, making it easier to determine the root cause. It is also useful for troubleshooting slow queries and responses and to determine bad application configurations. If your server handles many calculations, it is suggested that the Index Tuning Wizard tool be used. The Index Tuning tools optimizes SQL queries, determines if indexes are used properly, and can also be used to increase the overall performance of the SQL server.

Blocker script output – a mechanism that once configured properly can be used to easily determine system blockings or troubleshoot performance problems. Data gathered from blocker scripts can be incorporated into monitoring software for better analysis and troubleshooting. We won’t touch on how to implement blocker scripts because this can be quite complicated and could require an entirely separate article.

SQL Server Performance Monitor log – the performance tool available with MS SQL. Using it you can monitor performance counters that are responsible for bottlenecks like memory and CPU usage, system blocking, etc. For troubleshooting the server’s performance we have to enable the performance counters. Gathering data can be done on the local SQL Server or it can be sent to the monitoring tool on a remote machine. Make sure that the following data is included in the analysis (information taken from Microsoft’s website): Paging file, Process, Processor, All SQL Server counters, Memory, Threads, Logical disk, Physical disk and System.

The perfect combination for monitoring an MS SQL server depends a lot in the purpose of the server, the hardware configuration, the number of computations and many other factors. At minimum, the following combination should be used for monitoring an SQL Server:

  • Processor – % Processor Time
  • Paging File – % Usage
  • Memory – Available Mbytes
  • System – Processor Queue Length
  • SQL Server: Buffer Manager – Page life expectancy
  • SQL Server: General Statistics – User Connections
  • SQL Server: Memory Manager – Memory Grants Pending
  • SQL Server: SQL Statistics – Batch Requests/sec
  • SQL Server: SQL Statistics – Compilations/sec
  • SQL Server: SQL Statistics – Recompilations/sec
  • Physical Disk – Avg. Disk sec/Read
  • Physical Disk – Avg. Disk sec/Write
  • Physical Disk – Disk Reads/sec
  • Physical Disk – Disk Writes/sec

Optimal values that should alert system administrators are hard to be determined. If the system is running a dedicated instance of the MS SQL Server, the overall usage can rise up to 80% because in case of bottlenecks only the SQL instance is affected. If you are using a shared system then it is recommended that the SQL processes should not consume more than 30-40% of system resources. It all comes down to the System Administrators decisions on what values are optimal, when alerts should be raised and what steps are taken to troubleshoot and fix the Server’s performance.

We hope this article will you understand more about monitoring MS SQL Server, please share your thoughts about this topic. Have a great day and stay tuned for following articles.

Dan Popescu

You can learn more about Dan Popescu by visiting him on Google+

Share →

4 Responses to How to Monitor MS SQL Server

  1. ACTSupport says:

    All the tools mentioned to measure the performance are good one. System administrators have different optimal values to raise alerts based on their resources, but normally we should ensure that it doesn’t exceed 50% at any time for to maintain the better performance..

  2. […] This article excerpt, by Dan Popescu, originally appeared here. […]

  3. […] article excerpt, by Dan Popescu, originally appeared here: http://bit.ly/1ja70CH Using the SQL Profiler trace log – this powerful utility is one of the best to use for […]

  4. […] for this guide require that a domain controller has already been configured and there is a SQL Server that can be used to host the SharePoint 2013 database. We will be installing SharePoint 2013 onto a […]

(ec2)