Hi Friends,
There may be the situations when you got an email about a query which was running fine till yesterday but today it is running very slow. First question which come to our mind is why it was running fine earlier but now running slow. There may be various things you will check to find out the reason. You can troubleshoot this issue much faster, if you have the query execution plan for the earlier executions and the current execution.
But we knew that the plan cache in the SQL Server contain the latest execution plan for the executed queries. So you can’t find out the execution plan for previous execution until you have some third party tool or any other mechanism created for such type of information collection.
Microsoft has announced a new feature “Query Data Store” in SQL Server 2016 CTP2. By using this Query Data Store feature you can easily find out the execution plans for previous executions of that same query. I think this is a great feature that will really help the DBA’s for troubleshooting various query performance issue.
This is a database level feature and you can enable this feature through database properties by using SQL Server Management Studio or by using TSQL also.
To enable it from Management Studio, open the SSMS > Connect to your Instance > Inside object Explorer go to the database for which you want to enable the Query Data Store feature > Right Click on that database > Click on properties > this will open up the database properties Dialog box. Here in the left hand side you can select Query Store > Now it will show you the UI looks like below. Here you can set the Enable property to TRUE. There is various other setting on that same page. These properties will automatically set to the default values at the time you enable the feature by setting true.
You can also enable this feature by using TSQL:
ALTER DATABASE [DATABASENAME] SET QUERY_STORE=ON
After enabling this feature you can see the Query store tab under the database for which you have enabled the feature.
PS: Data collected and stored by Query Store will not be flushed like Plan Cache even after the Server or Service Restart.
HAPPY LEARNING!
Regards:
Prince Kumar Rastogi
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook
