WITH DB_CPU_StatsAS (    SELECT DatabaseID        ,DB_Name(DatabaseID) AS [Database Name]        ,SUM(total_worker_time) AS [CPU_Time_Ms]    FROM sys.dm_exec_query_stats AS qs    CROSS APPLY (        SELECT CONVERT(INT, value) AS [DatabaseID]        FROM sys.dm_exec_plan_attributes(qs.plan_handle)        WHERE attribute = N‘dbid‘        ) AS F_DB    GROUP BY DatabaseID    )SELECT ROW_NUMBER() OVER (        ORDER BY [CPU_Time_Ms] DESC        ) AS [CPU Rank]    ,[Database Name]    ,[CPU_Time_Ms] AS [CPU Time (ms)]    ,CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER () * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]FROM DB_CPU_StatsWHERE DatabaseID <> 32767 -- ResourceDBORDER BY [CPU Rank]OPTION (RECOMPILE);

DECLARE @ts_now BIGINT = (        SELECT cpu_ticks / (cpu_ticks / ms_ticks)        FROM sys.dm_os_sys_info WITH (NOLOCK)        );SELECT TOP (256) 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 WITH (NOLOCK)        WHERE ring_buffer_type = N‘RING_BUFFER_SCHEDULER_MONITOR‘            AND record LIKE N‘%<SystemHealth>%‘        ) AS x    ) AS yORDER BY record_id DESCOPTION (RECOMPILE);

SELECT TOP (50) DB_NAME(t.[dbid]) AS [Database Name]    ,t.[text] AS [Query Text]    ,qs.total_worker_time AS [Total Worker Time]    ,qs.min_worker_time AS [Min Worker Time]    ,qs.total_worker_time / qs.execution_count AS [Avg Worker Time]    ,qs.max_worker_time AS [Max Worker Time]    ,qs.execution_count AS [Execution Count]    ,qs.total_elapsed_time / qs.execution_count AS [Avg Elapsed Time]    ,qs.total_logical_reads / qs.execution_count AS [Avg Logical Reads]    ,qs.total_physical_reads / qs.execution_count AS [Avg Physical Reads]    ,qp.query_plan AS [Query Plan]    ,qs.creation_time AS [Creation Time]FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS tCROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qpORDER BY qs.total_worker_time DESCOPTION (RECOMPILE);

SELECT total_physical_memory_kb / 1024 AS [Physical Memory (MB)]    ,available_physical_memory_kb / 1024 AS [Available Memory (MB)]    ,total_page_file_kb / 1024 AS [Total Page File (MB)]    ,available_page_file_kb / 1024 AS [Available Page File (MB)]    ,system_cache_kb / 1024 AS [System Cache (MB)]    ,system_memory_state_desc AS [System Memory State]FROM sys.dm_os_sys_memory WITH (NOLOCK)OPTION (RECOMPILE);

SELECT physical_memory_in_use_kb / 1024 AS [SQL Server Memory Usage (MB)]    ,large_page_allocations_kb    ,locked_page_allocations_kb    ,page_fault_count    ,memory_utilization_percentage    ,available_commit_limit_kb    ,process_physical_memory_low    ,process_virtual_memory_lowFROM sys.dm_os_process_memory WITH (NOLOCK)OPTION (RECOMPILE);

SELECT @@SERVERNAME AS [Server Name]    ,[object_name]    ,cntr_value AS [Memory Grants Pending]FROM sys.dm_os_performance_counters WITH (NOLOCK)WHERE [object_name] LIKE N‘%Memory Manager%‘ -- Handles named instances    AND counter_name = N‘Memory Grants Pending‘OPTION (RECOMPILE);

SELECT [name] AS [Name]    ,[configuration_id] AS [Number]    ,[minimum] AS [Minimum]    ,[maximum] AS [Maximum]    ,[is_dynamic] AS [Dynamic]    ,[is_advanced] AS [Advanced]    ,[value] AS [ConfigValue]    ,[value_in_use] AS [RunValue]    ,[description] AS [Description]FROM [master].[sys].[configurations]WHERE NAME IN (        ‘Min server memory (MB)‘        ,‘Max server memory (MB)‘        );

SELECT *FROM sys.configurationsWHERE configuration_id IN (        ‘1543‘        ,‘1544‘        )

SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [% Signal (CPU) Waits]    ,CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [% Resource Waits]FROM sys.dm_os_wait_stats WITH (NOLOCK)WHERE wait_type NOT IN (        N‘BROKER_EVENTHANDLER‘        ,N‘BROKER_RECEIVE_WAITFOR‘        ,N‘BROKER_TASK_STOP‘        ,N‘BROKER_TO_FLUSH‘        ,N‘BROKER_TRANSMITTER‘        ,N‘CHECKPOINT_QUEUE‘        ,N‘CHKPT‘        ,N‘CLR_AUTO_EVENT‘        ,N‘CLR_MANUAL_EVENT‘        ,N‘CLR_SEMAPHORE‘        ,N‘DBMIRROR_DBM_EVENT‘        ,N‘DBMIRROR_EVENTS_QUEUE‘        ,N‘DBMIRROR_WORKER_QUEUE‘        ,N‘DBMIRRORING_CMD‘        ,N‘DIRTY_PAGE_POLL‘        ,N‘DISPATCHER_QUEUE_SEMAPHORE‘        ,N‘EXECSYNC‘        ,N‘FSAGENT‘        ,N‘FT_IFTS_SCHEDULER_IDLE_WAIT‘        ,N‘FT_IFTSHC_MUTEX‘        ,N‘HADR_CLUSAPI_CALL‘        ,N‘HADR_FILESTREAM_IOMGR_IOCOMPLETION‘        ,N‘HADR_LOGCAPTURE_WAIT‘        ,N‘HADR_NOTIFICATION_DEQUEUE‘        ,N‘HADR_TIMER_TASK‘        ,N‘HADR_WORK_QUEUE‘        ,N‘KSOURCE_WAKEUP‘        ,N‘LAZYWRITER_SLEEP‘        ,N‘LOGMGR_QUEUE‘        ,N‘ONDEMAND_TASK_QUEUE‘        ,N‘PWAIT_ALL_COMPONENTS_INITIALIZED‘        ,N‘QDS_PERSIST_TASK_MAIN_LOOP_SLEEP‘        ,N‘QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP‘        ,N‘REQUEST_FOR_DEADLOCK_SEARCH‘        ,N‘RESOURCE_QUEUE‘        ,N‘SERVER_IDLE_CHECK‘        ,N‘SLEEP_BPOOL_FLUSH‘        ,N‘SLEEP_DBSTARTUP‘        ,N‘SLEEP_DCOMSTARTUP‘        ,N‘SLEEP_MASTERDBREADY‘        ,N‘SLEEP_MASTERMDREADY‘        ,N‘SLEEP_MASTERUPGRADED‘        ,N‘SLEEP_MSDBSTARTUP‘        ,N‘SLEEP_SYSTEMTASK‘        ,N‘SLEEP_TASK‘        ,N‘SLEEP_TEMPDBSTARTUP‘        ,N‘SNI_HTTP_ACCEPT‘        ,N‘SP_SERVER_DIAGNOSTICS_SLEEP‘        ,N‘SQLTRACE_BUFFER_FLUSH‘        ,N‘SQLTRACE_INCREMENTAL_FLUSH_SLEEP‘        ,N‘SQLTRACE_WAIT_ENTRIES‘        ,N‘WAIT_FOR_RESULTS‘        ,N‘WAITFOR‘        ,N‘WAITFOR_TASKSHUTDOWN‘        ,N‘WAIT_XTP_HOST_WAIT‘        ,N‘WAIT_XTP_OFFLINE_CKPT_NEW_LOG‘        ,N‘WAIT_XTP_CKPT_CLOSE‘        ,N‘XE_DISPATCHER_JOIN‘        ,N‘XE_DISPATCHER_WAIT‘        ,N‘XE_TIMER_EVENT‘        )OPTION (RECOMPILE);

