CODEWITHSUNDEEP
sql-serversql-server-2008partitioning
Boppity Bop
Boppity Bop

Reputation: 10463

How to store partitions with latest data on one disk and historical data on another?

I am going to partition my data by date.

Can I separate latest data from historical data on daily basis?

I.e. I want rolling date window - say latest 30 days of data on one physical disk and the rest of them (older than that) on the another physical disk.

How do I roll it given the date data comes once in the morning?

Upvotes: 0

Views: 894

Answers (2)

RC_Cleland
RC_Cleland

Reputation: 2294

Partitioning on two disks is not going to solve your performance issue because of the extra IO which will be involved in moving the data between disks, removing data from partition, updating indexes and the labor involved in verifing all data has been moved.

Is your data clustered on a date column? You state that 80% of the queries are for the last two days so if your data was clustered on date then the IO would be essentially a sequential read.

Indexes, filtered or partitioned, will only point to the cluster or heap rows which contain the data and will result in random IO if the data is not in date sequence. An index ordered and filtered on date which includes all columns of the table will be a good performance boost if you cannot cluster the table on date.

If you cannot make changes to the table structure and the table is not clustered on date I suggest:

  • Move all table data to one set of disks.

    Use the other set of disk as index only

  • Create a filtered index which spans a date range of three days. Include all columns of the table
  • Create a filtered index which spans a date range of the next three days. Include all columns of the table
  • Create a filtered index which spans a date range of the next three days. Include all columns of the table
  • Six days after you created the first index drop that index and create two filtered indexes which spans a date range of three days. Include all columns of the table
  • Repeat dropping and creating indexes.

  • Automate this process with SQL Agent

    Monitor the index creation with alerts

The only way to prove whether any change results in an improvement is to have a benchmark of before and after.

Upvotes: 1

Andrew
Andrew

Reputation: 27294

Absolutely possible, when you define the partition schema, you indicate which file group each partition within that schema resides on.

The issue will come when you are rolling the data and need an entire partition moved from one disk to another - that operation will have to be performed carefully and depending on the data quantities could take some time.

Given only 2 physical disks are being mentioned, what is driving the need for that split?

Upvotes: 0

Related Questions