I had a good time giving my presentation and demo of SQL Server Utility during the fourth hour of the 24 Hours of PASS this morning at 9:00AM Mountain time. We had close to 700 attendees in the Live Meeting, with a lot of good questions at the end. I wanted to thank Rick Heiges for stepping in at the end as a substitute moderator. As part of my demonstration, I showed some queries that you can use to discover some interesting information about your instances and databases, both with SQL Server Utility and without SQL Server Utility.

Those queries are listed below:

-- 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 '%<SystemHealth>%') 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;