December 19, 2012 at 10:17 am
Hi,
I am using sqlserver2008r2,I want to automate daily health check for my sql servers.I also wanted that If I will run a query and I will get the output of that query .I wanted to know the scheduled the T sql statement and output should come in every two hours on my personal emailid to get my Servers status.
Can any one suggest the solution?
Thanks
December 19, 2012 at 10:19 am
Do you have the query you want to run? Or are you asking for help with that?
Or do you just need help with scheduling and e-mailing the results?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 19, 2012 at 10:29 am
Yes I wanted the HELP!!!
I have query I wanted this querys output as HTML Format or CSV. on my email ID this query should execute in every one hour and result I should get
on my mail ID just like alert
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)]
FROM sys.dm_os_sys_info
GO
--
--
select count(*) as CPUsInUseBySQL from sys.dm_os_schedulers where status='VISIBLE ONLINE'
--
--
select value_in_use as SQLMAXDOP from sys.configurations where name = 'max degree of parallelism'
--
-- *** GET CPU UTILIZATION HISTORY ***
SELECT 'CPU UTILIZATION HISTORY DATA'
declare @ts_now bigint
--select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info
select @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) from sys.dm_os_sys_info;
select TOP 20
dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,
SQLProcessUtilization,
100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization,
SystemIdle
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
GO
--
-- *** SIGNAL & RESOURCE WAITS PERCENTAGE ***
-- Calculates the signal and resource waits as a percentage of overall wait time.
-- As Signal waits are the time waiting for the CPU to service a thread, if the signal wait
-- time is above 10-15%, then your CPU may be under pressure. These wait stats are cumulative
-- since SQL started, so will not give a result that reflects CPU loading over the previous
-- few minutes. You can use DBCC to clear the wait stats and get a better snapshot of the
-- current CPU loading state.
-- Resource Waits occur when a worker requests access to a resource that is not available
-- because it is being used by another thread, or not yet available. These are the most
-- common types of waits, and surface as locks, latches, network and I/O.
-- Summary:
-- The time waiting for a resource is shown as Resource Waits.
-- The time waiting in the runnable queue for CPU is called Signal Waits.
-- CPU pressure which may be alleviated by faster or more CPUs. Alternately, CPU pressure can
-- be reduced by eliminating unnecessary sorts (indexes can avoid sorts in order & group by’s)
-- and joins, and compilations (and re-compilations).
--
-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)
SELECT 'SIGNAL & RESOURCE WAIT DATA'
Select
signal_wait_time_ms=sum(signal_wait_time_ms),
'%Signal (CPU) Waits - waits above 10-15% is usually a sign of CPU pressure' = cast(100.0 * sum(signal_wait_time_ms) / sum (wait_time_ms) AS numeric(20,2)),
resource_wait_time_ms=sum(wait_time_ms - signal_wait_time_ms),
'%Resource waits'= cast(100.0 * sum(wait_time_ms - signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))
From sys.dm_os_wait_stats
GO
--
--
DECLARE @pg_size INT, @Instancename varchar(50)
SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name])))
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio'
PRINT '----------------------------------------------------------------------------------------------------'
PRINT 'Memory usage details for SQL Server instance ' + @@SERVERNAME + ' (' + CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - ' + SUBSTRING(@@VERSION, CHARINDEX('X',@@VERSION),4) + ' - ' + CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'
PRINT '----------------------------------------------------------------------------------------------------'
SELECT 'MEMORY CONFIGURATION ON THE SERVER VISIBLE TO THE OS'
SELECT physical_memory_in_bytes/1048576.0 as [Physical Memory_MB],
physical_memory_in_bytes/1073741824.0 as [Physical Memory_GB],
virtual_memory_in_bytes/1048576.0 as [Virtual Memory_MB],
virtual_memory_in_bytes/1073741824.0 as [Virtual Memory_GB]
FROM sys.dm_os_sys_info
--
SELECT 'TOTAL MEMORY USED BY SQL SERVER INSTANCE'
SELECT cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Total Server Memory (KB)'
--
-- *** MEMORY UTILIZATION BY DATABASE ***
SELECT 'MEMORY UTILIZATION DATA BY DATABASE'
SELECT DB_NAME(database_id) AS [Database Name], COUNT(*) * 8 / 1024 AS [MB Used]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY COUNT (*) * 8 / 1024 DESC
GO
--
-- *** PAGE LIFE EXPECTANCY (PLE) VALUE FOR DEFAULT INSTANCE ***
-- PLE is a good measurement of memory pressure. Higher PLE is better, below 300 is generally bad.
-- Watch the trend, not the absolute value.
SELECT 'PAGE LIFE EXPECTANCY DATA'
SELECT [object_name], [counter_name], cntr_value AS [Page Life Expectancy < 300 maybe bad]
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME LIKE '%Buffer Manager%' -- Modify this if you have named instances
AND counter_name = 'Page life expectancy'
GO
--
-- *** BUFFER CACHE HIT RATIO ***
-- Alter the 'MSSQLServer:Buffer Manager' if using a name instance e.g --'MSSQL$INST1:Buffer Manager'
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 = 'SQLServer: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 = 'SQLServer:Buffer Manager' AND counter_name = 'Buffer cache hit ratio base'
) AS B;
GO
December 19, 2012 at 10:41 am
In addition to Gus' question, are you going to manually look through this data every two hours? Or are you storing it somewhere? If it's the former, I think you're making a design mistake.
Moved to 2012 forum
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply