Top things you need in a SQL Server performance monitoring tool (from performance metrics to wait statistic and query performance monitoring)

SQL Server is an excellent platform to use as a backend for various database applications. It offers a fast and stable basis that allows the application to run fast, increasing the end user productivity and faster turnaround for a company. Coupled with a reliable tool with a graphical interface such as SQL Server Management Studio it makes creating tables, data manipulation, functions and stored procedures development, resources optimization, etc., to be performed in no time at all. 

In the beginning, applications are created and optimized in a development environment, fine-tuned in a test environment, and that allows the application to run fast in a production… at least at the beginning.

Over the time as use of the application increases as well as the amount of data that is stored in the database, manipulation with data starts to be slower that causes that application starts to suffer from some performance degradation which inevitably leads to end-user complaints. The actions they performed without waiting now takes several seconds or even minutes, reports that are generated in a couple of minutes now takes an hour or two

And this is where various techniques for SQL Server performance monitoring and tuning come into play. SQL Server performance monitoring tools should be used not only as a tool for resolving the issues that are already arisen but also in a regular SQL Server maintenance and for supporting and troubleshooting the performance problems at their early stage or even before they arise and thus keep the applications and SQL Server running at peak performance

This article will deal with the top things that SQL Server performance monitoring tool should have to be able to help database administrator in keeping the SQL Server in a top condition:

  • Windows and SQL Server performance counters
  • Wait statistics analysis
  • Query waits analysis
  • Information about locking and blocking
  • Query performance info
  • Query execution plan analysis

One of those tools is ApexSQL Monitor. ApexSQL Monitor is and web-based SQL Server performance monitoring tool capable of tracking SQL Server and underlying Windows OS performance counters, detailed wait statistic and query wait analysis, real-time alerting, index monitoring and comprehensive reporting, to mention just a some of its features

ApexSQL Monitor is suited for real-time monitoring of Windows OS, SQL Server, and database performance metrics using over 30 counters out of the box and enhanced with the ability for adding any existing Windows OS performance counter or creating own SQL Server and database performance counters. ApexSQL Monitor allows selecting SQL Server instances, databases and hardware devices to be monitored or excluded from monitoring. It also grants a high granularity in selecting metrics to be tracked for each monitored SQL Server, database, and Windows OS. That includes the ability to fine-tune monitoring frequency per each individual metric, to specify the alert threshold values using the predefined or baseline thresholds and alert suppression ability via setting the minimum period in which value must exceed the threshold in order an alert to be raised.

ApexSQL Monitor conveys the performance metrics in real-time graphs and stores the corresponding metrics data in a central repository database for later use.

The System performance page displays Windows OS metrics of the selected instance that hosts the monitored SQL Server. The default set of performance metric consists of:

  • pages input per second
  • processor queue length
  • free space
  • utilization of processor time
  • network utilization
  • paging file usage
  • pages per second
  • average bytes per reading
  • average bytes per write
  • average disk queue length
  • current disk queue length
  • average read time
  • average write time
  • disk reads per second
  • disk writes per second
  • system availability
  • available megabytes of memory

Periods of 30 minutes, 1 hour, 12 hours, 1 day and 7 days can be displayed in the charts

The SQL Server performance page displays performance metrics on the SQL Server instance level:

  • Batch request per second
  • Compilations per second
  • Re-compilations per second
  • Longest running transaction
  • Full scans per second
  • Index searches per second
  • Forwarded records per second
  • Free list stalls per second
  • Lazy writes per second
  • Lock requests per second
  • Deadlocks per second
  • User connections
  • Target server memory
  • Total server memory
  • Memory grants pending
  • Buffer cache hit ratio
  • Page life expectancy
  • Page reads per second
  • Page writes per second
  • Page splits per second
  • SQL Server agent status
  • SQL Server availability

The Database performance page displays specific database configuration details and information, and the vital database performance metrics for the selected database:

  • Number of unused indexes
  • Status changes
  • Total database size
  • Data file size
  • Log file size
  • Log growth
  • Transactions per second

Each of the above charts regardless of the performance metric type can be enlarged to full-screen size for more detailed view

ApexSQL Monitor uses a very detailed AlwaysOn Availability Group dashboard. The application can graphically convey and display a complete Windows Server Failover Cluster (WSFC) and complete AlwaysOn infrastructure that is hosted on that WSFC. The graphically conveyed topography of AlwaysOn availability groups can be displayed not only for monitored AlwaysOn AG but the full structure of AlwaysOn AG servers that are hosted on the same WSFC, thought only the AlwaysOn replicas that are parts of the added AlwaysOn Ag can be performance monitored

Tracking the AlwaysOn specific metrics is available via AlwaysOn metrics page. For more details on performance monitoring, AlwaysOn groups check Monitoring AlwaysOn Availability Groups and Performance monitoring of AlwaysOn Availability Groups – Part 2 articles

Also, check the Performance metrics quick guide for quick insight into the available performance metrics

