Server Fault is a question and answer site for system and network administrators. 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

I have 500GB of database stored in SQL Server. Because it has too big a database, it takes time to update and insert data. Now the case is that I want to partition the data. So which partitioning techniques should I use? Or is there any other technique which will improve my performance?

share|improve this question
This might help you a bit:… – Joseph Kern Sep 18 '09 at 10:00

The performance issues aren't going to be with the database as a whole, they'll be with individual tables and queries.

You want to identify what is slow first. Which tables, and why. Is it I/O related? If so, partitioning might not help if you are stuck on the same I/O subsystem. If it's page splits, then examine that issue. look to see if your clustered index or fill factor is set in a way that does not optimize performance.

Partitioning is an Enterprise onyl feature. Is that what you have? If not, go down another route.

Likely you can tune the queries, even INSERTs, to get better performance.

share|improve this answer

It's impossible to tell without knowing your database schema.

How many tables? How big? How much normalized? What about indexes?

There really are lots of thing to take into account here.

A 500-GB database isn't such a big deal per se, as long as it's properly structured.

But if you have a single big table with 100 columns of type VARCHAR(MAX)... there's nothing that can help you.

share|improve this answer

There are tool to help you do partitioning. Here is one example:

Other things that could improve performance:

  • faster disks
  • check indexes
  • log files on sepatate disk
  • indexes on separate disk
share|improve this answer

Your Answer


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