1. Time statistics
In this final chapter, we'll introduce some tools and commands available in SQL Server to help analyze and tune query performance.
2. SQL Server Management Studio
Most SQL Server practitioners use SQL Server Management Studio, or SSMS for short, for creating and editing queries. The tools and commands discussed in this chapter are accessed within SSMS. The SSMS graphics are from the 2017 version and may vary slightly from other releases.
3. STATISTICS TIME in SSMS
Throughout this course, we’ve heard terms like “increase the time it takes for a query to run.” In SQL Server there are many tools and commands available to quantify and measure performance. One way to measure query time is with the command STATISTICS TIME which reports the milliseconds, required to parse, compile, and execute a query.
In SSMS, if settings are set to Results to Grid, the statistics are displayed in the Messages tab to the right of the Results tab.
4. SQL Server Execution Times
The output contains several time statistics, However, we’ll only discuss CPU time and elapsed time under the heading SQL Server Execution Times.
"CPU time" is the actual time taken up by the database server processors to process the query and "elapsed time" is the total duration of the query from execution to returning the complete results to back to us.
5. Example: query 1
Let’s look at STATISTICS TIME in action. In this example, our query returns Region, Country, and Capital, where the 2017 population of a capital city is more than one million. This query uses two sub-queries in a WHERE filter condition -- first, to check for the presence of the capital city in the Cities table, and second, to return cities with populations of more than one million.
6. Example: query 1
To get the time statistics, we first need to run SET STATISTICS TIME ON before executing our query. Looking at the statistics output both CPU and elapsed time are close to 400 milliseconds.
7. Example: query 2
We can re-write the query and replace the two sub-queries in the WHERE filter condition with EXISTS and one sub-query. Because STATISTICS TIME is already on, we do not need to rerun it.
8. Example: query 2
The output shows a significant improvement in the time. The database processors spent less than one millisecond on the query and total execution time was only two milliseconds. Once we’ve completed our assessment of the time statistics, we turn it off by running SET STATISTICS TIME OFF.
9. Comparing queries
When we compare our two examples, the first query was slower than the second because of the two sub-queries in the WHERE filter condition. In the first query, each sub-query must collect all the results first before further processing whereas the second query uses EXISTS which stops searching the sub-query when there is a match -- much more efficient.
10. Elapsed time vs. CPU time
When analyzing query time statistics, the elapsed time may vary because it is sensitive to several factors, including the load on the server and network bandwidth between the database server and a query editing application like SSMS. The CPU time is less sensitive and will generally show little variation.
One issue with using the CPU time is when the server processors are running in parallel. In this case, it is possible that the CPU time is multiplied by the number of processors making it appear significantly higher than the elapsed time and therefore making it less useful for query performance tuning.
In most cases, the desired outcome is to tune a query to return the results to us as fast as possible. Therefore, the elapsed time is the best measure to use.
11. Taking an average
Because of variability, particularly in the elapsed time, it is good practice to execute a query multiple times and take an average of time measurements.
12. Let's practice!
Let's practice.