Stored procedure for SQL Server that provides information related to system, memory, CPU, and expensive queries

Stored procedure for SQL Server that provides information related to system, memory, CPU, and expensive queries:

CREATE PROCEDURE sp_db_info AS BEGIN -- Get system information SELECT @@SERVERNAME AS "Server Name", @@VERSION AS "SQL Server Version", SERVERPROPERTY('Edition') AS "Edition", SERVERPROPERTY('ProductLevel') AS "Product Level", SERVERPROPERTY('ProductUpdateLevel') AS "Product Update Level", SERVERPROPERTY('ProcessID') AS "Process ID", SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS "Computer Name" -- Get memory information SELECT (physical_memory_in_use_kb / 1024) AS "Memory Used (MB)", (available_physical_memory_kb / 1024) AS "Memory Available (MB)" FROM sys.dm_os_sys_memory -- Get CPU information SELECT cpu_count AS "CPU Count", hyperthread_ratio AS "Hyperthread Ratio", cpu_ticks/(cpu_ticks/ms_ticks) AS "CPU Usage (%)" FROM sys.dm_os_sys_info -- Get top 10 most expensive queries SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS [Query Text], qs.total_worker_time AS [Total CPU Time], qs.total_logical_reads AS [Total Logical Reads], qs.total_logical_writes AS [Total Logical Writes], qs.execution_count AS [Execution Count], qs.total_elapsed_time AS [Total Elapsed Time] FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.total_worker_time DESC END 

When executed, this stored procedure will return four result sets containing information about the SQL Server system, memory usage, CPU usage, and the top 10 most expensive queries.