Include Index the same as clustered index

Question

Post reply

Include Index the same as clustered index

mec

SSC Journeyman

Points: 96
More actions
April 14, 2010 at 2:55 am

#223303

This is actually on a 2k8 box, but I suspect that makes little or no difference..
I've got a table that looks like:
CREATE TABLE [dbo].[SomeTable](
[ID] [bigint] NOT NULL,
[Col01] [datetime] NULL,
[Col02] [int] NULL,
[Col03] [nvarchar](255) NULL,
[Col04] [bigint] NULL,
<more columns..>
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
In total it has 21 columns, total row size is 2.3k.
The table is used in a statement, joined by its ID to another table. Columns 1 - 4 are in the select list, so the tuning advisor reccomends the following:
CREATE NONCLUSTERED INDEX [_dta_index_SomeTable_7_1867973831__K1_10_12_15_21] ON [dbo].[SomeTable]
(
[ID] ASC
)
INCLUDE ( [Col01], [Col02], [Col03], [Col04]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
I understand how the include stuff works, but what I don't understand is why it reccomends this index ?
I always thought the reason you can't use include on clustered indexes was that it brings no benefit, as essentially you read the table when reading a clustered index anyway ?
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

i dont think this suggested index is required here as clustered index itself fullfil the requirement

and also we should not blindly trust tuning advisor.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

mec SSC Journeyman Points: 96 More actions · Answer 2

I agree, I just wondered why it suggested it.

Thanks!

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

The index tuning wizard works according to the queries that were presented to it. If you have a work load file that has a work load that simulate the real work, then the chances that the recommendations are beneficial will increase. If on the other hand you ran only a single query, then chances are that you’ll get some recommendations that are not beneficial.

Regarding the recommendation that you got - Most chances are that the reason for this recommendation is the average size of the full record compare to the average size to the columns that are used in the suggested index. For example suppose that the average size of the full record is 6000 bytes. This means that on each page you have only one record. If the average size of the all the columns in the suggested index is 50 Bytes, then on a page you can have around 160 records. Now let’s suppose that in your query you want to get 1000 records according to a range that is based on the ID column. If the clustered index will be used, the server will have to read about 1000 pages. If the suggested index will be used, the server will have to read about 7 pages. If you have many queries like that, then such index can be beneficial. If you have very few queries like that, then it won’t be beneficial.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 4

mec (4/14/2010)
I always thought the reason you can't use include on clustered indexes was that it brings no benefit, as essentially you read the table when reading a clustered index anyway ?

The recommended index here is a NON clustered index with the included columns. So, for the reasons mentioned in the previous post, using the recommended index could make a huge difference in performance.

Rob Schripsema
Propack, Inc.

mec SSC Journeyman Points: 96 More actions · Answer 5

I realised it was a non clustered index, I was just surprised that with only three records per page (row size is 2.3k) and a clustered index on the same column that it would see a benefit from the reccomended non clustered index.

I can understand what Adi says if there were many rows her page, just surprised its still the case with such a low number of rows per page.

Stefan_G SSCertifiable Points: 6609 More actions · Answer 6

mec (4/15/2010)
I realised it was a non clustered index, I was just surprised that with only three records per page (row size is 2.3k) and a clustered index on the same column that it would see a benefit from the reccomended non clustered index.
I can understand what Adi says if there were many rows her page, just surprised its still the case with such a low number of rows per page.

But in this case, the full table has a rowsize of 2.1k, giving only 2-3 rows per page.

The index has a rowsize of 28+sizeof(Col03), I guess this could be something like 150 bytes average. giving over 50 rows per page.

So, a scan over the recommended index would need to read about 20 times less pages than a scan over the full table.

Depending on the queries it is very possible that the index can make a huge difference.

So I agree with Adi.

/SG