Reindex
-
When I run a Reindex job, the cpu usage goes to 100%. The tables are big (about 1 million rows) but there are only 5 big tables. The database itself is 85 GB. The log is growing automatically by 10%. Why does the cpu usage go to 100%?
Thank you.
-
So, 5 big tables at app. 1 million rows ea. + some other tables occupying 85 GB ? Is this right ? (85 GB data file?)
How are you doing the reindex "DBCC DBREINDEX <table>" for instance ?
What ver of SQL Server and current service pack ?
Hardware Spec's (disk size, ram, processor/s)?
OS and current service pack status ?
I'm not trying to be rude - these questions all have a bearing.
DB
The systems fine with no users loggged in. Can we keep it that way ?br> -
Just to add to the questions
- How many indexes are you rebuilding?
- What types are the columns you are indexing?
- How many columns do the indexes contain?
- James
--
James Moore
Red Gate Software Ltd -
I don't know how many cpu's you have but why shouldn't the cpu spike ? If you have one cpu I might expect this. That's why dbreindex should be considered an offline process.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
I don't have such a table/database to hand to test on but I just ran a dbcc on the largest table in my test db on a single socket dual core box. the cpu hit 90% for both cores. On a larger multicore box runnning enterprise ed I'd expect to see less imapct as the load would be spread, in fact I just did a test on a 4 way dual core box and hit 95% on one core and av 65% on the other 7 ( duration was short in all cases ).
I'd say what you see is normal.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Hi,
Have you tried using DBCC INDEXDEFRAG instead? This is not classed as an offline process, does this still use a large amount of CPU?
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk -
Going back to your original post - do you have a problem with the cpu usage ?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
... you may be already aware of this, but just want to mention that INDEXDEFRAG is not the same thing as DBREINDEX. Also, it may cause more problems than not as INDEXDEFRAG can be more intrusive, performance-wise, than DBREINDEX. You might want to look at a post on this from last week.
-
I find it very usefull when an actual link is posted. That way the date becomes much less of an issue when trying to find the said thread. Thanx in advance for posting this information.
-
Fair point. Here you go
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=341596#bm342761
-
make sure to flip your database in simple recovery mode while doing reindexing...
-
Be careful about simple recovery mode unless you can be positive no other activity could be occurring, or if you're also going to establish a new (full or differential) backup immediately after the rebuild.
Sure, DBREINDEX prevents some access (because it locks the indexes it's rebuilding) but only for the table being worked on. It doesn't actively prevent access - it just has that general effect .
Of course the alternative might be a huuuuge transaction log. so i can understand the suggestion...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply