The Query Store is a feature new to SQL Server that helps database administrators (DBAs) and developers find the cause of troublesome queries. It works at the database level and is compatible with both on-premise and Azure SQL databases. Query plans have long been useful in helping to detect code that needs performance tuning. The Query Store retains historical plans, so comparisons can be made over time, and you can see when a plan has changed.
The Query Store cannot be enabled for the master, msdb, or tempdb system databases. It is not an option on the model database either, so you cannot set it as a default option for new databases
Enabling Query Store
ALTER DATABASE [TSQL] SET QUERY_STORE = ON;
When you enable the Query Store, SQL Server creates a Query Store subfolder under the database node, in the Object Explorer in SSMS containing the following views:
- Regressed Queries
- Overall Resource Consumption
- Top Resource Consuming Queries
- Tracked Queries
The views can be used immediately but, depending on the workload of the database, it will take approximately one day for the Query Store to gather accurate metrics. Query Store collates metrics for:
- CPU time
- Duration
- Execution count
- Logical reads
- Logical writes
- Memory consumption
- Physical reads
No comments:
Post a Comment