Link to home
Create AccountLog in
Avatar of Mahesh Yadav
Mahesh YadavFlag for India

asked on

Sql server using memory

Hi,

I have a web application on a dedicated server.

I don;t have lot of users at the moment usiing this around 20-30 users in a day.

I have noticed that in couple of weeks the memory usage by SQL server increases a lot.

Any input what this could be causing this...and how i can handle this.

I have checked that the connections are closed properly.

Thanks,

Mahesh
Avatar of Bawer
Bawer
Flag of Afghanistan image

Can you check in the task manager in processes and see what are the tasks using alot of memory better you print a screen,
Avatar of Mahesh Yadav

ASKER

Yes i checked that...and Sql server is using most of the memory....which was 10 time less when the server start. And there is only this application with one sql server db....so there is no other process working with SQL server as such.

and in couple of days SQL server ends up using the maximum memory.

Here is the screen shot of task manage

 User generated image
ASKER CERTIFIED SOLUTION
Avatar of san_spy
san_spy
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Agree with the above post. Even if you set the max memory it will try to use all available memory which not used by the OS/other processes.
Since this is SQL Server 2005, you can set the max memory right away in the SQL Server properties.

It will gradually go down to the max limit you specify.
Ok...i will try setting the memory limit...but would that effect the performance of the application when the no of users increase to say couple of hundreds.
Along with setting memory limit, do SQL server profiling and fine are there any SPs, View or triggers which are fetching large data in the memory. It may also cause large memory consumption.
yes the application performance will go down if the allocated memory is not sufficient to the SQL Server. You need to do further investigation on what is the amount of memory really required by your application.

you need to figure out how much memory is required for your application..
//Agree with the above post. Even if you set the max memory it will try to use all available memory which not used by the OS/other processes. //
No this is wrong, if you set the SQL Server max memory it wont use any thing beyond that.

Profiler wont help you to check the memory utilisation. Run the performance monitor with memory counters for a couple of day and check what is the max memory used by SQL Server (If possible restart the SQL Server before setting up this as we can get some fresh values). Based upon that you can come to a conclusion on how much sqlserver is using.
>> And there is only this application with one sql server db....so there is no other process working with SQL server as such.<<
If against recommended practices you have SQL Server sharing a server with other applications, than you have no choice but to cripple SQL Server by setting the max memory used the the SQL Server Engine.

sql server 64 bit any editio are  bad consumers of memory.
add available mbytes counter in perfmon, if mbytes are less then 5 to 25 mb for more then 1 hour, then there is a memory leak.
Application performance will anyway be impacted if the OS is short of resource in your machine. So try to calculate an optimal amount of memory which if allocated to SQL Server will not choke the OS.

Setting the max memory will still be a good idea, considering optimally. You can give us the total memory in the machine and what else do you run from this machine so that I can suggest an optimal value for you.

>>sql server 64 bit any editio are  bad consumers of memory.<<
Do you have any reputable documentation to back that up?
//>>sql server 64 bit any editio are  bad consumers of memory.<<
Do you have any reputable documentation to back that up?//

Memory Leaks & this are all nonsense ignore those. There is no difference in the memory consumption between 32 & 64 bit.
>>Memory Leaks & this are all nonsense ignore those. <<
I am afraid I cannot ignore this type of "nonsense". These urban myths needs to be held up to the light.  Either you present some valid documentation that there are problems or you do not continue to spread these stories.  Hopefully, if they are challenged enough enough times they will either comply or act professionally.
>>There is no difference in the memory consumption between 32 & 64 bit. <<
Actually there is a vast difference in the way memory is used and 64-bit is IMHO vastly superior and is fast replacing any existing 32-bit servers.  I would be very interested in seeing some numbers, but I would be surprised if there are more than 30% of Production servers still using 32-bit O/S.
Dude I am working in SQL Server 2005 for the past 5 years and I have never heared about memory leaks in SQL Server. I have atleast 500+ SQL Server installed in my farm till now.
>>Dude I am working in SQL Server 2005 for the past 5 years and I have never heared about memory leaks in SQL Server. <<
Since my name is not "Dude" and I am in your camp, the whole "sql server 64 bit any editio are  bad consumers of memory" is a myth, I have to assume you are addressing sqlservr or your comprehension of the English language is somewhat diminished or it is your second language.
Now perhaps we should get back to addressing the author's question...
@acperkins - dont go too far frst language second language business. It was the reply to whom ever talking abt SQL Server 64 bit using more memory, memory leaks, urban myths blah blah..This is not an english forum...if ur so keen on ur first language and a place to show off ur "English" expertise look some where else

