System Data Collection Sets

SELECT @dbsize = SUM(convert(bigint,case when type = 0 then size else 0 end)) 
      ,@logsize = SUM(convert(bigint,case when type = 1 then size else 0 end)) 
      ,@ftsize = SUM(convert(bigint,case when type = 4 then size else 0 end)) 
FROM sys.database_files

SELECT @reservedpages = SUM(a.total_pages) 
       ,@usedpages = SUM(a.used_pages) 
       ,@pages = SUM(CASE 
                        WHEN it.internal_type IN (202,204) THEN 0 
                        WHEN a.type != 1 THEN a.used_pages 
                        WHEN p.index_id < 2 THEN a.data_pages 
                        ELSE 0 
                     END) 
FROM sys.partitions p  
JOIN sys.allocation_units a ON p.partition_id = a.container_id 
LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id 

SELECT 
        @dbsize as ''dbsize'',
        @logsize as ''logsize'',
        @ftsize as ''ftsize'',
        @reservedpages as ''reservedpages'',
        @usedpages as ''usedpages'',
        @pages as ''pages''

INSERT INTO @tran_log_space_usage 
EXEC(''DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS'');
SELECT 
    database_name,
    log_size_mb,
    log_space_used,
    status    
FROM @tran_log_space_usage

SELECT 
    LEFT (wait_type, 45) AS wait_type, 
    SUM (waiting_tasks_count) AS waiting_tasks_count, 
    SUM (wait_time_ms) AS wait_time_ms, 
    SUM (signal_wait_time_ms) AS signal_wait_time_ms
FROM
 (SELECT 
    LEFT (wait_type, 45) AS wait_type, 
    waiting_tasks_count, 
    wait_time_ms,  
    signal_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE waiting_tasks_count > 0 OR wait_time_ms > 0 OR signal_wait_time_ms > 0
UNION ALL 
    SELECT 
        LEFT (wait_type, 45) AS wait_type, 
        1 AS waiting_tasks_count, 
        wait_duration_ms AS wait_time_ms, 
        0 AS signal_wait_time_ms
    FROM sys.dm_os_waiting_tasks
    WHERE wait_duration_ms > 60000
) AS merged_wait_stats
GROUP BY wait_type

SELECT 
  LEFT(latch_class,45) as latch_class,
  waiting_requests_count,
  wait_time_ms
FROM sys.dm_os_latch_stats 
WHERE waiting_requests_count > 0 OR wait_time_ms > 0

SELECT 
    pm.physical_memory_in_use_kb            AS sql_physical_memory_in_use_kb, 
    pm.large_page_allocations_kb            AS sql_large_page_allocations_kb, 
    pm.locked_page_allocations_kb           AS sql_locked_page_allocations_kb, 
    pm.total_virtual_address_space_kb       AS sql_total_virtual_address_space_kb, 
    pm.virtual_address_space_reserved_kb    AS sql_virtual_address_space_reserved_kb, 
    pm.virtual_address_space_committed_kb   AS sql_virtual_address_space_committed_kb, 
    pm.virtual_address_space_available_kb   AS sql_virtual_address_space_available_kb, 
    pm.page_fault_count                     AS sql_page_fault_count, 
    pm.memory_utilization_percentage        AS sql_memory_utilization_percentage, 
    pm.available_commit_limit_kb            AS sql_available_commit_limit_kb, 
    pm.process_physical_memory_low          AS sql_process_physical_memory_low, 
    pm.process_virtual_memory_low           AS sql_process_virtual_memory_low, 
    
    sm.total_physical_memory_kb             AS system_total_physical_memory_kb, 
    sm.available_physical_memory_kb         AS system_available_physical_memory_kb, 
    sm.total_page_file_kb                   AS system_total_page_file_kb, 
    sm.available_page_file_kb               AS system_available_page_file_kb, 
    sm.system_cache_kb                      AS system_cache_kb, 
    sm.kernel_paged_pool_kb                 AS system_kernel_paged_pool_kb, 
    sm.kernel_nonpaged_pool_kb              AS system_kernel_nonpaged_pool_kb, 
    sm.system_high_memory_signal_state      AS system_high_memory_signal_state, 
    sm.system_low_memory_signal_state       AS system_low_memory_signal_state, 
    
    si.bpool_commit_target                  AS bpool_commit_target, 
    si.bpool_committed                      AS bpool_committed, 
    si.bpool_visible                        AS bpool_visible
FROM sys.dm_os_process_memory AS pm
CROSS JOIN sys.dm_os_sys_memory AS sm   -- single-row DMV
CROSS JOIN sys.dm_os_sys_info AS si;    -- single-row DMV

SELECT 
    memory_node_id, 
    virtual_address_space_reserved_kb, 
    virtual_address_space_committed_kb, 
    locked_page_allocations_kb, 
    single_pages_kb, 
    multi_pages_kb, 
    shared_memory_reserved_kb, 
    shared_memory_committed_kb
FROM sys.dm_os_memory_nodes

SELECT 
    type,
    memory_node_id as memory_node_id,
    SUM(single_pages_kb) as single_pages_kb,
    SUM(multi_pages_kb) as multi_pages_kb,
    SUM(virtual_memory_reserved_kb) as virtual_memory_reserved_kb,
    SUM(virtual_memory_committed_kb) as virtual_memory_committed_kb,
    SUM(awe_allocated_kb) as awe_allocated_kb,
    SUM(shared_memory_reserved_kb) as shared_memory_reserved_kb,
    SUM(shared_memory_committed_kb) as shared_memory_committed_kb
FROM sys.dm_os_memory_clerks
GROUP BY type, memory_node_id

SELECT 
    [parent_node_id],
    [scheduler_id],
    [cpu_id],
    [status],
    [is_idle],
    [preemptive_switches_count],
    [context_switches_count],
    [yield_count],
    [current_tasks_count],
    [runnable_tasks_count],
    [work_queue_count],
    [pending_disk_io_count]
FROM sys.dm_os_schedulers
WHERE scheduler_id < 128

SELECT 
    DB_NAME (f.database_id) AS database_name, f.database_id, f.name AS logical_file_name, f.[file_id], f.type_desc, 
    CAST (CASE 
        -- Handle UNC paths (e.g. ''\\fileserver\readonlydbs\dept_dw.ndf'' --> ''\\fileserver\readonlydbs'')
        WHEN LEFT (LTRIM (f.physical_name), 2) = ''\\'' 
            THEN LEFT (LTRIM (f.physical_name), 
            CHARINDEX (''\'', 
            LTRIM (f.physical_name), 
            CHARINDEX (''\'', 
            LTRIM (f.physical_name), 3) + 1) - 1)
        -- Handle local paths (e.g. ''C:\Program Files\...\master.mdf'' --> ''C:'') 
        WHEN CHARINDEX (''\'', LTRIM(f.physical_name), 3) > 0 
            THEN UPPER (LEFT (LTRIM (f.physical_name), CHARINDEX (''\'', LTRIM (f.physical_name), 3) - 1))
        ELSE f.physical_name
    END AS nvarchar(255)) AS logical_disk, 
    fs.num_of_reads, fs.num_of_bytes_read, fs.io_stall_read_ms, fs.num_of_writes, fs.num_of_bytes_written, 
    fs.io_stall_write_ms, fs.size_on_disk_bytes
FROM sys.dm_io_virtual_file_stats (default, default) AS fs
INNER JOIN sys.master_files AS f ON fs.database_id = f.database_id AND fs.[file_id] = f.[file_id]