Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » How to solve this issue sqlserver.exe process...
Add to briefcase
21 posts, Page 1 of 3
123»»»

How to solve this issue sqlserver.exe process is taking so much of memory [70%] Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
naga.rohitkumar
Posted Wednesday, November 14, 2012 3:39 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:10 PM
Points: 529, Visits: 1,038
HI to all,

How to solve this issue sqlserver.exe is taking so much of memory [70%]

in task manager --->in process --> sqlserver.exe is taking nearly 70% of memory windows team sent a mail and asked to resolve


1. in this case what should i do ???
and

2 . shall i take ---Performance- top Queries by Average IO and top 10 long running queries and shall i ask application team to make proper tunnnig

3. or else what ever the things i can do to solve this please suggest with clear analysis step by step .

Thanks & Regards
NAGA.ROHITKUMAR
Post #1384510
GilaMonster
Posted Wednesday, November 14, 2012 3:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 38,028, Visits: 30,334
In general, you should do nothing other than notifying the Windows team that high memory usage by SQL Server is perfectly normal and desirable. SQL uses memory to avoid having to read from disk (slow) or frequently compile queries (slow)

If SQL is using too much memory, reduce the value of max server memory slightly. That said, 70% of total sounds reasonable, maybe a little on the low side assuming it's a dedicated server.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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
Post #1384513
naga.rohitkumar
Posted Wednesday, November 14, 2012 4:39 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:10 PM
Points: 529, Visits: 1,038
Hi sir

i run this script in t&d server but memory usage is same and it is increasing

script 1:
sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'max server memory', 100
reconfigure with override
go

----------------------------
script 2:
USE master;
GO
EXEC sp_configure 'show advanced option','1';
RECONFIGURE WITH OVERRIDE;


RECONFIGURE;
EXEC sp_configure;

USE master;
GO
EXEC sp_configure 'max server memory (MB)','100';
RECONFIGURE WITH OVERRIDE;

Thanks & Regards
NAGA.ROHITKUMAR
Post #1384536
GilaMonster
Posted Wednesday, November 14, 2012 5:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 38,028, Visits: 30,334
naga.rohitkumar (11/14/2012)
script 1:
sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'max server memory', 100
reconfigure with override
go


Seriously? You told a SQL Server instance to use no more than 100 MB of memory? Does that sound the slightest bit like a good idea?

I would strongly suggest you fix that before you restart the instance or server, or you may end up with a SQL Server that fails to start.

Once more with feeling...
SQL Server is designed to use as much memory as it can get
SQL Server uses memory to avoid having to read from disk (slow) or recompile exec plans (slow)
As a result, you want to give a SQL Server as much memory as possible
Using 70% of the memory on a server is a good thing, not a problem that needs fixing.

Now, go fix that max server memory and put it back to a sensible value (like 70% of the total memory on your server), tell the windows team that high memory usage by SQL Server is a good thing, not a problem that needs fixing and read chapter 4 of this http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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
Post #1384575
naga.rohitkumar
Posted Thursday, November 15, 2012 12:18 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:10 PM
Points: 529, Visits: 1,038
but quite oppositely wintel team saying that of ur sqlserver.exe use more sapce it might be problem in file transfer or in any uploads

Thanks & Regards
NAGA.ROHITKUMAR
Post #1384993
Bhuvnesh
Posted Thursday, November 15, 2012 3:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453
naga.rohitkumar (11/15/2012)
but quite oppositely wintel team saying that of ur sqlserver.exe use more sapce it might be problem in file transfer or in any uploads
?????? need mmore clarification

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1385045
GilaMonster
Posted Thursday, November 15, 2012 3:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 38,028, Visits: 30,334
naga.rohitkumar (11/15/2012)
but quite oppositely wintel team saying that of ur sqlserver.exe use more sapce it might be problem in file transfer or in any uploads


If the server is not dedicated to SQL Server (it's a file server or other stuff as well), then you need to set a sensible max server memory that allows SQL to use memory up a level that will not cause problems with whatever else is running on the server.

That doesn't mean limit SQL to 100MB of memory, that'll never work.

Please read through chapter 4 of http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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
Post #1385049
naga.rohitkumar
Posted Thursday, November 15, 2012 3:46 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:10 PM
Points: 529, Visits: 1,038
i didn't konw actually i goolged and set the value 100 in t&d later i tried 512 mb for the max memoery instead of 1024 mb [actuall] but before changing i checked the utilisation of memory in the task manager sqlserver.exe process but after changing to 512 mb also it remained same and some times it's increasing than the pervious and i want permenent solution which may reduce the memory utilization in the sqlserver.exe in the process in taskmanager

Thanks & Regards
NAGA.ROHITKUMAR
Post #1385058
GilaMonster
Posted Thursday, November 15, 2012 4:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 38,028, Visits: 30,334
Oh for ...

SQL uses large amounts of memory to speed up it's operation. 512MB is not large (10 years ago it might have been). You need to set a sensible max server memory (not so low that SQL can't work) that allows SQL to leave enough memory free so that other processes have enough memory. How much memory does that server have? 32 GB? 64GB? More?

The permanent solution is setting max server memory to a sensible value and educating your windows team as to how SQL Server uses memory.

Please read through chapter 4 of http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

Also don't use Task Manager to monitor SQL's memory. Again, see the aforementioned chapter.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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
Post #1385068
naga.rohitkumar
Posted Thursday, November 15, 2012 5:11 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:10 PM
Points: 529, Visits: 1,038
in client its damm difficult to make them accept to increase RAM and all and about windows team they are doing there work to intimate me they are doing that if possible can u please suggest the root solution and about memory and all iam not that much knowledge cause iam a L1

Thanks & Regards
NAGA.ROHITKUMAR
Post #1385095
« Prev Topic | Next Topic »

Add to briefcase
21 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.