June 15, 2008 at 10:11 am
Hi,
I've SQL server Dev 64BIT SP2 on Windows Server ENT 64BIT SP2
and i've the issue with the memory in the task manager.
the task manager show that the SQL Server Service use 150MB of MEM,while Perfmon show different.i've attached a photo.
why is that?
i've an issue with SQL server that my CPU is use a lots.
the CPU is always on 45%-60% and can be on 85%-95% for a few hours.
when no activity in the SQL the CPU work good.
can you tell me where is my problem?(I've 2 CPU XEON 3.0 and 6GB MEM).i've added another pic for the PF.
who is taking all the mem? according to the task manager no one.
June 15, 2008 at 10:32 am
First - what is reported in that view does not tell you how much memory SQL Server is actually using. You can look at PF Usage to get a better idea.
Second, what did you set the min/max memory settings to?
Did you configure 'lock pages in memory'?
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 15, 2008 at 10:39 am
first - i didn't understand your first question.
second - min = 1500 , max = 5200
yes i've configured the lock pages in memory for the SQL server service that start the service.
June 15, 2008 at 10:59 am
Switch over to the Performance tab and view the PF Usage graph. That should show you the max that is being used.
To verify that SQL Server is using the memory it should - run DBCC MEMORYSTATUS.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 15, 2008 at 11:02 am
Oh, also - if you only have 6GB of memory available, setting max memory to 5GB (5200) might be a bit high. I would drop that down to 4GB (4096MB), and if you are running anything else on that server you might need to drop it even more.
If you are running any SSIS packages - they will need up to 2GB of memory to process.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 15, 2008 at 11:38 am
also keep in mind, if SQLServer doesn't need memory, it will not use memory.
i.e. it may be your sqlserver only uses 150mb, if it decides it doesn't need more.
However because of your min memory setting 1,5Gb it will try to keep 1,5Gb once it has reached it.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 15, 2008 at 12:34 pm
the PF show 5.7GB usage.
June 15, 2008 at 1:11 pm
With the system using 5.7GB of memory (page file) - you are definitely running into memory issues. This is only leaving approximately 300MB available to the OS and other processes.
Do you have other processes that need to run on this server (i.e. SSIS, DTS, application code, etc...). If so, any time these start up they will need memory from SQL Server. If SQL Server is using that memory - it may not be released causing the problems you are seeing.
I would recommend lowering the max memory setting to at least 4096 - and possibly lower depending upon what else you have running on that server. And then I would look at adding memory to the server.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 15, 2008 at 1:26 pm
Jeffrey Williams (6/15/2008)
With the system using 5.7GB of memory (page file) - you are definitely running into memory issues. This is only leaving approximately 300MB available to the OS and other processes.Do you have other processes that need to run on this server (i.e. SSIS, DTS, application code, etc...). If so, any time these start up they will need memory from SQL Server. If SQL Server is using that memory - it may not be released causing the problems you are seeing.
I would recommend lowering the max memory setting to at least 4096 - and possibly lower depending upon what else you have running on that server. And then I would look at adding memory to the server.
Jeff
have you looked at the 2 photos in the beginning of this thread?
who is taking all the MEM?
according to the task manager in the photo that is arrange by MEM usage desc no one is taking the 3.5GB but still the task manager show that something is eating the MEM
June 15, 2008 at 1:52 pm
Yes - I did look at those. And once again, you will not see how much memory SQL Server is actually using from that view. You even show how much memory SQL is actually using from the system monitor which does show that SQL is using most of the memory.
SQL Server is using the memory that you told it to and to verify that you can run DBCC MEMORYSTATUS (did you run that before?).
When the system runs out of memory you are going to see CPU spikes and I am betting that is what is causing the problems. By lowering the memory configuration you leave more memory available to the OS to manage that memory and allow other processes to use that same memory.
Again I ask - do you have other processes running on that server other than SQL Server (i.e. SSIS, DTS, application code, etc...). If so, when those run they need memory and SQL Server has than memory already allocated and is using it - so it can give it up to those processes. When that occurs, you will see a lot more CPU activity as the OS tries to swap memory in/out for each process.
My recommendation is to lower the max memory setting to start with and also pursue adding memory to the system.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 15, 2008 at 2:09 pm
I've reduce the max memory to 4096.
i don't have other processes running beside SQL Server(TLog and Full Backup by Maintenance Plan).
no SSIS,DTS,Application Code,this is very standard SQL Server.
I'm responsible for more then 25 SQL Server some 32BIT and some 64BIT and in every SQL in the task manager i see the process sqlsrvr.exe use the current amount of MEM,for example if the SQL Server use 2.5GB i see in the task manager that the process sqlsrvr.exe use 2,500MB which it's right.
but not in this case.
anyway my final configuration is as follow - min MEM = 1500 , max MEM = 4096, SQL server use all 4CPU(xeon dual core) should i remove the hyper threading from the CPU in the BIOS?
lock pages in memory are setup.
should i configure anything else?
June 15, 2008 at 2:41 pm
I disable hyper-threading on all of my SQL Servers. I have not found anything other than problems with having it enabled - and only rare situations where it actually helps.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 15, 2008 at 11:26 pm
what about /3GB and /PAE do i need to set them UP?
my Server is W2K3 ENT 64BIT and SQL Dev 64BIT.
June 15, 2008 at 11:58 pm
avipenina (6/15/2008)
what about /3GB and /PAE do i need to set them UP?my Server is W2K3 ENT 64BIT and SQL Dev 64BIT.
No you're on 64bit.
In task manager, in the processes tab you can also show column "virtual memory" (vm size). If this one differs very much from "Mem usage", you may have an issue.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 16, 2008 at 1:10 pm
ok - forget task manager and get process explorer - never understand why so many dba's look to task manager for memory usage - it doesn't get it right!
There are some good blog posts about x64, including my own blog, http://sqlblogcasts.com/blogs/grumpyolddba/
I figure that 4gb for the o/s is the bare minimum you should allow, I usually allocate 6gb - it's quite an interesting subject and disabling HT would probably free up around a gb of ram allocated to the schedulers and worker threads.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply