master

分支 (1)

管理

管理

master

MVPOps
/
MSSQLPerformanceInfos
/
InstanceInfos
/
InstancePerformanceInfos

/*
I/O
CPU
memory
network
from Instance standpoint
AG？
*/
--DECLARE @SQLProcessUtilization INT;			--SQL CPU使用玿
DECLARE @PageReadsPerSecond BIGINT --每秒page读取数
DECLARE @PageWritesPerSecond BIGINT --每秒page写入数
DECLARE @CheckpointPagesPerSecond BIGINT --每秒checkpoint数
DECLARE @LazyWritesPerSecond BIGINT --每秒Lazywrite数
DECLARE @BatchRequestsPerSecond BIGINT --每秒Batch请求数
DECLARE @CompilationsPerSecond BIGINT --每秒编译数
DECLARE @ReCompilationsPerSecond BIGINT --每秒重编译数
DECLARE @PageLookupsPerSecond BIGINT --每秒PageLookup数
DECLARE @TransactionsPerSecond BIGINT --每秒事务数
DECLARE @NetworkBytesTotalPerSecond BIGINT --每秒
DECLARE @stat_date DATETIME --取样时间，暂定5s
	-- Table for First Sample
DECLARE @RatioStatsX TABLE (
	[object_name] VARCHAR(128)
	,[counter_name] VARCHAR(128)
	,[instance_name] VARCHAR(128)
	,[cntr_value] BIGINT
	,[cntr_type] INT
	)
-- Table for Second Sample
DECLARE @RatioStatsY TABLE (
	[object_name] VARCHAR(128)
	,[counter_name] VARCHAR(128)
	,[instance_name] VARCHAR(128)
	,[cntr_value] BIGINT
	,[cntr_type] INT
	)

INSERT INTO @RatioStatsX (
	[object_name]
	,[counter_name]
	,[instance_name]
	,[cntr_value]
	,[cntr_type]
	)
SELECT [object_name]
	,[counter_name]
	,[instance_name]
	,[cntr_value]
	,[cntr_type]
FROM sys.dm_os_performance_counters

SET @stat_date = getdate()

SELECT TOP 1 @PageReadsPerSecond = cntr_value
FROM @RatioStatsX
WHERE counter_name = 'Page reads/sec'
	AND object_name = CASE
		WHEN @@SERVICENAME = 'MSSQLSERVER'
			THEN 'SQLServer:Buffer Manager'
		ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager'
		END

SELECT TOP 1 @PageWritesPerSecond = cntr_value
FROM @RatioStatsX
WHERE counter_name = 'Page writes/sec'
	AND object_name = CASE
		WHEN @@SERVICENAME = 'MSSQLSERVER'
			THEN 'SQLServer:Buffer Manager'
		ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager'
		END

SELECT TOP 1 @CheckpointPagesPerSecond = cntr_value
FROM @RatioStatsX
WHERE counter_name = 'Checkpoint pages/sec'
	AND object_name = CASE
		WHEN @@SERVICENAME = 'MSSQLSERVER'
			THEN 'SQLServer:Buffer Manager'
		ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager'
		END

SELECT TOP 1 @LazyWritesPerSecond = cntr_value
FROM @RatioStatsX
WHERE counter_name = 'Lazy writes/sec'
	AND object_name = CASE
		WHEN @@SERVICENAME = 'MSSQLSERVER'
			THEN 'SQLServer:Buffer Manager'
		ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager'
		END

SELECT TOP 1 @BatchRequestsPerSecond = cntr_value
FROM @RatioStatsX
WHERE counter_name = 'Batch Requests/sec'
	AND object_name = CASE
		WHEN @@SERVICENAME = 'MSSQLSERVER'
			THEN 'SQLServer:SQL Statistics'
		ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':SQL Statistics'
		END

SELECT TOP 1 @CompilationsPerSecond = cntr_value
FROM @RatioStatsX
WHERE counter_name = 'SQL Compilations/sec'
	AND object_name = CASE
		WHEN @@SERVICENAME = 'MSSQLSERVER'
			THEN 'SQLServer:SQL Statistics'
		ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':SQL Statistics'
		END

SELECT TOP 1 @ReCompilationsPerSecond = cntr_value
FROM @RatioStatsX
WHERE counter_name = 'SQL Re-Compilations/sec'
	AND object_name = CASE
		WHEN @@SERVICENAME = 'MSSQLSERVER'
			THEN 'SQLServer:SQL Statistics'
		ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':SQL Statistics'
		END

SELECT TOP 1 @PageLookupsPerSecond = cntr_value
FROM @RatioStatsX
WHERE counter_name = 'Page lookups/sec'
	AND object_name = CASE
		WHEN @@SERVICENAME = 'MSSQLSERVER'
			THEN 'SQLServer:Buffer Manager'
		ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager'
		END

SELECT TOP 1 @TransactionsPerSecond = cntr_value
FROM @RatioStatsX
WHERE counter_name = 'Transactions/sec'
	AND instance_name = '_Total'
	AND object_name = CASE
		WHEN @@SERVICENAME = 'MSSQLSERVER'
			THEN 'SQLServer:Databases'
		ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Databases'
		END

-- Wait for 5 seconds before taking second sample
WAITFOR DELAY '00:00:05'

DECLARE @ts_now BIGINT

SELECT @ts_now = cpu_ticks / (cpu_ticks / ms_ticks)
FROM sys.dm_os_sys_info

-- Table for second sample
INSERT INTO @RatioStatsY (
	[object_name]
	,[counter_name]
	,[instance_name]
	,[cntr_value]
	,[cntr_type]
	)
SELECT [object_name]
	,[counter_name]
	,[instance_name]
	,[cntr_value]
	,[cntr_type]
FROM sys.dm_os_performance_counters

SELECT @@servername AS ServerName
	,cpu.SQLProcessUtilization
	,cpu.OtherProcessUtilization
	,cpu.SystemIdle
	,(a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]
	,c.[PageReadPerSec] [PageReadsPerSec]
	,d.[PageWritesPerSecond] [PageWritesPerSecond]
	,e.cntr_value [UserConnections]
	,f.cntr_value [PageLifeExpectency(s)]
	,(SELECT  (select convert(int,value)/1024 from sys.configurations where name ='max server memory (MB)')/4*300) as [RecommandPLE(s)]
	,g.[CheckpointPagesPerSecond] [CheckpointPagesPerSecond]
	,h.[LazyWritesPerSecond] [LazyWritesPerSecond]
	,(i.cntr_value / 1024) AS [FreeSpaceInTempdb(MB)]
	,j.[BatchRequestsPerSecond] [BatchRequestsPerSecond]
	,k.[SQLCompilationsPerSecond] [SQLCompilationsPerSecond]
	,l.[SQLReCompilationsPerSecond] [SQLReCompilationsPerSecond]
	,(m.cntr_value / 1024) AS [Target Server Memory (MB)]
	,(n.cntr_value / 1024) AS [Total Server Memory (MB)]
	,o.[AvgTaskCount]
	,o.[AvgRunnableTaskCount]
	,o.[AvgPendingDiskIOCount]
	,p.PercentSignalWait AS [PercentSignalWait]
	,q.PageLookupsPerSecond AS [PageLookupsPerSecond]
	,r.TransactionsPerSecond AS [TransactionsPerSecond]
	,s.cntr_value [MemoryGrantsPending]
	,GETDATE() AS [CaptureDate]
FROM (
	SELECT *
		,1 x
	FROM @RatioStatsY
	WHERE counter_name = 'Buffer cache hit ratio'
		AND object_name = CASE
			WHEN @@SERVICENAME = 'MSSQLSERVER'
				THEN 'SQLServer:Buffer Manager'
			ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager'
			END
	) a
JOIN (
	SELECT *
		,1 x
	FROM @RatioStatsY
	WHERE counter_name = 'Buffer cache hit ratio base'
		AND object_name = CASE
			WHEN @@SERVICENAME = 'MSSQLSERVER'
				THEN 'SQLServer:Buffer Manager'
			ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager'
			END
	) b ON a.x = b.x
JOIN (
	SELECT (cntr_value - @PageReadsPerSecond) / (
			CASE
				WHEN datediff(ss, @stat_date, getdate()) = 0
					THEN 1
				ELSE datediff(ss, @stat_date, getdate())
				END
			) AS [PageReadPerSec]
		,1 x
	FROM @RatioStatsY
	WHERE counter_name = 'Page reads/sec'
		AND object_name = CASE
			WHEN @@SERVICENAME = 'MSSQLSERVER'
				THEN 'SQLServer:Buffer Manager'
			ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager'
			END
	) c ON a.x = c.x
JOIN (
	SELECT (cntr_value - @PageWritesPerSecond) / (
			CASE
				WHEN datediff(ss, @stat_date, getdate()) = 0
					THEN 1
				ELSE datediff(ss, @stat_date, getdate())
				END
			) AS [PageWritesPerSecond]
		,1 x
	FROM @RatioStatsY
	WHERE counter_name = 'Page writes/sec'
		AND object_name = CASE
			WHEN @@SERVICENAME = 'MSSQLSERVER'
				THEN 'SQLServer:Buffer Manager'
			ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager'
			END
	) d ON a.x = d.x
JOIN (
	SELECT *
		,1 x
	FROM @RatioStatsY
	WHERE counter_name = 'User Connections'
		AND object_name = CASE
			WHEN @@SERVICENAME = 'MSSQLSERVER'
				THEN 'SQLServer:General Statistics'
			ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':General Statistics'
			END
	) e ON a.x = e.x
JOIN (
	SELECT *
		,1 x
	FROM @RatioStatsY
	WHERE counter_name = 'Page life expectancy '
		AND object_name = CASE
			WHEN @@SERVICENAME = 'MSSQLSERVER'
				THEN 'SQLServer:Buffer Manager'
			ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager'
			END
	) f ON a.x = f.x
JOIN (
	SELECT (cntr_value - @CheckpointPagesPerSecond) / (
			CASE
				WHEN datediff(ss, @stat_date, getdate()) = 0
					THEN 1
				ELSE datediff(ss, @stat_date, getdate())
				END
			) AS [CheckpointPagesPerSecond]
		,1 x
	FROM @RatioStatsY
	WHERE counter_name = 'Checkpoint pages/sec'
		AND object_name = CASE
			WHEN @@SERVICENAME = 'MSSQLSERVER'
				THEN 'SQLServer:Buffer Manager'
			ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager'
			END
	) g ON a.x = g.x
JOIN (
	SELECT (cntr_value - @LazyWritesPerSecond) / (
			CASE
				WHEN datediff(ss, @stat_date, getdate()) = 0
					THEN 1
				ELSE datediff(ss, @stat_date, getdate())
				END
			) AS [LazyWritesPerSecond]
		,1 x
	FROM @RatioStatsY
	WHERE counter_name = 'Lazy writes/sec'
		AND object_name = CASE
			WHEN @@SERVICENAME = 'MSSQLSERVER'
				THEN 'SQLServer:Buffer Manager'
			ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager'
			END
	) h ON a.x = h.x
JOIN (
	SELECT *
		,1 x
	FROM @RatioStatsY
	WHERE counter_name = 'Free Space in tempdb (KB)'
		AND object_name = CASE
			WHEN @@SERVICENAME = 'MSSQLSERVER'
				THEN 'SQLServer:Transactions'
			ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Transactions'
			END
	) i ON a.x = i.x
JOIN (
	SELECT (cntr_value - @BatchRequestsPerSecond) / (
			CASE
				WHEN datediff(ss, @stat_date, getdate()) = 0
					THEN 1
				ELSE datediff(ss, @stat_date, getdate())
				END
			) AS [BatchRequestsPerSecond]
		,1 x
	FROM @RatioStatsY
	WHERE counter_name = 'Batch Requests/sec'
		AND object_name = CASE
			WHEN @@SERVICENAME = 'MSSQLSERVER'
				THEN 'SQLServer:SQL Statistics'
			ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':SQL Statistics'
			END
	) j ON a.x = j.x
JOIN (
	SELECT (cntr_value - @CompilationsPerSecond) / (
			CASE
				WHEN datediff(ss, @stat_date, getdate()) = 0
					THEN 1
				ELSE datediff(ss, @stat_date, getdate())
				END
			) AS [SQLCompilationsPerSecond]
		,1 x
	FROM @RatioStatsY
	WHERE counter_name = 'SQL Compilations/sec'
		AND object_name = CASE
			WHEN @@SERVICENAME = 'MSSQLSERVER'
				THEN 'SQLServer:SQL Statistics'
			ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':SQL Statistics'
			END
	) k ON a.x = k.x
JOIN (
	SELECT (cntr_value - @ReCompilationsPerSecond) / (
			CASE
				WHEN datediff(ss, @stat_date, getdate()) = 0
					THEN 1
				ELSE datediff(ss, @stat_date, getdate())
				END
			) AS [SQLReCompilationsPerSecond]
		,1 x
	FROM @RatioStatsY
	WHERE counter_name = 'SQL Re-Compilations/sec'
		AND object_name = CASE
			WHEN @@SERVICENAME = 'MSSQLSERVER'
				THEN 'SQLServer:SQL Statistics'
			ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':SQL Statistics'
			END
	) l ON a.x = l.x
JOIN (
	SELECT *
		,1 x
	FROM @RatioStatsY
	WHERE counter_name = 'Target Server Memory (KB)'
		AND object_name = CASE
			WHEN @@SERVICENAME = 'MSSQLSERVER'
				THEN 'SQLServer:Memory Manager'
			ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Memory Manager'
			END
	) m ON a.x = m.x
JOIN (
	SELECT *
		,1 x
	FROM @RatioStatsY
	WHERE counter_name = 'Total Server Memory (KB)'
		AND object_name = CASE
			WHEN @@SERVICENAME = 'MSSQLSERVER'
				THEN 'SQLServer:Memory Manager'
			ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Memory Manager'
			END
	) n ON a.x = n.x
JOIN (
	SELECT 1 AS x
		,AVG(current_tasks_count) AS [AvgTaskCount]
		,AVG(runnable_tasks_count) AS [AvgRunnableTaskCount]
		,AVG(pending_disk_io_count) AS [AvgPendingDiskIOCount]
	FROM sys.dm_os_schedulers
	WHERE scheduler_id < 255
	) o ON a.x = o.x
JOIN (
	SELECT 1 AS x
		,SUM(signal_wait_time_ms) / sum(wait_time_ms) AS PercentSignalWait
	FROM sys.dm_os_wait_stats
	) p ON a.x = p.x
JOIN (
	SELECT (cntr_value - @PageLookupsPerSecond) / (
			CASE
				WHEN datediff(ss, @stat_date, getdate()) = 0
					THEN 1
				ELSE datediff(ss, @stat_date, getdate())
				END
			) AS [PageLookupsPerSecond]
		,1 x
	FROM @RatioStatsY
	WHERE counter_name = 'Page Lookups/sec'
		AND object_name = CASE
			WHEN @@SERVICENAME = 'MSSQLSERVER'
				THEN 'SQLServer:Buffer Manager'
			ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Buffer Manager'
			END
	) q ON a.x = q.x
JOIN (
	SELECT (cntr_value - @TransactionsPerSecond) / (
			CASE
				WHEN datediff(ss, @stat_date, getdate()) = 0
					THEN 1
				ELSE datediff(ss, @stat_date, getdate())
				END
			) AS [TransactionsPerSecond]
		,1 x
	FROM @RatioStatsY
	WHERE counter_name = 'Transactions/sec'
		AND instance_name = '_Total'
		AND object_name = CASE
			WHEN @@SERVICENAME = 'MSSQLSERVER'
				THEN 'SQLServer:Databases'
			ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Databases'
			END
	) r ON a.x = r.x
JOIN (
	SELECT *
		,1 x
	FROM @RatioStatsY
	WHERE counter_name = 'Memory Grants Pending'
		AND object_name = CASE
			WHEN @@SERVICENAME = 'MSSQLSERVER'
				THEN 'SQLServer:Memory Manager'
			ELSE 'MSSQL$' + rtrim(@@SERVICENAME) + ':Memory Manager'
			END
	) s ON a.x = s.x
JOIN (
	SELECT TOP 1 SQLProcessUtilization
		,SystemIdle
		,100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization
		,1 AS x
	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
	) AS CPU ON a.x = cpu.x


/*
select object_name,counter_name,instance_name,cntr_value
from sys.dm_os_performance_counters
 where object_name like '%replica%'

*/