March 1, 2011 at 11:18 am
I had an index seek costing 68% and key lookup tied to it which cost 4% - I was seeing a total of 84 logical reads.
So I added the 3 fields as an INCLUDE to the 68% index to remove the key look - and my reads jumped from 84 to 115.
I've never seen the removal of a Key Lookup increase the logical read count - any ideas?
Doug
March 1, 2011 at 11:28 am
Makes sense if the # of pages in the index increased a lot. Makes the seek much costlier where the key lookup wasn't that much of a big deal.
P.S. 84 page read is not a big deal... unless that query runs 1000 times / sec.
March 1, 2011 at 3:31 pm
Ninja's_RGR'us (3/1/2011)
Makes sense if the # of pages in the index increased a lot. Makes the seek much costlier where the key lookup wasn't that much of a big deal.P.S. 84 page read is not a big deal... unless that query runs 1000 times / sec.
I've got to agree with the Ninja here. Have a look at the data size for the 3 include columns and what's the ratio between the columns of the old and new index? This will give you an idea of the change in the leaf level pages. You can use the DMV's related to physical index usage to see what's going on there.
Even though the include columns are only added on the leaf level, if these are large char columns you may doing more harm than good.
I also go with the PS, and I'd probably not be spending too much time trying to cut the 4%, what's taking the other 28%?
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
March 1, 2011 at 3:42 pm
Following on my previous post, look at this DMV.
dm_db_index_physical_stats(database_id,object_id,index_id,partition_number,mode) -- Function
Here's a typical example to run before and after changing an index.
select database_id, object_id, index_id, partition_number, index_type_desc,
alloc_unit_type_desc, index_depth, index_level, avg_fragmentation_in_percent,
fragment_count, avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent, record_count, ghost_record_count
from sys.dm_db_index_physical_stats(db_id('scratch'),OBJECT_ID('ReplDemo'),2,NULL,'DETAILED')
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply