For an instructor lead, in-depth look at learning SQL click below.
Understanding how to analyse query performance is a critical skill in SQL server optimisation. The SQL Server Query Store feature provides you with insight on query plan choice and performance. It simplifies the performance troubleshooting, allowing you to quickly find performance differences and detect potential issues.
Understanding Query Store
The Query Store is essentially a record keeper, it collects detailed performance information of queries such as runtime statistics and query execution plans. This information is invaluable when you’re attempting to solve performance problems and optimize your SQL Server performance.
Using Query Store
First Step to using Query Store is to enable it. For that, you can use the following SQL code:
|
1 2 3 4 |
ALTER DATABASE AdventureWorks SET QUERY_STORE = ON; |
Collecting Data
Once Query Store is enabled, it starts collecting the data. You can view this data using several views, but one commonly used view is the ‘sys.query_store_run_time_stats’ view. Here is an example:
|
1 2 3 4 |
SELECT * FROM sys.query_store_run_time_stats; |
This query will provide a list of all the statistics collected by Query Store so far.
Querying Specific Data
If you want to query specific data, say, for a specific execution plan, you can do so by using the following:
|
1 2 3 4 5 |
SELECT PlanID, count_execution, avg_duration FROM sys.query_store_run_time_stats WHERE PlanID = 5; |
This query will return the PlanID, count_execution and average duration for PlanID 5.
Conclusion
As you can see, SQL Query Store offers a robust and efficient way to monitor and optimize query performance. Understanding how to utilize it can help decrease query times and increase overall SQL Server performance.