No problem, I realize now you did not understand me, I know what it is like, English is my second language, too.  

Now, whatever happened to the author?
Lets get back to where we left off:

Application performance will anyway be impacted if the OS is short of resource in your machine. So try to calculate an optimal amount of memory which if allocated to SQL Server will not choke the OS.

Setting the max memory will still be a good idea, considering optimally. You can give us the total memory in the machine and what else do you run from this machine so that I can suggest an optimal value for you.
@san_spy

//Agree with the above post. Even if you set the max memory it will try to use all available memory which not used by the OS/other processes. //
No this is wrong, if you set the SQL Server max memory it wont use any thing beyond that.

We've 16G RAM on our Server and the MAX memory was set to its default(some TB). I see all 15.G was being used. I changed the MAX Memory to 14Gig and restart the service (not the server) still I do not see any changes on the TASK manager and Resource Monitor(15.6G shows up all the time)

Okay. But If I looked at the SQLserver:MemoryManager: DB Server Memory counter I get to see the 14G used.

My question is which one is wrong?  In order to see the changes do I need to restart the server? Could you explain?

Thanks.
>>No this is wrong, if you set the SQL Server max memory it wont use any thing beyond that. <<
That is correct. This is all covered in SQL Server BOL:
Effects of min and max server memory
http://msdn.microsoft.com/en-us/library/ms180797.aspx
Optimizing Server Performance Using Memory Configuration Options
http://msdn.microsoft.com/en-us/library/ms177455.aspx

>>In order to see the changes do I need to restart the server?<<
It is not necessary to restart the server or the service for that matter.  See here (also from SQL Server BOL):
Server Memory Options
http://msdn.microsoft.com/en-us/library/ms178067(v=sql.100).aspx
Thanks @acperkins!  I stand corrected now.

From this link "http://msdn.microsoft.com/en-us/library/ms178067(v=sql.100).aspx"

"To reduce the max server memory you may need to restart SQL Server to release the memory. For information about how to set memory options, see How to: Set a Fixed Amount of Memory (SQL Server Management Studio)."

@sventhan - How long does it take to reach 14GB of memory is it immediate or it takes a while. If its immediate what is the min server memory?
It takes few minutes to get to the 14G (15.6 in TaskManager as I described before). Our Min server is 0.

Do I need to set the min server memory too? What is the recommended settings for min server memory?

Sorry to ask the questions here. I'll open a new thread about it.

 
you dont need to set any min memory 0 is fine. Can you check the below url

http://technet.microsoft.com/en-us/library/cc966540.aspx#EGAA

and see if you can find out if there is some memory pressure. Since you are running IIS also in the same box Can you set the max memory to 10 GB.
I've restarted the server and service but still I see the 15.6G used on Task Manager and Resource Monitor. This makes my first post is correct(partly).
I've attached screenshots to show you what I'm seeing.

If you want to check the Memory usage we should look out this perf counter not the other sources.

screenshots.docx
hi
   can you please look into the memory consumed by sqlservr.exe in task manger-->processes--> performances--> click on show all the process from all users--> there sqlservr.exe shows the exact amont of memory occupied. as per my investigation. on your server , the screen shots sent by you, there are no reporting services, ruuning on your system.which are good memory consumers, i have a doubt is your sql server 64 bit or 32 bit, why because sql server 64 bit is a bad consumer of memory. CPU usage is at 45 % avg , which is s good,sign..
     i will  be waiting for your screen shot.        
Avatar of danubian
danubian

Before this memory increasie did you changed something in the application ?
Or the number of concurrent conectioons increased ?
sqlservr,

>>why because sql server 64 bit is a bad consumer of memory.<<
Here you go again.  If you cannot produce a single shred of documentation that this is true, kindly stop repeating that.  It is simply not true.  Now can we get beyond that?

For what it is worth it looks like the author has abandoned the question.
Thank you all for your comments...and i am sorry for getting back so late...