In SQL Server Mangement Studio there are some built-in reports, but they do not cover everything…
In SQL Server Mangement Studio there are some built-in reports, but they do not cover everything you might want to know about your server, databases, tables, indexes or statistics. In addition to the built-in reports, I will create some useful custom reports for SQL Server 2012, and my second report is the “Execution Plan re-use statistics” report. This report will show information about :
- How many single-use plans you have in cache
- How many MB the single-use plans use in the cache
- How many re-used plans you have in cache
- How many MB the re-used plans you have in cache
- The rate between single and re-used plans in cache
With this report you can investigate if you are using majority of the plan cache for single use plans. As single use plans are only used once, they use valuable plan cache memory that could have been used by multi use plans. Consider configuration of the server to “optimize for ad-hoc workload” if you have a majority of single use plans.
Download SQL Server 2012 Management Studio Report SQLServiceMonitor_ExecutionPlan_reuse.zip
I will post all my custom reports at this link : www.sqlservice.se/ssms/custom_reports.aspx
Best regards
Håkan Winther
Twitter: @h_winther