SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


findingdifference between full or partial index scan in graphical execution plan?


findingdifference between full or partial index scan in graphical execution plan?

Author
Message
WolfgangE
WolfgangE
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1307 Visits: 798
hi all,

many times when looking to graphical excution plans I see the operator "clustered index scan". The description says "scanning a clustered index, entirely or only a range". But how do I find out whether it is entirely or only a range scan?

Here's an example.
-- ==============================================
-- Step 1: create data table
-- ==============================================
set nocount on;

create table #TestIndexAccess ( col1 int not null primary key clustered ( col1 ) );

declare @counter int = 0;
while @counter <= 500000
begin
insert into #TestIndexAccess ( col1 ) values ( @counter )
set @counter += 1;
end


-- ==============================================
-- Step 2: querying and looking at execution plan
-- ==============================================
select min( col1 ) from #TestIndexAccess;
select count( col1 ) from #TestIndexAccess;

--drop table #TestIndexAccess;
go



The execution plans can be found in the attachment (did not know which file format is preferred so I uploaded two formats).

Both queries containt the clustered index scan, but how to determine the difference?
Of course there is a much thicker line for the second statement and of course I can have a look at the logical reads but this would not work for large tables.

Any ideas?

Thank's a lot, Wolf
Attachments
execution plan.jpg (13 views, 71.00 KB)
execution plan.sqlplan (9 views, 16.00 KB)
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99367 Visits: 33014
Since these are two different queries, you have to examine what they're doing in order to understand what the execution plan is representing to you. One is doing a MIN, the other is doing a COUNT. If you look at the properties of the MIN operation, you can see that the scan is an ordered scan. This means it's using the order of the values to do the scan. Which means it hits the first value and stops. That's why the estimate is only a single row (same as the actual). Logically, this answers your question. The other query requires accessing all the data and then performing an aggregation action (stream aggregate in this case) and it's reflected within the properties again, the scan is unordered and it returns everything.

Neither of these queries involves a predicate or other filtering mechanism, so seeks are not possible. To know what's happening with a scan with a predicate, you compare the predicate to the data. A predicate involving, for example, a function, means that all data in the table will be scanned. A predicate that returns more than a small percentage of rows will result in a scan (possibly) but will start and stop based on the predicate value. The plan itself only tells you how much data it thought it needed to access (estimated rows) and, with an actual plan, how much data it did access (actual rows). It's up to you to interpret the methods of access.

----------------------------------------------------
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)

Group: General Forum Members
Points: 227839 Visits: 46339
Typically a scan is a full scan (in the absence of TOP or other operators that give row goals). It's the seek that can be a single row or a range (up to the entire table)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


WolfgangE
WolfgangE
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1307 Visits: 798
Hello Grant Fritchey,

thank's a lot for your very detailled explanation. I'm still not sure if I understand the part I'm interested in most.

I know these queries had no predicate and I'm aware of the differences of index scans and index seeks. The problem I personally have is that many times I see index scan operations and many times I'm not sure if it scans the whole index or just a part of it.
If I use the following query:

select top 1000 * from #TestIndexAccess



I still have a clustered index scan but it is NOT an ordered scan. Still the first query needs only 3 logical reads. I don't think it did a full scan. Now how do you see the difference of this last query to the others?
If I have a table with several million of rows and the partial scan returns some 100 thousands of them I dont's see a possibility to determine which kind of index access happend.

Now, did I not understand all of your information or did I forget something else? :-)

Thank's a lot, Wolf
WolfgangE
WolfgangE
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1307 Visits: 798
GilaMonster (11/26/2012)
Typically a scan is a full scan ...


Hi Gila,
that's the point, it seems that it is NOT ALWAYS a full scan. The example shows it, using the min()-Function does not a full scan. Now, only at looking at the query plan, how do I know what it actually is?

And also the operator description says it could be not a full scan like seen in the attachment.
Attachments
operator description.jpg (11 views, 49.00 KB)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)

Group: General Forum Members
Points: 227839 Visits: 46339
weberharter (11/26/2012)
select top 1000 * from #TestIndexAccess



I still have a clustered index scan but it is NOT an ordered scan. Still the first query needs only 3 logical reads. I don't think it did a full scan.


No, it didn't do a full scan. The operator's the same, you can tell partially from the number of rows outputted and the reads done. It's still a scan, not a seek

p.s. It's not an ordered scan because there's no order by on your query, hence no requirement for the data to be in any particular order. Put an Order By <clustered index column> and you'll get an ordered scan.

Ordered scan = scan the index in order and preserve the order
Unordered scan = scan the index and don't worry about the order

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


WolfgangE
WolfgangE
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1307 Visits: 798
[b]GilaMonster (11/26/2012)
... you can tell partially from the number of rows outputted...


Yes, in this case I can. But we have many, many, many tables and I don't know the number of records of any table by heart. So any time I want to know if it's a partial scan I have to determine the full number of rows first?

The point is, many of our foreign keys reference to columns that are the clustered index so many joins result in clustered index lookups or scans. And that's where I want to know the difference. As the scan is done maybe multiple times I cannot even have a look at the logical reads as they are multiplied.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)

Group: General Forum Members
Points: 227839 Visits: 46339
weberharter (11/26/2012)
GilaMonster (11/26/2012)
Typically a scan is a full scan ...


Hi Gila,
that's the point, it seems that it is NOT ALWAYS a full scan.


Well if you selectively quote me, yes what you quote will be incorrect.

What I said was
"Typically a scan is a full scan (in the absence of TOP or other operators that give row goals)"

Hence, if you see a scan and there is not something that could stop the scan part way through (Top, Min, Max, Row_Number, a couple of other similar operators) it is a full scan.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)

Group: General Forum Members
Points: 227839 Visits: 46339
weberharter (11/26/2012)

The point is, many of our foreign keys reference to columns that are the clustered index so many joins result in clustered index lookups or scans. And that's where I want to know the difference.


Those will be full scans. Lookups are single row clustered index seeks.

As I said earlier, there's nothing in the operator itself that will tell you whether it's a full or a partial, but in the absence of TOP or other operators that give row goals, it is a full scan of the entire index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


WolfgangE
WolfgangE
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1307 Visits: 798
GilaMonster (11/26/2012)
What I said was
"Typically a scan is a full scan (in the absence of TOP or other operators that give row goals)"

Hence, if you see a scan and there is not something that could stop the scan part way through (Top, Min, Max, Row_Number, a couple of other similar operators) it is a full scan.



ah, ok :-) I thinkt the "in absence of"-part was the information I needed for understanding. So it seems that there is no "reliable" difference in the query plan. But with looking at the operators used in the quey text I should be able to "guess" what it is.

Thank's a lot for your help, I think I got it now :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search