October 4, 2012 at 6:01 pm
A basic question, i was analyzing an execution plan and found a keylookup using most of the CPU...So i tried to modify the current index to include the output columns of the keylookup so that i can avoid it, but i m not sure where else this index is being used....Does modifying this index by including a column, changes the performance of other queries???(I guess it may not decrease the performance because i am modifying the index only with included columns)
October 4, 2012 at 6:10 pm
You will slightly to heavily slow down any other query using that index, but that's not a bad thing depending on how large an item you stuffed in there.
At the leaf level of any non-clustered index, all included columns and necessary information to lookup the data off the clustered index is stored. The more data on the leaf, the more 8k pages the index needs to load.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 11, 2012 at 12:22 am
PradeepVallabh (10/4/2012)
i m not sure where else this index is being used....Does modifying this index by including a column, changes the performance of other queries
you can use sys.dm_dp_index_usage_stats dmv where you can see how frequently the mentioned index is being used then you can decide whether you can add the include part there or not
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply