May 20, 2009 at 12:13 am
Dear Experts,
I have a problem with CPU utilization. My production server is of SQL 2005 -64 Bit on Win 2003 -64 Bit.
It has 16 GB RAM - where allocated 12GB for SQL Server and 4 GB for CPU respectively.
Here am unable find why SQL is taking 13.2 GB memory when i checked in the Task Manager as i have given that as 12288 (12 GB). And because of this my CPU has less amount of memory and its reaching 100% many times and we are getting Timeout errors with application when it is in 100% CPU utilization.
Can any suggest and let me know that if CPU is in 100% utilization then the new processes will not be accepted.
CPU utilization 100% = Utilizing total memory of RAM which is allocated ???? is that correct ???
Can anyone provide me best solution for this. unable to attache the screenshot.
User connections for the server will be 500+ anytime. 180 will be active.
Please help me to fix this early ...
Urgent help required ...
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 20, 2009 at 12:55 am
SQL_Monster (5/20/2009)
Can any suggest and let me know that if CPU is in 100% utilization then the new processes will not be accepted.
No
CPU utilization 100% = Utilizing total memory of RAM which is allocated ???? is that correct ???
No
Can anyone provide me best solution for this. unable to attache the screenshot.
As has been previously suggested, find the queries that are using the most CPU and see if you can tune them. That may involve rewriting the queries, it may involve changing the indexes, it usually involves both.
Please help me to fix this early ...
Urgent help required ...
If it's this urgent and you have no optimisation skills in-house, may I suggest that you get a specialist in to have a look at this. They'll be able to get the problem fixed much quicker than you will, and you'll learn from them in the process.
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
May 20, 2009 at 1:07 am
About how to find out which queries cause the High CPU usage have a look at this post from last week:
http://qa.sqlservercentral.com/Forums/Topic716821-360-1.aspx#bm716925
[font="Verdana"]Markus Bohse[/font]
May 20, 2009 at 1:16 am
Refer following link to narrowdown your problem:
http://www.sql-server-performance.com/articles/audit/hardware_bottlenecks_p1.aspx
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 20, 2009 at 1:43 am
Thanks for the reply Gail.
I have already started working on the long running queries and the procedures. Done rewriting the queries and reduced the execution timings for the procedures as well.
Tuned 20+ queries and procedures till now. Following your suggestions...
I asked this as an URGENT issue because am doing the processes what ever u have suggested from earlier. There are some procedures which are creating the #temp tables.
Now am working to tune them as that was the application script for our home page, am unable to understand the coding....
Thanks for the inputs...
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 20, 2009 at 5:28 pm
I had this and it wasn't SQL it was the wmiprvse.exe this that would spike to 100% during the day and i believe it was from one of the latest updates. Something you may want to check out.
Memory on sql is this set to a limit for min and max and have you done lock in memory on windows too. This pervents OS Paging and using CPU when memory is necessary and swapping out.
May 20, 2009 at 11:58 pm
Thanks for the response,
Memory on sql is this set to a limit for min and max and have you done lock in memory on windows too. This pervents OS Paging and using CPU when memory is necessary and swapping out.
Could you please brief on the above lines ??
Or else please provide me any articles or links that helps me..
Thanks again in advance...
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 21, 2009 at 9:34 am
In sql manager in properties there is min and max for memory - set the max to say 10 GIG if you have 16 GIG so you leave 6 GIG for o/s. You can increase it to say 12 GIG but best leave some for os.
Now in SQL 2005 you had to do lock in memory pages in the OS for the operating system.
http://technet.microsoft.com/en-us/library/ms190730.aspx
Requires a reboot.
Then see how the CPU is doing......without this your see CPU hit 100% all day long.
Now check one other thing too WMIPSRVE.exe something like this in task manager i seen this shot to 100% CPU too and there a fix for that in ms..
May 22, 2009 at 8:43 pm
Do you have a maintenance job setup to rebuild your indexes. Update statistics? If not, I would at the very least update statistics in order to get the cpu down long enough to identify the root cause which will probably be from out of date statistics, bad indexes, missing indexes and last but not least, poorly written queries.
Good luck.
May 24, 2009 at 11:39 pm
Thanks for your replies Experts,
I have a scheduled job indexing for every week end. Its for Re-Indexing on every sunday night.
Not done for update statics.
Poor Queries are tuned and the execution time decreased to 30% better than earlier.
There are many HomePage (application) queries running and we have number of Temp tables executing for the homepage...
Tuning the procedures (Temp tables).
I couldnot understand reg : WMIPSRVE.exe , what should i actually do, if i have found the file in the processes in Task Manager ???
Could you please tell me how can i do that ?
Thanks in advance.
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 25, 2009 at 3:33 am
hi
try to configure, only the min mermory on sql server not max momery, sql server dynamically takes from os whenever required and after finishing the job, it leaves dynimically for OS.
to do so
SP_configure, set minmemory, 1024
sp_reconfigure
goodluck
Kingmanjunath
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply