Apologies if this isn't posted in the correct section - please redirect me if appropriate.
I've recently started a new position as a kind of analyst/ report writer. Our department has its own server with a small number of tables that are deleted and then rebuilt from a database on a separate server via a job run nightly. I have no Sys Admin or DBA experience which becomes important in this next bit...
Two of my largest tables cause serious performance issues in executing queries. One is roughly 40 million rows and the other is roughly 4 million. I've added indexes (there were none on any of our tables) and this has alleviated the issue somewhat when working with our small tables (10's of thousands of rows) in conjunction with my 4 million row table. I've taken several queries from several minutes to a few seconds.
Anything to do with our larger 40 million row table is still very much bottle necked.
I then started looking at our hardware situation. We're running SQL Server 2008 R2 on a server using Windows Server 2003 SP2. We have plenty of disk space, the database can grow as needed. I've looked and have not seen excessive Log or Back Up files (honestly, I was surprised to see Back Ups at all...).
I believe that the issue we're facing is with Memory. The server caps out at 4GB of RAM. The 40 million row table is over 18GB on its own.
If this is the case, I don't believe we have any other options outside of moving to a new box but I'd appreciate any commentary as to basic logic of my little investigation here. I realize I haven't given enough information for an actual diagnosis, but given the above does this sound like a plausible cause of our performance issue?
Thanks in advance!