SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance Monitoring with Dynamic Management Views


Performance Monitoring with Dynamic Management Views

Author
Message
GreyBeard
GreyBeard
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 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.
frome22
frome22
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 9
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
Dirk.Hondong
Dirk.Hondong
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 1141
Thank you frome22. Now I have something to read this morning Smile

--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
Peter Verstappen
Peter Verstappen
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 243
Thanks Frome22

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

Peter
webrunner
webrunner
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7866 Visits: 4000
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

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"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
epfax
epfax
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 29
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 ;-)
lrobbins
lrobbins
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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.
Mithra
Mithra
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 428
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
SQLQuest29
SQLQuest29
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2173 Visits: 4367
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 ... :-D
Getting all the scripts to be dynamic ....instead of 'MSSQL$URINSTANCENAMEBigGrinatabases'
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 + 'BigGrinatabases'
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 + 'BigGrinatabases'
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 + 'BigGrinatabases'
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 + 'BigGrinatabases'
AND counter_name = 'Transactions/sec'





______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor :-)
amns
amns
Say Hey Kid
Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)

Group: General Forum Members
Points: 671 Visits: 569
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search