Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performance issue related to Memory shortage? Expand / Collapse
Author
Message
Posted Saturday, July 19, 2014 7:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:18 AM
Points: 4, Visits: 93
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!
Post #1594351
Posted Tuesday, July 22, 2014 3:35 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 2:58 PM
Points: 653, Visits: 3,860
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.
Post #1595295
Posted Tuesday, July 22, 2014 4:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:18 AM
Points: 4, Visits: 93
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!
Post #1595305
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse