[dba-SQLServer] Weird Index stats

Did you try REBUILD instead of REORGANIZE?

Asger

-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Arthur
Fuller
Sendt: 16. juni 2008 13:23
Til: Discussion concerning MS SQL Server
Emne: [dba-SQLServer] Weird Index stats

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
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com