Query Analyzer VS Stored Proc 1

[roccorocks]

I have serveral stored procs that seem to be taking for ever to complete (about 50 sec). I take the same code and run the TSQL statement in query analyzer (about 1/2 sec). I have ran the estimated execution plan on both. The main thing that is different is the "Index Spool". The Stored proc shows the spool as 45% whereas the TSQL is only 2%. I have Auto Update Statistics set to TRUE. I am at a loss and any help will be appreciated (this is causing some serious application issues for a client). Also, this just started about a week ago (no service packs have been ran, nothing appears to have changed on the server).

Thanks,

TR

 

[AlexCuse]

Can you post the code for your proc?

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[roccorocks]

Sorry, but the code is confidential information that I cannot post. I assure you the code is just copied from the stored proc and pasted in query analyzer. An idea of the code is below:

SELECT Columns
FROM TableA INNER JOIN TableB ON TableA.Col1 = TableB.Col1 INNER JOIN TableC ON TableA.Col2 = TableC.Col1 LEFT OUTER JOIN TableD ON TableC.Col3 = TableD.Col1 LEFT OUTER JOIN TableE ON TableD.Col2 = TableE.Col1
WHERE TableA.Col1 = 'Value'

'Value' in the Store proc is the NVarchar variable @LC (does not matter if NVarchar or Varchar, tried it just for sanity). There is about 15 columns returned. It is a basic query.

Thanks,

Rocco

 

[AlexCuse]

Are you on SQL 2000? I think that you might benefit from using this technique, but I can't be sure without seeing your code.

Basically, you want to trick the optimizer so that it does not reconstruct the execution plan each time (based on the specific parameter passed). Have a read

http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[roccorocks]

We are on SQL Server 2005 SP2. I will read the log and let you know if that will help.

Thanks,

Rocco

 

[mrdenny]

What happens when you run the stored proc manually?

Have you tried recompiling the proc to force it to regenerate the execution plan?

When you run a procedure and then the code from the procedure you are actually using two different execution plans, even though the code appears to be the same.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[roccorocks]

What do you mean manually mrdenny? exec 'spnamehere' 'parameterhere'? I did this way and executed the sp with the right click and execute.

Yes I did recompile, did not help. I also dropped and recreated, thinking something maybe got screwed up in the system tables (this has happened before) and that did not help.

Yes I do realize that the execution plan is different, it was just that the index spool seemed bloated (compared to other stored procs that "seem" similar.

Alexcuse, that article was interesting. I actually had done something like that before, but not in the form of having a default parameter (you can declare within the body of the stored proc as well).

 

[mrdenny]

If you've dropped the procedure and recreated it they you have devinelty got clean execution plans.

Do the index scans / index seeks look the same between the execution plans?

I would probably look to index hints to force the subject.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com