WITH AggregateBufferPoolUsageAS (    SELECT DB_NAME(database_id) AS [Database Name]        ,CAST(COUNT(*) * 8 / 1024.0 AS DECIMAL(10, 2)) AS [CachedSize]    FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)    WHERE database_id <> 32767 -- ResourceDB    GROUP BY DB_NAME(database_id)    )SELECT ROW_NUMBER() OVER (        ORDER BY CachedSize DESC        ) AS [Buffer Pool Rank]    ,[Database Name]    ,CachedSize AS [Cached Size (MB)]    ,CAST(CachedSize / SUM(CachedSize) OVER () * 100.0 AS DECIMAL(5, 2)) AS [Buffer Pool Percent]FROM AggregateBufferPoolUsageORDER BY [Buffer Pool Rank]OPTION (RECOMPILE);

SELECT OBJECT_NAME(p.[object_id]) AS [Object Name]    ,p.index_id    ,CAST(COUNT(*) / 128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)]    ,COUNT(*) AS [BufferCount]    ,p.[Rows] AS [Row Count]    ,p.data_compression_desc AS [Compression Type]FROM sys.allocation_units AS a WITH (NOLOCK)INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK) ON a.allocation_unit_id = b.allocation_unit_idINNER JOIN sys.partitions AS p WITH (NOLOCK) ON a.container_id = p.hobt_idWHERE b.database_id = CONVERT(INT, DB_ID())    AND p.[object_id] > 100GROUP BY p.[object_id]    ,p.index_id    ,p.data_compression_desc    ,p.[Rows]ORDER BY [BufferCount] DESCOPTION (RECOMPILE);

SELECT TOP (10) mc.[type] AS [Memory Clerk Type]    ,CAST((SUM(mc.pages_kb) / 1024.0) AS DECIMAL(15, 2)) AS [Memory Usage (MB)]FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)GROUP BY mc.[type]ORDER BY SUM(mc.pages_kb) DESCOPTION (RECOMPILE);

SELECT TOP (10) [type] AS [Memory Clerk Type]    ,SUM(single_pages_kb) / 1024 AS [SPA Memory Usage (MB)]FROM sys.dm_os_memory_clerks WITH (NOLOCK)GROUP BY [type]ORDER BY SUM(single_pages_kb) DESCOPTION (RECOMPILE);

DBCC MEMORYSTATUS();

DECLARE @MemStat TABLE (    ValueName SYSNAME    ,Val BIGINT    );INSERT INTO @MemStatEXEC (‘DBCC MEMORYSTATUS() WITH TABLERESULTS‘);WITH MeasuresAS (    SELECT TOP 2 CurrentValue        ,ROW_NUMBER() OVER (            ORDER BY OrderColumn            ) AS RowOrder    FROM (        SELECT CASE                 WHEN (ms.ValueName = ‘Target Committed‘)                    THEN ms.Val                WHEN (ms.ValueName = ‘Current Committed‘)                    THEN ms.Val                END AS ‘CurrentValue‘            ,0 AS ‘OrderColumn‘        FROM @MemStat AS ms        ) AS MemStatus    WHERE CurrentValue IS NOT NULL    )SELECT TargetMem.CurrentValue - CurrentMem.CurrentValueFROM Measures AS TargetMemJOIN Measures AS CurrentMem ON TargetMem.RowOrder + 1 = CurrentMem.RowOrder;

