Execution Plans

RECOMPILE Hints and Execution Plan Caching

December 17, 2013

The DBA version of an XKCD classic. When you identify that parameter sniffing is a problem, you need to test whether implementing ‘recompile’ hints will help plan quality. (Not sure what parameter sniffing is? Learn from this blog post or this 50 minute free video.) You must decide: what hint or command do you use,…

Query Plans: Memory Grants and High Row Estimates

August 28, 2013

Kendra Little

Execution Plans, Memory Grants, SQL Server

SQL Server needs to have a reasonably correct estimate of how many rows your query is going to handle. If it doesn’t, things can go terribly, terribly wrong. Most often, the problem is that SQL Server under estimates its work. In this case, the query optimizer thinks it has just a little bit of work…

Introducing the SQL Server Plan Cache (and a Better sp_Blitz®)

November 19, 2012

Brent Ozar

Execution Plans, SQL Server, Videos

performance tuning, plan cache, procedure cache, sp_blitz

When you send your beautifully hand-crafted organic T-SQL statement to SQL Server, the database engine takes a moment to appreciate the poetry of your work. Before it turns your request into results, it has to build an execution plan. SQL Server considers: Which tables it needs to join together What subqueries it needs to execute…

The Art of the Execution Plan

January 5, 2012

Brent Ozar

Execution Plans

There’s beauty everywhere – even in SQL Server. I was just staring at this plan when I was struck by its beauty: Stored Procedure Part 1 Oh, sure, it’s ugly, and I recoiled in horror at first, but when you get over that reaction, there’s an amazing amount of stuff there. Someone – probably many…

Simon Sabin’s talk on query performance

November 21, 2009

Brent Ozar

Execution Plans

At #SQLbits, Simon Sabin (Twitter) talked about car crash queries: queries that suddenly have bad performance out of nowhere, yet if you copy/paste them into SQL Server Management Studio, they run quickly. The culprit is often parameter sniffing: the engine looks at the query and builds a plan, but if the parameters in the WHERE…

Book Review: SQL Server 2008 Query Performance Tuning Distilled

April 20, 2009

Brent Ozar

Book Reviews, Execution Plans

My gauge of an amazing book is simple: if I’ve got a question, and I reach for the book BEFORE I search the web, then it’s an amazing book. Several times in the last two weeks, I reached for this book first.

Getting Help With A Slow Query

March 22, 2009

Brent Ozar

Execution Plans

StackOverflow users often ask, “I’ve got this query that runs slow – can you help?” Here’s a few tips to get better, faster answers, including getting the execution plan and relevant Perfmon data.