Partition Table problems with Indexes

Question

Partition Table problems with Indexes

CrazyMan

SSChampion

Points: 12560
More actions
November 12, 2010 at 5:58 am

#226258

Hi All
I am trying to create a partition table, we have a table with a column ID and a column for datetime, before Partition the table has got a clustered primary key index based on the column ID, partition is been done based on datetime column which by default becomes clustered, then I have made ID as a Non-clustered unique primary key.
when i run the same query with a table before partition and table after partition i get different execution plan, Please find the attached , This is a just a sample table, I cant imagine if i run this SQL with a billion rows.
How can i make this run more efficiently, please advise
Thanks for your help
🙂

Viewing 4 posts - 1 through 3 (of 3 total)

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

CrazyMan (11/12/2010)
I am trying to create a partition table, we have a table with a column ID and a column for datetime, before Partition the table has got a clustered primary key index based on the column ID, partition is been done based on datetime column which by default becomes clustered, then I have made ID as a Non-clustered unique primary key.
when i run the same query with a table before partition and table after partition i get different execution plan, Please find the attached , This is a just a sample table, I cant imagine if i run this SQL with a billion rows.
How can i make this run more efficiently

A change of execution plan is a given in such an scenario, physical structure of the table and indexes has changed therefore you can expect optimizer to choose a different execution plan.

I would trace-test both versions of the query and compare I/O.

In regards to how to change the execution plan... hints are the tool of choice.

Question... have you gathered fresh performance statistics after partitioning the table?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

CrazyMan SSChampion Points: 12560 More actions · Answer 2

Thanks Paul , yes this are new execution plans, with updated stats, can you tell me what difference will it make for an SQL if it takes a clustered and a non clustered index into account, I mean in terms of execution . since I want to implement this to a table with a billion records and this test table has only got 200K records

🙂

nigelrivett SSCertifiable Points: 5362 More actions · Answer 3

Previously your clustered index was unique and the leaf level would be the data page.

Now you have the date as a clustered index.

The unique index will implicitely include the date and to access the data page will do it via the clustered index.

This means that the index will be larger (increased by the date column) and also need an extra read to fetch the data page.

Both of these means it is less efficient and it will be more likely to scan the table to access a lot of data.

If you have that many rows then you won't be returning many of them presumably so you should probably look at creating aggregate tables from the older more static data to service the queries. Then you would access the aggregate tables for most of the data and only access the detailed data for recent (last two months?).

Cursors never.
DTS - only when needed and never to control.