I recently tuned some queries by adding an index and am now trying to asess if the overall situation on that table got better.
I have captured some metrics from sys.dm_db_index_usage_stats. The first chart shows the difference between the number of overall user_seeks (scans, lookups and user_updates (writes)) for that specific new index. The second chart simply subtracts user_updates from all the reads on that index. Looking at those figures alone I can clearly see, that the index is more written to than actually read from.
However, this index particularly helped two monitoring queries hitting the server every minute 24/7. Before I added this index those queries did a clustered index scan. By looking at the metrics for the clustered index I can clearly see that the number of scans dropped by the same rate the new index now gets seeked (720 seeks per 6 hour window and thus 2.880 seeks (or former clustered index scans) per day.
Thank you for being patient and reading all this....now to my question. In what way am I able to calculate the volume of MB writes to my new index are causing. I'd like to do a comparison between the IO (in MB) with all that table scans and the IO afterwards with seeking and maintaining the new index.
So that's the calculation I did:
Read IO Table Scan 79.977 Reads / 128 = 765,45 MB
-Read IO Index Seek 15 Reads / 128 = 0,12 MB
= Read IO Savings per query 765,33 MB
Read IO savings per day 765,33 MB * 2.880 = 2.152 GB
-Writing new index per day 26.000 writes * 49 Byte per row written = 1.274.000 Byte
Overall benefit per day 2.152 - 754.000/(1024^3) = 2.152 - 0,0011= 2.151,99 ?????
My savings on read IO are quite straightforward as I collected this information during query tuning. However, how can I calculate (or do an educated guess) on the IO overhead for writing on that index? I know that I am doing approximately 26.000 writes per day. The index has the following structure:
[2 KEYS] column1 {datetime 8}, column2 {datetime 8} [3 INCLUDES] column3 {bit 1}, column4 {bigint 8}, column5 {int 4} [SECRET COLUMNS (Clustered Key)] [3 KEYS] column6 {bigint 8}, column7 {bigint 8}, column8{int 4}
So I guess the leaf level record has 49 byte (summing up all that numbers). Has it? How could I guess the intermediate levels?
Anyhow...(more on the direction of the "educated guess") in your experience does that really matter as I am saving me from scanning the table anyway and doing this in a regular fashion?
Thank you very much for reading and sharing your insights into query tuning profit calculation with me.
check out the dmv sys.dm_db_index_operational_stats, it will provide information about how many pages SQL Server is having to move around and read in order to fulfill queries/updates. This gives a better view of actual IO. Also take a good look at the columns on Waits, it will let you know if index maintenance is causing problems for other queries.