Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
1  
This might help you a bit: serverfault.com/questions/52955/… –  Joseph Kern Sep 18 '09 at 10:00
add comment

3 Answers

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

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

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

http://www.donwellsoft.com/Default.aspx

Other things that could improve performance:

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

Your Answer

 
discard

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