InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 21:05:41 GMT20http://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxThat link is just the page that DOES load. that article has a link embedded that no longer works.I guess an alternative to providing a fixed link would be to provide a similar solution?Wed, 24 Oct 2012 07:54:17 GMTlrobbinshttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxHere's a link in SkyDrive, maybe you'll have better luck with it.[url=https://skydrive.live.com/redir?resid=AC9FCEA14097CEE3!105&authkey=!AF0PrJZzRzbSadY][/url]Wed, 24 Oct 2012 07:51:20 GMTdfromehttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxOther people seem to have that problem, I'm not sure why, when I copy/paste the link into chrome it opens up the PDF fine (with Adobe integrated in).Wed, 24 Oct 2012 07:47:20 GMTdfromehttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxHi Ron,Great article! But I wasn't able to open the "SQL Server Instance Health Monitoring Tool" article. Can you give the the correct link?Regards!Wed, 24 Oct 2012 07:39:46 GMTamnshttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspx[quote][b]GreyBeard (11/17/2011)[/b][hr]Comments posted to this topic are about the item [B]&lt;A HREF="/articles/Performance+Tuning/71784/"&gt;Performance Monitoring with Dynamic Management Views&lt;/A&gt;[/B][/quote]I know this is OLD .. but still someone will stumble upon this and find it useful ... :-DGetting all the scripts to be dynamic ....instead of 'MSSQL$URINSTANCENAME:Databases'below is the code :-- Modified by Kin... to make the script dynamic irrespective of default of Named Instance[code="sql"]/*** * +-++-++-++-++-++-++-++-+ +-++-++-++-++-++-++-+ +-++-++-+ * |O||r||i||g||i||n||a||l| |A||u||t||h||o||r||:| |R||o||n| * +-++-++-++-++-++-++-++-+ +-++-++-+++-+-+-++-++-+-++-++-+ * |M||o||d||i||f||i||e||d| |B||y| |:| |K||i||n| * +-++-++-++-++-++-++-++-+ +-++-+ +-+ +-++-++-+ * Ref: http://www.sqlservercentral.com/articles/Performance+Tuning/71784/ * Modification: Made it dynamic to work with Default and Named Instances Divide by Zero Error is resolved by use of COALESCE and NULLIF */--Returns the buffer cache hit ratioSELECT ROUND(CAST(A.cntr_value1 AS NUMERIC) / CAST(B.cntr_value2 AS NUMERIC), 3) AS Buffer_Cache_Hit_RatioFROM ( SELECT cntr_value AS cntr_value1 FROM sys.dm_os_performance_counters WHERE object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'SQLServer' ELSE 'MSSQL$' + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR) END + ':Buffer Manager' AND counter_name = 'Buffer cache hit ratio' ) AS A ,( SELECT cntr_value AS cntr_value2 FROM sys.dm_os_performance_counters WHERE object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'SQLServer' ELSE 'MSSQL$' + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR) END + ':Buffer Manager' AND counter_name = 'Buffer cache hit ratio base' ) AS B--Returns the page life expectancy in minutesSELECT round((CAST(cntr_value AS NUMERIC) / 60), 1) AS 'Page Life Expectancy in Minutes'FROM sys.dm_os_performance_countersWHERE object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'SQLServer' ELSE 'MSSQL$' + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR) END + ':Buffer Manager' AND counter_name = 'Page life expectancy'--Returns pages read per secondSELECT cntr_value AS 'Page reads per Second'FROM sys.dm_os_performance_countersWHERE object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'SQLServer' ELSE 'MSSQL$' + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR) END + ':Buffer Manager' AND counter_name = 'Page reads/sec'--Returns pages written per secondSELECT cntr_value AS 'Page writes per Second'FROM sys.dm_os_performance_countersWHERE object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'SQLServer' ELSE 'MSSQL$' + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR) END + ':Buffer Manager' AND counter_name = 'Page writes/sec'--Returns Free list Stall per secondSELECT cntr_value AS 'Free List Stalls per second'FROM sys.dm_os_performance_countersWHERE object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'SQLServer' ELSE 'MSSQL$' + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR) END + ':Buffer Manager' AND counter_name = 'Free list stalls/sec'--Returns Lazy writes per secondSELECT cntr_value AS 'Lazy writes per second'FROM sys.dm_os_performance_countersWHERE object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'SQLServer' ELSE 'MSSQL$' + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR) END + ':Buffer Manager' AND counter_name = 'Lazy writes/sec'--Returns Total SQL Server MemorySELECT cntr_value AS 'Total SQL Server Memory'FROM sys.dm_os_performance_countersWHERE object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'SQLServer' ELSE 'MSSQL$' + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR) END + ':Memory Manager' AND counter_name = 'Total Server Memory (KB)'--Average Latch Wait TimeSELECT ROUND(CAST(A.cntr_value1 AS NUMERIC) / CAST(B.cntr_value2 AS NUMERIC), 3) AS [Average Latch Wait Time]FROM ( SELECT cntr_value AS cntr_value1 FROM sys.dm_os_performance_counters WHERE object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'SQLServer' ELSE 'MSSQL$' + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR) END + ':Latches' AND counter_name = 'Average Latch Wait Time (ms)' ) AS A ,( SELECT cntr_value AS cntr_value2 FROM sys.dm_os_performance_counters WHERE object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'SQLServer' ELSE 'MSSQL$' + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR) END + ':Latches' AND counter_name = 'Average Latch Wait Time Base' ) AS B-- Returns Pending memory grantsSELECT cntr_value AS 'Pending memory grants'FROM sys.dm_os_performance_countersWHERE object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'SQLServer' ELSE 'MSSQL$' + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR) END + ':Resource Pool Stats' AND counter_name = 'Pending memory grants count'-- Returns Pending Disk IO CountSELECT [pending_disk_io_count] AS [Pending Disk IO Count]FROM sys.dm_os_schedulers-- Returns the number of user connectionsSELECT cntr_value AS [User Connections]FROM sys.dm_os_performance_countersWHERE object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'SQLServer' ELSE 'MSSQL$' + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR) END + ':General Statistics' AND counter_name = 'User Connections'--Returns CPU Utilization PercentageSELECT coalesce((ROUND(CAST(A.cntr_value1 AS NUMERIC) / CAST(nullif(B.cntr_value2,0) AS NUMERIC), 3)),0) * 100 AS [CPU Utilization Percentage]FROM ( SELECT cntr_value AS cntr_value1 FROM sys.dm_os_performance_counters WHERE object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'SQLServer' ELSE 'MSSQL$' + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR) END + ':Resource Pool Stats' AND counter_name = 'CPU usage %' ) AS A ,( SELECT cntr_value AS cntr_value2 FROM sys.dm_os_performance_counters WHERE object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'SQLServer' ELSE 'MSSQL$' + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR) END + ':Resource Pool Stats' AND counter_name = 'CPU usage % base' ) AS B--Returns Data File SizeSELECT instance_name AS 'DB Name' ,cntr_value AS 'Data File Size'FROM sys.dm_os_performance_countersWHERE object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'SQLServer' ELSE 'MSSQL$' + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR) END + ':Databases' AND counter_name = 'Data File(s) Size (KB)'--Remaining Log File KB SELECT A.instance_name AS 'DB' ,CAST(Size AS NUMERIC) - CAST(Used AS NUMERIC) AS [Available Log File KB]FROM ( SELECT instance_name ,cntr_value AS Size FROM sys.dm_os_performance_counters WHERE object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'SQLServer' ELSE 'MSSQL$' + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR) END + ':Databases' AND counter_name = 'Log File(s) Size (KB)' ) AS AINNER JOIN ( SELECT instance_name ,cntr_value AS Used FROM sys.dm_os_performance_counters WHERE object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'SQLServer' ELSE 'MSSQL$' + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR) END + ':Databases' AND counter_name = 'Log File(s) Used Size (KB)' ) AS B ON A.instance_name = B.instance_name-- Returns percent Log File UsedSELECT instance_name AS 'DB' ,cntr_value AS 'Percent Log Used'FROM sys.dm_os_performance_countersWHERE counter_name = 'Percent Log Used'--Returns Transactions per secondSELECT instance_name AS 'DB Name' ,cntr_value AS 'Transactions per second'FROM sys.dm_os_performance_countersWHERE object_name = CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'SQLServer' ELSE 'MSSQL$' + CAST(SERVERPROPERTY('InstanceName') AS VARCHAR) END + ':Databases' AND counter_name = 'Transactions/sec'[/code]Fri, 05 Oct 2012 10:42:19 GMTSQLQuest29http://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxHi Guys, I read this article and it really looks very good.But i am not able to open the link 'SQL Server Instance Health Monitoring Tool'.Looks like the PDF is broken.Could anyone help me on this.Please send me the pdf to my email(Bojannamk@gmail.com),if anyone could access and download.Thanks bunch!!!MithraFri, 27 Jul 2012 23:08:26 GMTMithrahttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspx@epfax,Most of the time you should be looking for consistently bad stats. Some stats are not as consistent in my opinion. E.g., Memory Grants Outstanding. You always want this value at 0.Wed, 23 Nov 2011 06:19:24 GMTlrobbinshttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxHi Ron, very interesting article. But one little thing: "frequency": how often a certain event will happen in a period of time - "intervall": a period of time between 2 certain events ;-)Wed, 23 Nov 2011 04:41:22 GMTepfaxhttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspx[quote][b]frome22 (11/21/2011)[/b][hr]FYI I found the correct link (via Google of all places).[url=http://rjssqlservernotes.files.wordpress.com/2011/11/performance-monitoring-with-dynamic-management-views.pdf]http://rjssqlservernotes.files.wordpress.com/2011/11/performance-monitoring-with-dynamic-management-views.pdf[/url][/quote]Many thanks!!- webrunnerTue, 22 Nov 2011 08:02:10 GMTwebrunnerhttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxThanks Frome22The link you send is working now en can make a performance monitor.PeterTue, 22 Nov 2011 03:16:45 GMTPeter Verstappenhttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxThank you frome22. Now I have something to read this morning :)Tue, 22 Nov 2011 01:26:41 GMTDirk.Hondonghttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxFYI I found the correct link (via Google of all places).[url=http://rjssqlservernotes.files.wordpress.com/2011/11/performance-monitoring-with-dynamic-management-views.pdf]http://rjssqlservernotes.files.wordpress.com/2011/11/performance-monitoring-with-dynamic-management-views.pdf[/url]Mon, 21 Nov 2011 18:46:57 GMTfrome22http://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxSend me an email (private message) and I'll send you the rar, I couldn't find any way to upload a rar on the blog.Mon, 21 Nov 2011 06:33:27 GMTGreyBeardhttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspx@ Old Hand,I recommend using this for that:SQL Server 2008 Query Performance Tuning Distilled (Expert's Voice in SQL Server)Chapter 2 specifically.One key thing is that the thresholds are best determined by comparing to a baseline you create. The baseline being what metrics look like under normal operating conditions (when things are going smooth).Fri, 18 Nov 2011 06:19:49 GMTlrobbinshttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxDear Ron,The link for In our paper, SQL Server Instance Health Monitoring Tool, dit not work.Get error "file dit not start with %PDF"PeterFri, 18 Nov 2011 01:05:07 GMTPeter Verstappenhttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxHi,Thanks man for merging this stuff into one article.DBA's might already know these Performance Counter and most of them are using them as well but may be not quite often. However, such articles gives more insight to a novice. This article must be meant for beginners and for those who are still reluctant to start the DMVs. [b]Here, i must suggest that please include the recommended threshold values of those counter. [/b]Reading the values and collecting the data is one thing, however, taking action on that data is only possible if one know what are recommended guidelines and what are the Best or worst level of thresholds which needs further actions from a DBA.Thanks.Thu, 17 Nov 2011 16:39:24 GMTiBarhttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxHello Ron,I tried to download the paper referenced in your article, "SQL Server Instance Health Monitoring Tool," http://rjssqlservernotes.files.wordpress.com/2011/10/sqlserverinstancehealthmonitor.pdflocated at this page:http://www.sqlservercentral.com/articles/Performance+Tuning/71784/But I get an error: "File does not begin with '%PDF-'Do you -- or does anyone else -- have a working link to this paper? I'm eager to read it.Thanks for any help.webrunnerThu, 17 Nov 2011 14:37:44 GMTwebrunnerhttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxExcellent, Thank you, Ron. Briefcased this one.Thu, 17 Nov 2011 12:53:25 GMTJon Russellhttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxAgreed, I have tried this link in both Firefox and IE and receive the same error:"File does not begin with '%PDF-".Thu, 17 Nov 2011 11:07:12 GMTSlevinhttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxTruly well written article. I personally was researching plans to setup something similar and your article has just made that job so much easier for me. :-)Thu, 17 Nov 2011 10:41:37 GMTlokeshgm7http://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxSapen,I recommend using this for that:SQL Server 2008 Query Performance Tuning Distilled (Expert's Voice in SQL Server)Chapter 2 specifically.One key thing is that the thresholds are best determined by comparing to a baseline you create. The baseline being what metrics look like under normal operating conditions (when things are going smooth).Thu, 17 Nov 2011 08:23:01 GMTlrobbinshttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxThanks much for the article Ron. For the perfmon counters where can I get the threshold values.Thu, 17 Nov 2011 08:19:37 GMTSapenhttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxRon,Thanks for taking the time to write an article using a real world example of why DMVs can be much more powerful than their Performance Monitor counterpart. Would you say that best use case of DMVs over performance monitor & SQL Profiler would be that the recorded data can be better used as a central repository for various SQL instances (like in your 50 geographical location) case?I typically use just performance monitor to do these things, and I'm not too sure of the best use cases for using DMVs over them, other than preference.Lastly, the counters you provided, do you recommend to monitor these 24x7 or just in re-active cases or scheduled pro-active cases (e.g., on the 1st day of every month for 24 hours).Thu, 17 Nov 2011 07:41:15 GMTlrobbinshttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxHiI was able to download the file, but I can't open it using acrobat reader, it says that file is not a PDF file...http://rjssqlservernotes.files.wordpress.com/2011/10/sqlserverinstancehealthmonitor.pdfBR PThu, 17 Nov 2011 06:58:53 GMTprzemyslaw.duszahttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxRon,Where can you download the script manager tool you wrote - that looks like a real neat utility I could use to categorize my scripts.Thanks,DougThu, 17 Nov 2011 06:44:09 GMTDouglas Osborne-229812http://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxEverything downloaded fine for me - nice article.DougThu, 17 Nov 2011 06:30:46 GMTDouglas Osborne-229812http://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspx[quote]I tried to take a look into the paper you´ve mentioned (SQL Server Instance Health Monitoring Tool) but it seems that the pdf is broken. It says the data type is incorrectly formatted. [/quote]yep, pdf is broken :(Thu, 17 Nov 2011 05:33:35 GMTprzemyslaw.duszahttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxHi Ian,thanks for mentoning the book. It´s now on my wish list The sample chapters look promising.RegardsDirkThu, 17 Nov 2011 05:27:34 GMTDirk.Hondonghttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxThanks for the article - good stuff.Thu, 17 Nov 2011 05:18:10 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxGood article, although a second one on what the figures mean in the real world would be useful.BTW the link to the first chapter of the book works fine, but is a bit slow.ChrisThu, 17 Nov 2011 02:33:19 GMTchris.puncherhttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxHi Ron,I tried to take a look into the paper you´ve mentioned (SQL Server Instance Health Monitoring Tool) but it seems that the pdf is broken. It says the data type is incorrectly formatted. RegardsDirkThu, 17 Nov 2011 02:22:24 GMTDirk.Hondonghttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxHi,Very nice article!Readers can discover a lot more about improving SQL performance via DMVs in this recently published book "SQL Server DMVs in Action" [url=http://www.manning.com/stirk]www.manning.com/stirk[/url]. It contains more than 100 scripts to identify problems, and offers a wide range of solutions.Chapters 1 and 3 can be downloaded for free. Chapter 1 includes scripts for:A simple monitorFinding your slowest queriesFind your missing indexesIdentifying what SQL is running nowQuickly find a cached planThanksIan Thu, 17 Nov 2011 00:19:05 GMTianstirkhttp://www.sqlservercentral.com/Forums/Topic1207258-2881-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Performance+Tuning/71784/">Performance Monitoring with Dynamic Management Views</A>[/B]Thu, 17 Nov 2011 00:02:03 GMTGreyBeard