| Author |
Topic |
|
pdset
Constraint Violating Yak Guru
310 Posts |
 Posted - 2010-04-19 : 01:26:55
|
| HiCan I do the UPDATE STATISTICS & RECOMPILE SP's on production Servers during Business hours? If not what are the Repurcusssions.Can Anyone let me know this. |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-04-19 : 12:26:16
|
| Can you? Yes. Should you? Not if you can find a better time of day.The UPDATE STATISTICS will place a burden on the table being scanned. How much will depend on the scan sample size and table size but you'll probably notice the impact. The recompile SPs will cause a momentary increase in work performed that you might not even notice.If you can, use the SQL Agent to schedule a time during off hours to perform these tasks.=======================================A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007) |
 |
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-04-19 : 20:20:10
|
| Yeah Quite Anticipated these answers, but I have done it on THOSE DB's with little impact during the business hours and it worked out well. However, Update and Recompile scheduled through maintenance plans at later after hours date. |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-04-21 : 13:19:36
|
| Not to be completely obtuse but... if it works don't fix it. If you are not experiencing an appreciable performance burden during the day, then party on, Garth. The question becomes, for me, why you get the bad results when the maintenance plans are performing the activity. There are those that say to never use the maintenance plans when other, better options are available (Scripts, etc.). Questions:1) How are you achieving your daytime activity? Are you manually running some scripts? Manually invoking the maintenance plan?2) Are there any other activities running when the maintenance plan runs during after hours?=======================================A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-21 : 13:54:14
|
| I think the issue for me would be (and it applies to running it during the night as well) that if an Sproc takes, say, 1 time-unit to run if the plan is cached but 10 time-units to MAKE a query plan, then after triggering it to recompile the next person to use it will get a 10 time-unit wait - which might well cause a TimeOut, particularly if the site was busy at the time and LOTS of Sprocs were running in 10 time-units instead of 1 time-unit because of having to re-make their Query Plans.We've thought about running all the key Sprocs with "known appropriate parameters" immediately after an Update Stats so that they immediately get re-run (during quiet time) and don't wreck the performance of the first real user that needs to run one. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-04-21 : 15:34:58
|
| Why are you recompiling all procs? An update stats invalidates all plans that depend on the updated statistics, hence they'll recompile anyway.--Gail ShawSQL Server MVP |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-04-23 : 00:25:16
|
| I have re-scheduled the Update Statistics into Maintenance plans along with Recompile SP's; Reindex and Checkdb purpose.But the amount of time is INADEQUATE as REST TIME for the DB's transactions will run from 6 AM to 9:30PM and Backups will start at 10 PM till 12 AM and the restoration happens onto Reporting server from 2 AM till 5:30 AM.So I have to reschedule SOME of the DB's in bits of pieces during the weekends.Is this the correct ways of Splitting the DB's through MP's?Thanks for your suggestions. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-04-23 : 03:42:55
|
| Do you need to update all statistics every night? If auto-update is enabled, that's unlikely. Spend some time investigating which statistics need updating (because they cause poor query performance) and update just those.You do know that reindex updates stats on those indexes? Hence no need to manually update stats as well. As mentioned, no need to recompile either, the stats update will invalidate the plans that use the updated statistics.--Gail ShawSQL Server MVP |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-04-23 : 10:47:44
|
| Another approach that I've seen implemented is:1) Define your maintenance window2) Start your index activity in that window (Reindex, statistics, et al)3) When the window ends, remember where you currently are (which table)4) At the next day's window, pick up where you left offJust an idea...=======================================A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007) |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-04-26 : 17:51:20
|
Thanks Bustaz Kool. This is something I haven't tried. But what would be the impact of the I/O when it is on same server. Will they not hamper the Other user activities in the down the line.Just negotiating.......... |
|
|
|
|
UPDATE STATISTICS & RECOMPILE SP's