<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Geoff Albin
»
How to Monitor for High CPU utilization in...
59 posts, Page 1 of 6
1
2
3
4
5
»
»»
How to Monitor for High CPU utilization in SQL Server
Rate Topic
Display Mode
Topic Options
Author
Message
Geoff A
Geoff A
Posted Tuesday, November 16, 2010 9:57 PM
SSC-Addicted
Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 485,
Visits: 1,568
Comments posted to this topic are about the item
How to Monitor for High CPU utilization in SQL Server
Post #1021910
vyankarla
vyankarla
Posted Tuesday, November 16, 2010 10:03 PM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:02 PM
Points: 4,
Visits: 52
Very good article. How about running the VBS file using windows Task scheduler. ?
Post #1021911
satalaj
satalaj
Posted Wednesday, November 17, 2010 1:09 AM
Forum Newbie
Group: General Forum Members
Last Login: Friday, December 03, 2010 8:07 AM
Points: 8,
Visits: 25
Hi,
What actions do you perform if it reaches more than 90%. In my scenario, I have multiple SQL jobs which runs in while loop
e.g.
While(1=1)
begin
-- do something
-- delete top 500 records from x table of SERVER A and insert into x table of SERVER B
END
CPU usage of SERVER A reaches more than 90%. I know its because of infinite while loop but
I have to transfer the record from server A to Server B as soon as records gets entered into SERVER A.
Is there any way to deal with it.
Thanks, Satalaj.
Post #1021952
arr.nagaraj
arr.nagaraj
Posted Wednesday, November 17, 2010 2:39 AM
SSC-Addicted
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:27 AM
Points: 483,
Visits: 1,191
Good article. Handy way to monitor CPU.
But one improvement I would like to see is a way to find which process is causing the CPU hit.
It may not be always SQL Server process. Or atleast it should say SQL usage % and other process usage %. Another improvement can be some information to figure out what internally caused the CPU pressure within SQL Server, if SQL was consuming the most of CPU. For addressing these, I prefer DMVs.
Regards,
Raj
Strictlysql.blogspot.com
Post #1021985
hrc
hrc
Posted Wednesday, November 17, 2010 3:18 AM
Grasshopper
Group: General Forum Members
Last Login: Tuesday, March 13, 2012 2:31 AM
Points: 12,
Visits: 44
What about @@CPU_BUSY and @@IO_BUSY
if you checked those two values e.g. every 10 seconds and the @CPU_BUSY had increased 8 seconds I would assume that it was running on 80%, that without the VBA and the security issues it imposes.
Post #1022005
Boxer
Boxer
Posted Wednesday, November 17, 2010 3:31 AM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, May 16, 2012 5:17 AM
Points: 11,
Visits: 23
Satalaj, you have to make sure that while running procedures in infinite loop, you make sure that you identify which procedures running
parallely
are getting the CPU usage up (by parallel, I mean the tasks running in parallel). Also, check the CXPACKETS running as the wait type.
You can also use SQL Server Profiler and also perfmon correlated together to toggle through the database and traces, to see when CPU usage creeps up.
Rgds!!
Post #1022016
arr.nagaraj
arr.nagaraj
Posted Wednesday, November 17, 2010 3:35 AM
SSC-Addicted
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:27 AM
Points: 483,
Visits: 1,191
What about @@CPU_BUSY and @@IO_BUSY
if you checked those two values e.g. every 10 seconds and the @CPU_BUSY had increased 8 seconds I would assume that it was running on 80%, that without the VBA and the security issues it imposes.
Sys.dm_os_ring_buffer gives CPU usage % directly. Refer
http://strictlysql.blogspot.com/2010/06/finding-cpu-utilization-in-sql-server.html
Regards,
Raj
Strictlysql.blogspot.com
Post #1022018
Geoff A
Geoff A
Posted Wednesday, November 17, 2010 3:52 AM
SSC-Addicted
Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 485,
Visits: 1,568
arr.nagaraj (11/17/2010)
What about @@CPU_BUSY and @@IO_BUSY
if you checked those two values e.g. every 10 seconds and the @CPU_BUSY had increased 8 seconds I would assume that it was running on 80%, that without the VBA and the security issues it imposes.
Sys.dm_os_ring_buffer gives CPU usage % directly. Refer
http://strictlysql.blogspot.com/2010/06/finding-cpu-utilization-in-sql-server.html
yes, that DMV will provide CPU info, however, those views are not available in SQL 2000. And unfortunately, like most of us, I still have quite a few SQL Server 2000 systems to look after.
Post #1022032
Geoff A
Geoff A
Posted Wednesday, November 17, 2010 3:59 AM
SSC-Addicted
Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 485,
Visits: 1,568
hrc (11/17/2010)
What about @@CPU_BUSY and @@IO_BUSY
if you checked those two values e.g. every 10 seconds and the @CPU_BUSY had increased 8 seconds I would assume that it was running on 80%, that without the VBA and the security issues it imposes.
might be possible, but i never found a good way to do that with either @@CPU_BUSY or sp_monitor.
by using the Win32_PerfRawData_PerfOS_Processor object, the data is coming from the same place that perfmon uses. I find that to be highly accurate.
Thanks
Post #1022033
satalaj
satalaj
Posted Wednesday, November 17, 2010 4:39 AM
Forum Newbie
Group: General Forum Members
Last Login: Friday, December 03, 2010 8:07 AM
Points: 8,
Visits: 25
yes, I know that query who leads high CPU utilisation.
I had written that query in
while
loop. It simply deletes top 500 records with UPDlock,READPAST
and insert them into linked SERVER B.
Any suggestion?
Post #1022062
« Prev Topic
|
Next Topic »
59 posts, Page 1 of 6
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
