SQL Server Utility Script From 24 Hours of PASS

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;

This entry was posted in SQL Server 2008 R2. Bookmark the permalink.

One Response to SQL Server Utility Script From 24 Hours of PASS

  1. Nadir says:

    Glen: Thanks much for sharing your helpful code with us. I found your presentation to be very lucid & would like to obtain a copy of the slide-deck from your blog download area.Best Regards,Nadir Doctor

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s