March 11, 2011 at 10:30 am
Michael Valentine Jones (3/11/2011)
Have you implemented table partitioning? With online index rebuilds of only the partitions that are fragmented, you should be able greatly reduce the amount of time needed for maintenance.Also, if you are reindexing instead of defragmenting, you should not have to update the statistics.
Actually, performing the sp_updatestats / or update statistics, may provide worse statistics for your indexes because they have already been updated "with full scan" at index rebuild time !
Gail explained it to me a long time ago, hence I disabled update statistics in my regular maintenance jobs and only perform the update stats on the instances I know that need it.
another ref : redgate (free download) ebook "sql server statistics"
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 11, 2011 at 10:39 am
Indianrock (3/11/2011)
ALZDBA (3/11/2011)
Can you elaborate on the "We seem to have solved a major IO problem last weekend so we'll see how long it runs this weekend." ?
[font="Times New Roman"]Our primary problem has been IO. High average read wait on the primary client database, and very high average write wait on tempdb. Much of this was apparently solved by turning off the host bus adaptor HBA Network cards to our Netapp disk array along with MPIO and using the "Microsoft ISCSI Initiator configuration" I'm not a network guy, but that's how it was explained to me. Previously we had lots of network errors with packets being resent.[/font]
Thank you for the feedback.
Are there typical slowdowns / app errors you receive ?
[font="Times New Roman"]I think previously most of our issues were with IO performance.[/font]
what's the pressure this instance esperiences ? (cpu bound / io bound / ram bound ) ?
[font="Times New Roman"]Sql is currently allowed to use 82GB of memory, with plan cache typically using 7.5GB As I said, IO was the problem. Monday we saw low IO and CPU maxed out for extended periods. Freeing plan cache fixed it. No major problems since.[/font]
Many single used plans ?
this time the url should work fine 😉
Can you split the maintenance so e.g. you only have to perform the bigger tables / indexes in the weekend and the smaler ones during the rest of the week ?
[font="Times New Roman"]So far no approval for weeknight maintenance, I think mainly because various batch processes run at night.[/font]
Let's hope the SAN modifications provide the needed time reduction for you maintenance jobs.
If you have partitioning, are you indexes alligned so you can perform index maintenance at partition level ?
[font="Times New Roman"] I don't believe we have that kind of partitioning, just tables and indexes split out into several filegroups and spread over about 18 data files.[/font]
That's a first start too.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 11, 2011 at 10:45 am
Actually it doesn't :w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t:
March 11, 2011 at 10:53 am
ALZDBA (3/11/2011)
Michael Valentine Jones (3/11/2011)
Have you implemented table partitioning? With online index rebuilds of only the partitions that are fragmented, you should be able greatly reduce the amount of time needed for maintenance.Also, if you are reindexing instead of defragmenting, you should not have to update the statistics.
Actually, performing the sp_updatestats / or update statistics, may provide worse statistics for your indexes because they have already been updated "with full scan" at index rebuild time !
Gail explained it to me a long time ago, hence I disabled update statistics in my regular maintenance jobs and only perform the update stats on the instances I know that need it.
another ref : redgate (free download) ebook "sql server statistics"
[font="Verdana"]Auto update stats is on, but in weekend maintenance all updates to statistics are done FULLSCAN ( i.e. 100% sample rate ). Also the job checks the currency of stats using an algorithm that takes record count and last update into account, so any stat updated by an index rebuild should not be touched in the subsequent stats job.
[/font]
March 11, 2011 at 11:09 am
Ninja's_RGR'us (3/11/2011)
Actually it doesn't :w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t:
Easy fix: the line break < br > is part of the link. Remove it from the end of the link to see the secret information 🙂
March 11, 2011 at 11:13 am
LutzM (3/11/2011)
Ninja's_RGR'us (3/11/2011)
Actually it doesn't :w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t:Easy fix: the line break < br > is part of the link. Remove it from the end of the link to see the secret information 🙂
I know, I'm laughing because he screwed up the very same link in another thread today... and he MADE SURE this one was fine :hehe:.
March 11, 2011 at 1:59 pm
Ninja's_RGR'us (3/11/2011)
LutzM (3/11/2011)
Ninja's_RGR'us (3/11/2011)
Actually it doesn't :w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t:Easy fix: the line break < br > is part of the link. Remove it from the end of the link to see the secret information 🙂
I know, I'm laughing because he screwed up the very same link in another thread today... and he MADE SURE this one was fine :hehe:.
Darn line break sneaked in :blush::blush::blush::blush::blush::blush::blush:
However I did check it with the preview, still I messed up somehow.
Now it _is_ time for weekend.;-)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply