April 29, 2010 at 8:48 am
Hi everyone,
we noticed that after the job (maintenance plan) to rebuild the indexes on a production system was run, the "CPU %Processor time" on the prod DB server almost double passing from around 15% to 30%.
12h00 later when the job (maintenance plan) to update statistics is finished, the "CPU %Processor time" return to around 15%.
I don't quite understand this behavior.
Can someone shed some ligth on this?
Regards.
Carl
April 29, 2010 at 9:30 am
I want to add that :
- Auto create statistics
- Auto update statistics
Are both at "ON" in the concerned database.
Regards.
Carl
April 29, 2010 at 11:54 am
If I understand what you said, statistics updates are taking 12 hours? That's a pretty serious problem. How big is the database in question?
"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
April 29, 2010 at 12:04 pm
Hello Grant,
Sorry I was not clear enough...
Here the sequence of things that happend:
1- The performances are pretty good : "CPU %Processor time" on the prod DB server is around 15%-20%
2- The job (maintenance plan) to rebuild the indexes is executed
3- After the precedent job (rebuild the index) is finished, "CPU %Processor time" on the prod DB server is around 30%-35%.
4- Many hours later, the job (maintenance plan) to update statistics is executed .
5- After the precedent job (update statistics) is finished, "CPU %Processor time" on the prod DB server goes back to 15%-20%.
Regards.
Carl
April 29, 2010 at 12:20 pm
Ah I get it. Performance goes down after the indexes are rebuilt and comes back up after the statistics are updated. Got it.
Well, it's hard to say precisely what the issue is. The index update can either reorg, which is defragment, or rebuild, what it says, the index. The rebuild is also going to update the statistics, but the reorg is not. It's hard to know what's happening exactly, but my first guess would be that the indexes are rebuilt, which updates the statistics, but you get a bad plan from the first execution after the stats update. Then, later, when just the stats are updated, you get a good plan from the first query to run after the update... That's pretty thin and a lot of guesswork though.
Why not simply run the stats update right after the index job?
"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
April 29, 2010 at 12:28 pm
Hello Grant,
The job is in fact a REBUILD INDEX job.
Yes we changed the schedule to have the update stats job that follow the rebuild index job.
I just wanted to know if it was a previously seen behavior or something that seem to be a bug.
Regards.
Carl
April 29, 2010 at 12:45 pm
No, not knowing more, I wouldn't call it a bug. It's probably something to do with statistics after the rebuild, but I'm just a bit surprised by it because an index rebuild results in the statistics being updated, just like a statistics update. As a matter of fact, unless you're updating your statistics with the UPDATE STATITICS WITH FULLSCAN, the index rebuild is actually better becuase it's not sampling the data. But that points in a direction, possibly. If you running the sp_updatestats command, that's a sampled update on the statistics, which will result in different stats than the full scan... maybe that's your problem. For whatever reason your queries like the stats supplied by the sampled updates rather than the full scan... that's kind of backwards from how most people get the problem, but not unheard of.
"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
April 29, 2010 at 12:50 pm
We are updating stats WITH FULL SCAN.
Regards.
Carl
April 29, 2010 at 1:14 pm
Then I'm stumped. It doesn't make any sense. To do an index rebuild, a full scan of the data is required and all new statistics based on the full scan are created. Running UPDATE STATS WITH FULL SCAN also does a full scan and rebuilds the stats. They both arrive at identical places. There shouldn't be differences in the stats or the execution plans created by them. Are you sure the index job isn't just doing a REORG?
"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
April 29, 2010 at 1:26 pm
Yes, I am sure it's not a REORG it's a REBUILD of the indexes.
We noticed that behavior two times.
Now that we have changed the schedule of these jobs, we won't see it again. Unless we would like to investigate it further and get back to inital schedule.
Regards.
Carl
April 29, 2010 at 7:37 pm
When you rebuild your indexes - do you by chance have the following setting?
STATISTICS_NORECOMPUTE = ON
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 30, 2010 at 5:22 am
Jeffrey Williams-493691 (4/29/2010)
When you rebuild your indexes - do you by chance have the following setting?STATISTICS_NORECOMPUTE = ON
Oooh, good catch. That's entirely possible.
"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
April 30, 2010 at 5:23 am
Carl B. (4/29/2010)
Yes, I am sure it's not a REORG it's a REBUILD of the indexes.We noticed that behavior two times.
Now that we have changed the schedule of these jobs, we won't see it again. Unless we would like to investigate it further and get back to inital schedule.
Regards.
Carl
No, I wouldn't recommend going back to that schedule anyway. If anything, I'd suggest you look to do targeted reorg or rebuild based on the level of fragmentation. That way indexes that are not fragmented at all, get left alone. Follow that up with a stats update and you should be good to go.
"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
May 3, 2010 at 6:57 am
Ok, thank's a lot Grant.
May 3, 2010 at 7:01 am
Hello Jeffrey,
We are using the build-in Rebuild Index task. I don't see any way to enable or disable statistics recomputation in that task.
Any way, lets say that we were using STATISTICS_NORECOMPUTE = ON, what would cause a defragmented index to be less "attractive" for the Optimizer after a rebuild compared to before the rebuild?
Regards.
Carl
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply