SQL with great memory usage
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
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
ASKER
No I don't have more memory.
Can you show me some performance counters to check ?
Can you show me some performance counters to check ?
SQL is designed to use memory as required, unless you set it a limit:
http://technet.microsoft.com/en-us/library/ms178067.aspx
http://technet.microsoft.com/en-us/library/ms178067.aspx
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.
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.
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
untitled.JPG
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
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
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) ASKER
yes I have the same with 90% of memory.
ASKER
for what is this option MAXDOP=1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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