• Home
  • Contact

John Sansom

SQL Server DBA Blog

  • About
    • The Blog
    • John Sansom
    • Contact
  • Ebook
  • Archives
    • Start Here
    • Popular Posts
    • All Posts
    • SFTW
  • Becoming a DBA
    • What it’s Really Like
    • Interview Tips
    • Certification
    • FAQ
  • Books
  • Resources
    • Blog Tools and Technology
    • UK Events Schedule
    • References & Resource Lists
  • Subscribe

Finding Bad Indexes Using SQL Server DMVs

February 24, 2009 by John Sansom 1 Comment

Today I want to show you how to go about finding bad indexes. SQL Server indexes are often promoted as the answer to all of our performance optimisation needs. It may then surprise you to hear that indexes can also be bad for performance.

“What are you yapping on about John” I hear you shout. Well let me explain….

You see, if the number of writes to a table (and its underlying indexes) is significantly greater than the number of reads, then it is possible that the indexes may actually be detrimental to performance overall.

This is because each time a write is made to a column that is involved in an index; a corresponding modification must also be applied to the indexes covering the column. Therefore, if the majority of activity is write activity, then it could be worth considering dropping or altering the indexes involved, in an effort to increase performance by reducing the overall amount of write activity.

I am not so crazy after all, well maybe just a little then.

Query to Identify Possible Bad Indexes

The following two queries can be used to assist you in your hunt to finding bad indexes (or rather potentially costly indexes):

--Possible bad Indexes (writes > reads)
SELECT  OBJECT_NAME(s.object_id) AS 'Table Name',
        i.name AS 'Index Name',
        i.index_id,
        user_updates AS 'Total Writes',
        user_seeks + user_scans + user_lookups AS 'Total Reads',
        user_updates - ( user_seeks + user_scans + user_lookups ) AS 'Difference'
FROM    sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
        INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON s.object_id = i.object_id
                                                       AND i.index_id = s.index_id
WHERE   OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
        AND s.database_id = DB_ID()
        AND user_updates > ( user_seeks + user_scans + user_lookups )
        AND i.index_id > 1
ORDER BY 'Difference' DESC,
        'Total Writes' DESC,
        'Total Reads' ASC ;
-- Index Read/Write stats for a single table
SELECT  OBJECT_NAME(s.object_id) AS 'TableName',
        i.name AS 'IndexName',
        i.index_id,
        SUM(user_seeks) AS 'User Seeks',
        SUM(user_scans) AS 'User Scans',
        SUM(user_lookups) AS 'User Lookups',
        SUM(user_seeks + user_scans + user_lookups) AS 'Total Reads',
        SUM(user_updates) AS 'Total Writes'
FROM    sys.dm_db_index_usage_stats AS s
        INNER JOIN sys.indexes AS i ON s.object_id = i.object_id
                                       AND i.index_id = s.index_id
WHERE   OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
        AND s.database_id = DB_ID()
        AND OBJECT_NAME(s.object_id) = 'AccountTransaction'
GROUP BY OBJECT_NAME(s.object_id),
        i.name,
        i.index_id
ORDER BY 'Total Writes' DESC,
        'Total Reads' DESC ;

Filed Under: Index Optimisation Tagged With: Bad Indexes, DMV Queries

About John Sansom

I’m a Microsoft Certified Master(MCM) of SQL Server. I’ve been working with database technology in a variety of flavors for over fifteen years. I absolutely love what I do and genuinely feel privileged to be a part of our tremendous technology community. Got a question about SQL Server or being a DBA? Ask me!

Popular Articles

  • Top 10 Free SQL Server Tools
  • Performing fast SQL Server delete operations
  • How to identify the most costly SQL Server queries using DMV’s
  • Top 10 Junior DBA Interview Tips
  • The Database Administrator’s Primary Responsibility
  • Your Road to Becoming a DBA: Laying a Strong Foundation
  • Top 5 SQL Forums
  • SQL Server Memory Configuration, Determining MemToLeave Settings
  • Script SQL Server Agent Jobs Using PowerShell
  • Using sys.dm_os_ring_buffers to Troubleshoot Connectivity Issues

Categories

  • Administration (38)
  • Blogging (8)
  • Customer Service (5)
  • Disaster Recovery (5)
  • DMVs (4)
  • Index Optimisation (6)
  • Interviews (1)
  • Link Posts (243)
  • Memory (2)
  • Performance Tuning (15)
  • Professional Development (70)
  • Reporting Services (5)
  • Reviews (1)
  • SQL Server Community (144)
  • SQL Server Tips (11)
  • SQLServerCentral Syndication (112)
  • SQLServerPedia Syndication (116)
  • Tools (7)

Copyright © 2023 · Santech Solutions Limited · Powered by the Genesis Framework · Privacy Policy