Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLPradeep
Starting Member
3 Posts |
 Posted - 2011-04-25 : 13:18:49
|
| I have an execution plan with RID Lookup(Heap) and Key Lookup, and that query is experiencing some performance issues.....Does RID Lookup and Key Lookup causes performance issues??Thanks,PradeepPradeep |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-04-25 : 13:24:28
|
| Yes it does cause performance issue because the column list in the select statement if not present in the index then the index has to do a "lookup" to get those columns from table.PBUH |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-04-25 : 14:23:18
|
quote:
Originally posted by tkizer
Can you create a covering index?Tara Kizer
I am trying to understand this, why covering indexes could be a lot faster. Aren't Indexes pointers to actual Data Pages? If the Data Page is in the cache wouldn't the time saving be neglible? Whether SQL Server goes through the index then follows the pointer to the leaf node and retrieves the included column or SQL Server goes through the index then follows the pointer to the data page and retrieves the non-included column - I just want to understand, how there can be a big time saving? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-04-25 : 14:27:10
|
| Indexes have at their leaf level all the key columns, all the include columns and a pointer to the cluster/heap. If the column is part of the key or include then the lookup to the heap/cluster is not necessary.Key/rid lookups are expensive because they are done one row at a time. A key lookup is a single row clustered index seek, minimum 1 page read, often more. If SQL is doing lookups for multiple rows it does them one by one.--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-04-25 : 15:56:07
|
quote:
Originally posted by tkizer
I would recommend doing some performance testing to see this in action.
OK - Done - here are my results.Query: select C1, C2 from T1 where C1 = 'SC'T1 is 15.7 million rows/ Query returns 67,000 rowsI compare Index without includes - nonclustrered C1 - and Index with includes - nonclustrered C1 Include (C2)Without IncludesCost: 145Logical Reads Empty Cache: 415,000Logical Reads 2nd Runs: 373, 000Time Empty Cache: 38 secondsTime 2nd Runs: 500ms CPU/ 530ms Elapsed With Included IndexCost: 0.32Logical Reads Empty Cache: 250Logical Reads 2nd Runs: 250Time Empty Cache: 450msTime 2nd Runs: 30-60ms CPU/ 445ms Elapsed I don't want to make sweeping conclusions over 1 experiment. But in this example, the covering index gave me a huge improvement on a Clean Cache. But when the Data Pages resided in the cache, based on the elapsed time, I get a small improvment - only 15% - with the Included index. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-04-25 : 16:02:43
|
| I don't think 15% is a small improvement. That's a big improvement. When you've got an application that has numerous connections, that 15% adds up quickly amongst those concurrent queries.Your logical numbers are drastically improved with the covering index. Drastically.Also, I would argue that returning 67,000 rows is not a good test as it isn't representative of production queries.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
|
|
Performance tuning question