June 2, 2008 at 4:13 am
Dear All,
I am now facing on the SQL server taking too much PCU processes. It seems takes more than 80% just SQL server alone.
Could anyone tell me about some trick to figure out which part take much of the process in SQL server?
What will be the step to track all these kinds of problem?
Waiting for kind reply.
Regards,
June 2, 2008 at 5:05 am
Run profiler against the server for a couple hours during your high CPU time. Events to monitor:
- Stored Procedures: RPC completed
- T-SQL:Batch Completed
The default columns should be good enough. Make sure you have the CPU, duration and TextData at a minimum
Save the results to a SQL table and search through for the queries using the most CPU.
Take those queries and see what optimisation you can do. It will probably be a mixture of badly-written SQL and inadequate indexes (it usually is). Concentrate on getting the code right first, then tuning the indexes after.
If you're willing to spend a bit, consider getting a consultant in to help you out at first, especially if you have no skills in-house in optimisation.
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
June 2, 2008 at 6:53 am
Are you running more than just SQL Server on the box? That will likely lead to problems later. SQL Server tends to use quite a lot of memory and processor. Having other applications running along side will hurt SQL Server, the other application, or both.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 2, 2008 at 11:06 am
If SQL server is consistently using 80%, then you don't have a big enough box. Or things aren't tuned well.
It will spike periodically. Anything less than 5 minutes I'd look to tune, but not overly worry about.
June 2, 2008 at 7:15 pm
I would not think other applications take the CPU process for now because the server mostly just responsible for database task.
Thank you very much for help.
I also have one more question about optimization on SQL query, is there any good article, URL, e-book which I can learn to optimize existing query?
I would really appreciate if I can get some tips or tricks on these.
Regards,
June 3, 2008 at 5:27 am
I would recommend starting with Itzik Ben-Gan's "Inside SQL Server 2005: T-SQL Querying." It's one of the best books on TSQL out there and I can't recommend it enough. I haven't read it, but I understand that Kalen Delaney's second book on Inside SQL Server is also very good and focused specifically on performance tuning.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 3, 2008 at 5:37 am
SQL Server Performance Tuning Distilled (by Sajal Dam) is a good book. It's for SQL 2000, not 2005, but is still a very worth while read. The principles of good code don't change.
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
June 3, 2008 at 5:56 am
Another one I haven't read but would like to is "The Art of SQL." I'm not sure of the authors name. Several people I talked to says it's a great read and very helpful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 3, 2008 at 8:12 am
Run perfmon on your SQL Server and switch to the "View Report" view (Ctrl-R). Right click and select "Add Counters". Under the "Thread" Performance object select the counters for "% Processor Time" and "ID Thread". Select all of the "sqlserver" instances (they will range from /0 to /96), then click the "Add" Button followed by the "Close" Button.
Now watch each of the counters and see if you can spot any that have sustained high CPU values. The Thread ID value corresponds to the KPID column in master.dbo.sysprocesses. Use that to get the SPID and then use DBCC INPUTBUFFER to figure out what that SPID is doing.
Alternatively you can use the DMVs to spot your CPU hogs. Read the section titled "CPU Bottlenecks" from this article: Troubleshooting Performance Problems in SQL Server 2005. It provides sample queries that you can use to find out where your CPU problems lie.
Kendal Van Dyke
June 5, 2008 at 1:19 am
check each and every thing if u r using this counters and u r getting the values out of the range then add additional ram into ur server .
Try to check which procs and query are taking time are u using a heap tables in ur procs check it out.
June 5, 2008 at 6:55 am
What SQL Server 2005 are you running and did you apply any service packs to it???
We had a issue on 2005 Standard edition 32bit where Temp db activity was high and high CPU and applied SP2 and fixed those issues ...
Another issue came up with High CPU utilization on 64bit Enterprise 2005 after applying SP2, we search and found a fix which was applied and that fixed that issue for us 😉
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
June 8, 2008 at 3:03 am
We had the same issue with our previous production server. If your application is not causing this issue, try adding more memory.
Do you also have a lot of blocking locks? That's also a signal for not enough memory.
Wilfred
The best things in life are the simple things
June 8, 2008 at 11:50 am
HI Pong
I had the same problem with one of my servers, i checked all codes, there was a new SP that started to run when the problem started, when i stopped this SP running then CPU dropped to 40% from 99%, so i did rewrite the code, since i cant optmist more now my CPU utilizxation is aroung 50% when this SP runs, then i looked into Indices on all the tables, most of the tables were fragemented, then i did a index rebuilt on all the tables that have 40% fragementation (30% is MS recommanded), then after a day my CPU dropped to 2 %, so try to find the code, then check for indexes.
Hope this helps mate
May 9, 2010 at 6:20 pm
CrazyMan (6/8/2008)
HI PongI had the same problem with one of my servers, i checked all codes, there was a new SP that started to run when the problem started, when i stopped this SP running then CPU dropped to 40% from 99%, so i did rewrite the code, since i cant optmist more now my CPU utilizxation is aroung 50% when this SP runs, then i looked into Indices on all the tables, most of the tables were fragemented, then i did a index rebuilt on all the tables that have 40% fragementation (30% is MS recommanded), then after a day my CPU dropped to 2 %, so try to find the code, then check for indexes.
Hope this helps mate
How did you find out that the new sp is the culprit, with that what I mean to ask is is there a script or should I use profiler to find out the queries that are expensive on the cpu?
Thanks
May 10, 2010 at 1:13 am
2 year old thread...
Profiler (or more correctly server-side trace) is the tool that you use. http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply