SQL Server Performance

Index ScanDensity very low on new index?

Discussion in 'Performance Tuning for DBAs' started by dataninja, Feb 27, 2003.

  1. dataninja New Member

    I have the following table in a database:

    CREATE TABLE [X] (
    [XGuid] [uniqueidentifier] NOT NULL ,
    [XID] [smallint] NOT NULL ,
    [XFlag] [char]

    CONSTRAINT [PK_X] PRIMARY KEY CLUSTERED
    (
    [XGuid],
    [XID]
    ) WITH FILLFACTOR = 90 ON [PRIMARY],

    ) ON [PRIMARY]
    GO


    The table contains around 1800 rows, and when I run a DBCC SHOWCONTIG the report is listed as follows:

    DBCC SHOWCONTIG scanning 'X' table...
    Table: 'X' (21575115); index ID: 1, database ID: 6
    TABLE level scan performed.
    - Pages Scanned................................: 7
    - Extents Scanned..............................: 5
    - Extent Switches..............................: 4
    - Avg. Pages per Extent........................: 1.4
    - Scan Density [Best Count:Actual Count].......: 20.00% [1:5]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 60.00%
    - Avg. Bytes Free per Page.....................: 1036.0
    - Avg. Page Density (full).....................: 87.20%


    Now to my problem - no matter what I do, I can't get the scan density to improve. I have run DBCC INDEXDEFRAG, DBCC DBREINDEX, I have dropped and recreated the clustered index (dropped and readded the PK constraint). If I run a DBCC DBREINDEX over and over, it will flip back and forth between %20 and %25 Scan Density. Is this normal behavior? I know it isn't good practice to have a clustered index on a GUID, but I don't have the ability to change the table. I have noticed this same problem with quite a few other tables as well.

    FYI - I am running SQL 2000 SP3 on the server.

    Any help would be appreciated. Thanks!

    -dataninja-

    "The normalcy of a database is inversely proportional to that of it's DBA."
  2. sqljunkie New Member

    Is there more than one file in the primary filegroup?
  3. bradmcgehee New Member

    How large, on average, is each row, in bytes? Sometimes you see an issue like this if the number of bytes in each row, when added together, leave a lot of extra space in a page because there is not enough room for one more row to fill up the empty space. I did notice that the average page density is 87.2%, which is very close to the fillfactor of 90%, which is what you would expect. I am guessing that perhaps this behavior is because the table is currently very small, and that when it gets bigger, you won't see this same strange behavior with the scan density (just a guess).

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  4. gaurav_bindlish New Member

    I agree with Brad, since this table is currently having just 7 pages and SQL server does not allocate an extent to a table until the number of pages crosses 8, the table will continue to have this behaviour ntil more data comes in. One solution can be to insert some dummy rows in the table to make the pages used more than 8 and then reindex the table after deleting the dummy rows.

    Gaurav

Share This Page