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…


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

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 was expected, but also…


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…