Paritioning key column as an included column or trailing index column

Question

Paritioning key column as an included column or trailing index column

Preethi S Raj

SSCommitted

Points: 1523
More actions
September 20, 2013 at 4:15 am

#282685

We know that,
SQL Server automatically adds the partition column to a secondary nonunique index as an included column if the CREATE INDEX statement does not already contain the partition column.
Is there is a performance gain by adding the partitioning column as a trailing index column instead of an included column? I am curious to know which is better from a performance perspective.
Regards..Preethi

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

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Preethi S Raj (9/20/2013)
SQL Server automatically adds the partition column to a secondary nonunique index as an included column if the CREATE INDEX statement does not already contain the partition column.

This is new to me. post any link you have for this.

Preethi S Raj (9/20/2013)
Is there is a performance gain by adding the partitioning column as a trailing index column instead of an included column? I am curious to know which is better from a performance perspective.

Here basic rule or feature of index will play role. IF there is need on ORDER BY , GROUP BY, ON clause , WHERE etc where you have filter on other column (existing columns of index ) PLUS this column also there in query's filterr part then defintely it will help.

INCLude column help in select columns part when there are lookups happeinig (Key lookup or RID lookup)

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

Preethi S Raj SSCommitted Points: 1523 More actions · Answer 2

Thanks for replying. Any inputs on the index storage level \ tree traversal in fetching the data?

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

Is the index stored on the partition scheme or not? It if its, the partitioning column is effectively the first column in the index.

If the index is stored elsewhere, the partitioning column is there only as a row locator. Whether it makes sense to add it as an index columns depends on your queries.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Preethi S Raj SSCommitted Points: 1523 More actions · Answer 4

Thanks for your feedback.

Is the index stored on the partition scheme or not?

<Reply> Yes it is a partition aligned index.

if its, the partitioning column is effectively the first column in the index.

<Reply> So does this partitioning column is added as an included column?

My goal is to compare the tree traversal cost

when the column is (a) an included column and (b) the last indexed column in a partition aligned secondary index.

Please share your thoughts.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Preethi S Raj (9/24/2013)
My goal is to compare the tree traversal cost
when the column is (a) an included column and (b) the last indexed column in a partition aligned secondary index.

Test and see? It's not hard to set up.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Bhuvnesh (9/20/2013)
Preethi S Raj (9/20/2013)
SQL Server automatically adds the partition column to a secondary nonunique index as an included column if the CREATE INDEX statement does not already contain the partition column.
This is new to me. post any link you have for this.

Books Online, the sections on partitioning nonclustered indexes.

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

Preethi S Raj SSCommitted Points: 1523 More actions · Answer 7

I did setup on my machine and ran a query which uses the partition aligned index in both cases. But the IO Stats is similar. Could you please suggest supporting commands ?

Also trying to analyse DBCC page command in both cases as explained by Kalen Delaney in the below links.

http://sqlblog.com/blogs/kalen_delaney/archive/2008/03/16/nonclustered-index-keys.aspx

http://sqlblog.com/blogs/kalen_delaney/archive/2010/03/07/more-about-nonclustered-index-keys.aspx