January 23, 2012 at 10:01 am
PLATFORM: SQL Server 2005 SP
Without changing the Indexes themselves is there any way to reduce the total time it takes the Indexes in a table to rebuild (not reorganize)?
Additional:
We are limited in what we can do with SQL Server (i.e. Upgrading versions) by the accounting software that uses the DB hosted on the SQL Server box. We are looking at upgrading to SQL 2008 sometime this year however that’s not a guarantee. Because this may not happen and since I have Table Index rebuilds that take a long time to complete I thought I’d ask around here about this.
As its stands I have one table, a key transaction related table, that takes just over 90 minutes to Rebuild its fragmented indexes using %30 as the breakpoint for Rebuilding versus Reorganizing. While that is the largest table and the one requiring the most time to Rebuild its indexes it is not the only table that takes a considerable amount of time. The next largest table with regular Index Rebuilds (regular being at least once a week or more) takes just over 40 minutes to Rebuild the fragmented indexes exceeding the breakpoint. After these two there are a handful of tables that take between 20-30 minutes to Rebuild and the rest all take less than 15 minutes with most falling in the less than 5 minutes range.
The accounting system that uses the DB on SQL Server is not exactly the most efficiently written applilcation (lots of less the optimal queries are used as can be found when profiling the systems activity) and so I’m trying to keep the indexes (and stats) as up to date and defragged as possible. Any thoughts on how to improve the Index Maintenance to reduce the time it takes to complete the job would be most welcome.
Thanks IN Advance
Kindest Regards,
Just say No to Facebook!January 23, 2012 at 10:40 am
Any possibility of federating (partitioning) the tables?
Then you could rebuild indexes incrementally. That can often help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 23, 2012 at 10:43 am
What's the speed gain you get from the rebuild?
This might be a case where not doing the rebuild is your best option.
Reorg is online operation so your system stays available while you run it.
January 27, 2012 at 9:19 am
Not sure how you can reduce the time taken to rebuild the indexes. But have you configured fill_factor for those indexes such that they don't get fragmented quickly. It's always best to analyze what you can do to avoid fragmentation in the first place and hence avoid rebuilding them regularly.
Blog
http://saveadba.blogspot.com/
January 27, 2012 at 9:53 am
GSquared (1/23/2012)
Any possibility of federating (partitioning) the tables?Then you could rebuild indexes incrementally. That can often help.
Unfortunately no. If I did something like that we'd risk breaking our support contract with the company who's software uses the database. Good suggetsion though.
Thanks
Kindest Regards,
Just say No to Facebook!January 27, 2012 at 9:57 am
Ninja's_RGR'us (1/23/2012)
What's the speed gain you get from the rebuild?This might be a case where not doing the rebuild is your best option.
Reorg is online operation so your system stays available while you run it.
To be honest I don't have hard numbers . That said I do know the difference is enough that our users notice it wehn a shceduled rebuild is missed. That has happened a few times over the last few years adn each time the apps financial reports and processes slooowwwweeed down. I haven't bothered with coimparing defragged verses fragged because not defragging is known to be a problem. The table in question is heavily used and in all reality is not properly normalized and I'm sure that (the table bloat) has added to the probelm. Not only is the table bloated but its also top heavy. I don't know if thats normal SQL jargon but its the slang term I use for a table with far too many columns. This table has more the 125 columns.
Kindest Regards,
Just say No to Facebook!January 27, 2012 at 10:02 am
savethytrees (1/27/2012)
Not sure how you can reduce the time taken to rebuild the indexes. But have you configured fill_factor for those indexes such that they don't get fragmented quickly. It's always best to analyze what you can do to avoid fragmentation in the first place and hence avoid rebuilding them regularly.
I completely agree. The problem is I can't alter the existing structure without risk of voiding our support agreement with the software company that supports the financial software we use that uses this DB. While this kind of change should not pose that kind of risk it does because the software vendors support would use any change at all as justification for any problems reported and refuse to support us until we bring the DB schema back in line with their default model.
I can get away with additional objects that are passive like views (SELECT only, no updatable views) or additional tables for utility purposes. I just can’t directly change their objects.
Thanks for the suggestion.
Kindest Regards,
Just say No to Facebook!Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply