SQL Server Performance

help with Index Rebuilding

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by sekharabcs, Oct 8, 2009.

  1. sekharabcs New Member

    Hi All,
    We have daily scheduled jobs which will rebuild indexes in all tables and views in all databases, update statistics all tables and views.
    However, when I have seen the fragmentation details in the server, I have got the below results:1. Around 30 tables have AvgPageFragmentation value > 50
    2. 23 tables have Page counts > 1000
    I have got the above stats by running the query:SELECT DB_NAME(DATABASE_ID) AS [DatabaseName], OBJECT_NAME(OBJECT_ID) AS TableName, SI.NAME AS IndexName, INDEX_TYPE_DESC AS IndexType, AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation, PAGE_COUNT AS PageCountsFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') DPSINNER JOIN sysindexes SI
    ON DPS.OBJECT_ID = SI.ID AND DPS.INDEX_ID = SI.INDIDI have got the above query from the Index Optimization forums from our community.I am just wondered I need to do any more maintenance activities on server. Could anyone please let me know why the tables still have the above values? Any ideas?
    Thanks
    Sekhar
  2. moh_hassan20 New Member

    Don't worry for fragmentation except on big tables with 1000+ pages(This is by-design)
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=244214

    from BOL:
    In general, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.

    • avoid shrink db after rebuilding index
    • for high insert table , set fill factor 70-90
    • for tables 1000+ page and fragmentation more 30% , rebuild index with DROP_EXISTING
  3. sekharabcs New Member

    Thank you Hassan for your help!
    Missing Indexes:
    I have a quick question on Missing Indexes. I have got the list of missing indexes from a script provided in forums. The output yields column_usage as INCLUDE, EQUALITY, INEQUALITY for the list of missing indexes.
    It has been mentioned the fields which are mentioned in INCLUDE needs to be included when creating a index. In this regard, how can I know whether to 1. create a new index or 2. include the INCLUDE fields in the existing index. If it’s the 2nd option, then to which index do I need to include the specified columns?
    Also could anyone please elaborate on the usage of EQUALITY, INEQUALITY.
    Thanks,
    Sekhar
  4. moh_hassan20 New Member

    Missing index DMVs show which quires haven't useful index (including ad-hock ones).

    take into account that indexes help in select queries, but more indexes on table may reduce performance for insert/update queries - so balance index usage.


    I advice you should know which query it is supposed to be improved by creating the index, and judge the value of creating it by reviewing its execution plan.
    [quote user="sekharabcs"] how can I know whether to 1. create a new index [/quote]
    Create the index for the more commonly used queries:
    -queries inside stored procedures , or more commonly used in patch operation.
    review what are the available index for the table , and if you can create or modify existing index

    [quote user="sekharabcs"]2. include the INCLUDE fields in the existing index.[/quote]
    Except you are sure that the only way to improve performance by creating index with include columns, avoid such indexes , especially for large tables.
    [quote user="sekharabcs"]Also could anyone please elaborate on the usage of EQUALITY, INEQUALITY.[/quote]
    Based on the query that may benefit from index , you can judge which one you can use (let the execution plan your favorite tool for judging)
  5. sekharabcs New Member

    Thank you Hassan. I will consider the above mentioned points.

Share This Page