MSSQL Query Statistics

January 16, 2023 3 min read BEN ABT

MSSQL Live Query statistics are a valuable tool for software developers working with Microsoft SQL Server (MSSQL). These statistics provide information about the performance of individual queries, allowing developers to identify and troubleshoot any issues that may be impacting the efficiency of their database.

There are several types of query statistics available in MSSQL. One of the most useful is the execution plan, which shows the steps that the database engine takes to execute a query. This can be especially helpful when dealing with complex queries, as it allows developers to see exactly how the database is processing the request and identify any potential bottlenecks.

Another type of query statistic is the actual execution statistics. These statistics provide detailed information about the resources used by a query, including the amount of CPU time, the number of reads and writes to the database and the duration of the query. This information can help developers understand the performance of their queries and identify opportunities for optimization.

Query statistics can also be useful for identifying issues with index usage. If a query is performing poorly, it may be because it is not using the most appropriate indexes. By analyzing the query statistics, developers can determine if this is the case and make any necessary changes to improve the performance of their queries. In addition to helping with performance troubleshooting and optimization, query statistics can also be used for capacity planning. By analyzing the resource usage of queries over time, developers can get a sense of the demand on their database and plan for future growth.

Activate Query Statistics

To enable query statistics in Microsoft SQL Server (MSSQL), you will need to use the SET STATISTICS IO and SET STATISTICS TIME Transact-SQL commands. These commands allow you to enable or disable the display of query statistics for your current session.

SQL
 1-- Enable query statistics
 2SET STATISTICS IO ON;
 3SET STATISTICS TIME ON;
 4
 5-- Run query
 6SELECT * FROM NewsLikes;
 7
 8-- Disable query statistics
 9SET STATISTICS IO OFF;
10SET STATISTICS TIME OFF;

The SET STATISTICS IO command enables the display of input/output (IO) statistics for your query, including the number of logical reads and physical reads that are performed by the query.
The SET STATISTICS TIME command enables the display of the CPU time and elapsed time for the query.

The output in my case is:

Shell
 1(0 rows affected)
 2Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
 3Table 'NewsLikes'. Scan count 0, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
 4
 5(1 row affected)
 6
 7 SQL Server Execution Times:
 8   CPU time = 0 ms,  elapsed time = 0 ms.
 9SQL Server parse and compile time: 
10   CPU time = 0 ms, elapsed time = 0 ms.
11
12 SQL Server Execution Times:
13   CPU time = 0 ms,  elapsed time = 0 ms.
14
15Completion time: 2023-01-16T08:41:41.0467462+01:00

This shows me my queries are fine and I have good indexes :-)

In summary, query statistics are an essential tool for software developers working with MSSQL. They provide valuable information about the performance and resource usage of individual queries, allowing developers to troubleshoot and optimize their database for maximum efficiency.