Monitoring Metrics

Monitoring metrics can be used to evaluate query runtime, detect performance pitfalls, and show how they can be improved. For example, they include:

1. Execution Time: The most important metrics to analyze the query performance is the execution time of the query. Execution time/Query duration is defined as the time taken by the query to return the rows from the database. We can find the query duration using the following commands:

   SET STATISTICS TIME ON
   SELECT * FROM SalesLT.Customer;
   

   

   By using STATISTICS TIME ON, we can see the parse time, compile-time, execution time, and completion time of the query.

   Parse and Compile Time: The time taken to parse and compile the query to check the syntax of the query is termed Parse and Compile time.

   Execution Time: The CPU time used by the query to fetch the data is termed Execution time.

   Completion time: The exact time at which the query returned the result is termed Completion time.

   By analyzing these times, we can get a clear picture of whether the query is performing up to the mark or not.

2. Statistics IO: IO is the major time spend accessing the memory buffers for reading operations in case of query. It provides insights into the latency and other bottlenecks for executing the query. By setting STATISTICS IO ON, we get the number of physical and logical reads performed to execute the query.

   SET STATISTICS IO ON
   SELECT * FROM SalesLT.Customer;
   

   

   Logical reads: Number of reads that were performed from the buffer cache.

   Physical reads: Number of reads that were performed from the storage device as they were not available in the cache.

   SELECT 
     p.Name, 
     Color, 
     ListPrice 
   FROM 
     SalesLT.Product p 
     INNER JOIN SalesLT.ProductCategory pc ON P.ProductCategoryID = pc.ProductCategoryID;
   

   

   As we can see above, the execution plan shows which tables were accessed, what index scans were performed for fetching the data. If joins are present it shows how these tables were merged.

   Further, we can see a more detailed analysis view of each sub-operation performed during query execution. Let us see the analysis of the index scan:

   

   As we can see above, we can get the values of the number of rows read, the actual number of batches, estimated operator cost, estimated CPU cost, estimated subtree cost, number of executions, actual rebinds. This gives us a detailed overview of the several cost involved in query execution.

3. Buffer cache: Used to reduce memory usage on the server.

4. Latency: Used to analyze the duration of queries or operations.

5. Indexes: Used to accelerate reading operations on the SQL Server.

6. Memory-optimized tables: Used to store table data in memory to make reading and writing operations run faster.

Learn More#

A Detailed Guide on SQL Query Optimization