Re: Odd query plan for view
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Wed, 29 Mar 2006 07:28:39 +0000 (UTC)
(JayCallas@xxxxxxxxxxx) writes:
So, a Clustered Index Scan is as bad as a Table Scan? In this case,
what is it actually checking? The LoadDate is not even used in the
query so I am not sure what it is scanning for. Is it just using the
scan to look up the rest of the row?
Let's say that you need to look up Michael Richardson in the telephone
book. Of course you open the book on R and quickly find him. You are seeking
the clustered index.
But say now that you are looking for someone whose first name is Jake,
and that he lives on Smallstreet, and you really need to find him. What
do you do? You read the phone book from start to end, that is you scan
the clustred index. The LastName, which is the key in the index is not
part of the search, but that is irrelevant.
As I already knew that the particular WHERE clause was bad, was I just
lucky all this time that the response time was good?
Bad is a little too strong a word. Problematic is more accurate.
The problem with a non-clustered index, is that if you get many hits,
and you for every hit you need to access the data page, you will do more
reads that you do, if you just scan the table from left to right.
Do query plans survive server reboots or restarting SQL?
No. The plan is in cache only, and could also disappear during run-time,
if the plan is aged out.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Odd query plan for view
- From: JayCallas
- Re: Odd query plan for view
- From: Erland Sommarskog
- Re: Odd query plan for view
- From: JayCallas
- Odd query plan for view
- Prev by Date: Re: Cursor looping versus set-based queries
- Next by Date: Re: Cursor looping versus set-based queries
- Previous by thread: Re: Odd query plan for view
- Next by thread: Re: Odd query plan for view
- Index(es):
Relevant Pages
|
