Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Boost Your Performance - Bad Index Detection

By Andre Vigneau,

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
Total article views: 15409 | Views in the last 30 days: 10
 
Related Articles
BLOG

Quotename in SQL

Quotename() is a SQL Server String function. It accepts an input string of of up to 128 characters....

FORUM

Boost Your Performance - Bad Index Detection

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/colu T...

FORUM

index

index

FORUM

Index

Index

FORUM

Index

index

Tags
indexing    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones