Execution Plans

SQL Server 2017 CU3 adds tempdb spill diagnostics in DMVs and Extended Events

Where have you been all my life? Whenever I’m teaching people about query tuning, two things come up almost without fail: parameter sniffing and tempdb spills. They’re not mutually exclusive — some queries will seemingly always spill to tempdb, whether parameter sniffing is present of not, and some queries experiencing issues with parameter sniffing will never spill…
Read More

SQL Server 2017 CU3 add a new CXCONSUMER wait type

SQL Server 2017’s new CXCONSUMER wait type was originally announced by Microsoft’s Pedro Lopes, and now it’s out. Here’s what it means for performance tuners. According to Pedro, this wait is the “safe” type of parallelism wait, as opposed to the CXPACKET wait type, which means work isn’t evenly balanced across all of our cores. Pedro blogged…
Read More

Query Plan Oddities: Two Identical Missing Indexes

Execution Plans
1 Comment
As I’ve been building labs for my Mastering Query Tuning class, I’ve been spelunking through data.stackexchange.com. It’s where anybody can write queries against the Stack Overflow databases, and share them with friends. For example, Daniel Vandersluis wrote a query to see how many edits he has: Transact-SQL -- How many edits do I have? --…
Read More
Pocket Square

sp_BlitzQueryStore: A Gentle Introduction

Odds and ends During the precon, we asked how many people were on 2016, how many people were aware of query store, how many people were using it, and how many people were using sp_BlitzQueryStore. About 1/3 of the hands went up at first, then for each successive question, fewer and fewer hands stayed up.…
Read More

Implied Predicate and Partition Elimination

Execution Plans
10 Comments
>implying Way back when, I posted about turning the Votes table in the Stack Overflow database into a Partitioned View. While working on related demos recently, I came across something kind of cool. It works for both partitioned tables and views, assuming you’ve done some things right. In this example, both versions of the table…
Read More

Microsoft’s Query Tuning Announcements from #PASSsummit

Execution Plans
3 Comments
Microsoft’s Joe Sack & Pedro Lopes held a forward-looking session for performance tuners at the PASS Summit and dropped some awesome bombshells. Pedro’s Big Deal: there’s a new CXPACKET wait in town: CXCONSUMER. In the past, when queries went parallel, we couldn’t differentiate harmless waits incurred by the consumer thread (coordinator, or teacher from my…
Read More

Adaptive Blog Posts

It turns out I can be dumb In a previous blog post about Adaptive Joins, I thought that EXISTS wasn’t supported by the new feature. It turns out that EXISTS is totally 100% supported, as long as your indexes support EXISTS. To show this, I need to show you a query that gets an Adaptive…
Read More

Why Columnstore Indexes May Still Do Key Lookups

I was a bit surprised that key lookups were a possibility with ColumnStore indexes, since “keys” aren’t really their strong point, but since we’re now able to have both clustered ColumnStore indexes alongside row store nonclustered indexes AND nonclustered ColumnStore indexes on tables with row store clustered indexes, this kind of stuff should get a…
Read More