SQLServerCentral Article

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:

  • A view gets created to proper format needed indexes information from related system objects within the database.
  • Then result sets will be generated to report possible anomalies.
  • Reports on all indexes on user tables within a database

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_recompile ‘Tablename’ 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:

  • Make sure all triggers are making use of “deleted” and “inserted” virtual tables using efficient joins instead of cursors.
  • Rewrite queries to use derived table style joins instead of sub-queries.
  • Capitalize on UNION statement (instead of big complex joins with complex filtering clauses) where possible.
  • Do not operate transformation on filtered values when possible.
  • Avoid selects in select lists.
  • Make sure the query performs well in parallel query resolution mode on multi-processor systems(see MAXDOP or sp_configure setting max degree of parallelism)

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

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (1)

You rated this post out of 5. Change rating

Share

Categories

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (1)

You rated this post out of 5. Change rating

Related content

SQLServerCentral Article

Indexed Views With Outer Joins

SQL Server 2000 has indexed views, which can greatly improve database performance. However there are a number of restrictions on building the view, including the restriction against outer joins. So how can this work? New author Jean Charles Bulinckx brings us a technique that can help you get around this restriction.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.5 (6)

You rated this post out of 5. Change rating

2005-05-19

23,063 reads

Technical Article

SQL Server Database Index Performance

When it comes to auditing index use in SQL Server databases, I sometimes get overwhelmed. For example, how to do you go about auditing indexes in a database with over 1,500 tables? While auditing a single index is relatively straight-forward, auditing thousands of them in multiple databases is not an easy task. Whether the task is easy or not, it is an important task if you want to optimize the performance of your SQL Server databases.

2005-01-18

3,860 reads

External Article

Clustering for Indexes

There is nothing spectacular about using indexes per say. However, on many occasions I have come across a variety of SQL coders that never consider validating that the index they think they are using is efficient or even being used at all. We can all put indexes on the columns that we think will be required to satisfy individual queries, but how do we know if they will ever be used. You see, if the underlying table data is constructed, contains, or is ordered in a particular way, our indexes may never be used. One of the factors around the use of an index is its clustering factor and this is what this article is about.

2004-12-23

3,412 reads