Large SQL Server advice

Question

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 database 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, 2GB RAM, Windows 2008, SQL Server 2005, one disk).

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?

Answer 1

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

Also: the Pentium E6300 seems to be a desktop-class processor. You want a computer designed to be a server. Just adding a couple of disks and more RAM may not be satisfactory.