使用SQL SERVER內建的DMV收集效能資訊

--使用SQL SERVER內建的DMV收集效能資訊
USE CustomLogging;
--建立儲存效能資訊的TABLE
IF NOT EXISTS
(
SELECT name
FROM sys.[tables]
WHERE [name] = 'dm_os_performance_counters'
)

CREATE TABLE dbo.dm_os_performance_counters
(
[object_name] VARCHAR(128), [counter_name] VARCHAR(128),
[instance_name] VARCHAR(128), [cntr_value] bigint,
[date_stamp] datetime
);
--使用SQL SERVER內建的DMV(動態管理檢視):sys.dm_os_performance_counters去收集資訊
INSERT INTO dbo.dm_os_performance_counters
([object_name], [counter_name], [instance_name],
[cntr_value], [date_stamp])
SELECT [object_name], [counter_name], [instance_name],
[cntr_value], GETDATE()
FROM sys.[dm_os_performance_counters]
WHERE [object_name] = 'SQLServer:Buffer Manager';

--找出最新的效能資訊
SELECT [object_name], [counter_name], [instance_name], [cntr_value]
FROM
(
SELECT [object_name], [counter_name], [instance_name], [cntr_value], [date_stamp],
ROW_NUMBER() OVER(PARTITION BY [counter_name] ORDER BY [date_stamp] DESC) AS 'rn'
FROM dbo.[dm_os_performance_counters]
) TEMP
WHERE rn = 1;