Begin Transaction
Begin Try
Declare @collection_set_id_1 int
Declare @collection_set_uid_2 uniqueidentifier
EXEC [msdb].[dbo].[sp_syscollector_create_collection_set] @name=N'Server Activity', @collection_mode=0, @description=N'Collects top-level performance indicators for the computer and the Database Engine. Enables analysis of resource use, resource bottlenecks, and Database Engine activity.', @logging_level=0, @days_until_expiration=14, @schedule_name=N'CollectorSchedule_Every_15min', @collection_set_id=@collection_set_id_1 OUTPUT, @collection_set_uid=@collection_set_uid_2 OUTPUT
Select @collection_set_id_1, @collection_set_uid_2
Declare @collector_type_uid_3 uniqueidentifier
Select @collector_type_uid_3 = collector_type_uid From [msdb].[dbo].[syscollector_collector_types] Where name = N'Generic T-SQL Query Collector Type';
Declare @collection_item_id_4 int
EXEC [msdb].[dbo].[sp_syscollector_create_collection_item] @name=N'Server Activity - DMV Snapshots', @parameters=N'
SET NOCOUNT ON
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
os_wait_stats
SET NOCOUNT ON
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
os_latch_stats
SET NOCOUNT ON
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
sql_process_and_system_memory
SET NOCOUNT ON
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
os_memory_nodes
SET NOCOUNT ON
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_idos_memory_clerks
SET NOCOUNT ON
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
os_schedulers
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]
io_virtual_file_stats', @collection_item_id=@collection_item_id_4 OUTPUT, @frequency=60, @collection_set_id=@collection_set_id_1, @collector_type_uid=@collector_type_uid_3
Select @collection_item_id_4
Declare @collector_type_uid_5 uniqueidentifier
Select @collector_type_uid_5 = collector_type_uid From [msdb].[dbo].[syscollector_collector_types] Where name = N'Performance Counters Collector Type';
Declare @collection_item_id_6 int
EXEC [msdb].[dbo].[sp_syscollector_create_collection_item] @name=N'Server Activity - Performance Counters', @parameters=N'', @collection_item_id=@collection_item_id_6 OUTPUT, @frequency=60, @collection_set_id=@collection_set_id_1, @collector_type_uid=@collector_type_uid_5
Select @collection_item_id_6
Commit Transaction;
End Try
Begin Catch
Rollback Transaction;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorNumber INT;
DECLARE @ErrorLine INT;
DECLARE @ErrorProcedure NVARCHAR(200);
SELECT @ErrorLine = ERROR_LINE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
RAISERROR (14684, @ErrorSeverity, 1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);
End Catch;
GO