Implementing a Common MS SQL Server Performance Indicator

Implementing a Common MS SQL Server Performance Indicator
Rate this post

Introduction

There is often a need to create a performance indicator that would show database activity related to the previous period or specific day. In the article titled “Implementing SQL Server Performance Indicator for Queries, Stored Procedures, and Triggers”, we provided an example of implementing this indicator.

In this article, we are going to describe another simple way to track how and how long the query execution takes, as well as how to retrieve execution plans for each time point. 

This method is especially useful in the cases when you need to generate daily reports, so you can not only automate the method but also add it to the report with minimum technical details.

In this article, we will explore an example of implementing this common performance indicator where Total Elapsed Time will serve as a metric.

Solution

First, we provide a general algorithm:

1) Take a snapshot of active queries
2) Save the result
3) Perform a general analysis and save the result to a table at the end of the day
4) Make a comparative analysis of the data received

Now, we are going to provide some details.

To take a snapshot of active queries, create the following tables:

  1. Table of query plans
  2. Table of queries
  3. Table with snapshots of active queries:
    Code

    In a similar way, we create a table for the [srv].[RequestStatisticsArchive] archive.

  4. Table with total daily statistics

  5. View by recorded snapshots of active queries
    Code

  6. View by the selection of current active requests:
    Code

To take a snapshot of active queries and save it to the tables described above, create a stored procedure:

Example of implementing a stored procedure to collect snapshots of active queries

Code

It is possible to automate the collection process. For example, if we add a call to this stored procedure with a schedule to the Agent task (for example, every 10 seconds) or by event (for example, [Databases]. [Active Transactions]. [_ Total]> 0).

At the end of the day, we perform a general analysis and save the result to the table calling the stored procedure.

Implementation of the table

Implementation of the stored procedure

Code