| Author |
Topic |
|
rongrzywacz
Yak Posting Veteran
57 Posts |
 Posted - 2002-02-13 : 11:52:21
|
| We have a web lookup application that calls a stored procedure to do a lookup of members in a table with about 600,000 members in it. The table is rebuilt weekly, It has multiple indexes on it, for the fields the users search on. Ocassionaly the stored procedure will begin to time out when searching on some fields. If I recompile the stored procedure it then works fine. Nothing changes with the structure of the table. Why do I need to recompile the SP?? Do I need to update the stats for this table?? Thanks for any Help Ron G.Ron Grzywacz |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-13 : 12:00:32
|
| Oh yeah, if you're rebuilding the table each week you absolutely should UPDATE STATISTICS on it. You should do a RECOMPILE every time you update the stats, 'cause it will most likely keep the old plan if you don't. Just add these two operations to your current rebuild job and you won't have to worry about it. |
 |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-13 : 12:01:30
|
| I would try updating the stats for the table. -Chad |
|
|
|
rongrzywacz
Yak Posting Veteran
57 Posts |
Posted - 2002-02-13 : 14:33:36
|
| I do have the update statistics option set for this DB and the STATS_DATE function for the table show the statistics as being updated for all indexes for the table? I am still confused why I have to recompile the Stored procedure after reloading the table every week. While the data does change(maybe a 5% of the data per week) these do not appear to be significant changes. Yet, if I recompile the Stored procedure it,it does definately run faster. There are also other stored procedures in the application which access this table, should I recompile them all. Is there a way to recompile all SP in my DB, Weekly (we don't have that many maybe 50 total.)We are SQLServer 7.0 Sp3Thanks for your input, RG Ron Grzywacz |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-13 : 14:52:42
|
| DBCC FREEPROCCACHE --this flushes out the procedure cachesDBCC FLUSHPROCINDB (dbid) --recompiles all SPs in the databaseYou need to grag the DB_ID() of the database, pop it into a variable, then pass the variable to FLUSHPROCINDB (you can't nest the DB_ID() function). |
|
|
|
ThreePea
Yak Posting Veteran
83 Posts |
Posted - 2002-02-13 : 15:11:50
|
quote:
DBCC FLUSHPROCINDB (dbid) --recompiles all SPs in the database
Is this an undocumented option to DBCC? It works, but I couldn't find reference to it anywhere in BOL.Jerry======================================================="The world has achieved brilliance without conscience.Ours is a world of nuclear giants and ethical infants." -- General Omar N. Bradley (1893-1981) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-13 : 15:51:26
|
| Yes, they are undocumented, I got them from:The Guru's Guide to Transact-SQL by Ken Henderson!Thanks, by the way, I haven't made a plug for Ken's book in almost 45 minutes!And yes, you should pick up a copy. The Undocumented chapter alone justifies the price.Edited by - robvolk on 02/13/2002 15:52:03 |
|
|
|
rongrzywacz
Yak Posting Veteran
57 Posts |
Posted - 2002-02-13 : 16:48:52
|
| Thanks for the help!Does it really recompile them or mark them for recompilation the next time they are run? Any downside on running this on my db on a regular basis.We do have some worktables that are dropped and rebuilt weekly and I would assume that any stored procedure that uses them would need to be recompiled. (I know they should be truncated, but I inherited this mess)Thanks Again, RG Ron Grzywacz |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-13 : 17:37:29
|
| The book says recompile, not mark for future recompile. It is used internally by sp_dboption to ensure that system settings that would affect SP execution are properly accounted for in the plan.Chad would know best, but I'm pretty sure this is safe to run whenever you feel like it. I don't believe compiling SPs takes all that long, so if you pick a normal maintenance window (after the weekly rebuild, for example) when it wouldn't affect performance, you can run these safely. |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-02-14 : 01:42:55
|
| Definately buy the book.Of course, as one word of warning for any undocumented procedure, the implementation may change or the function may be removed in the future.Hmm, the copy I had was for SQl Server 7, is there a more recent edition out Rob? (Or should it be Mr. Volk? Or maybe Robvolk? Or maybe Mr Pimp? I've never gone for the whole bulletin board thing before, so I don't know the level of formality.) I'm quite possibly too lazy to try to check at the moment. (Using dialup since the DSL I'm leeching is kind of spotty right now.)----------------------"O Theos mou! Echo ten labrida en te mou kephale!" |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-14 : 09:58:39
|
Yeah, Rob is OK, Putz is OK too, just don't call me....ah, I'm not gonna say what I don't like to be called! None of you will give me any peace.Ken has a new book out:The Guru's Guide to SQL Server Stored Procedures, XML, And HTMLPretty self-explanatory. It covers everything that didn't fit inThe Guru's Guide to Transact-SQLObviously, I recommend that you buy BOTH books  but if you only need one right now, go for The Guru's Guide to Transact-SQLIt has a higher "wow, this is wicked cool!" factor. You will read it and be glassy-eyed about SQL Server. The first book lacks nothing, and T-SQL hasn't changed much between 7.0 and 2000. |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-14 : 14:51:26
|
You Ask for it Rob , until you go away with that word in your title you have to bear the pain quote:
I'm not gonna say what I don't like to be called! None of you will give me any peace.
A Better title could be "The Guru's Guide to Transact Sql- Sql Messiah"  --------------------------------------------------------------"Happiness is not something you experience, it's something you remember." |
|
|
|
|
need to recompile Stored procedure