How to Tell if Execution Plans are Reused

SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL
I try to watch the search phrases that point people to the blog because sometimes, you get a sense of what problems people are running into. The latest question or phrase I've seen a lot lately is along the lines of "how do you know if an execution plan is being reused." Since compiling an execution plan can be an extremely expensive operation, it's worth your time to understand how well a given plan is getting reused. If you've seen me present, I'll frequently talk about the application that had a query with an 86 table join. Recompiles on that thing were frequent and extremely costly. The only good news was, they were recompiles. If we weren't getting plan reuse it would have been an even worse system than it was. There are…
Read More

Ad Hoc Queries Don’t Reuse Execution Plans: Myth or Fact

SQL Server 2005, SQL Server 2008, TSQL
Another frequently heard story is that stored procedures get and reuse execution plans, but ad hoc queries do not. A lot of people believe this and say as much online and in the real world. Unlike my last myth, this time, I'm going to give you the DBA answer to this question. It depends. There are ad hoc queries and there are ad hoc queries. The classic ad hoc query looks like this: [crayon-5a6c74a51d528342672545/] And as ad hoc TSQL goes, that one is actually some what clean. They get a lot worse. But, in this case, each and every time the @value variable is changed, you're going to get a different execution plan. That's because the full text of the query is used to determine if the existing plan will…
Read More