Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

3 Answers

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
add comment

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
add comment

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
add comment

Your Answer

 
discard

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.