[dba-SQLServer] Weird Index stats

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



More information about the dba-SQLServer mailing list