Implementing SQL Server Performance Indicator for Queries, Stored Procedures and Triggers

Preface

Sooner or later, a DB administrator would like to have a performance indicator for SQL Server queries. As we all know, running Profiler for 24 hours will lead to a considerable system load and therefore, it cannot be considered an optimal solution for databases used in the 24/7 mode.

So, how can we detect the state of SQL Server queries? How can we run trace for detected query-related problems without the human input?

In this article, I will provide an implementation of the SQL Server performance indicator for queries, stored procedures and triggers, as well as its usage for the trace run.

Solution

First of all, let’s take a look at the general approach to implementation of the performance indicator for queries, stored procedures and triggers:

  1. Creation of required tables for information collection and analysis.
  2. Creation of a view for information collection.
  3. Creation of stored procedures for information collection.
  4. Creation of a view for information output.

And now, let’s consider the implementation:

1. Creation of required tables for information collection and analysis.

1.1. For queries:

1.2. For stored procedures:

1.3. For triggers:

2. Creation of a view for information collection (here we can insert filters for getting rid of the irrelevant information (for instance, queries and procedures with replication triggers, etc).
2.1. For queries:

Here, the following system queries are used: sys.dm_exec_query_stats and sys.dm_exec_sql_text.
2.2. For stored procedures:

Here, the following system queries are used: sys.dm_exec_Procedure_stats and sys.dm_exec_sql_text.

2.3. For triggers:

Here, the following system queries are used: sys.dm_exec_trigger_stats and sys.dm_exec_sql_text.

3. Creation of stored procedures for information collection.

3.1. For queries:

3.2. For stored procedures:

3.3. For triggers:

4. Creation of a view for the information output.

4.1. For queries:

4.2. For stored procedures:

4.3. Views for triggers are created in a similar way (if required). As for me, I do not need tracing triggers, since if there are any problems with triggers, execution of stored procedures and queries will show them.

The following two parameters have vital importance for the implemented views:

  1. AvgWorkerSec — query execution time in seconds.
  2. AvgElapsedSec — wait time, or wait time+AvgWorkerSec.

As for the results of views, the following equality is important:

AvgWorkerSec=AvgElapsedSec

  1. AvgWorkerSec>AvgElapsedSec – here something loads processor heavily at the moment of query execution (as it turned out, the antivirus software scan was running; it may also be the fault of the plan paralleling).
  2. AvgWorkerSec

If AvgWorkerSec=AvgElapsedSec is adhered, the long execution time is related to the query itself and its execution time.

What is a criterion of the long query execution?

There is no absolute answer to this question. It depends on what a query does, where and how it is used, etc.

I have the following evaluation for the ad hoc queries and stored procedures:

  1. Up to 0.5 – good for stored procedures, no problems (no execution waits).
  2. Up to 0.1 – good for queries, no problems (no execution waits).
  3. 0.5 — 1.0 – bad for stored procedures, there are problems (there are no execution waits that are visible to a user, but they still exist and require solution).
  4. 0.1 — 0.5 — bad for queries, there are problems (there are no execution waits that are visible to a user, but they still exist and require solving).
  5. More than 1.0 – bad for stored procedures, there are problems (there is a high chance that there are waits that are visible to users, the problem require immediate solution).
  6. More than 0.5 – bad for queries, there are problems (there is a high chance that there are waits that are visible to users, the problem requires immediate solution).

As for the non-ad hoc queries (data upload, data load), the above evaluation is picked on an individual basis. Usually, it massively exceeds evaluations for ad hoc queries and stored procedures.

If all software works through the stored procedures, you can trace stored procedures only, since the work of queries always affects the work of stored procedures. That is why, let’s settle upon analysis of the stored procedures execution.

Let’s create a system for collecting information about the most heavy stored procedures for subsequent analysis and running of autotrace, according to the following algorithm:

1. Creation of a table for storing information:

2. Creation of a stored procedure for collecting information:

It is better to run this stored procedure immediately after collecting information about the stored procedures (we can set up a task in Agent for running it every 5-10 minutes for queries, stored procedures and triggers):

3. Running trace (every 5-10 minutes with the help of the Agent tasks, preferably right after collecting information):

The auto-trace stored procedure is implemented on an individual basis. For example:

For more information on setting trace, refer to How to: Create a Trace (Transact-SQL).

Conclusion

In this article, we considered an example of implementation of a system for collecting information about the state of a database, that does not load the system. In case of problem detection, this system runs the preset trace and saves results into a table. This approach can be extended to several servers. In this case, we need to collect information from all servers for subsequent sending of information to administrators.

It is also important to remember about deletion of old data from the used tables. It is quite sufficient to store data within a month, or two weeks.

References

Evgeniy Gribkov

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.
Evgeniy Gribkov

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.