SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance issue related to Memory shortage?


Performance issue related to Memory shortage?

Author
Message
Ngramstad
Ngramstad
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 95
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!
Chrissy321
Chrissy321
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1234 Visits: 4711
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.
Ngramstad
Ngramstad
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 95
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search