0

I am not entirely sure if this is even a question worth asking. However, I was curious as if there was some sort of standard, or specifically a metric, that some more seasoned DBA's use to gauge whether or not an index would be worth adding based on the general activity with the table. I mean anecdotally, yes we all understand tables that get written too frequently shouldn't have too many indexes and the more you have the bigger the issue is/can be. However, I was curious as to whether or not there is a generic standard which can be approach using some sort of formula?

I know through my research there's a lot of "it depends answer" because all environments are not the same, not too mention all infrastructures differ as well. I was hoping one of you smarter individuals would be so nice as to bestow some wisdom on me.

  • To add to it, I just assumed that there was some sort of "sweet spot" that could be evaluated by looking some numbers. How likely/difficult is it to generate that information? – Doug Coats Mar 25 at 20:28
  • 1
    Is this a Ferrari or a Lawn Tractor? – Max Vernon Mar 25 at 21:20
  • A similar question has several answers here: Is it bad to have index space larger than data space?. I don't want to mark this as an exact dupe, but maybe you can get a good enough answer there? – Erik Darling Mar 25 at 21:29
  • OLTP, OLAP, batch processing? What db server are you using? What version? What's your hardware HDD, SSD, SAN, NAS, stone tablets - you get the point! Impossible to answer this! – Vérace Mar 27 at 14:04
2

To add to it, I just assumed that there was some sort of "sweet spot" that could be evaluated by looking some numbers. How likely/difficult is it to generate that information?

I do not think that there is a golden ticket measure to get the index writes <> index reads.

One of the reasons for this is that the sys.dm_db_index_usage_stats dmv explained below might show:

10 USER_SEEKS vs. 200000 USER_UPDATES.

When looking at this difference the knee jerk reaction would be to say remove this index as it is not used that much.

But these 10 index seeks might be making a very important query go from 5 minutes to 5 seconds. While these 200000 user inserts might be single insert statements on a very big table.

These inserts might even have happened during non production hours for all we know.


Things to look at

This is not a complete list, simply listing a few things you could look at

Index Usage stats

The index usage stats from the sys.dm_db_index_usage_stats dmv Query source (Greg Robidoux) to get a basic idea of the SEEKS / SCANS / LOOKUPS vs. USER_UPDATES.

I would use this one to check if the index is even used and to get a basic idea of the READS <> WRITES

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES 
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 
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1;

Note that these counters reset each time you restart SQL Server.

Datatypes & Table Size (+ Future growth)

After this, I would look at the datatypes of the key columns / included columns you want to create the index on + the total Data size / Index size to get a general idea of the amount of data you are adding to your database.

An example:

enter image description here

The total data space of my table = 6MB, and I only have 0,1MB index space.

In most cases, indexing this table to remove a query that gets executed a lot and uses too much resources would be a no brainer.

Most of the time, a query you need indexed would be on bigger tables.

In these cases it comes down to:

  • The data you are indexing (INT?, VARCHAR(100)?, datetime?,...)
  • The size of your table
  • The queries / access ratios on the clustered index / heap table.

2 thirds of these you can easily find, the other one you can find with the DMV and by monitoring the queries executing on this table.

After validating this, and proceeding

These two points are not the only ones to look at, there is much more than this.

Another important part is to test & monitor

Most of the time, you will not be 100% sure when applying indexes, it comes down to using common sense and looking at the most obvious parts. The unexpected can always happen.

  • I had a feeling there wasnt going to be a fancy "golden mean" or whatever. I was just kind of hoping that there was :(. Thanks though, appreciate it – Doug Coats Mar 26 at 13:52
  • @DougCoats no problem! Would be helpful if it ever exists :) – Randi Vertongen Mar 26 at 14:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.