Performance optimization on huge data

Question

Performance optimization on huge data

Viewing 5 posts - 16 through 19 (of 19 total)

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

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 1

/* Depends what the key is. If it's bigger than the clustering key or the index is unique, sure. If it's unique (ie the clustering key isn't needed at the intermediate levels) and the key is smaller than the cluster, it should be the same size or slightly smaller. Leaf level'll be the same size as the leaf of the cluster.

*/

Gail/Grant,

'Leaf level should be same size when we have a unique Non Cluster index' ?

I was just thinking NC leaves should be slightly bigger because clustered index is also part of the included columns

( Original poster claims all columns have been included which means cluster as well )

So i felt it should be

NCIndex key + Included column( which includes cluster) + cluster key( As for unique NC index keys

are left out only at intermediate and retained at leaf).

I did a quick test, but to my surprise I found the AVg row size of leaf of NC to be 3 bytes less than

the cluster's index consistently( I even made NCindex size bigger. Cluster's rows were bigger by 3 bytes always). I used index_physical_stats dmv to check.

Am I missing something..

Please post your comments/results after testing. Thanks .

Regards,
Raj

http://Strictlysql.blogspot.com

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

arr.nagaraj (12/4/2009)
So i felt it should be
NCIndex key + Included column( which includes cluster) + cluster key

SQL's not stupid enough to store a column in an index twice.

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

GilaMonster (12/4/2009)
arr.nagaraj (12/4/2009)
So i felt it should be
NCIndex key + Included column( which includes cluster) + cluster key
SQL's not stupid enough to store a column in an index twice.

That's true and I hadn't thought about it. You may be right. It might not ever be larger... I still want to set up some tests. need time.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

Grant Fritchey (12/5/2009)
I still want to set up some tests. need time.

I haven't got too much on my schedule next week. I'm sure I can find a couple hours.

I think it can be possible to get the NC bigger, make it non-unique (so that the clustering key's at all levels), have the clustering key itself quite small and the NC index key massive. The leaf level shouldn't be bigger, the non-leafs will.

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

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 5

In my (Quick) tests yesterday,in such a case, clearly the non leaves of NC Index were bigger than Cluster's.

But Leaves of NC were always smaller.

In another quick test, I managed to make overall NC bigger than Cluster.

My unique cluster was on a int column.

NC was a char(890). All other columns as included columns.

Infact, NC had more intermediate levels as the table was growing and number of pages at intermediate

levels alone was increasing.

Regards,
Raj

http://Strictlysql.blogspot.com