INDEX

  • Hi All,

    Today i was working on index

    i created on table

    CREATE TABLE EMPLOYEE

    (

    E_ID INT ,

    D_ID INT NOT NULL,

    DES_ID INT NOT NULL,

    NAME VARCHAR(100) NOT NULL,

    SALERY INT NOT NULL

    )

    and i created

    one clustered and two on -cluster index on employee using GOI.

    then i inserted 6000 record using

    INSERT INTO EMPLOYEE VALUES (1,11123,7,'ddddddddddddddd',20)

    go 6000

    then t checked using below script that what is the average index fragmentation levels using below script i found that there are three indes % of fragmentation

    SELECT DB_NAME(PS.database_id) AS dbName,

    S.name AS SchemaName,

    O.name AS TableName,

    b.name,

    ps.avg_fragmentation_in_percent

    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps

    INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id

    INNER JOIN sys.objects O ON PS.object_id = O.object_id

    INNER JOIN sys.schemas S ON S.schema_id = O.schema_id

    WHERE ps.avg_fragmentation_in_percent >= 20 -- Indexes having Fragmentation >=20

    AND PS.index_type_desc IN ('CLUSTERED INDEX','NONCLUSTERED INDEX') -- Only get clustered and nonclustered indexes

    AND b.is_hypothetical = 0 -- Only real indexes

    AND O.type_desc = 'USER_TABLE' -- Restrict to user tables

    AND PS.page_count > 8 --- ignore tables less tha 64K

    ORDER BY ps.avg_fragmentation_in_percent DESC

    Then it was LESS THEN 30% so i did

    ALTER INDEX nc_index

    ON EMPLOYEE REBUILD

    then when i again execute the above script for average index fragmentation levels for finding the |%

    it was showing only two index ---- What it did and where is my threed index which i created ?

    Rajesh_only_TestdboEMPLOYEEnc193.3333333333333

    Rajesh_only_TestdboEMPLOYEEclu66.6666666666667

    Next i executed

    SELECT

    OBJECT_NAME(i.OBJECT_ID) AS TableName,

    i.name AS TableIndexName

    FROM

    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') phystat

    INNER JOIN sys.indexes i

    ON i.OBJECT_ID = phystat.OBJECT_ID AND i.index_id = phystat.index_id

    WHERE

    phystat.avg_fragmentation_in_percent > 20

    AND OBJECT_NAME(i.OBJECT_ID) IS NOT NULL

    ORDER BY phystat.avg_fragmentation_in_percent DESC

    it is showing three index

    EMPLOYEEnc_index

    EMPLOYEEnc1

    EMPLOYEEclu

    EMPLOYEENULL

  • rajeshjaiswalraj (12/13/2012)


    Then it was LESS THEN 30% so i did

    ALTER INDEX nc_index

    ON EMPLOYEE REBUILD

    then when i again execute the above script for average index fragmentation levels for finding the |%

    it was showing only two index ---- What it did and where is my threed index which i created ?

    Probably filtered out by the filters on the query. Specifically

    AND PS.page_count > 8 --- ignore tables less tha 64K

    That doesn't ignore tables below 8 pages, it ignores indexes with less than 8 pages.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi Gila,

    Thanks for the reply.

    I removed the below line but i got the same result.

    AND PS.page_count > 8 --- ignore tables less tha 64K

    Then i make some changes in my script

    WHERE ps.avg_fragmentation_in_percent >= 20 -- Indexes having Fragmentation >=20

    i removed 20 and i make it 0 - after this i got correct result .....

    Thanks for your help.....

  • Bear in mind, if you're writing a script to identify indexes that need rebuilding, you want those filters in. You may well want the size filter to be larger than 8 pages too.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply