-- SQL Server Process Address space info --(shows whether locked pages is enabled, among other things) SELECT physical_memory_in_use_kb/1...

-- Good basic information about memory amounts and state SELECT total_physical_memory_kb/1024000 Total_Physical_MM_GB,...

-- Get CPU Utilization History for last 256 minutes (in one minute intervals) -- This version works with SQL Server 2008 and SQL Server...

-- Get Average Task Counts (run multiple times) SELECT AVG(current_tasks_count) AS [Avg Task Count], AVG(runnable_tasks_count) AS [Avg...

-- Get logins that are connected and how many sessions they have SELECT login_name, COUNT(session_id) AS [session_count] FROM...

-- Signal Waits for instance SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu)...

-- Common Significant Wait types with BOL explanations -- *** Network Related Waits *** -- ASYNC_NETWORK_IO Occurs on network writes...

-- Get total buffer usage by database for current instance SELECT DB_NAME(database_id) AS [Database Name], COUNT(*) * 8/1024.0 AS [Cached...

-- Get CPU utilization by database WITH DB_CPU_Stats AS (SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time)...

-- Recovery model, log reuse wait description, log file size, log usage size -- and compatibility level for all databases on instance...

-- Volume info for all databases on the current instance (SQL Server 2008 R2 SP1 or greater) SELECT DB_NAME(f.database_id) AS...

SELECT DB_NAME([database_id])AS [Database Name], [file_id], name, physical_name, type_desc, state_desc, CONVERT( bigint, size/128000.0)...

-- Get configuration values for instance SELECT name, value, value_in_use, [description] FROM sys.configurations ORDER BY name OPTION...

-- Get processor description from Windows Registry EXEC xp_instance_regread 'HKEY_LOCAL_MACHINE', 'HARDWARE\DESCRIPTION\System\CentralPro...

-- Get System Manufacturer and model number from -- SQL Server Error log. This query might take a few seconds -- if you have not recycled...

SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio], cpu_count/hyperthread_ratio AS [Physical CPU Count],...

-- SQL Server Services information (SQL Server 2008 R2 SP1 or greater) SELECT servicename, startup_type_desc, status_desc,...

-- Windows information (SQL Server 2008 R2 SP1 or greater) SELECT windows_release, windows_service_pack_level, windows_sku,...

-- SQL and OS Version information for current instance SELECT @@VERSION AS [SQL Server and OS Version Info];

The following script will generate output script with restore command to restore all log backup located at L:\Log_Backup\DB_Name\ exec...