Solved

SQL with great memory usage

Posted on 2014-02-04
9
220 Views
Last Modified: 2014-03-17
Hi Experts,

I have a SQL Cluster but the cluster takes nearly all memory and is slow.
Can we check together from where it comes ?
It is SQL 2008 on WIN 2008 machine.
Some DB´s installed nearly 100GB DB's.
The RAM has 20GB
0
Comment
Question by:Eprs_Admin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 12

Expert Comment

by:Alex Green
ID: 39832458
Lol,

Yeah if you've got 100GB database I'm not surprised it's ripping apart the 20GB you have installed. SQL is designed to pull every single bit of memory that's available to it. The reason it's slow is you're probably running out of memory whilst it's doing some of the more complicated queries.

To put it into perspective, I have a 45Gb database which runs complicated queries and it's got 128GB of memory.... SQL takes it all.

If you can, throw in some more memory, if it's a a VM then assign it 64GB and see how it goes with that. If you don't have the memory you could try increasing your page file but the performance still won't be great.

Regards

Alex
0
 

Author Comment

by:Eprs_Admin
ID: 39832512
No I don't have more memory.
Can you show me some performance counters to check ?
0
 
LVL 15

Expert Comment

by:jerseysam
ID: 39832592
SQL is designed to use memory as required, unless you set it a limit:

http://technet.microsoft.com/en-us/library/ms178067.aspx
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39832597
well dont worry about SQL Server hogging the complete memory, as long as that cluster is used only SQL Server alone.... and no other services are installed on it.

There are many reasons why SQL Server can take more memory

1) the minimum server memory can be set up to 19 GB
2) SQL Server will cache up some execution plans in memory for efficient reuse.
and many more.
0
 
LVL 12

Expert Comment

by:Alex Green
ID: 39832622
Well the thing is if you assign SQL a certain amount of memory it'll take it all. If you want to see how much it's using then check the working set of memory I think it is
untitled.JPG
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39832635
if you have 20GB of memory in the machine, set the max memory setting of the sql server properties to 16000 (which is in MB ), this should help already (a sql instance restart may be needed to "force" the setting fast)

other than that, a sql profiler can help to see which queries are taking long, and check them for missing indexes, or (which is also often the case) for queries running in "parallel mode" which is often counter-productive. the following option, added to the sql, will then solve the issue
OPTION (MAXDOP=1) 

Open in new window

0
 

Author Comment

by:Eprs_Admin
ID: 39832646
yes I have the same with 90% of memory.
0
 

Author Comment

by:Eprs_Admin
ID: 39835615
for what is this option MAXDOP=1
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39835639
that is to force sql server to run the query "non-parallelized", hence using 1 CPU only.
you may say "why limit the usage of CPU", but I have seen plenty of cases where small queries went very bad (minutes and more instead of subsecond) due to the overhead of having to "split" the work (data to be processed) to several CPU, and recombine the result later.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
Resolving an irritating Remote Desktop connection that stops your saved credentials from being used.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question