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

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

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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

If you have a CPU hot, 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 IO 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 IO pressure:

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

  • 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.

share|improve this answer
    
Hi Tomtom, thanks for your input that shed some light on the relative size of the numbers. I will specially check the nb of cores vs nb of files. Selecting is usually made on orderId or by range, that's one of the reason why I started to look at partitioning. The table structure and PK are certainly not ideal but changing the (legacy) applications is not a cheap choice. -- Dom –  user20783 May 5 '10 at 10:07
    
Actually adding indices IS a cheap choice. How comes you select by PK range? Do not tell me someone was so stupid to use the PK for the order ID? Ensure you have proper formatting (64kb NTFS Node size) AND aligned partitions - that may kill a lot of your IO already. –  TomTom May 5 '10 at 10:40

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.