Boost Your Performance - Bad Index Detection


Boost your databases performance -- Detect bad index design.

Good performance can depend on many factors. You can achieve so much by acting on the physical aspects of a database server. Monitoring to find bottlenecks is certainly not an aspect to neglect. But once you have done all you could by adding memory, verified that CPUs were sufficient, optimized I/Os by managing database file components and object location and put you DB server on a GB backbone. What is left for you to do? You have to look at the database design and the possibility of scaling out your database architecture.

Those two aren’t as easy as it looks. Applying modifications to a database design can put you in a risky situation where a minor change can have a great impact on application code. Worst the application vendor might not support his product anymore. There are other possibilities, before you look at changing a database data model, you can act on procedures, functions, and triggers optimization with the vendor blessing, hopefully. Make sure your changes will produce the same result faster. But even before doing this, I recommend you do a deep analysis of existing indexes and usage.

I always manage to get a sample (an hour or so) of what ran on the specific database I am concerned about using the Profiler. I do this by choosing a particular time of the day where the Server isn’t heavily used but still processing representative transactions and queries. With that in hand I can comfortably start my index analysis. You will find below a script I used to detect basic anomalies. You could be surprised how many of those typical mistakes can be found in databases. The script does the following:

This report is kind of handy to have for a reference and contains these parts.

Reports Tables with Missing Clustered index

If you believe that a clustered index is a free index, it could effectively replace an existing one. You have to be careful on your choice. If data does not get inserted reasonably ordered you will introduce data fragmentation. Having orderly inserts, fearing hotspots, does not appear to be as bad as it was in the time of page level locking. You can ameliorate your performance with a clustered index since it does not have to do bookmark lookups to evaluate row data.

Reports Tables with Missing Primary Keys

This is more a design defect. It leaves the door open to duplicates and unexpected data retrieval issues. Before you mount a primary key on a deficient table you must locate the combination of attribute that will uniquely identify the row.

Reports Possible Redundant Index keys

The optimizer will choose the first index it finds that will help resolve the work in an acceptable time. The data pages of the chosen index are going into the cache. Let’s say that you have an index on field1 and another index on field1 and field2. You can keep only one of the two. Witch one to keep will depends on your analysis of the query plans regarding this particular table. You have to be careful thought in the case where hints are being used within the application or database scripts. It might be easy to modify the reference to indexes in databases scripts but not as simple when the reference is in the compiled application code.

Reports Possible Reverse Index key

A reverse key is a composite index that is one that is built on a similar set of fields but in a different order. Let's say that you have an index on field1 and field2 and the other one is on field2 and field1 or even only field2. There are cases when this might be justifiable. Most of the time one of them must go.

Having fewer indexes to maintain, greatly improves performance during transaction processing. Having covered queries by some index can greatly improve reports resolution time. Using the fillfactor to release stress on transaction processing minimizing page splitting and all the overhead and housekeeping negative effects it has is also a must. Keep your index small and on native numeric and fixed length datatype columns. Make sure the statistics a well maintained since the optimizer is making his decision on those. When you are changing something into an index structure don’t forget to run a sp_recompileTablename’ command to make sure all related objects will be flagged to recompile and take advantage of your change.

I suggest that you plan the optimization implementation all at once to feel the pulse. Users would not notice minor changes on a daily basis. You wouldn’t want them to have the perception that there were no noticeable improvements. At the same time you should maintain a rollback procedure.

Once you are satisfied with the index organization and with their utilization you can start improving performance by rewriting inefficient databases scripts you will find in the procedures, functions and triggers. You can get a Profiler image of long running queries to start withand then use these hints:

Once you have accomplished all this and performance is still not quite what they need to be, you can get into the database scaling solutions. Many possibilities exist, but not all are easy or possible to implement for particular databases. I might write an article on this subject someday…

Index Report Code

CREATE VIEW dbo.INDEXVIEW
 
 
AS
/****************************************************************************************************
** Creation Date: ?
** Modif Date   : Nov. 27, 2002
** Created By   : avigneau
** Database     : any
** Description  : Reports on all indexes and / or heaps on user tables within a database
** Parameters   : none
** Compatibility: SQL Server 6.X, 7.0, 2000
** Remark       : System tables are used to be compatible with version 6.x.
                                         But I believe it would still be difficult to obtain the same results 
                  using INFORMATION_SCHEMA views and new object and system property functions.
** Example      : SELECT 'Showing All Indexes' AS Comments, I.*
                                                FROM dbo.INDEXVIEW I
                                         SELECT 'Showing Tables with Missing Clustered index' AS Comments, I.*
                                                FROM dbo.INDEXVIEW I
                                                WHERE ClusterType = 'HEAP'
                                              SELECT 'Showing Tables with Missing Primary Keys' AS Comments,  I.*
                                                FROM dbo.INDEXVIEW I
                                                       LEFT OUTER JOIN dbo.INDEXVIEW I2
                                                               ON I.TableID = I2.TableID AND I2.UniqueType = 'PRIMARY KEY'
                                                WHERE I2.TableID IS NULL   
                                              SELECT 'Showing Possible Redundant Index keys' AS Comments,  I.*
                                                FROM dbo.INDEXVIEW I
                                                       JOIN dbo.INDEXVIEW I2
                                                               ON I.TableID = I2.TableID AND I.ColName1 = I2.ColName1 
                                                               AND I.IndexName <> I2.IndexName
                                              SELECT 'Showing Possible Reverse Index keys' AS Comments,  I.*
                                                     FROM dbo.INDEXVIEW I
                                                             JOIN dbo.INDEXVIEW I2
                                                                     ON I.TableID = I2.TableID AND I.ColName1 = I2.ColName2 
                                                                     AND I.ColName2 = I2.ColName1 AND I.IndexName <> I2.IndexName
************************************************************************************************/
 
