Arthur Fuller
fuller.artful at gmail.com
Mon Jun 16 06:22:36 CDT 2008
I have an issue with a particular table that is heavily trafficked. Two of its seven indexes report serious fragmentation, one at 98% and the other (the PK) at 87%. What is strange to me is that a nightly job runs that does the following on this table: (this includes only the code to touch the indexes in question; the full code does the same thing to all seven indexes) <sql> ALTER INDEX [IX_BESTSecurityLog__BSID_BSLID_CID] ON [dbo].[BESTSecurityLog] SET (ALLOW_PAGE_LOCKS = ON) ALTER INDEX [IX_BESTSecurityLog__BSID_BSLID_CID] ON [dbo].[BESTSecurityLog] REORGANIZE WITH ( LOB_COMPACTION = ON ) ALTER INDEX [IX_BESTSecurityLog__BSID_BSLID_CID] ON [dbo].[BESTSecurityLog] SET (ALLOW_PAGE_LOCKS = OFF) ALTER INDEX [PK_BESTSecurityLog] ON [dbo].[BESTSecurityLog] SET (ALLOW_PAGE_LOCKS = ON) ALTER INDEX [PK_BESTSecurityLog] ON [dbo].[BESTSecurityLog] REORGANIZE WITH ( LOB_COMPACTION = ON ) ALTER INDEX [PK_BESTSecurityLog] ON [dbo].[BESTSecurityLog] SET (ALLOW_PAGE_LOCKS = OFF) </sql> Identical code runs on the other indexes and their fragmentation levels are in the range of 3% or less. The PK is identity(1,1). Why would it report such a high fragmentation level? I'm not sure how to solve this puzzle. Should I drop and recreate the indexes instead of altering them as in the code above? Any clues are gratefully appreciated, Arthur