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.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2005-12-01 : 04:30:04
|
Salutary lesson coming up  I ran our report of slow running SProcs the other day. We have an Sproc that is called on every page of our web site. Checked the stats for 21st - 25th November and they showed daily figures of around:Called 300,000 times per day, average elapsed time = 280msThat's a lot of seconds in a day!!So I had a look at the Sproc. Its a straightforward SELECT that checks the branding for the domain the user has arrived on, looks to see what language, catalogue, etc. etc. etc. is "most appropriate" for that domain, and then returns about 20 rows of "template content" to make the page.For a given Domain name [and one other parameter] it always returns the same stuff; for a given domain there are a choice of 2 available templates (based on the "other parameter" which in effect depends what part of the site the user is on). That's it.Clients have maybe half a dozen domains, tops. So maybe 12 possible resultsets.But the SELECT statement JOINs in a dozen or more tables, and is pretty intensive at finding the "most appropriate" template stuff.So I thought "This is daft, the templates change once in a blue moon, we should have a Caching/Warehousing Table keyed on Domain that cuts straight to the chase"So I changed the SProc to do an IF NOT EXISTS and if not to then INSERT the data into the new cache table; then/otherwise just SELECT from Cache table.I put it on the QA server the day before yesterday, ran the overnight test suite, checked our image comparison of all the tests - no differences. Good to go!Got up at 5am yesterday to roll it out - all went well.Checked the performance stats this morning - all fine - <16ms average. BUT the performance was magic on the preceding days too.Check some more ... server was rebooted on 26th November. Stats have been fine since then ...... so a sp_recompile would have done me! (Although I'm sure that the Cache is better in the long run)Now I have NO IDEA why the performance had drifted off so badly. The tables being queried are VERY unlikely to have changed, and certainly not by much. Stats. are updated every night.Maybe the first time the SProc was called (previously) it created a poor query plan [because of the cache parameters that it was given]?So ... new house rule. Slow running Sprocs get sp_recompile before any ad hoc R&D !!Kristen |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-12-01 : 04:40:59
|
| I found the SP useful especially on objects referencing to a very large table that changes abruptly via triggers (cascades during archiving)in one of our servers I scheduled it once a month and this has improved performance compared before--------------------keeping it simple... |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-01 : 05:01:17
|
Thanks Jen. After writing the above I re-remembered a plan we had to autoMagically recompile when the elapsed time drops below some pre-calculated threshold.A new buzzword in fact: "Just-in-time-for-next-time"  Kristen |
|
|
|
|
|
|
|
|
Use sp_recompile before optimising