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

Scripts To Monitor SQL SERVER Expand / Collapse
Author
Message
Posted Monday, April 1, 2013 11:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 25, 2013 1:48 PM
Points: 25, Visits: 74
Hi Everybody ,
it's been two or three weeks I'm trying to find scripts to monitor different SQL SERVER indicators, but I can't find. Could someone help me Plz?
- Weekly CPU (to create a graph for 7 days )
-Weekly Buffer Hit Ratio
Post #1437699
Posted Tuesday, April 2, 2013 3:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 6:36 AM
Points: 102, Visits: 1,076
This query actually tells you the CPU utilization by SQL Server 2008, in one minute increments.


-- Get CPU Utilization History for last 30 minutes (SQL 2008)
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

SELECT TOP(30) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
'int')
AS [SQLProcessUtilization], [timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%') AS x
) AS y
ORDER BY record_id DESC;


Regards,
Kumar
Post #1437769
Posted Tuesday, April 2, 2013 3:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 25, 2013 1:48 PM
Points: 25, Visits: 74
Thank You ,
Have you a script like this , that return information about Buffer Cache hit ratio over time ?
Post #1437776
Posted Tuesday, April 2, 2013 4:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 6:36 AM
Points: 102, Visits: 1,076
Hope after some modify,it will helps!
select [object_name], [counter_name], [cntr_value]
from master.dbo.sysperfinfo
where counter_name = 'Buffer cache hit ratio'
or counter_name = 'Buffer cache hit ratio base'


Regards,
Kumar
Post #1437820
Posted Tuesday, April 2, 2013 5:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 25, 2013 1:48 PM
Points: 25, Visits: 74
Re ,
I don't see how to modify the script to obtain the history of the buffer cache hit ratio, have you an idea?
Post #1437845
Posted Wednesday, April 3, 2013 11:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:51 PM
Points: 7,140, Visits: 12,763
Some of the data you want is available through the DMVs, but only for a short time period and it is completely lost after a service restart. To monitor SQL Server and trend it's activity over time you'll need to capture and store that information long term. You can spend lots of time building up scripts to do this manually. Or you could look into the Management Data Warehouse or thrid-party tools like redgate SQL Monitor.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1438519
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse