SQL Server Performance

need help on index tuning

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by sqldbarocks, Jan 12, 2011.

  1. sqldbarocks New Member

    Hi All,
    Mine is a small firm where 20 users are hitting the dbs for their dev part.Recently our dba left our orgnisation and I was appointed for the post. I need to do the maintanance work for our dbs.after performing the below steps how I can proceed further to resolve index issues. any help would be great
    select * from sys.dm_db_index_physical_stats(10,null,null,null,null)
    order by avg_fragmentation_in_percent desc
    results:
    10 325576198 1 91.1764705882353 1 CLUSTERED INDEX IN_ROW_DATA 2 0 34 1 34 NULL NULL NULL NULL NULL NULL NULL NULL NULL
    10 293576084 1 88.8888888888889 1 CLUSTERED INDEX IN_ROW_DATA 2 0 9 1 9 NULL NULL NULL NULL NULL NULL NULL NULL NULL
    10 901578250 2 86.3905325443787 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 151 1.11920529801324 169 NULL NULL NULL NULL NULL NULL NULL NULL NULL
    10 933578364 3 80 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 9 1.11111111111111 10 NULL NULL NULL NULL NULL NULL NULL NULL NULL
    10 933578364 2 57.1428571428571 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 9 1.55555555555556 14 NULL NULL NULL NULL NULL NULL NULL NULL NULL
    10 277576027 1 50 1 CLUSTERED INDEX IN_ROW_DATA 2 0 2 1 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL
    how to proceed further with the object id and index fragmentation details to solve the index fragmentation problem.Actually do not know how to get the table/index details by using the above query results
    can some pls provide me the steps how and where to reorganize/rebuild the indexes in appropriate places??
    Is there any maintenance plan for checking fragmentation and reorganize/rebuild according to the fragmentation percentage....? so that i can save it and use as regular maintenance activity???
  2. Luis Martin Moderator

    Check:
    http://www.sql-server-performance.com/articles/per/online_indexing_2005_p1.aspx
    http://sql-server-performance.com/Community/forums/p/22025/126763.aspx
  3. FrankKalis Moderator

    This is a basic skeleton from which you'll get the index name, the table name and the table schema. I dump this into a temp table and then build up the "ALTER INDEX..." statement from that table and execute the string.
    SELECT
    OBJECT_SCHEMA_NAME(FRAG.[object_id]) + '.' + OBJECT_NAME(FRAG.[object_id]),
    SIX.[name],
    FRAG.avg_fragmentation_in_percent,
    FRAG.page_count
    FROM
    sys.dm_db_index_physical_stats
    (
    DB_ID(), --use the currently connected database
    0, --Parameter for object_id.
    DEFAULT, --Parameter for index_id.
    0, --Parameter for partition_number.
    DEFAULT --Scanning mode. Default to "LIMITED", which is good enough
    ) FRAG
    JOIN
    sys.indexes SIX ON FRAG.[object_id] = SIX.[object_id] AND FRAG.index_id = SIX.index_id
    ORDER BY
    FRAG.avg_fragmentation_in_percent DESC;
    There is alos some additional logic to work out if an index really needs treatment or not. I blindly maintain all indexes upon every execution of this scheduled job.
  4. satya Moderator

    Index tuning is a kind of black-art process, its not easy to say to fix this by adding so and so index. In your case I would like to know your knowledge about performance & optimization aspects.
    As referred this site has plenty of resources on performance tuning which requires your time and patience to go through the content.
  5. sqldbarocks New Member

    Thanks for help

Share This Page