Partitioned Aligned Indexes & Primary Key

Question

Partitioned Aligned Indexes & Primary Key

Welsh Corgi

SSC Guru

Points: 116520
More actions
November 19, 2015 at 9:06 am

#399353

I'm not clear in on Indexes on a Partitioned Table do I just use the Natural Key or do I include the portioning key (Begin_Date)?
When I create the Clustered Index or Non Clustered what do I specify?
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
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/

Viewing 12 posts - 1 through 11 (of 11 total)

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

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

Depends on whether you want the indexes partition-aligned or not. There are valid reasons to do both

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Thanks Gail.

Could you please provide me a few reason to do both?:unsure:

For better, quicker answers on T-SQL questions, click on the following...
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/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

What circumstances do you consider in making an Index aligned or not?

I have googled but I have not found a straight forward answer.

Thank you.

For better, quicker answers on T-SQL questions, click on the following...
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/

MadAdmin SSChampion Points: 11261 More actions · Answer 4

I think to do partition switching you need all indexes to be partition aligned.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

I think that partitioning the tables is not the best approach.

There are hundreds of Stored Procedures and embedded T-SQL in .NET Code.

It could be a disaster.

All I need to do is archive the tables via the SP that I wrote and I do not need to worry it will affect performance.

Edit: KISS

For better, quicker answers on T-SQL questions, click on the following...
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/

ScottPletcher SSC Guru Points: 100942 More actions · Answer 6

The main thing is to make sure the tables are properly clustered. Often that will give you good performance from the table even without archiving (or partitioning).

Partitioning is sometimes falsely credited with speeding up queries because during the process of partitioning, a better clustered index key(s) is(are) chosen, in order to provide the best partition breaks, and that new key is what drastically improved performance, not the partitioning itself.

For example, say a table is clustered on identity. When you decide to partition, you partition by date. Then, to keep indexes aligned, you also cluster first by date. Suddenly all table queries perform much better and you think partitioning did it. Not really. It was properly clustering by date that really gained the performance.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Thanks Scott.

The current indexes could be a lot better.

The main problem is that the tables are so large.

They have old data which needs to be archived

Another issue is that the queries that go up against them are not optimal.

I do not feel comfortable drastically changing the indexes when archiving speed things up.

For better, quicker answers on T-SQL questions, click on the following...
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/

ScottPletcher SSC Guru Points: 100942 More actions · Answer 8

You could also archive first and modify the indexes later.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

I would prefer to archive the database using code that I already wrote.

I do not see any value added in partitioning the table.

Another thing is that the current file groups are convoluted in the database that is to be archived.

Namephysical_name

PrismData_DataD:\MSSQL12.MSSQLSERVER\MSSQL\DATA\PrismData_Data.mdf

PrismData_LogF:\MSSQL12.MSSQLSERVER\MSSQL\Data\PrismData_Log.ldf

PrismData_FG1_0D:\MSSQL12.MSSQLSERVER\MSSQL\DATA\PrismData_FG1_0.ndf

PrismData_FG1_1D:\MSSQL12.MSSQLSERVER\MSSQL\DATA\PrismData_FG1_1.ndf

It is inconsistent as to what the current indexes are stored.

For better, quicker answers on T-SQL questions, click on the following...
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/

ScottPletcher SSC Guru Points: 100942 More actions · Answer 10

I'm not talking about partitioning, I'm talking about index tuning, particularly first selecting the best clustered index. Most tables don't really need partitioned. I partition mainly to allow older data to be page compressed and current data to be only row compressed or not compressed at all.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 11

Thanks for the information.

I have already created an Archive Database with a Lookup table used to control the batch size and the WHILE Loop.

Edit: Not sure to do with the existing file groups. It is a mess.

For better, quicker answers on T-SQL questions, click on the following...
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/