Search Results for “execution plan”

Query Plan Oddities: Two Identical Missing Indexes

Last Updated December 9, 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: Transact-SQL -- How many edits do I have? --…

Partitioned Views, Aggregates, and Cool Query Plans

Last Updated December 4, 2017

Erik Darling

Execution Plans

The Max for the Minimum Paul White (obviously of course as always) has a Great Post, Brent® about Aggregates on partitioned tables Well, I’m not that smart or good looking, so I’ll have to settle for a So-So Post about this. There are actually quite a few similarities between the way a partitioned table and…

How to Get Live Query Plans with sp_BlitzWho

Last Updated November 18, 2017

Brent Ozar

Execution Plans, First Responder Kit, sp_BlitzWho, SQL Server 2016

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…

SQL Server 2017 Showplan Schema Is Available

Last Updated September 28, 2017

Erik Darling

Execution Plans, SQL Server, SQL Server 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,…

Live Query Plans and Blocking

Last Updated September 21, 2017

Erik Darling

Execution Plans, Indexing, Locking, Blocking, and Isolation Levels, SQL Server

You’d think this would be cool After all, you’ve got a query plan being executed LIVE ON NATIONAL TELEVISION! But it’s not. It’s actually one of those weird reasons why you still need other ways of looking at DMVs for issues. Demo Library Let’s take a closer look! First, we need an index. Indexes are…

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

Last Updated January 8, 2019

Brent Ozar

Execution Plans, Memory Grants

Earlier this week, I showed you two nearly identical queries and their estimated plans: One of these things is much like the other They look pretty darned identical – so how much worse can one query be? Here’s how much worse: Top 100: 51.0 seconds duration, 141.6 seconds CPU time Top 101: 160.0 seconds duration,…

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

Last Updated September 16, 2017

Brent Ozar

Execution Plans

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:…

Query Plans: Trivial Optimization vs Simple Parameterization

Last Updated June 16, 2017

Erik Darling

Execution Plans, Indexing, SQL Server

Facemaking You know when you think you know something, and the obviousness of it makes you all the more confident that you know it? That’s usually the first sign that there’s a giant gotcha waiting for you. And that’s what happened to me over the weekend. The setup I answered a question. And after I…

Why Your Biggest Query Plans Don’t Show Up in Some DMVs

Last Updated May 26, 2017

Brent Ozar

Bad Idea Jeans, Execution Plans

SQL Server has three ways to get execution plans from the plan cache: sys.dm_exec_query_plan – around since SQL Server 2005 sys.dm_exec_text_query_plan – added in 2005 SP2 sys.query_store_plan – new in SQL Server 2016 And there’s an important problem with the first one. To show it, let’s take one of my many bad ideas, building queries that take 12+…

SQL Server 2017: Interleaved Execution for MSTVFs

Last Updated May 15, 2017

Erik Darling

Execution Plans, SQL Server, SQL Server 2017

What I don’t want you to take away from this Is that I want you to start using Multi Statement Table Valued Functions all over the place. There are still problems with them. Backed by table variables Lots of hidden I/O cost Number of executions may surprise you One important current limitation (May-ish of 2017)…

Breaking: SQL Server 2016 SP1 CU2 Supports ENABLE_PARALLEL_PLAN_PREFERENCE Hint

Last Updated May 13, 2017

Erik Darling

Execution Plans, SQL Server, SQL Server 2017

Hot Diggety! After reading an excellent tip by Dmitry Pilugin about the new hint for forcing a parallel plan in SQL Server 2017, I figured I’d test it out on SQL Server 2016’s latest CU to see if it had been back ported yet. Good news! It has. Obviously. That’s the title of the blog…

Decrypting Insert Query Plans

Last Updated March 25, 2017

Erik Darling

Execution Plans, SQL Server

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…

2.4 Common Problems in Query Plans (16m)

Last Updated April 17, 2018

Brent Ozar Unlimited Team

Many performance issues boil down to just a handful of bad T-SQL patterns and engine decisions. In newer versions of SQL Server, you can spot missing indexes, implicit conversions, TempDB spills, and missing join predicates in execution plans. You’ll learn to recognize their symptoms and fix them.

You’ll need a membership to access this course. Check out my training classes.

01. A Tale of Two Plans (7m)

Last Updated June 3, 2018

Brent Ozar Unlimited Team

We look at two execution plans for the same query, talk about why they’re different, and figure out which one makes the most sense.

Some Plans Are Wider Than Others

Last Updated February 13, 2017

Brent Ozar

Execution Plans, SQL Server

Using the publicly available Stack Overflow database (the 100GB 2016/03 version), here’s an execution plan for an update statement: The Narrow Plan (click to see PasteThePlan details) 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…

Using Plan Guides to Remove OPTIMIZE FOR UNKNOWN Hints

Last Updated May 26, 2020

Brent Ozar

Execution Plans, Parameter Sniffing, SQL Server

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…

Silent Demo: The Plan Cache

Last Updated November 16, 2017

Brent Ozar

Execution Plans, sp_BlitzCache, Videos

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

PasteThePlan Update: See the Most Recently Pasted Plans

Last Updated February 13, 2017

Brent Ozar

Execution Plans, PasteThePlan.com

Just another terrifying query PasteThePlan.com lets you paste in execution plans and get a link to share them with other people. In our company chat room, we get an alert whenever a new plan comes in, and I’m not ashamed to admit that we’re geeky enough that we usually jump straight into it and start…

The Open Source Tools of Paste The Plan

Last Updated September 15, 2016

Richie Rump

PasteThePlan.com

If you couldn’t tell, we’re big fans of open source software. We like it so much that we recently released our First Responder Kit, including sp_Blitz, under the MIT license. So when we were building Paste The Plan, our free tool to share SQL Server execution plans, there was no question that we were going to use…

Question From Office Hours: SQL Handle vs. Plan Handle

Last Updated February 13, 2017

Erik Darling

Execution Plans

Great question! We recently added some columns to sp_BlitzCache to help you remove undesirable plans from the cache. Doing this will force SQL to come up with a new plan, or you know, just re-create the old plan. Because that just happens sometimes. I answered it, but I wasn’t happy with my answer. So here’s…

Search Results for “execution plan”

Hi! I’m Brent Ozar.