Table size and table partitioning on a single drive server

Question

Suppose I have a big table on a server with only one drive. The table may grow up to a half TB (hundred million rows). I think the purpose of the partitioning is mainly for maintenance purpose because of one drive.

At which size should the table be partitioned?

Answer 1

Kendra Little just released an excellent article on the topic: How To Decide if You Should Use Table Partitioning.

This is usually the first question people have: “Is my table big enough?” My response is: “Let’s talk about why you’re interested in table partitioning. What is the problem you’re experiencing?”

Go over the article linked, is very good. It even has pictures of Unicorns! Hopefully it will shed some light for you.

Answer 2

I'd question why you could have 500GB database with one drive.

Forget about partitioning for now: what about drives (LUNs or RAID arrays) for

• log files (LDF)
• OS
• SQL Binaries
• System databases
• Tempdb
• ...

Partitioning is not some magical silver bullet

Answer 3

In this situation, it can be useful if you want to, say, compress historic data that doesn't change, while leaving the current data uncompressed to maximize insert/update performance. I've done this in a couple of places on our server. You also have the option of different backup schedules across the different filegroups.

But if you're trying to improve overall performance, there will be much more effective things to try first.