clustered vs non-clustered performance

microsoft.public.sqlserver.programming

8 Dec 2005 5:10 PM

Panos Stavroulis.

Hi,

In a previous post I've asked a question which in the end boiled down to
the differences between clustered & non-clustered indexes.

I am not using clustered indexes in general but people here suggested that I
should. Here is an example of a query that should have made a difference
after I've changed my indexes to clustered from nonclustered as people
suggested.

With all nonclustered...
Table 'REVIEW_PROCESS_STATUS'. Scan count 2, logical reads 527, physical
reads 0, read-ahead reads 0.
Table 'REP_REVIEW_ALLOCATION_REF'. Scan count 1, logical reads 17024,
physical reads 0, read-ahead reads 0.
Table 'REVIEW'. Scan count 2, logical reads 650, physical reads 0,
read-ahead reads 0.
Table 'REVIEW_TYPE'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
Table 'PROCESS_STATUS'. Scan count 3, logical reads 84, physical reads 0,
read-ahead reads 0.
Table 'ABN_MEMBER'. Scan count 2, logical reads 132, physical reads 0,
read-ahead reads 0.
Table 'ENTITY_TYPE'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
Table 'RISK_TYPE'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.

With tables I can create as Clustered cause data arrive in the right order.
That means table review, abn_member and entity_type were changed.

Table 'REVIEW_PROCESS_STATUS'. Scan count 2, logical reads 527, physical
reads 0, read-ahead reads 0.
Table 'REP_REVIEW_ALLOCATION_REF'. Scan count 1, logical reads 17024,
physical reads 0, read-ahead reads 0.
Table 'REVIEW'. Scan count 2, logical reads 658, physical reads 0,
read-ahead reads 0.
Table 'PROCESS_STATUS'. Scan count 3, logical reads 165, physical reads 0,
read-ahead reads 0.
Table 'REVIEW_TYPE'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
Table 'ABN_MEMBER'. Scan count 2, logical reads 106, physical reads 0,
read-ahead reads 0.
Table 'ENTITY_TYPE'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
Table 'RISK_TYPE'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.

That means the nonclustered is actually better by 16 logical reads,
basically the same performance. I take logical reads as the best way to judge
performance. Someone mentioned that doing insertions with clustered indexes
is better cause it knows where to insert do you know by how much?

Also do you know what happens if the size of a table is over 8K > size of
page and the index is clustered, does this create any issues with performance?

Thank you,

Panos.

P.S the old post was here...
http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.programming&mid=154df24f-1747-4aea-9c1d-e45330ee0f81

8 Dec 2005 7:14 PM

David Gugick

Panos Stavroulis. wrote:

> Hi,
>
> In a previous post I've asked a question which in the end boiled
> down to
> the differences between clustered & non-clustered indexes.
>
> I am not using clustered indexes in general but people here suggested
> that I should. Here is an example of a query that should have made a
> difference after I've changed my indexes to clustered from
> nonclustered as people suggested.
>
> <SNIP>

Using logical reads is only one metric to measure performance. CPU would
be another very useful metric. Your statement about changing indexes
from non-clustered to clustered has me worried. Firstly, you can only
have one clustered index per table. So either your tables only have a
single index on each or you somehow chose which index to change.

I'm in the camp that believes there's almost always a compelling reason
for each table to have a clustered index. But there are many
considerations you have to take into account before choosing which
index, if any, is best for this. Have a look here for some good
information:

http://www.sql-server-performance.com/clustered_indexes.asp
http://www.sql-server-performance.com/nonclustered_indexes.asp

--
David Gugick
Quest Software
www.imceda.com
www.quest.com

8 Dec 2005 8:41 PM

Greg Linwood

Hi Panos

Non-clustered indexes have the advantage of being able to be specifically
tailored to queries (having exacctly the columns required by the query, no
more, no less). This is commonly refered to as "covering" the query.

From a performance tuning perspective, Clustered indexes have the
dis-advantage of always having EVERY column in the table in their leaf
index. So, unless your query actually requires all columns (eg, select
*...), any i/o performed against the leaf pages in a clustered index
involves reading data that is not required for the query.

Hence, its common to see i/o against non-clustered indexes be less than
against clustered indexes.

I generally use clustered indexes to manage physical database maintenance
issues rather than performance tuning.

Regards,
Greg Linwood
SQL Server MVP

Show quote

"Panos Stavroulis." <PanosStavrou***@discussions.microsoft.com> wrote in
message news:09D211F2-EC83-4410-A129-4FDA5EDE6050@microsoft.com...
> Hi,
>
> In a previous post I've asked a question which in the end boiled down to
> the differences between clustered & non-clustered indexes.
>
> I am not using clustered indexes in general but people here suggested that
> I
> should. Here is an example of a query that should have made a difference
> after I've changed my indexes to clustered from nonclustered as people
> suggested.
>
> With all nonclustered...
> Table 'REVIEW_PROCESS_STATUS'. Scan count 2, logical reads 527, physical
> reads 0, read-ahead reads 0.
> Table 'REP_REVIEW_ALLOCATION_REF'. Scan count 1, logical reads 17024,
> physical reads 0, read-ahead reads 0.
> Table 'REVIEW'. Scan count 2, logical reads 650, physical reads 0,
> read-ahead reads 0.
> Table 'REVIEW_TYPE'. Scan count 1, logical reads 1, physical reads 0,
> read-ahead reads 0.
> Table 'PROCESS_STATUS'. Scan count 3, logical reads 84, physical reads 0,
> read-ahead reads 0.
> Table 'ABN_MEMBER'. Scan count 2, logical reads 132, physical reads 0,
> read-ahead reads 0.
> Table 'ENTITY_TYPE'. Scan count 1, logical reads 1, physical reads 0,
> read-ahead reads 0.
> Table 'RISK_TYPE'. Scan count 1, logical reads 1, physical reads 0,
> read-ahead reads 0.
>
>
> With tables I can create as Clustered cause data arrive in the right
> order.
> That means table review, abn_member and entity_type were changed.
>
> Table 'REVIEW_PROCESS_STATUS'. Scan count 2, logical reads 527, physical
> reads 0, read-ahead reads 0.
> Table 'REP_REVIEW_ALLOCATION_REF'. Scan count 1, logical reads 17024,
> physical reads 0, read-ahead reads 0.
> Table 'REVIEW'. Scan count 2, logical reads 658, physical reads 0,
> read-ahead reads 0.
> Table 'PROCESS_STATUS'. Scan count 3, logical reads 165, physical reads 0,
> read-ahead reads 0.
> Table 'REVIEW_TYPE'. Scan count 1, logical reads 1, physical reads 0,
> read-ahead reads 0.
> Table 'ABN_MEMBER'. Scan count 2, logical reads 106, physical reads 0,
> read-ahead reads 0.
> Table 'ENTITY_TYPE'. Scan count 1, logical reads 1, physical reads 0,
> read-ahead reads 0.
> Table 'RISK_TYPE'. Scan count 1, logical reads 1, physical reads 0,
> read-ahead reads 0.
>
> That means the nonclustered is actually better by 16 logical reads,
> basically the same performance. I take logical reads as the best way to
> judge
> performance. Someone mentioned that doing insertions with clustered
> indexes
> is better cause it knows where to insert do you know by how much?
>
> Also do you know what happens if the size of a table is over 8K > size of
> page and the index is clustered, does this create any issues with
> performance?
>
> Thank you,
>
> Panos.
>
> P.S the old post was here...
>
> http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.programming&mid=154df24f-1747-4aea-9c1d-e45330ee0f81
>
>