Memory utilization issue

Question

Memory utilization issue

saidapurs

Hall of Fame

Points: 3477
More actions
August 9, 2012 at 12:27 am

#259816

Hi All,
I have SQL SERVER 2008R2 in Windows Server 2008. Server have 8GB physical memory & total utilization 6.5GB, the SQL server using 5.5 GB of physical memory because of memory the users are going to sleeping mode.
How do i release 5.5GB of SQL Utilization?
Thanks in advance...
Satish

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

SQL Server uses as much memory as it can. It's a normal behaviour.

If you have other processes running on the server and you need to allocate memory to them, you must tell SQL Server to use less memory. You can do it using the MAX_SERVER_MEMORY setting. You can change it from the GUI in Management Studio (right click the SQL Server instance node, click properties, open the "memory" applet, set the max server memory and click ok) or in T-SQL with

EXEC sp_configure 'MAX_SERVER_MEMORY', <someReasonableValue>;

GO

RECONFIGURE;

GO

Hope this helps

Gianluca

EDIT: typo

-- Gianluca Sartori

saidapurs Hall of Fame Points: 3477 More actions · Answer 2

Hi Gianluca,

I already assign 5GB to SQL SERVER before it was 4GB that time some users are going to sleeping mode after that i change to 5GB past two months it's work very nice now again Users are going to sleeping mode & CPU usage is hitting 100%.

Let me know any alternate solution for this??

Thanks

Satish

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

If CPU is hitting 100% I'm pretty sure adding more memory won't help.

You seriously need to find what sends CPU to the roof. It could be a runaway query that needs to be tuned.

-- Gianluca Sartori

saidapurs Hall of Fame Points: 3477 More actions · Answer 4

Hi,

When i run the profiler most of the time going to hang, I want to close this issue as soon as.

Thanks

Satish

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Don't run profiler. Use sp_WhoIsActive or something similar to find the offending query.

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Chapters 3 and 4: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass