Execution Plans Archives - Page 11 of 11

Performance Tuning

Are SQL Server Functions Dragging Your Query Down?

October 14, 2014

In most coding languages, functions are often-used blocks of code that can be reused from multiple locations, leading to less code – and cleaner code. SQL Server also lets us create functions that can be used the same way. They are reusable blocks of code that can be called from multiple locations. So, if you need to format phone numbers a certain way, or parse for specific characters, you can do so using a function.

Performance Tuning

Comparing Estimated and Actual Execution Plans in SQL Server

July 16, 2014

Let’s say you want to have an addition built on your house. You contact a contractor, who comes to your house, looks at your lot, looks at what exists, and asks you questions about exactly what you want. He then gives you an estimate of the work – approximately how long he thinks it will take his crew, what supplies will be needed, and the cost of those supplies.

Performance Tuning

Stabilizing Execution Plans: Plan Guides and NORECOMPUTE

July 10, 2014

Sometimes you end up in a good plan / bad plan situation: an important query runs just fine most of the time. The query is parameterized, a good execution plan gets re-used, everything is cool. But sometimes, a "bad plan" gets compiled and starts to be reused. This is "bad" parameter sniffing. "Bad plans" can come in a few…

Performance Tuning

Five Reasons a Production DBA Should Learn to Read Execution Plans

April 9, 2014

While chatting with attendees before my Don’t Fear the Execution Plan webcast, a question was posed: “As a DBA who mainly worries about backups, index fragmentation and checkdb, should I also be looking at execution plans?”

YES! Here’s why.

Performance Tuning

Finding One Problem Query With Extended Events

March 19, 2014

It’s easy to find problem queries on your SQL Server after they’ve happened. You can open your favorite monitoring tool or look in the plan cache. Those options are great for finding things after they’ve happened, but what if you want to find problems as they happen?
Server Side Trace
You could set up a SQL Server Server Side Trace. This is an acceptable solution if you’re using SQL Server 2008 or earlier. There are a few problems with this approach.

Performance Tuning

Exploring the Magic of the Plan Cache

January 28, 2014

The plan cache holds a lot of secrets about what’s going on inside SQL Server. In the First Responder Kit we shared one of our plan cache scripts to find the top resource consuming queries. That query works well, but over time we’ve added some additional functionality to the query. I figured it was time to share the new query that we’re using to analyze SQL Server performance.

Performance Tuning

RECOMPILE Hints and Execution Plan Caching

December 17, 2013

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, and where do you put it? What trade-offs are you making when it comes to being able to performance tune your SQL Server in the future?

Performance Tuning

Query Plans: Memory Grants and High Row Estimates

August 28, 2013

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…

Performance Tuning

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

November 19, 2012

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…

Performance Tuning

The Art of the Execution Plan

January 5, 2012

There's beauty everywhere - even in SQL Server. I was just staring at this plan when I was struck by its beauty: 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 someones - poured every…

Performance Tuning

Book Review: SQL Server 2008 Query Performance Tuning Distilled

April 20, 2009

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.

Performance Tuning

Getting Help With A Slow Query

March 22, 2009

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.