0

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?

Dennis Williamson
  • 60,515
  • 14
  • 113
  • 148
  • 1
    This might help you a bit: http://serverfault.com/questions/52955/easy-table-partitioning-in-sql-server-2005-for-fact-tables – Joseph Kern Sep 18 '09 at 10:00

3 Answers3

3

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.

Steve Jones
  • 795
  • 5
  • 8
1

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.

Massimo
  • 68,714
  • 56
  • 196
  • 319
0

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
Shiraz Bhaiji
  • 2,219
  • 8
  • 34
  • 47