Wierd Behaviour - Temp Indexes

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

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Wierd Behaviour - Temp Indexes

Author

Topic

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-12-23 : 15:27:11

We had a slow Stored Proc. We created indexes on the Temp Tables in the Stored Proc. Now its fast. But when we re-run the original stored proc (ie. without the indexes on the Temp Tables) now it too is fast.

Any insight?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-23 : 16:00:22

Sounds like an execution plan or statistics problem.

Did you grab the before and after execution plans?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-23 : 16:01:06

Your change would have caused a recompile to the stored procedure, which meant that the plan(s) in cache for it would be dropped. And then when you ran it for the first time, it created a new plan.

So you likely had a bad plan in cache.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-23 : 16:01:44

Oh and if that is the case, then you could have simply recompiled the stored procedure via sp_recompile to fix it rather than modifying the code.

DBCC FREEPROCACHE would have fixed it too, but that would have dropped all plans from cache.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-12-23 : 16:19:16

quote:
Originally posted by tkizer

Your change would have caused a recompile to the stored procedure, which meant that the plan(s) in cache for it would be dropped. And then when you ran it for the first time, it created a new plan.

So you likely had a bad plan in cache.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Thankyou, I wish I had tried the recompile first. No, I did not keep the execution Plans

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-23 : 16:24:19

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-12-23 : 16:25:25

quote:
Originally posted by tkizer

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Do you think it is a good practce as part of a Maintnenance Plan to Recompile all once a week or so? Or mark all for Recompilation?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-23 : 16:27:43

I don't. I think that's a bad idea.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-23 : 16:30:20

I agree with russell.

Only recompile on a case by case basis. If you find yourself recompiling the same stored procedure over and over again, then set the recompile option for that specific stored procedure. If you find yourself recompiling many stored procedures over and over again, then you've got to look into this deeper.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources