On 19th December, we had completed the anniversary for the SQL Server Community for Delhi NCR. For this event, the agenda was as follows:
|Session Title||Speaker||Contact Details|
|Leverage SQL 2016 Query Store for PTO||Pranab Mazumdar||Twitter , Blog|
|Tips for Query Tuning and Understand execution plan patterns||Harsh Chawla||Twitter , Blog|
|Tips of optimal Query Writing||Sourabh Agarwal||Twitter , Blog|
In the first session, Pranab talked about query store a new feature for SQL 2016. The best , I could make out of the session was – now we can have run time plan captured in the tool for us. Before SQL 2014, we used to get compile time plan and to get the runtime plan, we had to run the query manually. Moreover, this feature can also be enabled on SQL Azure DBs as well.
Moreover, it’s really handy tool to enforce the best execution plan from all the execution plans already captured. Generally, we face issues after the upgrade to the newer versions i.e. our critical queries start taking longer time than than expected due to plan changes.If the backup of previous plan is not done, then it’s difficult to restore the execution time back to as normal.
Now, we can enable this tool on the test environment and replay the load of production system. It can help to capture the execution plans efficiently and help to pick up the efficient plan if there is any performance degradation on the upgraded SQL instance due to the new execution plan.
PPT and demo scripts are attached with this post – please feel free to download and try it on SQL server 2016 instances.