Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Copied my question from StackOverflow after advice from a fellow user:

I'm dealing with a quite large SQL Server database and encountering a few problems.

The database is quite new and I've been importing data into the tables and it's making the whole server go quite slow. The db is now 15GB and in 1 table for example it has just 4 columns, but 122 million rows and takes up 3.5GB. Another table has about 35 columns with 3 million rows and takes up about 1.5GB.

Whenever I open one of the big tables, or start using it with my MVC3 application, it nearly cripples the server the the memory usage hits max and it starts running really slowly. (Server spec: Pentium dual core E6300 2.8GHz, with 2GB ram, windows 2008, SQL Server 2005)

This is really the first dealings I've had with this amount of data so have been adding indexes to try and speed things up but once the memory hits the max level, I've had to restart the server in order for it to work at normal speed.

Anyway, I don't feel that 15GB is that big - surely 2GB ram could handle it? I've not even finished adding the first round of data so it's going to get considerably bigger.

Does anyone have any advice for how it could be set up better and be more efficient. All PK's and FK's have been set up and I've added indexes to the large tables to see if that helps any but it's eventually started going slowly again. BTW, in the database properties, it says 'Space Available - 1596.96MB' How do you increase that figure?

Thanks very much

share|improve this question
    
2GB RAM for a production database server? That's definitely not enough, especially with that DB size. My guess is that the server is hogging your disks (you do have more than one do you?) –  a_horse_with_no_name Jul 4 '12 at 13:22
    
haha - I just checked, there is only one disk. So, looks like the server needs a few things bought for it then? I wasn't sure if it would - as I said over on the post on SO, while I was looking for solutions, I came across a guy who had 1200 databases on a 4GB server - is there no amount of streamlining that will overcome the 2GB RAM factor? –  e-on Jul 4 '12 at 13:27
    
this was cross-posted stackoverflow.com/questions/11329607/large-sql-server-advice –  bluefeet Jul 4 '12 at 15:17
add comment

1 Answer

up vote 8 down vote accepted

2GB of RAM is barely acceptable to run Windows, let alone SQL Server on top.

Obviously your 15GB of data doesn't fit into 2GB of RAM. So, any and every query you run that requires rows that aren't in RAM will require reads from disk. From there, you can do the math on how much data you're requesting versus how fast your disks can deliver it. Given that the server is so woefully under specified for memory, one can assume you've got just a single spinner delivering IOPS at miserably slow rate, perhaps 50MB/sec for a sequential read but as low as 1MB/sec with several queries fighting for their share.

Have 5 users each run a query that needs 1GB of data that isn't currently in RAM and you'll be waiting 15+ minutes for the data to crunch its way through the system.

RAM is comically cheap, especially compared to the cost of you wasting your time trying to optimise 15GB into 2GB. Fill the server, or buy a new one.

share|improve this answer
    
Thanks for this answer Mark - I've sent it to my boss, so hopefully we'll get an upgrade soon! –  e-on Jul 4 '12 at 13:55
2  
Just to pile on a little bit, the Pentium E6300 seems to be a desktop-class processor. You want a computer designed to be a server, not a desktop machine with delusions of grandeur. In other words, just adding a couple of disks using software RAID and more RAM isn't going to be satisfactory. IMO. –  darin strait Jul 4 '12 at 14:40
1  
@e-on As a comparison and to add weight to the conversation with whoever holds the purse strings, my laptop has 30GB more than your server. –  Mark Storey-Smith Jul 4 '12 at 14:59
    
@darinstrait Good point, I didn't clock that on first read. –  Mark Storey-Smith Jul 4 '12 at 15:02
    
Thanks a lot guys - I've had a little bit of background into that server and it turns out that it was built a few years ago with only a small project in mind, not for something of this size. This project has plans to be global and I think the db could realistically be 70-80GB when in full operating mode so it looks like a full upgrade will be needed rather than adding new parts. Appreciate the advice folks. –  e-on Jul 4 '12 at 16:27
add comment

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.