March 2, 2010 at 11:50 pm
Hi
Does someone know what the best policy is to do indexing in a 24/7 environment with SQL Server 2008 Standard Edition.
The database is about 3TB in size with one of the tables that needs indexes added as new functionality is added of 90 000 000 rows.
We have found that adding/updating indexes can take up to 4 hour and with Standard Edition the table is then locked which is unacceptable for the business.
We are thinking of setting up a second server for one way replication and then doing the indexing on it.
Switch the applications over to the second server's SQL as soon is replication is in sync.
Change the replication to work the other way and build the indexes on the first server.
In this way we have a fail-over box as well as have the ability to build indexes in a 24/7 environment with Standard Edition as the cost of Enterprise Edition is to high for management.
Any suggestions will be welcome.
Anton
March 16, 2010 at 1:13 pm
You will have to make "smarter" index maintenance.
- If you can isolate only the necessary indexes that will help alot with the availability.
- Secondly you will have to see if REORGANIZE is good enough for you so that you can avoid rebuilds.
- Last if you go the REORG route make sure to update the stats of that index
It is really sad that Std Ed does not operates the index rebuilds in parallel...
* Noel
March 17, 2010 at 3:39 pm
Develop a script (you can even get one from msdn, I think) which checks for fragmentation and only rebuilds the indexes that are necessary. This will not prevent the table locks when those indexes are being rebuilt, but at least it may minimize the total number of indexes to be rebuilt on any given iteration.
You can also use reorganize in some cases, which is an online process.
Really, if you want 24/7 uptime with SQL Server, you should go with Enterprise Edition. IMO, it is the only one that is truly 24/7. With Enterprise, you can rebuild the indexes online, as well.
March 17, 2010 at 4:52 pm
I recommend that you use a script such as the one that Michelle Ufford has developed. You can find it at her blog at http://sqlfool.com/.
It performs a smart index rebuild/ reorg based on inputs from you.
Other options are to build your own script, and then selectively rebuild indexes on certain nights.
I have used a script in the past to rebuild indexes based on frag level, number of pages, and depending on which filegroup the table belongs.
There are plenty of options and avenues to take with this.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply