July 19, 2014 at 7:10 pm
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!
July 22, 2014 at 3:35 pm
Read this http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Your memory theory sounds plausible but I would look at query optimization first before you throw money at the problem.
July 22, 2014 at 4:25 pm
Thanks Chrissy, I'll try to incorporate the formats in the link in the future.
Allegedly, money has already been thrown at the problem. 🙂 I even believe that our replacement box is on site and has been for some time. With some luck, it might even be operational before we're brought to a standstill by our large table.
I have no experience in the hardware side of things and was trying to determine whether I was completely chasing my tail before spending more time on this. I appreciate your response!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply