One of amazing new feature that came up with SQL 2016 release is ‘Query store’. This is going to be the game changer and most useful for SQL DBA, Support Engineers and consultant those used to rely on ‘plan cache’ (AKS ‘Procedure Cache’) to track down query statistics and execution plans for queries run in past. Plan cache is useful to know what got change after XYZ changes such as missing index, application upgrade…etc.
The biggest disadvantage with ‘planned cache’ is that it remain in memory and get flush out once SQL service restart (OR sever reboot). High memory pressure on SQL Instance also cause plan cache to flush out from memory.
Now with SQL 2016, if ‘Query store’ is enabled, execution plans are saved on disk and can be accessed even if server is rebooted. This new feature is enabled on an individual database with minimum configuration change. The saved execution plan can be accessed using GUI reports OR built in DMV queries.