use msdb;
Begin Transaction
Begin Try
Declare @collection_set_id_27 int
Declare @collection_set_uid_28 uniqueidentifier
EXEC [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.', @target=N'', @logging_level=1, @days_until_expiration=14, @proxy_name=N'', @schedule_name=N'CollectorSchedule_Every_15min', @collection_set_id=@collection_set_id_27 OUTPUT, @collection_set_uid=@collection_set_uid_28 OUTPUT
Select @collection_set_id_27, @collection_set_uid_28
Declare @collector_type_uid_29 uniqueidentifier
Select @collector_type_uid_29 = collector_type_uid From [dbo].[syscollector_collector_types] Where name = N'Generic T-SQL Query Collector Type';
Declare @collection_item_id_30 int
EXEC [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
physical_memory_in_use_kb ,
large_page_allocations_kb,
locked_page_allocations_kb,
total_virtual_address_space_kb ,
virtual_address_space_reserved_kb ,
virtual_address_space_committed_kb ,
virtual_address_space_available_kb ,
page_fault_count ,
memory_utilization_percentage ,
available_commit_limit_kb ,
process_physical_memory_low ,
process_virtual_memory_low
FROM sys.dm_os_process_memory
os_process_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_30 OUTPUT, @frequency=60, @collection_set_id=@collection_set_id_27, @collector_type_uid=@collector_type_uid_29
Select @collection_item_id_30
Declare @collector_type_uid_31 uniqueidentifier
Select @collector_type_uid_31 = collector_type_uid From [dbo].[syscollector_collector_types] Where name = N'Performance Counters Collector Type';
Declare @collection_item_id_32 int
EXEC [dbo].[sp_syscollector_create_collection_item] @name=N'Server Activity - Performance Counters', @parameters=N'', @collection_item_id=@collection_item_id_32 OUTPUT, @frequency=60, @collection_set_id=@collection_set_id_27, @collector_type_uid=@collector_type_uid_31
Select @collection_item_id_32
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