-- Some UCP Related queries
-- 24 Hours of PASS
-- Glenn Berry
-- May 19, 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry
-- Hardware information from SQL Server 2008 R2
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)],
affinity_type_desc, time_source_desc,
process_user_time_ms,
CAST (CAST(process_user_time_ms AS FLOAT) /
(CAST(process_kernel_time_ms AS FLOAT) + CAST (process_user_time_ms AS FLOAT))
* 100 AS DECIMAL(9,2)) AS [% SQL User Time],
process_kernel_time_ms,
CAST (CAST(process_kernel_time_ms AS FLOAT) /
(CAST(process_kernel_time_ms AS FLOAT) + CAST (process_user_time_ms AS FLOAT))
* 100 AS DECIMAL(9,2)) AS [% SQL Kernel Time],
sqlserver_start_time
FROM sys.dm_os_sys_info;
-- Get CPU Utilization History for last 30 minutes (SQL 2008 and 2008 R2)
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 '%%') AS x
) AS y
ORDER BY record_id DESC;
-- Look at aggregated managed instance health (msdb)
SELECT mi_count, mi_healthy_count, mi_unhealthy_count, mi_over_utilize_count, mi_under_utilize_count,
mi_on_over_utilized_computer_count, mi_on_under_utilized_computer_count,
mi_with_files_on_over_utilized_volume_count, mi_with_files_on_over_utilized_volume_count,
mi_with_over_utilized_file_count, mi_with_over_utilized_file_count,
mi_with_over_utilized_processor_count, mi_with_under_utilized_processor_count
FROM msdb.dbo.sysutility_ucp_aggregated_mi_health;
-- Look at managed instance information (msdb)
SELECT *
FROM msdb.dbo.sysutility_ucp_managed_instances AS mi
LEFT OUTER JOIN msdb.dbo.sysutility_ucp_mi_health AS mih
ON mi.instance_name = mih.mi_name
LEFT OUTER JOIN msdb.dbo.sysutility_ucp_instances AS inst
ON mi.instance_name = inst.Name
LEFT OUTER JOIN msdb.dbo.sysutility_ucp_computers AS comp
ON mi.virtual_server_name = comp.virtual_server_name;
-- Look at hardware info for managed instances (sysutility_mdw)
SELECT TOP(25)virtual_server_name ,physical_server_name,is_clustered_server,
num_processors ,cpu_name,cpu_caption,cpu_architecture,
cpu_max_clock_speed,cpu_clock_speed,l2_cache_size,l3_cache_size,
percent_total_cpu_utilization
FROM sysutility_mdw.sysutility_ucp_core.latest_computers;
-- Look at server level CPU utilization (sysutility_mdw)
SELECT TOP(1000) physical_server_name, processing_time, percent_total_cpu_utilization
FROM sysutility_mdw.sysutility_ucp_core.cpu_utilization
WHERE object_type = 1
ORDER BY processing_time DESC;
-- Look at instance level CPU utilization (sysutility_mdw)
SELECT TOP(1000) server_instance_name, processing_time, percent_total_cpu_utilization
FROM sysutility_mdw.sysutility_ucp_core.cpu_utilization
WHERE object_type = 3
ORDER BY processing_time DESC;
-- Look at most recent instance level CPU utilization (sysutility_mdw)
SELECT TOP(25)server_instance_name, instance_processor_usage, batch_time
FROM sysutility_mdw.sysutility_ucp_staging.latest_instance_cpu_utilization
ORDER BY server_instance_name;
-- Look at hardware information (sysutility_mdw)
SELECT TOP(25) physical_server_name, num_processors, cpu_name, cpu_caption,
cpu_architecture, cpu_max_clock_speed,cpu_clock_speed, l2_cache_size,
l3_cache_size, server_processor_usage, batch_time
FROM sysutility_mdw.sysutility_ucp_staging.latest_computer_cpu_memory_configuration
ORDER BY [Rank];
-- Look at wait types and categories
SELECT category_name, wait_type, ignore
FROM sysutility_mdw.core.wait_types_categorized
ORDER BY category_name;
-- Merci à Glenn Berry http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!6476.entry
