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