Category: Execution Plans

Performance Tuning

Memory Grants: SQL Server’s Other Public Toilet

February 15, 2018

Sharing Is Caring
When everything is going well, and queries are behaving responsibly, one need hardly think about memory grants.

The problem becomes itself when queries start to over and under estimate their practical needs.
Second Hand Emotion
Queries ask for memory to do stuff. Memory is a shared resource.

Read more about Memory Grants: SQL Server’s Other Public Toilet 13 comments — Join the discussion
Performance Tuning

Functions Can Still Use Indexes, Kinda.

February 1, 2018

This sentence gets repeated a lot. You know the one: "Functions prevent the use of indexes." Well, knowing you, it's probably just your indexes. I've seen your indexes.

Functions can use indexes, but even so, that doesn't mean that they're going to perform as well as queries without functions.
Which Indexes?
In the SUPERUSER database (yeah, I know, I'm cheating on Stack Overflow), all the tables have a PK/CX on an Id column, which is an Identity.

Read more about Functions Can Still Use Indexes, Kinda. 16 comments — Join the discussion
T-SQL & Development

“But It Worked in Development!” – 3 Hard Performance Problems

January 15, 2018

You've been performance tuning queries and indexes for a few years, but lately, you've been running into problems you can't explain. Could it be RESOURCE_SEMAPHORE, THREADPOOL, or lock escalation? These problems only pop up under heavy load or concurrency, so they're very hard to detect in a development environment. In a very fast-paced session, I'll…

Read more about “But It Worked in Development!” – 3 Hard Performance Problems Be the first to comment
Performance Tuning

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

January 11, 2018

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 to tempdb.

Read more about SQL Server 2017 CU3 adds tempdb spill diagnostics in DMVs and Extended Events 4 comments — Join the discussion
Performance Tuning

SQL Server 2017 CU3 add a new CXCONSUMER wait type

January 10, 2018

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 about SQL Server 2017 CU3 add a new CXCONSUMER wait type 13 comments — Join the discussion
Performance Tuning

SQL Server 2017 CU3 adds execution statistics for scalar-valued, user-defined functions

January 9, 2018

Update
This seems to finally be fixed as of CU12 for SQL Server 2017. If you're not already patched up, head over to the link to get there.
Estimated Plans Need Not Apply
This is, of course, only available in actual plans.

All together now:

Read more about SQL Server 2017 CU3 adds execution statistics for scalar-valued, user-defined functions 4 comments — Join the discussion
Performance Tuning

Query Plan Oddities: Two Identical Missing Indexes

December 13, 2017

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:
[crayon-6a6e906b796c1324433810/]
When I run that on my unindexed copy of the Stack Overflow database, I get a missing index recommendation - no surprise there:

Read more about Query Plan Oddities: Two Identical Missing Indexes 1 comment — Join the discussion
Performance Tuning

How to Get Live Query Plans with sp_BlitzWho

October 31, 2017

sp_BlitzWho is our open source replacement for sp_who and sp_who2. It has all kinds of really neat-o outputs like how many degrees of parallelism the query is using, how much memory it's been granted, how long it's been waiting for memory grants, and much more.

If you're on SQL Server 2016 SP1 or newer, it can show you a query's live execution plan from sys.dm_exec_query_statistics_xml.

Read more about How to Get Live Query Plans with sp_BlitzWho 4 comments — Join the discussion
Performance Tuning

What Is Estimated Subtree Cost? Query Bucks. No, Really.

October 31, 2017

When you look at a query plan, SQL Server shows a tooltip with an Estimated Subtree Cost:

A long time ago in a galaxy far, far away, it meant the number of seconds it would take to run on one guy's Dell desktop. These days, it's just a set of hard-coded cost estimates around CPU & IO work requirements - it isn't really tied to time at all.

Read more about What Is Estimated Subtree Cost? Query Bucks. No, Really. 21 comments — Join the discussion
Performance Tuning

SQL Server 2017 Showplan Schema Is Available

September 28, 2017

File this under...
Things probably only I care about. But the Showplan Schema for SQL Server 2017 popped up... well, I caught it last night. I probably could have written this sooner, huh?

So what's new? Well, if I compare the 2016 SP1 schema using KDiff, a few things stand out. Some I've blogged about, and some... I have no idea what they are.

Read more about SQL Server 2017 Showplan Schema Is Available 2 comments — Join the discussion
Performance Tuning

Which sp_configure Options Clear the Plan Cache?

September 27, 2017

When you change MAXDOP and run RECONFIGURE, SQL Server clears the plan cache. (It's documented, by the way, as is the rest of the behavior I'm about to show you, but sometimes I like to learn the hard way by running experiments.) Here's the easiest way to see it - but make sure to only do this on a development server, not in production:

Read more about Which sp_configure Options Clear the Plan Cache? 6 comments — Join the discussion
Performance Tuning

Why Columnstore Indexes May Still Do Key Lookups

September 22, 2017

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 closer look.

Read more about Why Columnstore Indexes May Still Do Key Lookups 10 comments — Join the discussion