Measure and improve SQL query performance

Some tips for writing custom database queries or stored procedures that perform well, or improving the performance of existing SQL.

This page was inspired and informed by a thread in the Tessitura Network Slack. Thanks to all the contributors to that thread.

It can be difficult to evaluate the how quick (or slow) queries are when you run them in SSMS because:

there can be a significant difference between the time to run a query (as reported as SQL Server Execution time) versus the "clock" time to return the results (as shown at the bottom right of the window)
query caching can skew results of re-run queries
general database load can affect timings, if you're writing queries on a live or actively used database (don't do this, if you can help it)

Use these directives to improve the quality and consistency of query metrics, though they may not work on RAMP deployments:

DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON

Change the final statement to `SELECT 1;` to measure just the query time without returning data. If this shows a significant difference in performance you are likely experiencing key lookups, try gradually adding back the real SELECT statements to find the one(s) that are slow. In all cases avoid returning * from tables, only return what you really need.