March 9, 2015 at 12:41 pm
I have i table which has 1 clustered index and 8 nonclustered indexes.I want to include a column on one of the NC index(or maybe create new NC index) to increase performance on some reports.There are about 5000 inserts into this table a day.The NC indexed column that i want to modify has 4 distinct values.My question is when there is an insert what happens to NC indexes that don't get new values?.are they getting updated too?
March 9, 2015 at 4:46 pm
Checkout sys.dm_db_index_operational_stats to see what indexes are getting updated and used.
All indexes will get updated and sql will need to do the work during an insert to find where in the NJC index the new row goes and shift pointers around.the more indexes the more work sql has to do on inserts and updates so use that dmc to work out what indexes are useful.
March 9, 2015 at 6:47 pm
Thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply