SQL Server Performance

PK Shows TABLE Level Scan performed

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by jbates99, Jun 18, 2009.

  1. jbates99 Member

    hi everyone,
    This is SQL Server 2005 Std SP2

    Hmmm... even after dropping and recreating the index and clustered Pprimary Key, DBCC SHOWCONTIG still shows "TABLE Level Scan performed" for the PK.
    DBCC SHOWCONTIG scanning 'SMN_BAL_WHZNBN_LOT_SRL_LVL_BAL' table...Table: 'SMN_BAL_WHZNBN_LOT_SRL_LVL_BAL' (379616891); index ID: 1, database ID: 6TABLE level scan performed.- Pages Scanned................................: 14357- Extents Scanned..............................: 1808- Extent Switches..............................: 2392- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 75.01% [1795:2393]- Logical Scan Fragmentation ..................: 4.76%- Extent Scan Fragmentation ...................: 18.20%- Avg. Bytes Free per Page.....................: 678.0- Avg. Page Density (full).....................: 91.62%DBCC SHOWCONTIG scanning 'SMN_BAL_WHZNBN_LOT_SRL_LVL_BAL' table...Table: 'SMN_BAL_WHZNBN_LOT_SRL_LVL_BAL' (379616891); index ID: 67, database ID: 6
    This is the PK :
    ADD CONSTRAINT [SMN_BAL_WHZNBN_LOT_SRL_LVL_BAL_PK] PRIMARY KEY CLUSTERED ( [BAL_WAREHOUSE_ID] ASC, [BAL_ZONE_ID] ASC, [BAL_BIN_ID] ASC, [BAL_PART_NO] ASC, [BAL_PART_STATUS] ASC, [BAL_LOT_NO] ASC, [BAL_SERIAL_NO] ASC, [BAL_PART_CONDITION] ASC, [BAL_PART_CRT_OU] ASC, [BAL_WAREHOUSE_OUINSTANCE] ASC, [BAL_SMN_OUINSTANCE] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    I ran update statistics and update usage.

    Anyone know why the PK would still perform a Table Scan? (The regular index is ok)

    Thanks, John

  2. Sandy New Member

    Hii John,
    I am not sure for what reason it is happening. You can do one more thing here if this is not the production DB then just drop the table and recreate it.
    Hope it will works for you.
    Thank you,
    Sandy.
  3. jbates99 Member

    Thanks, Sandy.

    Yes, this is a production database, unfortunately. But I may be able to recreate it, then repopulate it in the middle of the night. I was wondering what I might do between 2 am and 3 Sunday morning. Now I know :)

    John
  4. satya Moderator

    GOIng forward based on number of rows added/deleted on that table may take longer time to complete, so in this case I would like to suggest in selecting required indexes to REINDEX than performing all the indexes on the table.

Share This Page