SQL Server Performance

Indexes going to 99.99% fragmentation nightly

Discussion in 'SQL Server 2005 General DBA Questions' started by XjeaxaxX, Aug 6, 2009.

  1. XjeaxaxX New Member

    I have a SQLServer 2005 database withseveral tables, each containing between 10 and 40 million records. Eachtable has a clustered index (which clusters data into blocks of about100,000 records or so based on customer), as well as 2 or 3non-clustered indexes (primary key, order codes). I have a maintenanceplan that runs nightly for each table:

    DBCC DBREINDEX ('Table') WITH NO_INFOMSGS;

    These plans run with no errors, and if I check at night, the indexes all seem to have .01% fragmentation, or something similar.

    Yet, by 8am the next morning, all of the non-clustered indexes are 97to 99.999% fragmented. The odd thing is, no users have been on thesystem. No data has been added/deleted/modified.

    Has anyone else experienced such behavior? If so, were there any solutions?

    Thanks!
  2. FrankKalis Moderator

    On SQL Server 2005 and above you should use ALTER INDEX...REORGANIZE (or REBUILD) as DBCC DBREINDEX will be removed in a future version.
    How do you check the fragmentation?
  3. XjeaxaxX New Member

    <p>&nbsp;I use this to check fragmentation:</p><p>&nbsp;DBCC SHOWCONTIG ('TableName') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS </p><p>&nbsp;The next to last column (LogicalFragmentation) is what I look at (these instructions coming to me from my dba)</p><p>Today, here is an example of one table:</p><p><img src="http://bit.ly/mvc56" mce_src="http://bit.ly/mvc56" height="317" width="750"></p><p>&nbsp;</p><p>&nbsp;</p><p>&nbsp;</p><p>&nbsp;</p>
  4. XjeaxaxX New Member

    Oh, and it also seems to happen in a random fashion. Some days this happens on one table, another day on another table. Sometimes this happens on two or three tables at the same time. Sometimes, all tables are OK when I come in in the morning. This has been going on for about 6 or 7 months now. I never see fragmentation occur on the clustered index (in the pic above, the clustered index was not rebuilt the previous night, but the non-clustered indexes were, with success).
  5. imSQrLy New Member

    Is there an OS level defrag running at night? Not sure of the effect but came to mind. Also possibly try updating the service pack. Can you reproduce this on a test instance?
  6. XjeaxaxX New Member

    Going to start OS level defrags tonight. Another person suggested that, too. My dba assures me that an OS disk defrag will not have any effect, since that's what SQL Server does with the clustered indexes. I think he's full of hot air.
    Test server? Are you kidding me? That would mean my boss would actually care enough to spend on some extra hardware for his flagship product!
  7. Luis Martin Moderator

    OS disk defrag will not help.
    What fillfactor are you using?
  8. preethi Member

    Is there any SQL Server Jobs / OS level scheduled tasks running during the night?
  9. XjeaxaxX New Member

    The only thing scheduled at night is the defragmenting of indexes (DBCC DBREINDEX ('Table') WITH NO_INFOMSGS;), and then a backup of the database. We used to shrink the database after the index defrag and before the backup... but I read that might not be a good thing. So I have stopped that about 3 weeks ago. Seems to have made no difference at all.
    Fill factors are either 80 or 100 on all indexes. We've experimented with different values over the past 3 years, but have never found any real difference as far as day to day performance or the problem in question.
    I'm wondering if any sort of corruption could be causing this... and, if so, how do I discover and correct it?
  10. madhuottapalam New Member

    Do you have any text /varbinary(max) column in this table? Can you run the following query and post the resultSELECT
    index_type_desc, page_count,avg_fragmentation_in_percent,avg_page_space_used_in_percent
    ,avg_record_size_in_bytes
    ,forwarded_record_count,fragment_countFROM sys.dm_db_index_physical_stats(db_id('databaseName'),
    object_id(''TableName),default, default, 'detailed');Madhu
  11. XjeaxaxX New Member

    <p>&nbsp;</p><p>One of the tables in question has 6 nvarchar(max) columns (out of 58 total columns). Yeah, I know... it's a beast.&nbsp; I didn't design it.<br></p><p>Another table, however, has no (max) columns.&nbsp; This other table only has 8 columns (5 GUIDs, 2 floats, and a bit).&nbsp; Tthere are currently 41,993,652 records.&nbsp; This morning, the non-clustered indexes were (again) all 99% fragmented.&nbsp; I ran the statement above on it, and received:</p><p><img src="http://bit.ly/WPnQD" title="fragmentation" alt="fragmentation" mce_src="http://bit.ly/WPnQD" border="1" height="350" width="1205"></p><p>&nbsp;</p><p>If you can't see the entire picture, it is at: <a href="http://bit.ly/WPnQD" title="http://bit.ly/WPnQD" target="_blank" mce_href="http://bit.ly/WPnQD">http://bit.ly/WPnQD</a></p><p>Each index is listed four times.<br></p><p>&nbsp;</p><p>&nbsp;</p>
  12. XjeaxaxX New Member

    And here's the results for the biggest table (58 columns... several of which are nvarchar(max)):
    http://bit.ly/lkkAO
    It has 28,696,234 records.
  13. gurucb New Member

Share This Page