Execution Plan Interpretation Archives - Page 4 of 5

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.

Performance Tuning

How Much Can One Row Change A Query Plan? Part 1

September 12, 2017

Last week, Erik showed two queries that were aaaaalmost identical, with only one extra column - and the execution plans were dramatically different. Adding just one eensy column made all the difference in the world.

Now, check out these two queries - the first asks for top 100, and the second asks for top 101:
[crayon-6a71e8fa76f34544829360/]
They produce estimated plans that seem identical (PasteThePlan), even down to the estimated costs - note that they're both 50%:

Performance Tuning

How Much Can One Column Change A Query Plan? Part 2

September 8, 2017

What happened in Part 1?
Join Elimination, naturally. Until the end. My copy of the Stack Overflow database doesn't have a single foreign key in it, anywhere.

If we go down the rabbit hole a couple steps, we end up at a very quotable place, with Rob Farley.
2. Duplicated rows

Performance Tuning

How Much Can One Column Change A Query Plan? Part 1

September 5, 2017

Beyond Key Lookups
I'm going to show you two queries, and two query plans. The two queries are only different by one selected column, but the two query plans are wildly different.

Unless I've talked to you in the last few days, these may surprise you as well.

[Video] Office Hours 2017/08/02 (With Transcriptions)

August 7, 2017

This week, Brent, Erik, and Richie discuss unit testing, tSQLt, availability groups, duplicate indexes, reporting services 2016 on availability groups, troubleshooting Entity Framework queries, joining views in tables, SQL Server caching issues, and why procedures have plans inside the cache.

Here’s the video on YouTube:

T-SQL & Development

What Do You Think About ORMs?

July 20, 2017

I was recently asked what I thought about ORMs (Entity Framework, NHibernate, Dapper, etc) while we were looking at implicit conversion warnings in execution plans.

Before I answered the question, I let them know that my answer is based on being a production DBA and the numerous performance problems I have had to troubleshoot that were caused by improper usage of an ORM.

[Video] Office Hours 2017/06/07 (With Transcriptions)

June 11, 2017

This week, Brent, Erik, and Richie discuss running SQL Server 2017 CTP on Linux, receive side scaling on servers and VMware, query tuning, nested roles vs. assigning roles to users, hierarchical data type, apps and scripts for automating restores, and learning Azure and AWS. The audio's kinda rough this week because Brent & Erik were…

Performance Tuning

Is it ever worth adding indexes to table variables?

April 20, 2017

Disclaimer
I found this totally by accident, and it even surprised me.

You can probably guess the TL;DR on this is yes, huh?
Dude, where's my table variable?
I was trying to come up with a demo for something totally different. Don't ask. Seriously. It's top secret.

Performance Tuning

Does a TempDB spill mean statistics are out of date?

April 12, 2017

When you see a yellow bang in an execution plan, and you hover over it to discover that SQL Server spilled out to TempDB, your first reaction is to wail in pain and rend your clothes.

After that, though, it seems like people always ask the same question:

Performance Tuning

Decrypting Insert Query Plans

March 30, 2017

We've all been there Staring at a simple query that has a three mile long execution plan. After the first time, you learn to always look to make sure there's not a secret view involved. After looking at query plans for modifications involving indexed views, I decided to look at some other fairly common table…

[Video] Office Hours 2017/02/22 (With Transcriptions)

February 27, 2017

This week, Erik, Tara, and Richie discuss tempdb etiquette for developers, elastic search vs full-text search, server CPU spiking issues, source control in a database project, querying transaction logs, and more.

Here’s the video on YouTube:

Performance Tuning

Some Plans Are Wider Than Others

December 29, 2016

Using the publicly available Stack Overflow database (the 100GB 2016/03 version), here's an execution plan for an update statement:

It's a pretty simple query - it just scans the Posts table looking for any post with PostTypeId = 8, and then updates a single field for the first 250 rows it finds.

Index Scans: Not Nearly As Bad As You Might Think

November 25, 2016

Using our good old buddy the StackOverflow demo database, let's look at the Users table. I want to get the first 10 users in the table ordered by Id, the primary clustered key, which is an identity field.

Here's the actual execution plan:

Performance Tuning

What’s the Difference Between Estimated and Actual Execution Plans?

November 23, 2016

I'm going to use the dbo.Users table in the StackOverflow demo database and run a pretty simple query: [crayon-6a71e8fa7af19151032579/] First, hit Control-L in SSMS and get the estimated execution plan. Here it is: Click on the plan, and hover your mouse over the various operators. You'll notice that almost all of the fields are prefixed…

My How to Think Like the Engine Class is Now Free – and Open Source

October 26, 2016

You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server. You've never had a formal database internals class, and you don't really have the patience to read a book on it - but you want a quick foundation.

MAX Data Types Do WHAT?

October 20, 2016

Users are horrible, I know
You can just never rely on them to pass in reasonable search strings. When you write stored procedures to retrieve information for them, it's really tempting to define all your string variables as MAX length. As long as the actual data type, N/VARCHAR matches, you're cool, right? Avoid that pesky implicit conversion and live another day.

T-SQL & Development

Set Statistics… Profile?

October 11, 2016

Forgotten, But Not Gone
Two of the best built-in tools to figure out if your query tuning efforts are headed in the right direction are SET STATISTICS TIME ON and SET STATISTICS IO ON. If you're really fancy, you can use SET STATISTICS TIME, IO ON. By fancy I mean lazy. By lazy I mean efficient. You get the idea. Magic.

[Video] Office Hours 2016/09/14 (With Transcriptions)

September 19, 2016

This week, Brent, Richie, and Erik discuss execution plans for table value functions, how to prepare for the Senior DBA course, source control software, generating fragmentation, backups, and the new TV shows the team is looking forward to this season.

Here’s the video on YouTube:

News & Opinion

Announcing PasteThePlan.com: An Easier Way to Share Execution Plans

September 13, 2016

Since the dawn of man, people have struggled with sharing execution plans with each other for performance tuning. Now, it's easy. First, get yourself a plan: Get the estimated execution plan by hitting control-L or clicking Query, Display Estimated Plan. Right-click on the graphical plan, and click View XML. Copy all of that. Or even…

News & Opinion

Bad Idea Jeans Week: Dynamically Generating Long Queries

August 24, 2016

As part of an experiment, I needed to build a really long query. (Don't ask.)

From another recent experiment, I know that SQL Server won't let a query return more than 65,535 columns. I set about writing a one-line query that would return 65,535. I'm a big fan of writing the simplest reproduction scripts possible - I don't want them to rely on tables if they don't have to - so we'll start a CTE like this: