SQL Server Threshold of Write per X when considering Indexes? 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?


I do not think that there is a golden ticket measure to get the index writes &lt;> 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 &lt;> 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 &amp; 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:



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 &amp; 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.