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

Question

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

naga.rohitkumar

SSCertifiable

Points: 5738
More actions
November 14, 2012 at 3:39 am

#149553

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
Naga.Rohitkumar

Viewing 15 posts - 1 through 15 (of 20 total)

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

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

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, 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

naga.rohitkumar SSCertifiable Points: 5738 More actions · Answer 2

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
Naga.Rohitkumar

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

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, 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

naga.rohitkumar SSCertifiable Points: 5738 More actions · Answer 4

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
Naga.Rohitkumar

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

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----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

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, 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

naga.rohitkumar SSCertifiable Points: 5738 More actions · Answer 7

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
Naga.Rohitkumar

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

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, 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

naga.rohitkumar SSCertifiable Points: 5738 More actions · Answer 9

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
Naga.Rohitkumar

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

naga.rohitkumar (11/15/2012)
if possible can u please suggest the root solution and about memory and all iam not that much knowledge cause iam a L1

I have. Repeatedly.

If SQL is using too much memory, reduce the value of max server memory slightly.

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.
Please read through chapter 4 of http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

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/

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

naga.rohitkumar SSCertifiable Points: 5738 More actions · Answer 11

please suggest the quick solution to reduce the memory utilization high again same issue happend and windows team send me the mail to resolve this i tried to reduce that but it is not hapening if any other solution please suggest me to reduce and solve this issue

Thanks
Naga.Rohitkumar

Ant-Green SSC Guru Points: 113507 More actions · Answer 12

As Gail has already said a number of times, high memory usage on a SQL server is a VERY GOOD THING[/u].

Go and get a copy of the Accidental DBA guide Gail has referenced and read chapter 4 on memory management.

Now please answer these questions

1. How much memory does the server have in total?

2. Is this a dedicated SQL server? Or is it used for other purposes? File server? IIS Server? Exchange Server?

3. What is the current Min and Max memory settings of the server?

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

naga.rohitkumar (11/16/2012)
please suggest the quick solution to reduce the memory utilization high again same issue happend and windows team send me the mail to resolve this i tried to reduce that but it is not hapening if any other solution please suggest me to reduce and solve this issue

Have you even read any of my posts?

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

naga.rohitkumar SSCertifiable Points: 5738 More actions · Answer 14

1. i followed ur posts and suggested same to my team also but not accepting just they want to reduce onlyy and ur posts also provided nice guidence

2. the server is also occupied with other services and applications like .net and iis

and min and max memory of the server are

----2147483647[maximumsever memory in MB]

----1024[minimum memory per query KB]

Thanks
Naga.Rohitkumar