November 14, 2014 at 6:39 am
I have few questions which might look pretty straightforward.
1. Can we start index defragmentation job for different DBs at same time ? Will it effect anything ? Is it recommended ? (considering the target on weekends when there is no activity happening)
2. Can we make different index defragmentation jobs with different set of tables within a DB and schedule for same time ? (just to save time for while doing performance)
Same question for update stats as well ?
Strategy with above questions on environment where table partitioning being used.
November 15, 2014 at 5:34 am
sqlnaive (11/14/2014)
I have few questions which might look pretty straightforward.1. Can we start index defragmentation job for different DBs at same time ? Will it effect anything ? Is it recommended ? (considering the target on weekends when there is no activity happening)
Yes. You can.
But, remember that you're dealing with shared resources, so the system memory, the disks, any sorts necessary in tempdb, all these things will be competing for resources not only with each other, but with any other sessions connected to the system at the same time. So you can do this, but the question you have to ask is, should you? I can't answer that for you because I don't know what your systems look like. I probably wouldn't. I suspect, on most systems, you'd get better performance doing this serially, one at a time, rather than trying to multi-thread the task. But, test it.
2. Can we make different index defragmentation jobs with different set of tables within a DB and schedule for same time ? (just to save time for while doing performance)
Same question for update stats as well ?
Strategy with above questions on environment where table partitioning being used.
Same issues as above. You can launch these at the same time, yes, but you may see resource contention.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 15, 2014 at 5:37 am
Oh, and for a more sophisticated look at index & stats maintenance, I'd suggest you track down the Midnight DBA scripts for Minion. They've put a lot of thought into this process.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply