We are working on trying to reduce the high number of IO waits on one of our production servers, a large percentage of which appears to come from one of our applications (based on the queries that have the highest accumulated physical reads, other investigations we have done).
The application writes a large amount of data into a holding table (kind of like a temp table), then runs various queries to normalize (distribute the data into smaller tables with specific data, say one table for sales info, one table for invoices/sales info, one for employee info, one table for customer info, addresses, etc.) and partitions the data (into a month/year partition, i.e., Invoices_Oct12, Invoices_Nov12, etc.).
I realize this is one of those "it depends" kind of questions, but I need to get a sanity check before I commit these changes:
One of the stored procedures (the one that normalizes the data) runs 18 individual queries as part of the normalization. When reviewing the execution plans, each query produced a "missing index" recommendation. Just for testing, I applied all of the recommendations (they just go on the holding table, not the normalized tables receiving the final data). The overall query time does not change significantly (nature of the inserts?), but the CPU time, scan counts, logical and physical reads definitely go down (in some cases, 94% improvement). However, this comes at the expense of the indexes consuming more space than the data (3:1 ratio). Question:
would it make sense to apply the indexes and live with the higher space consumption (which is kind of temporary and should not really increase over time) versus the CPU and read performance?
I am thinking the answer in this case is YES, but I would be interested in other people's opinions.
The application does bulk inserts, so I do not believe the initial inserts into the holding/temp table would be affected (still to be tested).
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)