data-konzept

'The SQL Server query cache contains valuable performance statistics and execution plans of recently executed query workloads that can be useful to tuning your server. Many DBA's use this information on a daily basis for performance analysis and optimization. While this information is easily obtained using DMV's, a thing to note is that it gets wiped out on each SQL Server restart or partially lost when the server is under memory pressure.

Another problem is that execution plans are in XML format. That means some diagnostic queries which require content analysis of execution plans may need shredding of multiple XML files which could be quite resource intensive and sometimes not desirable to be run on production servers. So, it would be very useful to collect and analyze this information on a centralized non-production server where we could create XML indexes to facilitate more efficient XML parsing.

Once collected on a central server, we could create various reports to show top 'N' underperforming queries, average executions stats, historical trends, etc. What's more important is that this would allow us to analyze performance data at the enterprise level.

Although SQL Server 2016 has introduced the Query Store feature which allows this kind of collection and analysis, in this tip I'll show how to build a simple centralized query store with earlier versions of SQL Server.'...

https://www.mssqltips.com/sqlservertip/4264/creating-a-centralized-performance-collection-solution-for-sql-server/