Partition Table by Year

Question

Post reply

Partition Table by Year

Viewing 3 posts - 16 through 18 (of 18 total)

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

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

EXEC sp_SpaceUsed tblCall

namerows reserved Data index_size unused

tblCall1296410 19254232 KB 13406168 KB 3424264 KB 2423800 KB

Number of records archive

11,80,873

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/

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 2

I'll also add that the people that want to do the partitioning for performance are making a big mistake. Partitioning doesn't improve performance in almost every case. Proper indexing and query design does. You've already found that out in the performance improvements you've made.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 3

Welsh Corgi (1/19/2016)
EXEC sp_SpaceUsed tblCall
name rows reserved Data index_size unused
tblCall1296410 19254232 KB 13406168 KB 3424264 KB 2423800 KB
Number of records archive
11,80,873

With 19GB in tblCall, I'll say that it's starting to get to the point where partitioning could be of some help for reduction in backup time but only if you can make the old years "read only".

With that in mind, let me ask the question again... are you saying that they update CDRs that were made in 2014 even at this late date?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)