Tag Archives: optimize queries sql

Analyze Indexes – with or without them?

SQL Indexes – what a great subject to discuss on! I just love them. It is incredible to find out each time, what a proper index can do to your database…

As far as I can think of there are 3 issues related to Indexes:

  1. Fragmentation
  2. Query Optimization
  3. Storage problems – I already blogged about it and if you follow the link you will see the whole story.

I. Fragmentation

Inside each DBA’s maintenance tasks, should be included a step which will collect fragmentation on all Production most critical databases.
You can schedule the Index part once per month, or as frequent as you consider necessary.

The SQL query is rather simple:

  1. Create a table with structure:
     

    use tempdb
    go
    CREATE TABLE #indexes (
       ObjectName CHAR (255),
       ObjectId INT,
       IndexName CHAR (255),
       IndexId INT,
       Lvl INT,
       CountPages INT,
       CountRows INT,
       MinRecSize INT,
       MaxRecSize INT,
       AvgRecSize INT,
       ForRecCount INT,
       Extents INT,
       ExtentSwitches INT,
       AvgFreeBytes INT,
       AvgPageDensity INT,
       ScanDensity DECIMAL,
       BestCount INT,
       ActualCount INT,
       LogicalFrag DECIMAL,
       ExtentFrag DECIMAL)
    
  2. Open a cursor that contains 2 parameters: schema name and table name selected from INFORMATION_SCHEMA.TABLES and execute for each:
     

     
     INSERT INTO #indexes
     EXEC ('DBCC SHOWCONTIG ('schemaname.tablename') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
    
  3. The execution output will be stored in a table with same structure as the output’s columns, used for future analysis.
    I only focus on Indexes that have a fragmentation higher than 5% and have more than 10000 pages. In theory, there is no point in rebuilding an index that occupies less than 24 pages. In practice, it’s generally recommended not to do anything for indexes with less than 10000 pages.
    Next step:
     

    delete from #indexes where LogicalFrag <= 5 or CountPages <= 10000
    

The list resulted can be split in two action strategies:

  • Reorganize Indexes that have a fragmentation rate > 5% and < 30%
  • Rebuild Indexes that have a fragmentation rate > 30%

 
Check this also for more info on fill factor importance and Alter Index syntaxes:

Managing SQL Server Database Fragmentation
How to check Fragmentation on SQL Server 2005


II. Query Optimization

I have been asked so many times, questions like: "Why this procedure is running so slowly?" or "why so many blockages?", "I have a new script and it takes ages until it is completed!" and I can go on. Almost every time, in a bigger or a smaller part - a lack or surplus of one/many indexes is involved.

How to know if you have unused indexes

Indexes in surplus - causes SQL to do as many index updates as many indexes are there on a table that include the same column which is modified in a single statement.
I saw many cases with tables having tens of indexes. Some of them were doubled and some of them were created just following SQL hints from SQL Query Optimizer. There is more damage than good, when you follow literary SQL's hints. First of all, multiple updates on those many indexes which slow down the query, secondarily index disk usage which can get substantial.

To understand which indexes are really unused and should be dropped at next maintenance window, SQL Server must not be restarted for some time (1 month - 6 months), considering that it will have exhausted all possible recurrent user queries, recurrent stored procedures, application transactions that run on it. This is because SQL will hold the statistics until next reboot. In order to have valuable statistics the SQL should have run for an enough period of time.

  1. Look at existing Indexes in your database:

    The script will avoid Primary Keys, Unique keys and already Disabled indexes.

    use your_database
    go
    select i.name index_name, o.name [object_name], i.type_desc, o.type_desc , i.is_disabled, i.is_unique
    from sys.indexes i inner join sys.objects o on 
    i.[object_id] = o.[object_id] where o.type_desc = 'USER_TABLE' and i.name is not null 
    and i.is_primary_key = 0 and i.is_disabled = 0 and i.is_unique = 0
    order by o.name
    
  2. Check how many seeks/scans are done on above extracted indexes. You can filter the query by specifying one or more object names and index names. This select will display also each table's row count.

    Example 1: you see a number of 1867777 scans on a 2000000 rows table and close to 0 seeks. Big problem. It means the index is not well designed. It is used but not as it should be.
    Example 2: you see a number close to 0 both for seeks and scans columns. Why? The index has never been used since SQL was restarted.

    In conclusion, seeks column should filled with numbers. If both seeks and scans have numbers, the indexes should be left there. user lookups are used mostly by primary keys or clustered indexes, so it is unlikely you will something there.

    use your_database 
    go
    IF (object_id('tempdb..#temp') IS NOT Null)
       DROP TABLE #temp   
    
    CREATE TABLE #temp (
    table_name sysname ,
    row_count INT,
    reserved_size VARCHAR(50),
    data_size VARCHAR(50),
    index_size VARCHAR(50),
    unused_size VARCHAR(50))
    SET NOCOUNT ON
    INSERT #temp
    EXEC sp_msforeachtable 'sp_spaceused ''?'''
    
    
    SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],S.[OBJECT_ID],
             I.[NAME] AS [INDEX NAME],
             USER_SEEKS,
             USER_SCANS,
             USER_LOOKUPS,
             USER_UPDATES, t1.row_count
    FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S
             INNER JOIN SYS.INDEXES AS I
               ON I.[OBJECT_ID] = S.[OBJECT_ID]
                  AND I.INDEX_ID = S.INDEX_ID
                  inner join #temp t1 on S.object_id = object_id (t1.table_name)
                  
    WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1  
    and is_disabled = 0 and is_unique = 0 and i.name is not null -- and user_seeks =0
    and  OBJECT_NAME(s.[OBJECT_ID]) in ('object1') 
    and I.[name] IN ('index1', 'index2','index3')
    order by  OBJECT_NAME(s.[OBJECT_ID]) ,i.name
    
  3. Check leaf updates, inserts and deletes on indexes identified in previous step with zero scans/seeks.
    This is the ultimate proof that an index which is not used in optimization, is used to be updated at insert/update/delete operations --> bad performance.

    use your_database 
    go
    SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
           I.[NAME] AS [INDEX NAME],
           A.LEAF_INSERT_COUNT,
           A.LEAF_UPDATE_COUNT,
           A.LEAF_DELETE_COUNT
    FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
           INNER JOIN SYS.INDEXES AS I
             ON I.[OBJECT_ID] = A.[OBJECT_ID]
                AND I.INDEX_ID = A.INDEX_ID
    WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1 
    and OBJECT_NAME(A.[OBJECT_ID]) in ('object1') 
    
    order by  I.[NAME]
    
  4. At last, identify Indexes that don't do any harm, but overcrowd the Indexes list and use space, these are indexes completely useless and should be dropped.
     

    DECLARE @dbid INT
    SELECT @dbid = DB_ID(DB_NAME())
    SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
    INDEXNAME = I.NAME,
    I.INDEX_ID, i.object_id
    FROM SYS.INDEXES I
    JOIN SYS.OBJECTS O
    ON I.OBJECT_ID = O.OBJECT_ID
    WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
    AND I.INDEX_ID NOT IN (
    SELECT S.INDEX_ID
    FROM SYS.DM_DB_INDEX_USAGE_STATS S
    WHERE S.OBJECT_ID = I.OBJECT_ID
    AND I.INDEX_ID = S.INDEX_ID
    AND DATABASE_ID = @dbid) and I.NAME is not null 
    and is_unique =0 and is_disabled = 0
    ORDER BY OBJECTNAME,
    I.INDEX_ID,
    INDEXNAME ASC
    GO