November 17, 2011 at 12:53 pm
Excellent, Thank you, Ron. Briefcased this one.
November 17, 2011 at 2:37 pm
Hello 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.pdf
located 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.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 17, 2011 at 4:39 pm
Hi,
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.
Here, i must suggest that please include the recommended threshold values of those counter.
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.
November 18, 2011 at 1:05 am
Dear Ron,
The link for In our paper, SQL Server Instance Health Monitoring Tool, dit not work.
Get error "file dit not start with %PDF"
Peter
November 18, 2011 at 6:19 am
@ 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).
November 21, 2011 at 6:33 am
Send 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.
November 21, 2011 at 6:46 pm
FYI I found the correct link (via Google of all places).
November 22, 2011 at 1:26 am
Thank you frome22. Now I have something to read this morning 🙂
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
November 22, 2011 at 3:16 am
Thanks Frome22
The link you send is working now en can make a performance monitor.
Peter
November 22, 2011 at 8:02 am
frome22 (11/21/2011)
FYI I found the correct link (via Google of all places).
Many thanks!!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 23, 2011 at 4:41 am
Hi 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 😉
July 27, 2012 at 11:08 pm
Hi 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!!!
Mithra
October 5, 2012 at 10:42 am
GreyBeard (11/17/2011)
Comments posted to this topic are about the item <A HREF="/articles/Performance+Tuning/71784/">Performance Monitoring with Dynamic Management Views</A>
I know this is OLD .. but still someone will stumble upon this and find it useful ... 😀
Getting 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
/***
* +-++-++-++-++-++-++-++-+ +-++-++-++-++-++-++-+ +-++-++-+
* |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 ratio
SELECT ROUND(CAST(A.cntr_value1 AS NUMERIC) / CAST(B.cntr_value2 AS NUMERIC), 3) AS Buffer_Cache_Hit_Ratio
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 + ':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 minutes
SELECT round((CAST(cntr_value AS NUMERIC) / 60), 1) AS 'Page Life Expectancy in Minutes'
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 = 'Page life expectancy'
--Returns pages read per second
SELECT cntr_value AS 'Page reads per Second'
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 = 'Page reads/sec'
--Returns pages written per second
SELECT cntr_value AS 'Page writes per Second'
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 = 'Page writes/sec'
--Returns Free list Stall per second
SELECT cntr_value AS 'Free List Stalls per second'
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 = 'Free list stalls/sec'
--Returns Lazy writes per second
SELECT cntr_value AS 'Lazy writes per second'
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 = 'Lazy writes/sec'
--Returns Total SQL Server Memory
SELECT cntr_value AS 'Total SQL Server Memory'
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 + ':Memory Manager'
AND counter_name = 'Total Server Memory (KB)'
--Average Latch Wait Time
SELECT 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 grants
SELECT cntr_value AS 'Pending memory grants'
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 = 'Pending memory grants count'
-- Returns Pending Disk IO Count
SELECT [pending_disk_io_count] AS [Pending Disk IO Count]
FROM sys.dm_os_schedulers
-- Returns the number of user connections
SELECT cntr_value AS [User Connections]
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 + ':General Statistics'
AND counter_name = 'User Connections'
--Returns CPU Utilization Percentage
SELECT 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 Size
SELECT instance_name AS 'DB Name'
,cntr_value AS 'Data File 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 = '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 A
INNER 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 Used
SELECT instance_name AS 'DB'
,cntr_value AS 'Percent Log Used'
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Percent Log Used'
--Returns Transactions per second
SELECT instance_name AS 'DB Name'
,cntr_value AS 'Transactions per second'
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 = 'Transactions/sec'
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
October 24, 2012 at 7:39 am
Hi 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!
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply