Have you had a chance to look at the Query Data Store introduced in SQL Server 2014. This is a great, out of the box, monitoring tool when it comes to query execution plans. The execution plan for a query may change over time due to different factors.
- When available memory is stressed, SQL Server will release cached query plans for general consumption.
- Indexes may be dropped
- Statistics may be out of date due to a lot of insert, update, or delete activities
- Schema changes can impact your query cache
When these kinds of events occur, without enabling the Query Data Store, the only information available to you will be the latest query plan for a specific query. By default, the query data store is not enabled for every database. You have to turn on this capability for each individual database this behavior is desired.
Once the query store is enabled it can be used for a number of things. It provides a history of query plans for any specific query. This can be helpful for identifying when a query begins to degrade in performance. It can be used to identify the top queries consuming your server resources. It can provide a count of the number of times a query is executed.
Frankly, this is just a gold mine of data for anyone managing an SQL Server database. The history of queries, plans, and runtime statistics result in detailed database monitoring from an application perspective that may be enabled with a simple flip of a switch.
Would you like to know more about the Query Data Store? Check out this MSDN article for a start.
Cheers,
Ben