SELECT record_id    ,dateadd(ms, (y.[timestamp] - tme.ms_ticks), GETDATE()) AS [Notification_Time]    ,NotificationFROM (    SELECT record.value(‘(./Record/@id)[1]‘, ‘int‘) AS record_id        ,record.value(‘(./Record/ResourceMonitor/Notification)[1]‘, ‘varchar(50)‘) AS Notification        ,TIMESTAMP    FROM (        SELECT TIMESTAMP            ,CONVERT(XML, record) AS record        FROM sys.dm_os_ring_buffers        WHERE ring_buffer_type = N‘RING_BUFFER_RESOURCE_MONITOR‘        ) AS x    ) AS yCROSS JOIN sys.dm_os_sys_info tmeORDER BY record_id DESC;SELECT dateadd(ms, (rbf.[timestamp] - tme.ms_ticks), GETDATE()) AS [Notification_Time]    ,cast(record AS XML).value(‘(//Record/ResourceMonitor/Notification)[1]‘, ‘varchar(30)‘) AS [Notification_type]    ,cast(record AS XML).value(‘(//Record/MemoryRecord/MemoryUtilization)[1]‘, ‘bigint‘) AS [MemoryUtilization %]    ,cast(record AS XML).value(‘(//Record/MemoryNode/@id)[1]‘, ‘bigint‘) AS [Node Id]    ,cast(record AS XML).value(‘(//Record/ResourceMonitor/IndicatorsProcess)[1]‘, ‘int‘) AS [Process_Indicator]    ,cast(record AS XML).value(‘(//Record/ResourceMonitor/IndicatorsSystem)[1]‘, ‘int‘) AS [System_Indicator]    ,cast(record AS XML).value(‘(//Record/MemoryNode/ReservedMemory)[1]‘, ‘bigint‘) AS [SQL_ReservedMemory_KB]    ,cast(record AS XML).value(‘(//Record/MemoryNode/CommittedMemory)[1]‘, ‘bigint‘) AS [SQL_CommittedMemory_KB]    ,cast(record AS XML).value(‘(//Record/MemoryNode/AWEMemory)[1]‘, ‘bigint‘) AS [SQL_AWEMemory]    ,cast(record AS XML).value(‘(//Record/MemoryNode/SinglePagesMemory)[1]‘, ‘bigint‘) AS [SinglePagesMemory]    ,cast(record AS XML).value(‘(//Record/MemoryNode/MultiplePagesMemory)[1]‘, ‘bigint‘) AS [MultiplePagesMemory]    ,cast(record AS XML).value(‘(//Record/MemoryRecord/TotalPhysicalMemory)[1]‘, ‘bigint‘) AS [TotalPhysicalMemory_KB]    ,cast(record AS XML).value(‘(//Record/MemoryRecord/AvailablePhysicalMemory)[1]‘, ‘bigint‘) AS [AvailablePhysicalMemory_KB]    ,cast(record AS XML).value(‘(//Record/MemoryRecord/TotalPageFile)[1]‘, ‘bigint‘) AS [TotalPageFile_KB]    ,cast(record AS XML).value(‘(//Record/MemoryRecord/AvailablePageFile)[1]‘, ‘bigint‘) AS [AvailablePageFile_KB]    ,cast(record AS XML).value(‘(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]‘, ‘bigint‘) AS [TotalVirtualAddressSpace_KB]    ,cast(record AS XML).value(‘(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]‘, ‘bigint‘) AS [AvailableVirtualAddressSpace_KB]    ,cast(record AS XML).value(‘(//Record/@id)[1]‘, ‘bigint‘) AS [Record Id]    ,cast(record AS XML).value(‘(//Record/@type)[1]‘, ‘varchar(30)‘) AS [Type]FROM sys.dm_os_ring_buffers rbfCROSS JOIN sys.dm_os_sys_info tmeWHERE rbf.ring_buffer_type = ‘RING_BUFFER_RESOURCE_MONITOR‘ORDER BY rbf.TIMESTAMP ASC;

-- there are thousands of different countersSELECT *FROM sys.dm_os_performance_counters;SELECT *FROM sys.dm_os_performance_countersWHERE counter_name = ‘Page Life expectancy‘    AND object_name LIKE ‘%Buffer Manager%‘;SELECT *FROM sys.dm_os_performance_countersWHERE counter_name LIKE ‘%Average Wait Time%‘    AND instance_name = ‘Database‘;

DECLARE @BRPS BIGINTSELECT @BRPS = cntr_valueFROM sys.dm_os_performance_countersWHERE counter_name LIKE ‘Batch Requests/sec%‘WAITFOR DELAY ‘000:00:10‘SELECT (cntr_value - @BRPS) / 10.0 AS "Batch Requests/sec"FROM sys.dm_os_performance_countersWHERE counter_name LIKE ‘Batch Requests/sec%‘