;WITH tgt AS (SELECT instance_name, cntr_value FROM SYS.DM_OS_PERFORMANCE_COUNTERS WHERE COUNTER_NAME = 'Target Node Memory (KB)' UNION ALL SELECT 'TOTAL', cntr_value FROM SYS.DM_OS_PERFORMANCE_COUNTERS WHERE COUNTER_NAME = 'Target Server Memory (KB)'), tot AS (SELECT instance_name, cntr_value FROM SYS.DM_OS_PERFORMANCE_COUNTERS WHERE COUNTER_NAME = 'Total Node Memory (KB)' UNION ALL SELECT 'TOTAL', cntr_value FROM SYS.DM_OS_PERFORMANCE_COUNTERS WHERE COUNTER_NAME = 'Total Server Memory (KB)'), dbc AS (SELECT instance_name, cntr_value FROM SYS.DM_OS_PERFORMANCE_COUNTERS WHERE COUNTER_NAME = 'Database Node Memory (KB)' UNION ALL SELECT 'TOTAL', cntr_value FROM SYS.DM_OS_PERFORMANCE_COUNTERS WHERE COUNTER_NAME = 'Database Cache Memory (KB)'), stl AS (SELECT instance_name, cntr_value FROM SYS.DM_OS_PERFORMANCE_COUNTERS WHERE COUNTER_NAME = 'Stolen Node Memory (KB)' UNION ALL SELECT 'TOTAL', cntr_value FROM SYS.DM_OS_PERFORMANCE_COUNTERS WHERE COUNTER_NAME = 'Stolen Server Memory (KB)'), fre AS (SELECT instance_name, cntr_value FROM SYS.DM_OS_PERFORMANCE_COUNTERS WHERE COUNTER_NAME = 'Free Node Memory (KB)' UNION ALL SELECT 'TOTAL', cntr_value FROM SYS.DM_OS_PERFORMANCE_COUNTERS WHERE COUNTER_NAME = 'Free Memory (KB)'), frn AS (SELECT instance_name, cntr_value FROM SYS.DM_OS_PERFORMANCE_COUNTERS WHERE COUNTER_NAME = 'Foreign Node Memory (KB)' UNION ALL SELECT 'TOTAL', cntr_value = CONVERT(INT, NULL)) SELECT tgt.instance_name, target_kb = tgt.cntr_value, total_kb = tot.cntr_value, dbCache_kb = dbc.cntr_value, stolen_kb = stl.cntr_value, free_kb = fre.cntr_value, foreign_kb = frn.cntr_value FROM tgt JOIN tot ON tgt.instance_name = tot.instance_name JOIN frn ON tgt.instance_name = frn.instance_name JOIN dbc ON tgt.instance_name = dbc.instance_name JOIN stl ON tgt.instance_name = stl.instance_name JOIN fre ON tgt.instance_name = fre.instance_name;
q
No comments:
Post a Comment