Query Optimization and Execution Plans Archives - Page 23 of 29

Performance Tuning

Memory Grants and Data Size

February 3, 2017

What does memory do?
In SQL Server, just about everything. We cache our data and execution plans in it (along with some other system stuff), it gets used as scratch space for temporary objects, and of course queries use it for certain operations, most notably sorting and hashing. And of course, now Hekaton comes along to eat up more of our RAM.

[Video] Office Hours 2017/1/4 (With Transcriptions)

January 9, 2017

On this first Office Hours call of 2017, Brent, Erik, Tara, and Richie discuss multi-server jobs, how to find motivated Junior DBAs, working with SAP databases, partitioning, AlwaysOn Availability Groups, tracking SQL users, SQL monitoring tools, data capture, source archiving, and much more!

Here’s the video on YouTube:

Performance Tuning

Filtered Indexes and Variables: Less Doom and Gloom

January 4, 2017

It Is Known
That when you use filtered indexes, they get ignored when your queries are parameterized. This is a Plan Caching Thing©, of course. The simplest example is a bit column with a filtered index. If your index is on WHERE Bit = 1, it doesn't have data for WHERE Bit = 0. That index would only be suitable for one variation of the query, so caching a plan that uses an index which can't be reused for every variation isn't feasible.

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.

Filtered Statistics Follow-up

December 22, 2016

During our pre-con in Seattle
A really sharp lady brought up using filtered statistics, and for a good reason. She has some big tables, and with just 200 histogram steps, you can miss out on a lot of information about data distribution when you have millions or billions of rows in a table. There's simply not enough room to describe it all accurately, even with a full scan of the stats.

Ten Ways to Set MAXDOP

December 21, 2016

Whenever I work with SQL Server, I'm amazed at how many ways there are to influence its behavior. For example, take the maximum degree of parallelism for a query. Just offhand, I thought of ten different ways you can tweak it:

1. At the server level with SSMS. In SSMS, right-click on the server, click Properties, Advanced, scroll down into the Parallelism section, and set MAXDOP to 1. Click OK. (Be aware that this blows your plan cache instantly.)

Performance Tuning

Indexing Temp Tables

December 15, 2016

People often don't give this thought
Which is a shame, because I see people sticking fairly large amount of data into temp tables. On the rare occurrence that I do see them indexed, it's a nonclustered index on a column or two. The optimzer promptly ignores this index while you select 10 columns and join 10,000 rows to another temp table with another ignored nonclustered index on it.

Performance Tuning

Date Math In The WHERE Clause

December 1, 2016

Oh, THAT SARGability
I realize that not everyone has a Full Metal Pocket Protector, and that we can't all spend our days plunging the depths of query and index tuning to eek out every CPU cycle and I/O operation from our servers. I mean, I don't even do that. Most of the time I'm just happy to get the right result back!

Performance Tuning

Spills SQL Server Doesn’t Warn You About

November 30, 2016

Don't make me spill Table variables get a lot of bad press, and they deserve it. They are to query performance what the TSA is to air travel. No one's sure what they're doing, but they've been taking forever to do it. One particular thing that irks me about them (table variables, now) is that…

[Video] Office Hours 2016/11/23 (With Transcriptions)

November 28, 2016

This week, Brent and Richie discuss their hardware setups, lazy spools, SQL 2016 Service Pack 1, Always On Availability Groups, Database IDs, GroupBy.org, SQL on Linux, and new features they would like to see in the new version of SQL.

Here’s the video on YouTube:

Performance Tuning

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-6a70eaad8ed75790257008/] 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…

Performance Tuning

Using Plan Guides to Remove OPTIMIZE FOR UNKNOWN Hints

November 17, 2016

Say you've got an application that has tons of OPTIMIZE FOR UNKNOWN hints in the T-SQL, and you're getting bad query plans.

We're going to use the same StackOverflow query (and the same index on Reputation) that I demoed in the post Why Is This Query Sometimes Fast and Sometimes Slow? This technique produces a query that will produce two different execution plans depending on the Reputation parameter.

Performance Tuning

Why Is This Query Sometimes Fast and Sometimes Slow?

November 16, 2016

You swear you didn't change anything, but all of a sudden the SQL Server is going doggone slow. What happened? Parameter sniffing might be the problem, and to explain it, let's see how it works. I'm going to use the StackOverflow database - particularly, the Users table that I demo in How to Think Like the…

Performance Tuning

Top 5 Overlooked Index Features

November 10, 2016

I see a lot of indexes Most of them are pretty vanilla. Some are pretty memorable, like one that I saw with 93 INCLUDES. A lot of things that people could do with indexes get overlooked, either because they don't know about them, or they're not sure when they'll help. So, here goes my attempt…

Performance Tuning

Can Indexes My Query Doesn’t Use Help My Query?

November 9, 2016

This came up during Office Hours
And I love questions like this, because they reveal some interesting underpinnings of how the cardinality estimator works. It was something along the lines of "I have a slow query, and when I add an index the query goes faster even though it doesn't use the index I created."

Why You Should Test Your Queries Against Bigger Data

November 1, 2016

I Like Testing Things Especially Brent's patience. And I like watching people test things. SQL Server is sort of perfect for that. You make some changes, you hit f5, and you wait for something to finish. If it's faster, you ship it. Right? I mean, we all know you just added NOLOCK hints anyway. A…

Performance Tuning

Silent Demo: The Plan Cache

October 31, 2016

Shhh, and pay close attention. In 76 seconds, I'll teach you something intriguing about the way SQL Server caches execution plans:

https://www.youtube.com/watch?v=KfdfNRrIsU0

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.