One of the important features of ApexSQL Monitor is its ability to track the Wait statistics of monitored SQL Server. One of the first steps in SQL Server performance analysis and troubleshooting is to check the wait statistic of the monitored SQL Server to be able to understand whether some unexpected waits occurs on the server and what wait stats types are involved in that potential performance issue.

 

The names of the wait types in the legend of the chart are listed in descending order based on accumulated wait time for each wait type, therefore Wait types with the highest wait time are listed first. The wait statistic allows configuring alerts as well as creating baselines and baseline-based alerting. By default, the essential Wait types are monitored in ApexSQL Monitor, but application allows the user to configure what wait types to monitor and what wait types to exclude from monitoring via the configuration page

Closely related to the global wait statistic is probably the most important feature that every modern performance monitoring tool must have – the ability to track the query wait statistic. End-user applications that use SQL Server as its backend communicates with SQL Server in only one way – using the queries based on T-SQL and therefore almost all performance issues that affect the application or SQL Server can be put in relation with the queries that are executing by applications. What the Query waits feature is, is a granular and most detailed interpretation of SQL Server wait statistic and this is the ultimate performance monitoring tool in every database administrator.

This is also the most closely related feature to an actual end-user experience as it will tell the database administrator directly how responsive the SQL Server is on application requests (and therefore to the end user requests) and it can provide the most detailed information about the bottlenecks and root causes of most if not all performance issues. It is sort of the surgically precise tool that will return precise information about why and what is causing the query to wait for between its execution start and end. ApexSQL Monitor additionally enhanced the ability of this tool with its availability to track and collect the wait statistic for each and every query execution issued against the SQL Server and to store the statistical data for each execution in the repository database for historical review when needed

More details about this comprehensive feature can be found in the following articles:

The part of the Query waits feature is its functionality to monitor the blocking that occurs on SQL Server, to collect and store details about blocked queries as well as about queries that caused the blocking, and to display those data on demand in the user interface. When the query acquires the lock on an SQL object, that lock causes any other query that needs to access the locked object to wait until the lock is removed, thus delaying the execution of the query that is forced to wait. The state when a query is waiting for the lock to be released is a situation that is known as a block in SQL Server.

The feature provides necessary functionality for determining what queries performed the blocking and what SQL Server objects are involved as well as all necessary details, needed for troubleshooting the blocking issue and more extensive analysis, such as wait statistic of that execution, statistical data of that query execution and relevant information about the affected SQL Server, user, application, etc.

In addition, there is information about the query that caused the blocking with a link to the full details page of that query

For a detailed guide on how to use this feature practically, read Techniques to identify blocking queries and causes of SQL Server blocks

The ApexSQL Monitor Query performance feature designed for quick and convenient insight into the slowest running queries in SQL Server. To allow better insight in the performance of potentially problematic queries, besides retrieving information about Slowest queries by average execution time, it also provides details for the Slowest queries by average CPU usage and Slowest queries by average I/O activity. Having info about slowest queries for each of those three categories allows more precise identification of the potential cause of the query slowness

Quick tip:

Information and details retrieved and displayed in this feature are retrieved from the SQL Server dynamic management views (DMV) and are not stored in the repository database. That means that information that tool retrieves are from the last SQL Server restart

For real-world examples on how to use this feature for the purpose of SQL Server performance troubleshooting and for a more detailed description of Query performance feature, please read How to identify and troubleshoot slow-running queries in SQL Server

Another feature that is a must-have in any performance monitoring tool is its ability to display the execution plan for queries executed on monitored SQL Server. ApexSQL Monitor provides this ability via the Query waits and Query performance features

In case of Query waits features, all execution plans associated with the collected queries are stored in the repository database and can be reviewed on demand via the Query plan link that is associated with collected queries in the table of the user interface

As for the Query performance feature, the application retrieves query execution plans directly from the SQL Server when the Query plan link is selected. Query plans obtained via the Query performance feature are not stored in the repository database, though there is a significant possibility that the query plan for a particular query of interest can be reviewed via the Query waits feature as it is highly likely that query is also captured by the Query waits feature

Quick tip:

It is important to understand that in case of highly active servers, once the queries are retrieved and displayed, after some time, SQL Server might flush the DMV data and thus the query execution plan of the query displayed, which can be displayed as empty in such case. Use the Refresh button to reload the slowest queries and then check for the query execution plan

The excellent companion for ApexSQL monitor users is the free ApexSQL Plan tool. It is a more comprehensive and powerful tool specialized for analyzing the query execution plans and can provide a plethora of associated data required for fine-tuning of the specific queries identified via the ApexSQL Monitor, and therefore gaining the maximum performance from them. Please check the Top things you need in a SQL Server query plan analysis tool article for better insight into what is ApexSQL Plan capable for.

More things needed in SQL Server performance monitoring tools and available in ApexSQL Monitor can be found in the Top things you need in a SQL Server performance monitoring tool (from deadlocks monitoring to alerting and index monitoring) article.

Useful resources:

All about locking in SQL Server

Troubleshoot SQL query performance using SQL Server 2016 Live Execution Statistics
How to identify slow running queries in SQL Server
SQL Server performance myth busters

 

August 6, 2018