SELECT o.id AS TableID ,u.name Owner,o.name TableName,
               i.Indid AS IndexID
               , CASE i.name
                       WHEN o.name THEN '** NONE **' 
               ELSE i.name END AS IndexName, 
       CASE i.indid
                       WHEN 1 THEN 'CLUSTERED' 
                       WHEN 0 THEN 'HEAP' 
               ELSE 'NONCLUSTERED' END AS ClusterType,
               CASE
                  WHEN (i.status & 2048) > 0 THEN 'PRIMARY KEY' 
                       WHEN (i.status & (2|4096)) > 0 THEN 'UNIQUE' 
               ELSE '' END AS UniqueType,
           CASE       
                       WHEN (i.status & (2048)) > 0 
                                      OR ((i.status & (4096)) > 0 )
                               THEN 'CONSTRAINT' 
                       WHEN i.indid = 0 THEN ' '
               ELSE 'INDEX' END AS IndexType,
-- This following part is non essential
-- It is a pre char aggregate I use in other scripts 
-- to generate create and drop scripts
               CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 1) IS NULL THEN '' 
               ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 1) END +
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 2) IS NULL THEN '' 
               ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,2) END +
               CASE       
                       WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 3) IS NULL THEN '' 
               ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,3) END +
           CASE       
                       WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 4) IS NULL THEN '' 
               ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,4) END +
               CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 5) IS NULL THEN '' 
               ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,5) END +
      CASE       
                       WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 6) IS NULL THEN '' 
               ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,6) END +
      CASE       
                       WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 7) IS NULL THEN '' 
               ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 7) END +
      CASE       
                       WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 8) IS NULL THEN '' 
               ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,8) END +
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 9) IS NULL THEN '' 
               ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,9) END +
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 10) IS NULL THEN '' 
               ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,10) END +
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 11) IS NULL THEN '' 
               ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,11) END +
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 12) IS NULL THEN '' 
               ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,12) END +
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 13) IS NULL THEN '' 
               ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,13) END +
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 14) IS NULL THEN '' 
               ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,14) END +
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 15) IS NULL THEN '' 
               ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,15) END +
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 16) IS NULL THEN '' 
               ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,16) END AS AllColName,
-=- 
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 1) IS NULL THEN '' 
               ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,1) END  AS ColName1,
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 2) IS NULL THEN '' 
               ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,2) END AS ColName2,
               CASE       
                       WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 3) IS NULL THEN '' 
               ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,3) END AS ColName3,
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 4) IS NULL THEN '' 
               ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,4) END AS ColName4,
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 5) IS NULL THEN '' 
               ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,5) END AS ColName5,
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 6) IS NULL THEN '' 
               ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,6) END AS ColName6,
      CASE       
                       WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 7) IS NULL THEN '' 
               ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,7) END AS ColName7,
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 8) IS NULL THEN '' 
               ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,8) END AS ColName8,
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 9) IS NULL THEN '' 
               ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,9) END AS ColName9,
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 10) IS NULL THEN '' 
               ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,10) END AS ColName10,
           CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 11) IS NULL THEN '' 
               ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,11) END AS ColName11,
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 12) IS NULL THEN '' 
               ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,12) END AS ColName12,
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 13) IS NULL THEN '' 
               ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,13) END AS ColName13,
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 14) IS NULL THEN '' 
               ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,14) END AS ColName14,
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 15) IS NULL THEN '' 
               ELSE INDEX_COL(u.name+'.'+QUOTENAME (o.name), i.indid,15) END AS ColName15,
      CASE       
                       WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 16) IS NULL THEN '' 
               ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,16) END AS ColName16
 FROM sysobjects o (NOLOCK)
        LEFT OUTER JOIN sysindexes i (NOLOCK)
               ON o.id = i.id
   JOIN sysusers u (NOLOCK) 
               ON o.uid = u.uid
 WHERE o.type = 'U' AND i.indid < 255
 AND o.name NOT IN ('dtproperties')
 AND i.name NOT LIKE '_WA_Sys_%'
-- because of SQL Server 7.0
 
GO
 
SELECT 'Showing All Indexes' AS Comments, I.* 
 FROM dbo.INDEXVIEW I
GO
SELECT 'Showing Tables with Missing Clustered index' AS Comments, I.*
 FROM dbo.INDEXVIEW I
 WHERE ClusterType = 'HEAP'
GO
SELECT 'Showing Tables with Missing Primary Keys' AS Comments,  I.*
 FROM dbo.INDEXVIEW I
        LEFT OUTER JOIN dbo.INDEXVIEW I2
               ON I.TableID = I2.TableID 
               AND I2.UniqueType = 'PRIMARY KEY'
 WHERE I2.TableID IS NULL
GO
SELECT 'Showing Possible Redundant Index keys' AS Comments ,  I.*
 FROM dbo.INDEXVIEW I
        JOIN dbo.INDEXVIEW I2
               ON I.TableID = I2.TableID 
               AND I.ColName1 = I2.ColName1 
               AND I.IndexName <> I2.IndexName
 ORDER BY I.TableName,I.IndexName
GO
SELECT 'Showing Possible Reverse Index keys' AS Comments ,  I.*
 FROM dbo.INDEXVIEW I
        JOIN dbo.INDEXVIEW I2
               ON I.TableID = I2.TableID 
               AND I.ColName1 = I2.ColName2 
               AND I.ColName2 = I2.ColName1 
               AND I.IndexName <> I2.IndexName
GO
DROP VIEW INDEXVIEW
GO

Sponsored Links: Is SQL the Center of your Universe?
SQLCentric is a comprehensive web-based network database monitoring and alert system. - brought to you by Pearl Knowledge Solutions, Inc.
http://www.pearlknows.com