February 26, 2015 at 1:43 pm
Hello all,
We use an ERP system that is leveraging SQL2008R2 x64 database. Some of the ERP tables are massive holding close to 275 million rows of data.
Our work environment is close to 24/7 so addressing index fragmentation has been impossible. As an accidental dba, I am doing my best to read and educate myself.
I am reading about table partitioning to see if it can be leveraged but then again, there are dependencies and limitations posed by the ERP software which makes the situation even more difficult. I have also tried index maintenance with ONLINE option enabled, which ended up creating a concern. Towards the latter part of the completion of that task, the process takes a lock on the table which impacted business.
Question: What if I create a brand new index, an exact duplicate of an existing index that I'm trying to run the maintenance on ? That way, the existing index will continue to be in use while the duplicate index is being built. When the new index finishes, I can issue the delete command on that old one. At that point, the newly created index will started getting used.
Is my thinking wrong, will this work ? are there any downfalls for going this route ?
regards,
SQLJay
February 26, 2015 at 2:38 pm
Creating or rebuilding indexes online have different behaviors as far as the final lock is concerned.
When an index is (re)built, a temporary mapping index is populated and, at the end of the population, the index has to be pushed in.
That last phase requires a table lock: for index creation it's a shared lock, for index rebuild it's a SCH-M lock.
You can read more about online operations in this whitepaper (for sql 2005, but should be still valid as far as I know) http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/OnlineIndex.doc
So, your idea should be worth investigating.
However, I would ask myself whether fragmentation is really a concern and why:
1) It impacts huge sequential scans, which are not what you typically see in OLTP databases, such as ERPs. Are you missing some indexes?
2) It doesn't impact data that resides in memory: are you suffering from memory pressure?
3) It's a major concern on clustered indexes rather than nonclustered indexes: are your clustered indexes designed correctly? A good clustering key is small, unique, unchanging and monotone.
4) Are you sure that the cause of poor performance is fragmentation? How did you determine that?
5) Old data doesn't change: have you considered partitioning your tables? Partitions with old data could go to readonly filegroups and have zero fragmentation. This way, read-write partitions could be maintained more easily.
Hope this helps
Gianluca
-- Gianluca Sartori
February 27, 2015 at 6:28 am
I'd suggest taking a look at Minion Reindex. I wrote a review of it here[/url]. I think that'll help you.
"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
February 27, 2015 at 8:19 am
SQLJay (2/26/2015)
Hello all,We use an ERP system that is leveraging SQL2008R2 x64 database. Some of the ERP tables are massive holding close to 275 million rows of data.
Our work environment is close to 24/7 so addressing index fragmentation has been impossible. As an accidental dba, I am doing my best to read and educate myself.
I am reading about table partitioning to see if it can be leveraged but then again, there are dependencies and limitations posed by the ERP software which makes the situation even more difficult. I have also tried index maintenance with ONLINE option enabled, which ended up creating a concern. Towards the latter part of the completion of that task, the process takes a lock on the table which impacted business.
Question: What if I create a brand new index, an exact duplicate of an existing index that I'm trying to run the maintenance on ? That way, the existing index will continue to be in use while the duplicate index is being built. When the new index finishes, I can issue the delete command on that old one. At that point, the newly created index will started getting used.
Is my thinking wrong, will this work ? are there any downfalls for going this route ?
regards,
SQLJay
Which edition of SQL Server do you have? Standard or Enterprise? Also, how much free space do you have where the MDF file lives and how much free space do you have where the LDF file lives? Can you post the CREATE TABLE statement for the table along with all indexes and constraints so that we might be able to do a deeper dive for you?
Last but not least, how many GB does the table and it's indexes occupy? One way to find out is to run the following code
EXEC sp_SpaceUsed 'puttablenamehere';
Part of the reason I'm asking all the questions is, yes... there are many downfalls and "tricks of the trade" when it comes to index maintenance on large tables that a lot of the "canned" solutions that people have provided simply haven't included because they really require some human evaluation and deep concern for disk space.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply