January 5, 2012 at 11:18 am
Good morning, SQL Server Central! 🙂
I have a bit of a puzzle for my esteemed colleagues. I have a table which is completely rebuilt each night. We were having fragmentation issues with the indexes (which seemed odd to me). So, I updated the rebuild job to drop the non-clustered indexes, truncate the table, then reload the data (pre-sorted in primary key order). The last step of the job is to rebuild each of the indexes.
So, when I came in this morning, I was astonished to find that the indexes were very fragmented. How can this be? There are no updates to the table during the day. It is only used for SELECTs. What key fact about indexes am I missing here?
Here is the code I use to rebuild the table each night.
USE TPS;
TRUNCATE TABLE TPS.dbo.AccountDimension;
DROP INDEX [IX_InactiveAccountName]
ON TPS.dbo.AccountDimension;
DROP INDEX [IX_AccountName_AccountType]
ON TPS.dbo.AccountDimension;
DROP INDEX [IX_AccountID]
ON TPS.dbo.AccountDimension;
DROP INDEX [IX_AccountName]
ON TPS.dbo.AccountDimension;
INSERT TPS.dbo.AccountDimension
SELECT *
FROM TPS.dbo.AccountDimensionView
ORDER BY ContractID;
CREATE INDEX [IX_InactiveAccountName]
ON [AccountDimension]
( AccountIsInactive
,ContractIsInactive
,AccountName
,AccountManager
);
CREATE INDEX [IX_AccountName_AccountType]
ON [AccountDimension]
( AccountName
,AccountType
);
CREATE INDEX [IX_AccountID]
ON TPS.dbo.AccountDimension
( AccountID );
CREATE INDEX [IX_AccountName]
ON TPS.dbo.AccountDimension
( AccountName );
When I came in this morning, here is the what I saw:
Index Name___________________Index Type_____Total Fragmentation
PK_ContractID________________Clustered_______98.517....
IX_InactiveAccountName_______NonClustered____90.0235581....
IX_AccountName_______________NonClustered____95.867....
IX_AccountID_________________NonClustered____93.75
IX_AccountName_AccountType___NonClustered____96.7391....
After rebuilding the indexes (again) this morning, here is what I see:
Index Name___________________Index Type_____Total Fragmentation
PK_ContractID________________Clustered_______0
IX_InactiveAccountName_______NonClustered____0
IX_AccountName_______________NonClustered____0
IX_AccountID_________________NonClustered____35.714285714....
IX_AccountName_AccountType___NonClustered____0
Any light you can shed on this mystery for me would be much appreciated. 🙂
January 5, 2012 at 11:19 am
Index fragmentation is meaningless when the indexes are small... How many pages are your indexes?
Jared
CE - Microsoft
January 5, 2012 at 11:27 am
The table has about 10,000 records. It is extensively used in reporting queries by a very active data warehouse.
We were seeing performance problems on reports referencing this table yesterday. When we rebuilt the indexes yesterday afternoon, the performance problems immediately disappeared. So, I believe that index fragmentation on this table is relevant to performance.
However, even if the fragmentation was not relevant to performance, I would still want to understand why the indexes are so fragmented when no updates, inserts or deletes have taken place since the indexes were created.
Thank you for your help! 🙂
January 5, 2012 at 11:36 am
David Moutray (1/5/2012)
The table has about 10,000 records.
Not interested in the number of records. How many pages does that index have?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2012 at 11:37 am
p.s. Is autoshrink on? Is there a daily job shrinking the database?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2012 at 11:49 am
Auto-shrink is not on. The clustered index has 443 pages. The other indexes are about 70 pages each.
January 5, 2012 at 11:53 am
Both of those are under the threshold where one would worry at all about fragmentation (that's around 1000 pages).
A shrink job would cause massive fragmentation. Are you absolutely sure there are no changes? You truncate the table, insert the data and it's fragmented at that point immediately after the insert?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2012 at 11:53 am
There is also no job shrinking the database. (I do know enough to avoid that. :-))
January 5, 2012 at 12:01 pm
Well, everyone swears that no updates are done. I do not believe any updates are done. I also do not believe that any other processes occur which might "adjust" the data (like a database shrink). However, I have not directly observed this fragmentation immediately after the index was built (at 11:30 p.m.) so I can't swear to this under pain of death.
I can try this process in my dev environment and see what the fragmentation looks like afterwards.
Also, I'll make a point of looking at the indexes again immediately after the process runs in production tonight.
Just to be certain I understand: when I drop the indexes and recreate them, there should be no fragmentation at that moment. Am I correct in saying this?
January 5, 2012 at 12:19 pm
David Moutray (1/5/2012)
Just to be certain I understand: when I drop the indexes and recreate them, there should be no fragmentation at that moment. Am I correct in saying this?
No, it's unlikely to be absolutely 0. It should be quite low.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2012 at 12:21 pm
David Moutray (1/5/2012)
Well, everyone swears that no updates are done. I do not believe any updates are done. I also do not believe that any other processes occur which might "adjust" the data (like a database shrink).
Try profiling the database to see whats going on.
There are different levels of fragmentation which are you seeing. What are the results of the frag report.
With a low number of pages the engine will usually scatter them to fill empty space.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 5, 2012 at 12:27 pm
Hmmm. It is quite low when I run this process in the dev environment.
So, something is modifying this table: either the data or the underlying physical storage of the data.
That is good to know. Now I can embark on a Search and Destroy mission. 🙂
January 5, 2012 at 1:09 pm
David Moutray (1/5/2012)
Hmmm. It is quite low when I run this process in the dev environment.
Disk and file layouts are bound to be different between dev and live aren't they?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 5, 2012 at 1:12 pm
They are. I'll just have to check this right after the rebuild process runs tonight. 'Tis a mystery.
January 5, 2012 at 1:43 pm
BTW - inserting data using an ORDER BY clause will not guarantee that the data is inserted with no fragmentation. Even if you have an IDENTITY column as the clustered index - there is no guarantee that there will not be any fragmentation once the table is loaded.
I use a slightly different process. Instead of dropping the non-clustered indexes, I disable them. Once the data is loaded, I then rebuild all indexes (including the clustered index).
This won't make a difference for your non-clustered indexes being fragmented when you arrive - because that really has to be some other process running after you build them.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply