InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 15:12:22 GMT20http://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxNice article!Tue, 25 Dec 2012 08:17:32 GMTNeha05http://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxI think[code="plain"]i = 0 Do While i < 1[/code]Should be [code="plain"]i = 0 Do While i < 10[/code]Fri, 19 Oct 2012 07:47:39 GMTkrowleyhttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxGeoff,I've been using it more as a generic CPU indicator. It let's me know when things are crossing the threshold and for how long. If you want to go beyond that you should probably get a more robust monitoring tool.VadimFri, 11 Mar 2011 14:08:58 GMTVadim Mordkovichhttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxHey Vadim,thanks for the script. however, it presents a bit of a problem when you have multiple instances on a server.it also does not account for 'other' CPU hogs. i have had to deal with things like anti-virus engines eating up 90% of the CPU. now even though as a DBA, it might not be my job to worry about anti-virus software, i do need to be aware when the SQL server is struggling to process requests because of it.thanks.Fri, 11 Mar 2011 11:53:35 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxDECLARE @CPU_BUSY int, @IDLE int, @seconds int, @secondsString varchar(50) SELECT @CPU_BUSY = @@CPU_BUSY, @IDLE = @@IDLE WAITFOR DELAY ''000:00:01''set @seconds = 0WHILE (SELECT (@@CPU_BUSY - @CPU_BUSY)/((@@IDLE - @IDLE + @@CPU_BUSY - @CPU_BUSY) *1.00) *100 AS CPUBusyPct) > 80 AND @seconds < 55BEGIN set @seconds = @seconds + 1 SELECT @CPU_BUSY = @@CPU_BUSY, @IDLE = @@IDLE WAITFOR DELAY ''000:00:01'' if @seconds % 10 = 0 BEGIN -- DO Something like send an email ENDENDTue, 01 Mar 2011 14:26:24 GMTVadim Mordkovichhttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxNice and very helpful articleMon, 21 Feb 2011 22:44:18 GMTbakiaraj_cithttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxAhhhhhhhhhhhhhh. Sorry, not a VBScript guy and had a bit of a brain freeze this afternoon while stepping through your process.Thanks!!Wed, 26 Jan 2011 13:51:21 GMTDBA_Oshvegashttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspx[quote][b]DBA_Oshvegas (1/26/2011)[/b][hr]I am confused about the part "SQLCMD that uses the raiserror command to send messages to the Windows Event Application Log". Is this a job step executed as Operating System (CmdExec) or is this something else?Thanks![/quote]its executed from within the VB Script. same way it would work from a DOS prompt.you could open a cmd prompt where SQL had been installed and run SQLCMD.Wed, 26 Jan 2011 13:20:13 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxI am confused about the part "SQLCMD that uses the raiserror command to send messages to the Windows Event Application Log". Is this a job step executed as Operating System (CmdExec) or is this something else?Thanks!Wed, 26 Jan 2011 13:05:54 GMTDBA_Oshvegashttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxGreat article Geoff, very useful thanks for sharing!Thu, 13 Jan 2011 18:19:25 GMTKim Killian-SiteDataViewhttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxeditted - removed questionTue, 04 Jan 2011 09:20:24 GMTrew-370421http://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxI can't wait for an article on the dashboard. :-PSat, 20 Nov 2010 01:24:36 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspx[quote][b]BobSaint (11/19/2010)[/b][hr]Geoff - that is awesome!! Very nice presentation - and it covers all the prime touch points near and dear to the DBA at a glance. My curiosity is bristled - data cached in a local database? for how long? User-settable thresholds on all counters? User-settable sample rates? Alerts when thresholds exceeded (SQL or UI)? All hooks through WMI? Can you track other stuff like auto-grows, errors, failed authentications?To be honest, I have yet to dabble in PS, although it seems like all the rage for scripting these days.Again - very nice job.[/quote]its a small local DB. its all configurable.alerts are not a part of this project. almost all is through WMI except for user connections.alerting I handle differently, which is another article i still owe to Steve..... (part two of this article... [url]http://www.sqlservercentral.com/articles/Monitoring/71390/[/url]Fri, 19 Nov 2010 15:23:40 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxGeoff - that is awesome!! Very nice presentation - and it covers all the prime touch points near and dear to the DBA at a glance. My curiosity is bristled - data cached in a local database? for how long? User-settable thresholds on all counters? User-settable sample rates? Alerts when thresholds exceeded (SQL or UI)? All hooks through WMI? Can you track other stuff like auto-grows, errors, failed authentications?To be honest, I have yet to dabble in PS, although it seems like all the rage for scripting these days.Again - very nice job.Fri, 19 Nov 2010 14:14:59 GMTBobSainthttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxBobSaint, yes, I am hard at work learning what powershell and SSRS can do.... one day, when it is all finished I will write an article on how I created this SQL Server Dashboard. (for FREE)[img]http://66.73.36.7/temp/newProject.JPG[/img]Fri, 19 Nov 2010 13:46:49 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxGeoff - nice article, thanks for posting.One thing I wanted to mention on security, if the account that SQL Agent runs with is denied access to run a command line script (CmdExec), a workaround would be to create a Proxy for CmdExec and then map an account that does have rights to run this job.Although I have never tried this with SQL 2000, I have with 2005+ and the documentation seems to imply you can do it: http://support.microsoft.com/kb/890775This keeps all security concerns happy because it elevates permissions to run the command line (VBScript in your case) [i]only [/i]for a specific job or jobs.Overall a very resourceful solution. I've been working on a "roll my own" solution to monitor several parameters of my servers through WMI hooks for some time now - disk free%, CPU Utilization, memory, I/O, etc. Who knows when I'll ever get it completed/tested but if I do I'll be sure to share!Cheers.Fri, 19 Nov 2010 10:01:01 GMTBobSainthttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspx[quote][b]Boxer (11/18/2010)[/b][hr]Jeff Moden: Yes, we can[/quote]Cool! I'll have to give it a try. In the meantime, someone should write an article on that! :-)Thu, 18 Nov 2010 19:52:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxJeff Moden: Yes, we canThu, 18 Nov 2010 06:29:57 GMTBoxerhttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxcrazy4sql ,the server must be over the threshold for 10 consecutive loops.if you want to test, use something to cause your CPU to run over the threshold for at least 1 minute.Thu, 18 Nov 2010 06:01:53 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxi see....I tried to play with it...have configured vbs as CPU utilisation as 1% but not getting any alert....so i am thinking of 2 reason:-1)server not constantly more than 2%. It might be coming down and going up very frequently.2) I am wrong and need more modification in vbs.Thu, 18 Nov 2010 05:35:27 GMTcrazy4sqlhttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxhi crazy4sql,the connection between the vb script and the alert is the raiserror command. when the error hits the sql server error log, the sql server alert service sees it and fires the email.thanks for reading.Thu, 18 Nov 2010 05:31:01 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxI might be wrong, but want to know that how the alert is connecting your job or knowing the threshold limit of cpu usage which you mentioned in vbs.Thu, 18 Nov 2010 05:01:29 GMTcrazy4sqlhttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxhi sir,It is very good. but tell me more some idea to decreas cpu utilization..Wed, 17 Nov 2010 22:12:42 GMTpdeokarhttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspx[quote][b]dukedba (11/17/2010)[/b][hr]Why is it so complicated? You should use resources available in MS SQL server to monitor bottle neck: CPU, Hard drive, Memory, and Network. Use Perfmon from windows. Set up with counter logs to monitor counters. http://www.youtube.com/watch?v=3yEEzqDE5qI&feature=related[/quote]Can you get Perfmon to send an email notification on an event like when the CPU has been at 90% for a minute or more?Wed, 17 Nov 2010 21:32:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxWhy is it so complicated? You should use resources available in MS SQL server to monitor bottle neck: CPU, Hard drive, Memory, and Network. Use Perfmon from windows. Set up with counter logs to monitor counters. http://www.youtube.com/watch?v=3yEEzqDE5qI&feature=relatedWed, 17 Nov 2010 16:58:56 GMTdukedbahttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxWhy is it so complicated? You should use resources available in MS SQL server to monitor bottle neck: CPU, Hard drive, Memory, and Network. Use Perfmon from windows. Set up with counter logs to monitor counters. http://www.youtube.com/watch?v=3yEEzqDE5qI&feature=relatedWed, 17 Nov 2010 16:57:33 GMTdukedbahttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxMy only concern with using 3rd party monitoring tools is that they could suddenly present you with a load of measurements/alerts which you don't fully understand!We are currently building up our list of checks one at a time (real-time alert + nightly reports). That way we fully understand what each counter means and what is typlical for our environment. That said, I've evaluated a selection of the tools (redgate, quest etc) and they do look great. I expect we will end up investing.Good article.ThanksDavidWed, 17 Nov 2010 16:25:11 GMTDavid Betteridgehttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxSSC-Enthusiastic: Becoz thats where we observe the max utilization and we can do max optimization!!Wed, 17 Nov 2010 13:05:35 GMTBoxerhttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxNot sure this is a totally accurate indicator of utilization, but might be "in the ballpark":-- Low-Budget Way to Get CPU UtilizationDECLARE @CPUStart INT, @CPUEnd INT, @IdleStart INT, @IdleEnd INT, @TotalStart INT, @TotalEnd INT; -- Make First MeasurementSELECT @CPUStart = @@CPU_BUSY, @IdleStart = @@IDLE; -- Delay 1 SecondsWAITFOR DELAY '000:00:01';-- Make Second MeasurementSELECT @CPUEnd = @@CPU_BUSY, @IdleEnd = @@IDLE; -- Compute Totals SELECT @TotalStart = (@IdleStart + @CPUStart);SELECT @TotalEnd = (@IdleEnd + @CPUEnd);-- Compute DeltaDECLARE @DeltaCPU FLOAT;SELECT @DeltaCPU = 100.0 * (@CPUEnd - @CPUStart) / (@TotalEnd - @TotalStart);SELECT CONVERT(DECIMAL(12, 2), @DeltaCPU) AS 'PercentCPU';Wed, 17 Nov 2010 12:05:17 GMTjohn_jakobhttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxAgree with Geoff :-)Wed, 17 Nov 2010 10:34:19 GMTBoxerhttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspx[quote][b]sfrancis 18850 (11/17/2010)[/b][hr]Interesting article.But CPU utilization is just one of very many counters to be tracking. Virtual memory, page table entries, page faults, disk volume latency, network - not to mention scores of SQL server specific metrics that should be trended so you can see issues over time before they bite you (page splits, etc).Rather than scripting your own monitoring, it seems a far better use of a DBAs time to just use a system that does all the above for you, and alerts you in the event of issues, and allows you to see trends of your systems and metrics. IMO, DBAs should focus on where they add the most value - business logic, improving query executions, reports (and digging into what reports are supposed to do, from a business point of view, so they can present actionable data.)That is truly valuable stuff, that can't be automated.Leave things that can be automated to tools that automate them, rather than spending expensive DBA time on them.For a few hundred a month to cover tens of databases (or other systems), something like LogicMonitor will save you a ton of time in monitoring, alerting and trending. Not to mention better uptime and availability.[url]http://www.logicmonitor.com/monitoring/databases/sql-server-monitoring/[/url][/quote]third party tools are obviously the best choice. Idera, Confio and RedGate all have tools far superior than my little VB Script. But at the begining of the article, I stated this is for places where $$$ for 3rd party tools is not available. For example, where I am currently contracted, I use this while I wait for the PO to be approved for SQL Monitor 2.Once I have SQL Monitor 2 up and running, I will no longer use this method.Wed, 17 Nov 2010 10:27:29 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxsfrancis: Does it load the system performance? How often does this run?Wed, 17 Nov 2010 10:09:59 GMTBoxerhttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxSatalaj, I think your problem most probably is indexing. Make sure you have the proper indexes on your table and make sure your queries always use that index. Having no, or the incorrect index can make SQL Server take a page lock or a table lock, which invalidates the whole model.Wed, 17 Nov 2010 10:06:53 GMTBoxerhttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxInteresting article.But CPU utilization is just one of very many counters to be tracking. Virtual memory, page table entries, page faults, disk volume latency, network - not to mention scores of SQL server specific metrics that should be trended so you can see issues over time before they bite you (page splits, etc).Rather than scripting your own monitoring, it seems a far better use of a DBAs time to just use a system that does all the above for you, and alerts you in the event of issues, and allows you to see trends of your systems and metrics. IMO, DBAs should focus on where they add the most value - business logic, improving query executions, reports (and digging into what reports are supposed to do, from a business point of view, so they can present actionable data.)That is truly valuable stuff, that can't be automated.Leave things that can be automated to tools that automate them, rather than spending expensive DBA time on them.For a few hundred a month to cover tens of databases (or other systems), something like LogicMonitor will save you a ton of time in monitoring, alerting and trending. Not to mention better uptime and availability.[url]http://www.logicmonitor.com/monitoring/databases/sql-server-monitoring/[/url]Wed, 17 Nov 2010 10:03:15 GMTsfrancis 18850http://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxHi Satalaj,Using the UPDLOCK in conjunction with the READPAST gives the best performance for processing queues. ReadPast however, needs an index on the order by column to work properly. Do you have a clustered indexing on the tables?It could be that without the index, a sort operation is needed to satisfy the "order by" clause. The engine might be escalating the lock to a less granular level (page / extend / table), increasing the probability of concurrency contention, locking resources needed by the second session. Just a guess..Rgds!!Wed, 17 Nov 2010 10:02:02 GMTBoxerhttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxSeems like the idea of just checking SQL Server's cpu utilization instead of the whole box's cpu utilization is a somewhat flawed concept. Heaven forbid there be other apps running on the same box, but if there were, and the app did soak up the cpu from sql server, that would still adversely affect the sql server in a way that would require dba attention, don't you think?To me, monitoring the whole box's cpu utilization and being alerted regardless of *what* is using the cpu, as Geoff's approach does, is the best way to go... is there a compelling reason to only care about sql server's cpu utilization?Wed, 17 Nov 2010 08:41:08 GMTgetoffmyfoothttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspx@Larry,If its SQL 2k5 or 2k8, then DMVs like dm_os_ring_buffers,dm_os_waitstats,dm_os_schedulers can help.If interested, refer to the following two whitepapers.Waits and queues - Check cpu pressure section [url]http://msdn.microsoft.com/en-us/library/cc966413.aspx[/url]Troubleshooting performance problems - check cpu bottleneck section [url]http://msdn.microsoft.com/en-us/library/cc966540.aspx[/url]Wed, 17 Nov 2010 08:12:37 GMTarr.nagarajhttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxReply to rance.malcom: Why dont you use MS profiler to run a trace and check instantly?Wed, 17 Nov 2010 08:06:18 GMTBoxerhttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxHi Boxer, If you see that Delete statement it uses 2 SQL hints (Updlock and readpast) meaning is if first SP locks first 100 rows the second SP will call next 100 rows with readpast which are commited and not locked.Thanks, Satalaj.Wed, 17 Nov 2010 08:00:42 GMTsatalajhttp://www.sqlservercentral.com/Forums/Topic1021910-2831-1.aspxThis is great info and something we encounter quite often.Is anyone aware of other articles that might describe ways to determine specific queries or processes that might be causing CPU utilization to be high?Wed, 17 Nov 2010 07:57:35 GMTrance.malcom