Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

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.

share|improve this answer

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

share|improve this answer
The system do have a small direct attached drive for OS and programs. The big drive is a SAN LUN. The database is sort of data warehouse type so the DBA didn't create an extra drive for logs. – u23432534 Mar 7 '12 at 17:45

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.