Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Performance Monitoring with Dynamic Management Views Expand / Collapse
Author
Message
Posted Monday, November 21, 2011 6:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 3:07 PM
Points: 65, Visits: 331
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.
Post #1209226
Posted Monday, November 21, 2011 6:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 24, 2011 9:13 PM
Points: 1, Visits: 6
FYI I found the correct link (via Google of all places).

http://rjssqlservernotes.files.wordpress.com/2011/11/performance-monitoring-with-dynamic-management-views.pdf

Post #1209759
Posted Tuesday, November 22, 2011 1:26 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:08 PM
Points: 95, Visits: 1,139
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
Post #1209882
Posted Tuesday, November 22, 2011 3:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 5, 2014 8:45 AM
Points: 6, Visits: 203
Thanks Frome22

The link you send is working now en can make a performance monitor.

Peter
Post #1209944
Posted Tuesday, November 22, 2011 8:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:33 PM
Points: 2,432, Visits: 2,868
frome22 (11/21/2011)
FYI I found the correct link (via Google of all places).

http://rjssqlservernotes.files.wordpress.com/2011/11/performance-monitoring-with-dynamic-management-views.pdf



Many thanks!!

- webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #1210222
Posted Wednesday, November 23, 2011 4:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 7:53 AM
Points: 19, Visits: 26
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
Post #1210871
Posted Wednesday, November 23, 2011 6:19 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 1, 2012 12:40 PM
Points: 7, Visits: 44
@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.



Post #1210931
Posted Friday, July 27, 2012 11:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 3:45 PM
Points: 31, Visits: 383
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
Post #1336865
Posted Friday, October 5, 2012 10:42 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, December 4, 2014 11:04 AM
Points: 739, Visits: 3,809
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
Post #1369197
Posted Wednesday, October 24, 2012 7:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 21, 2014 4:56 AM
Points: 212, Visits: 490
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!
Post #1376476
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse