Developers Choice: Query progress – anytime, anywhere

Edit (4/23/2018): due to a possible random AV running your favorite monitoring stored procedure, please make sure you install KB 4078596 in your SQL Server 2016 and SQL Server 2017. Edit (9/24/2018): this feature is now enabled by default in SQL Server 2019. Trace flag not needed anymore! Also, new query hint query_plan_profile to enable…


New Showplan XML properties in SSMS October Release

Back in March we announced the availability of per-operator level performance stats for Query Processing (exposed in Showplan XML) with SQL Server 2014 SP2 and SQL Server 2016. However, SQL Server Management Studio (SSMS) did not expose this information, and so users had to look in the Showplan XML, in the context of the appropriate…

4

Memory grant related diagnostics

Back in March I blogged about Addressing large memory grant requests from optimized Nested Loops. To further enhance discoverability of memory grant related issues, in SQL Server 2016 we released a new xEvent (query_memory_grant_usage). This fires at the end of query processing for all queries with a memory grant over 5MB, and provides insight into…


April updates to Plan Comparison Tool

With the SQL Server Management Studio April update, we released a new update for the Plan Comparison Tool. We first released this tool as part of SSMS back in October (CTP2.4), with which you can do offline comparison of execution plans, from SQL Server 2005 to the latest and greatest. Learn more about how to…


Added per-operator level performance stats for Query Processing

When you are troubleshooting query performance, metrics are never too much. With that in mind, based on customer feedback, but also our own CSS support, we have added runtime information to Showplan, and exposed a new Extended Event in SQL Server 2016 RC0. We are adding the new Showplan info and Extended Event in SQL Server…


What’s new with SSMS Plan Comparison Tool?

With the SQL Server Management Studio February update, we released a new update for the Plan Comparison Tool. In case you missed it before, we introduced this tool inside SSMS back in October (CTP2.4), with which you can do offline comparison of execution plans, from SQL Server 2005 to the latest and greatest. You can…


Addressing large memory grant requests from optimized Nested Loops

Optimized Nested Loops (or Batch Sort) is effectively an optimization aimed at minimizing I/O during a nested loop when the inner side table is large, regardless of it being parallelized or not. The presence of this optimization in a given plan may not be very obvious when you look at an execution plan, given the…


SQL Server 2016 – Added information on TempDB Spill events – Showplan

For those that have some experience analyzing query plans, you probably have come across spills to TempDB. These warnings can show up in Hash or Sort operations as a yellow exclamation mark. In fact, although both use TempDB, these are quite different in intent and also in implementation. For instance, a hashing operation that spills…


Comparison Tool released with latest SSMS

The ability to compare plans is something that a lot of people have to do for troubleshooting reasons. Maybe it’s to find why a query or batch suddenly slowed down; to understand the impact of a rewrite; or observe how a specific performance-enhancing change introduced to the design (like an index) has effectively changed the…