Should I partition my main table with 2 millions rows?

Question

I am a developer and would need some DBA-advices.
We are starting to get performance problem with a MSSQL2005 database. The visible effects of the incidents is mainly CPU-hog on the server but operations reported that it was also draining resources from the SAN (not always). the main source of issues is for sure in some application but I am wondering if we should partition some of the main tables anyway in order to relax the I/O pressure.
The base is about 60GB in one file.
The main table (order) has 2.1 Million rows with a 215 colones (but none is huge).
We have an integer as PK so it should be OK to define a partition function.

Will we win something with partitioning? will partition indexes buy us something?
Here are some more facts about the DB and the table

database_name  database_size    unallocated space
My_base         57173.06 MB     79.74 MB
reserved        data            index_size      unused
29 444 808 KB   26 577 320 KB   2 845 232 KB    22 256 KB

name        rows            reserved    data        index_size      unused
Order   2 097 626       4 403 832 KB    2 756 064 KB    1 646 080 KB    1688 KB

Thanks for any advice

Dom

Answer 1

Ah - why? 15 years ago 1 million rows was considered small. Today, 100 million rows is considered small.

If you have a CPU-hog, I would start looking on what the problem is - this looks a lot more like an index issue and / or bad field design than anything else.

Now, SAN hogging - this is totally normal for any SQL Server. SAN people are normally extremely ignorant to the fact that database servers are IO heavy. Databases normally require a specific SAN setup that is optimized for them and can be fully utilized by them. It is not "hogging" it, it tries to use all the resources as good as possible.

Your database is SMALL - seriously. I do not really see any issue here. The order table has a merely 4gb in memory, which - interesting enough - is a size that should be answered from memory.

Partitioning is usefull for mass deletes (one table per year, dropping a year of orders is a table truncate, not a delete), but with your size this is a non-issue (I Have a table Prices that has about 1.5 BILLION entries, and that is small). It will not accellerate queries a lot - either the query can be seleated to only one partition (and no, the integer PK does not help, unless you select by PK range as filter) - or it can not. But even if it can, an index is nearly as fast.

What type of query is bad? How is the execution plan? Maybe you:

• Have too little memory (8gb or more?)

• Have a suboptimal / non matching index layout, so that the query basically turns into a table scan? In this case I would start fixing on that side.

• You load more data than you need?

Without your query execution plan this can not be answered.

BTW, 60GB in one file is gross neglect. ANY sizable database should have as many files as there are parallel operations possible (i.e. available server cores for SQL Server) ;) And I am sure your I/O is as badly organized - non-aligned partition, bad formatting, slowing you down (possibly a lot - bad disc setup can cost you up to 40% performance).

To relax the I/O pressure:

• Make sure your database server is properly installed (I rarely see one - admins seem to love ignoring documentation here)

• Make sure you have proper resources in the first place. How high is your IOPS budget on the disc subsystem? You DID measure it, or?

• Make sure the databases are properly set up (again, most admins love being ignorant in this case)

• Make sure you have a good table structure and good primary key (pretty much the only thing you have right).

Then - get into the profiler, find out the application and make sure this queries are optimized.