March 10, 2009 at 9:02 am
I have a Sql Job that rebuilds indexes. The job uses [sys].[dm_db_index_physical_stats] to determine which indexes need to be rebuilt based on the value of [avg_fragmentation_in_percent].
Out of curiosity, I decided to log which indexes were getting rebuilt, along with the fragmentation percent. To my surprise, there are numerous clustered indexes for underlying primary keys that are regularly becoming fragmented. The PK's are all on a single column, each with the IDENTITY property. (Most of the PK's are INT's.)
The developers tell me that they never delete rows from the various tables. Obviously, the IDENTITY columns are never updated. The inserts never specify the IDENTITY column value by setting IDENTITY_INSERT ON.
I'm at a loss. How/why would the indexes become fragmented?
March 10, 2009 at 9:15 am
Dave Mason (3/10/2009)
I'm at a loss. How/why would the indexes become fragmented?
Updates that increase the size of the row
Database shrinks
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
March 10, 2009 at 9:18 am
How frequently indexes are getting rebuilt? Too many updates/inserts could be the reason for fragmentation too.
MJ
March 10, 2009 at 2:02 pm
MJ,
I rebuild indexes once per day at 6am. On some days, a second index rebuild is required.
March 10, 2009 at 2:09 pm
Dave Mason (3/10/2009)
MJ,I rebuild indexes once per day at 6am. On some days, a second index rebuild is required.
Auto_shrink? Manual shrink?
What's your threshold for rebuilding the indexes and how big (number of pages) are the tables?
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
March 10, 2009 at 2:21 pm
Gail,
The AUTO_SHRINK option is OFF. I do not manually shrink any of the db files. (I doubt anyone else in my organization does this.)
The indexed column(s) in question are not getting updated. (Again, they are columns with the IDENTITY property.)
I was under the impression that updates to non-indexed columns don't affect index fragmentation.
Am I wrong? If so, I can offer some more specific input regarding the updates in a typical day.
March 10, 2009 at 2:32 pm
Dave Mason (3/10/2009)
I was under the impression that updates to non-indexed columns don't affect index fragmentation.
It does if the update makes the row grow (null to non-null, longer entries in varchar/nvarchar) and there isn't space on the page for the larger row. Remember with a cluster the leaf pages are the data pages.
What's your threshold for fragmentation and how big (number of pages) are the tables in question?
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
March 10, 2009 at 2:46 pm
Ok, this is starting to make a little more sense now. Yes, the indexes in question are clustered indexes.
However, the # of rows updated is relatively small. Here's an example from this morning when the Sql job ran:
Index Fragmentation: 25%
Page Count: 43,870
Total number of rows in table: ~1.5 million
Number of rows updated since the previous index rebuild: 221
Updating less than 1% of the rows in the table resulted in 25% index fragmentation. This just doesn't seem right. Am I making an unfair assessment?
Oh, as far as the fragmentation threshold goes, it was 3% (or more) this morning. Prior to that, it was 5%.
March 10, 2009 at 2:56 pm
In addition to what Gail has mentioned, how much space is available in the data file? Do you have enough space available to rebuild indexes - or, do you rely on autogrow to manage space?
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
March 10, 2009 at 2:56 pm
Dave Mason (3/10/2009)
Oh, as far as the fragmentation threshold goes, it was 3% (or more) this morning. Prior to that, it was 5%.
I wouldn't worry until it reaches 25-30. Fragmentation's mainly an issue with range scans, not seeks. If many of your queries do full table scans or large range scans, then maybe worry.
What's the schema of this table, and what does a typical row look like before and after one of those 200 updates?
Are you sure there's no shrink job anywhere? If you use maintenance plans, check that they don't have the 'release unused space' task.
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
March 10, 2009 at 3:03 pm
Jeffrey Williams (3/10/2009)
In addition to what Gail has mentioned, how much space is available in the data file? Do you have enough space available to rebuild indexes - or, do you rely on autogrow to manage space?
All the db files are enabled for autogrowth by 50 MB.
March 10, 2009 at 3:12 pm
Dave Mason (3/10/2009)All the db files are enabled for autogrowth by 50 MB.
Okay, so how large are the databases now - and is an autogrowth of 50MB large enough? Large enough would be a size that will allow for enough time to get a scheduled growth. At 50MB - you are probably growing the database several times a day.
You should run the Disk Usage report from SSMS to see how much space is available in the data files and log files for each database. Also check how often the database is autogrowing - you should not see any autogrowth if you are managing the space appropriately.
Schedule a time to grow the data files to allow for 6 months of activity. If you don't know how much space that is - grow it to leave approximately 20% available space and monitor the growth and schedule time to grow the data files as needed to keep at least 20% available space (or 6 months of activity).
If you have lots of autogrowth events for the database - you should also consider performing a file level defragmentation on the database file. This is best performed with SQL Server down and nobody accessing the database.
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
March 10, 2009 at 3:23 pm
GilaMonster (3/10/2009)
Dave Mason (3/10/2009)
Oh, as far as the fragmentation threshold goes, it was 3% (or more) this morning. Prior to that, it was 5%.I wouldn't worry until it reaches 25-30. Fragmentation's mainly an issue with range scans, not seeks. If many of your queries do full table scans or large range scans, then maybe worry.
What's the schema of this table, and what does a typical row look like before and after one of those 200 updates?
Are you sure there's no shrink job anywhere? If you use maintenance plans, check that they don't have the 'release unused space' task.
Normally, I wouldn't worry about the fragmentation until it reaches 15% (seems like I read about that threshold in some MS documentation somewhere). My performance barometer is a stored proc used for searches run from our main application. Most of the time, it runs in 5 ms or less. Sometimes, the main application has timeout exceptions. When I run a trace, I can see the Duration, Reads, and CPU spike. At that point, I'll rebuild the indexes manually. Afterwards, the application time outs cease and things return to normal. I've also tried updating statistics instead of rebuilding indexes, but the results are mixed.
As for shrinking the db, it's a fairly new instance of Sql Server 2005. There are no maintenance plans. The only Sql jobs were created by me. I suppose there could be some rogue process doing the shrinking (for instance, a Windows scheduled task on a different machine).
I ran a quick test and issued this command:
DBCC SHRINKDATABASE (MyTestDbName)
However, I didn't see anything in the sql logs to tell me that the db was shrunk (in SSMS, expand the Management node, expand the SQL Server Logs node, and select the first log item). Is there another mechanism that will tell me when the db was last shrunk?
March 10, 2009 at 5:57 pm
I'm with you. this doesn't seem right. I can't see how so few updates could result in so much fragmentation.
Assuming your stats are correct, could a badly behaved trigger be performing a large number of updates that do not occur in place?
.
March 10, 2009 at 6:02 pm
The trigger need not be on this table, of course ...
.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply