As developers, we often consider database and query optimisations only after deployment, or if there are performance issues.
Finding slowness in database queries could be very difficult, but SQL has tooling to help. Most of the time the slowness mainly comes down to indexing issues or bad query design.
Below are some of the before and after deployment tooling that can be used to find and improve slow queries.
Activity Monitor
SQL Server Activity Monitor is a built-in tool to monitor the real-time Activities of SQL Queries or commands running. This is an invaluable tool that all Database Administrators and developers should be familiar with when diagnosing performance issues.
To access the Activity Monitor, right-click on the instance name you have in SSMS. Click on “Activity Monitor”:
Identify the slow / longest running queries, right click and click on “Show Execution Plan”:
The execution plan will help you visually identify slow parts in your query:
TSQL Missing Index Query
You will not always know in advance which indexes to create. For these instances I use the query below to identify potential missing indexes:
Please take note that the list returned should only be seen as a recommendation, and not a license to blindly create indexes in your database.
Analyze the recommendation in detail and make sure you understand what the impact is when creating an index. Indexes speed up “selects” from your tables, but could potentially slow down the “inserts”. Quite often you will find that changing your “where” clause to rather use existing indexes will improve the performance.
Reference: Pinal Dave (https://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/)
Statistics
Statistics are quite handy while writing a new query, or when tweaking an existing query.
The SET STATISTICS TIME ON will display the execution time in the message tab. The SQL Server SET STATISTICS IO ON will display the extend which objects were queried.
A more visual way to access the statistics is to enable the “Client Statistics”:
This view will also keep track of multiple runs for easy comparison.
In Conclusion
Always plan your queries carefully in advance. Before creating a new query or modifying an existing one, ensure you understand the structure and indexes of the tables involved. Utilize SQL Server tools like “Activity Monitor” and “Statistics” to identify slow and long-running queries. Regularly manage and maintain your database indexes to keep performance optimized.