Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Execution Plan Expand / Collapse
Author
Message
Posted Thursday, April 3, 2014 4:28 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 1:05 PM
Points: 435, Visits: 443
Hi,

I saw Index scan in execution plan. The table has primary key. We are selecting that PK data based on where condition by using and & and clauses on the remaining columns.

I have the first where clause columnn is integer datatype. SO I created non-clustered index on that column. Now clustered Index scan become Index seek and key lookup.

Which is better scan or creating index and change to key lookup? please let me know
Post #1558300
Posted Thursday, April 3, 2014 4:34 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 218, Visits: 1,861
Why dont you add the column(s) in the key lookup to your index as included columns ?
This will most likely still allow an index seek and remove the key lookup altogether.
Post #1558301
Posted Thursday, April 3, 2014 4:58 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 1,194, Visits: 2,213
Key lookup is bad, instead you can avoid this by using a covering index by including all those referred columns in the index itself.
--
SQLBuddy
Post #1558311
Posted Friday, April 4, 2014 3:19 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:52 AM
Points: 156, Visits: 613
It is true that Key Lookup can be avoided by converting your index to covering index. But, you should be careful! If you include too much columns you will actually create a copy of your table. If you do this several times for several indexes you can end up with index size which is more time larger than the table itself. And performance for DML queries against your table can be dramatically reduced.

So, just simply adding included columns in an index to avoid Key Lookup is not always a solution. You can use it for to improve a query which is very frequently used and if you don't need to include more than 2-3 columns.


___________________________
Do Not Optimize for Exceptions!
Post #1558401
Posted Friday, April 4, 2014 3:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:01 AM
Points: 42,487, Visits: 35,555
sqlbuddy123 (4/3/2014)
Key lookup is bad, instead you can avoid this by using a covering index by including all those referred columns in the index itself.


Key lookup might be bad. You should evaluate and decide whether or not the key lookup is a problem. If it is, then and only then should you look for solutions



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1558413
Posted Friday, April 4, 2014 5:52 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 15,558, Visits: 27,932
If you have the clustered index key value in the WHERE clause and you're still getting a scan, the suggestion is that you must have one of two situations, your statistics are badly out of date on that index, so it doesn't appear useful to the optimizer, or, you have some type of function on the column that is causing the scan. If you can post the query we might be able to help.

But, your current situation, with a key lookup instead of a scan, might be better. How fast is the query running and how many reads are you seeing now?


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1558452
Posted Monday, April 7, 2014 9:29 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 1:05 PM
Points: 435, Visits: 443
I am sorry. I couldn't able to compare the timing.
Post #1559341
Posted Monday, April 7, 2014 10:56 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:06 PM
Points: 3,865, Visits: 7,130
Undo whatever you did so you can run the code below to get a baseline (as Gail and Jeff stated, your keylookup may not be a bad thing - you need to test it). Try running:
SET STATISTICS IO ON;
exec [query or your procedure here]
SET STATISTICS IO OFF;

Go to this wonderful site and paste the output into the window and click the magic button to view your reads...

Make your desired index changes (or remove any functions, etc in your WHERE clause predicates), then run the code again.

Go back to the website above, and compare your reads

If timing is an issue, add TIME to the SET statement above (ex:)
SET STATISTICS IO, TIME ON;
exec [query or your procedure here]
SET STATISTICS IO, TIME OFF;



______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1559361
Posted Sunday, April 13, 2014 9:44 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 1:05 PM
Points: 435, Visits: 443
Hey,

Providing the output in to that website means I am breaking the company rules right
Post #1561315
Posted Sunday, April 13, 2014 10:44 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:06 PM
Points: 3,865, Visits: 7,130
?

It's just the output of reads...in a more readable format, there's no actual "data"...

Hardly something to be worried about.


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1561321
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse