Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Why is SQL Server no re-using Query Plan
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Aged Yak Warrior

596 Posts

Posted - 01/20/2014 :  12:10:44  Show Profile  Reply with Quote
I have a query that is a little complex from another Department but let me simplify it as Q1:

select * from Table1 where UserID = 'x'

then I run Q2:

select * from Table1 where UserID = 'y'

Q1 runs slow on a clean cache and then fast on subsequent runs. Than I try Q2 without clearing the cache and it runs slow as well on the first run and fast on subsequent runs.

I first thought that the issue was bringing data into the cache but with SET STATISTICS TIME ON, it seems that it is compile time which is taking longer. Then I checked in dm_exec_query_stats and I see SQL server made a separate plan for Q1 and Q2.

Is there a way to force SQL Server to use the same query plan for Q1 and Q2? With a stored procedure it would since the UserIDs, 'x' or 'y' would be parameters. But this department said because of the tool they are using, they can't use Stored Procedures.

Aged Yak Warrior

550 Posts

Posted - 01/20/2014 :  14:47:25  Show Profile  Reply with Quote
You need to specify the table schema:

select * from dbo.Table1 where UserID = 'x'

Assuming you've allowed SQL to autoparameterize queries, I would think it would then do so for that query.

Edited by - ScottPletcher on 01/20/2014 14:48:33
Go to Top of Page

Almighty SQL Goddess

38200 Posts

Posted - 01/20/2014 :  14:51:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
Is their tool using prepared statements or adhoc queries? If adhoc queries, you could try this option:

How big is the plan cache?

Tara Kizer
SQL Server MVP since 2007
Go to Top of Page


United Kingdom
22859 Posts

Posted - 01/25/2014 :  15:16:51  Show Profile  Reply with Quote
If the code is being generated as adhoc SQL (e.g. from Application), I've had good results by getting the DEVs to change to use sp_ExecuteSQL instead. Usually when the DEVs are bolting together the SQL with string handling it isn't difficult for them to generate strings with @Parameters in place of SQL 'string constants' (i.e. representing data in the APP).

I'm sure you know this, but sp_ExecuteSQL is happy to be given a bunch of parameters, regardless of whether they appear in the query, so adhoc code generation can just build the parameterised SQL, and then use the same everything-you-could-possibly-need parameter definition etc.

Provided that some of the adhoc SQL strings repeat their patterns then my experience has been that there is a marked improvement in performance. The more the better of course

If you need me to elaborate pls just ask.
If this isn't adhoc SQL then please ignore!

Edited by - Kristen on 01/25/2014 15:23:08
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000