April 1, 2013 at 11:23 pm
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
April 2, 2013 at 3:31 am
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
April 2, 2013 at 3:48 am
Thank You ,
Have you a script like this , that return information about Buffer Cache hit ratio over time ?
April 2, 2013 at 4:55 am
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
April 2, 2013 at 5:54 am
Re ,
I don't see how to modify the script to obtain the history of the buffer cache hit ratio, have you an idea? :unsure:
April 3, 2013 at 11:56 am
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[/url].
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply