July 12, 2010 at 6:03 am
Hi there
I have a Windows 2003, MS SQL 2005 Standard Edition, 32 bit server.
Index rebuilds on a table (600mill records) take more than 4 hours to complete.
We cant upgrade to Enterprise (no budget) to make use of Partitioning.
What are the best practices for rebuilding indexes for VLDB, using Standard Edition? Is there possibly another method to make the index rebuilds a little faster?
Many thanks
A
July 25, 2010 at 1:07 am
[font="Courier New"]
Few random thoughts.
Faster tempDB potentially RAID10, more RAM.
Or take the overhead of creating a custom partition in the old fashioned way using multiple tables and a view with a bunch UNION ALL tables.
Index rebuilds on these large tables will only help if you have massive scans on the data. If the usage pattern is only fetch small amount of data using seeks then index rebuilding will NOT help that much. Update Stats regularly and you should be fine.
Which build are you on SQL Server 2005? If you are on SP3 and above you should look at using LOCK PAGES IN MEMORY setting using the Trace flag.
[/font]
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
July 26, 2010 at 11:22 am
Yo may also want to look at why you are rebuilding the indexes. Is this a batch that just rebuilds them blindly on a set schedule? You may want to change the job to look at the index fragmentation level and make a decision to rebuild or not rebuild an index based on that value. If an index is not very fragmented, it doesn't need to be rebuilt, which will result in a faster job completion.
July 26, 2010 at 6:34 pm
Instead of one reindex job, can you create multiple reindex jobs, so to run them in parallel?
July 26, 2010 at 8:26 pm
What kinds of indexes are on this table?
How many indexes are you rebuilding for this table?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 27, 2010 at 5:32 am
Hi All
Thanks for all your replies.
All large tables' fragmentation level is between 60 and 90%. Im rebuilding all clustered indexes for all large tables which are heavily fragemented
Thanks
A
July 27, 2010 at 8:51 am
I would look into doing the rebuilds more frequently and more targeted.
I would rebuild certain tables on certain nights and have a rebuild job running nightly.
Example:
TableA - monday night
TableB - tuesday night
and so on, depending on the number of tables and needs. The tables could be broken up into groups and each group would have a specific night for the reindex job.
Another option is to rebuild indexes based on filegroup - if you have multiple filegroups.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 27, 2010 at 9:03 am
I would also look at perhaps changing the fillfactor a bit to leave more free space, and perhaps limit page splits.
July 27, 2010 at 9:30 am
CirquedeSQLeil (7/27/2010)
I would look into doing the rebuilds more frequently and more targeted.I would rebuild certain tables on certain nights and have a rebuild job running nightly.
Example:
TableA - monday night
TableB - tuesday night
and so on, depending on the number of tables and needs. The tables could be broken up into groups and each group would have a specific night for the reindex job.
Another option is to rebuild indexes based on filegroup - if you have multiple filegroups.
[font="Courier New"]Jason,
But the question here is about how to improve the performance of one table with 600 million records not many tables if I understand correctly. When rebuilding indexes, it hits the tempdb hard (sort in tempdb setting) to sort the data, so tempdb write performance is important but will NOT give you dramatic results.
Having large memory also helps, so that lazywriter doesn't have to work to keep enough free buffers available.
Spinning a custom partition solution with many tables with union all will definitely help but there is some complexity and maintenance overhead.
Fillfactor will NOT directly help the index rebuilds but in general I would definitely look at that as well. [/font]
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
July 27, 2010 at 10:15 am
Sankar Reddy (7/27/2010)
CirquedeSQLeil (7/27/2010)
I would look into doing the rebuilds more frequently and more targeted.I would rebuild certain tables on certain nights and have a rebuild job running nightly.
Example:
TableA - monday night
TableB - tuesday night
and so on, depending on the number of tables and needs. The tables could be broken up into groups and each group would have a specific night for the reindex job.
Another option is to rebuild indexes based on filegroup - if you have multiple filegroups.
[font="Courier New"]Jason,
But the question here is about how to improve the performance of one table with 600 million records not many tables if I understand correctly. When rebuilding indexes, it hits the tempdb hard (sort in tempdb setting) to sort the data, so tempdb write performance is important but will NOT give you dramatic results.
[/font]
Read the last post by the OP where he posted a response to my question. This is about multiple tables being reindexed.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2010 at 6:11 pm
Steve's idea of increasing fill factor is quite interesting and it may decrease the frequency of index rebuilds. Maybe there is a way to analyze if the clustered index required on this key, maybe a nonclustered will be more efficient. Also I would look at partitioned views, in many scenarios they may be more interesting option than partitioned tables, and they work on every edition of SQL Server.
Regards
Piotr
...and your only reply is slàinte mhath
July 31, 2010 at 9:20 am
Sorry I haven't been back in here. I wasn't thinking that fillfactor would speed things up, but it might reduce the need to defragment.
I would also follow Piotr's advice of re-examining which key is clustered and which NCI.
August 3, 2010 at 9:11 pm
Too bad you might be also missing out on Parallel index operations which are available only in SQL Server 2005 Enterprise Edition and later, not just partitioning.
Simon Liew
Microsoft Certified Master: SQL Server 2008
August 4, 2010 at 11:03 am
What's the bottleneck on your particular system? If your accounting methods leave you with a hardware budget without a software license budget, you can try throwing hardware at it.
If it's disk, I'd look very hard at SSD's to put one or more of the critical tables on.
Also, I second the fillfactor + targeted reindexing recommendation in order to need to defrag fewer of the large tables each cycle. I often find that using even a significantly lower fillfactor takes up fewer pages than the amount of fragmentation uses up between defrag cycles.
August 4, 2010 at 12:08 pm
1) exactly what command is being issued to do your index maintenance on the large table?
2) are you using SORT_IN_TEMPDB option?
3) what is your tempdb IO configuration - is it on same spindles as other sql data/log files?
4) have you done an IO stall analysis on the server to see if you have IO problems?
5) do you have a lot of free space in the database or is it trying to grow during these maintenance windows? almost none of my clients proactively manage their database sizes and get into LOTS of trouble because of that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply