Query Store was introduced in SQL Server 2016 and on Azure SQL DB v12 as a way to track query execution statistics. When Query Store is enabled it will save the query SQL text, the execution plan and execution stats like number of executions, elapsed time, logical reads etc. The information is persisted in the database and allows for later analysis. Query Store should be the first stop for anyone doing performance analysis on SQL Server. Developers need to know about Query Store in order to identify the problem spots early and focus on improving the application performance by improving the queries that will yield the bigger impact. DBAs need to know about Query store for similar reasons to understand the performance bottlenecks, but also to understand the actual workload executed on a server.

Read the rest of this entry »

One of the most useful performance tools I used internally at Microsoft was the WindowsXRay. I’m pleased to find that it was released the new name of Media eXperience Analyzer. The tool was internally developed by the Windows media perf team and the release info is targeted toward the media developers. But rest assured, the tool can be used for all sort of performance analysis and I had successfully used in analysis of SQL Server performance problems. Using the Media eXperience Analyzer (XA) you typically start by collecting one or more ETL traces using the platform tools like the Windows Performance Recorder (WPR). XA is a visualization tool used to inspect these ETL traces, much like the Windows Performance Analyzer.