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:
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.
