Operator progress changes in LQS

Cross post with http://aka.ms/sqlserverteam One of the useful features that shipped with SQL Server 2016 is  LQS (Live Query Statistics), which is also compatible with SQL Server 2014. This feature provides real-time insights into the query execution process, as data flows from one query operator to another. This is very useful namely in long running…


Memory grant related diagnostics

Cross post with http://aka.ms/sqlserverteam 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 and 2014 SP2 we released a new xEvent (query_memory_grant_usage). This fires at the end of query processing for all queries with a memory…


MSSQLTIGER and May PASS Virtual Conferences

Cross post with https://aka.ms/sqlserverteam Tiger is delivering another session on an upcoming Performance Virtual Chapter (VC) meeting. This time the topic is about “Gems to help you troubleshoot query performance”.  What are we discussing in this session? Query Store in SQL Server 2016 is one of the most relevant improvements for production DBAs. It introduces…


Presenting at #SQLBits XV

I’ll be doing a two-part session at ‪#‎SQLBITS‬ on Friday: Enhancements that will make your SQL database engine roar – Part 1 (11h-13h) Enhancements that will make your SQL database engine roar – Part 2 (14h-15h) If you’re around, and want to learn more about features and enhancements on SQL Server 2012 SP3, SQL Server…


April updates to Plan Comparison Tool

Cross post with https://aka.ms/sql_server_team 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…


Added per-operator level performance stats for Query Processing

Cross post with https://aka.ms/sqlserverteam 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…


What’s new with SSMS Plan Comparison Tool?

Cross post with https://aka.ms/sqlserverteam 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…


Addressing large memory grant requests from optimized Nested Loops

Cross post with https://aka.ms/sqlserverteam 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…


Index Usage DMV behavior updated

Cross post with https://aka.ms/sqlserverteam Up until SQL Server 2008 R2, using  index usage stats entries in sys.dm_db_index_usage_stats to make some assumptions over index design and workload patterns was common. However, in SQL Server 2012 and higher versions, entries in this DMV were reset with every index rebuild, so this was no longer a viable way…


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

Cross post with https://aka.ms/sqlserverteam My previous post was about new information added to spill warnings. Those are visible at the query plan level, but what about if you need to track for an extended period of time? While spills are something that denotes some type of inefficiency, starting with IO usage where only memory usage…