Ultimate Guide to SQL Server Performance Tuning and Monitoring

-- Basic version check
SELECT @@VERSION AS SQLServerVersion;

-- Detailed edition and version information
SELECT 
    SERVERPROPERTY('productversion') AS ProductVersion,
    SERVERPROPERTY('productlevel') AS ProductLevel,
    SERVERPROPERTY('edition') AS Edition;

-- Check SQL Server start time
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

-- Alternative method using system processes
SELECT login_time FROM sys.sysprocesses WHERE spid = 1;

-- Database sizes in MB
SELECT 
    DB_NAME(database_id) AS DatabaseName,
    size * 8 / 1024 AS SizeMB
FROM sys.master_files;

-- With growth information
SELECT 
    name AS DatabaseName,
    size * 8 / 1024 AS SizeMB,
    growth * 8 / 1024 AS GrowthMB  
FROM sys.master_files;

CREATE TABLE DatabaseGrowthHistory (
    LogDate DATETIME,
    DatabaseName NVARCHAR(128),
    SizeMB DECIMAL(18, 2)
);

-- Top 10 long-running active queries
SELECT 
    r.session_id,
    r.start_time,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    s.text AS QueryText
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.status = 'running'
ORDER BY r.total_elapsed_time DESC;

-- Top CPU-consuming queries
SELECT TOP 10
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time/qs.execution_count AS AvgCPU,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_worker_time DESC;

-- Identify blocked processes
SELECT 
    blocking_session_id AS Blocker, 
    session_id AS BlockedProcess
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

-- Deadlock detection
SELECT 
    r.session_id AS DeadlockSessionID,
    r.blocking_session_id AS BlockingSessionID,
    r.wait_type,
    r.wait_time,
    s.login_name,
    s.host_name
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE r.blocking_session_id <> 0 AND r.status = 'suspended';

-- Indexes with no seeks/scans/lookups
SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB
FROM sys.indexes i
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE i.is_primary_key = 0 AND i.is_unique = 0 AND i.type_desc = 'NONCLUSTERED'
GROUP BY i.object_id, i.index_id, i.name, i.type_desc, p.rows
HAVING MAX(ISNULL(ius.user_seeks, 0)) = 0 
   AND MAX(ISNULL(ius.user_scans, 0)) = 0
   AND MAX(ISNULL(ius.user_lookups, 0)) = 0
ORDER BY TotalSpaceKB DESC;

-- Potential missing indexes
SELECT
    mid.statement AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS ImprovementMeasure
FROM sys.dm_db_missing_index_group_stats migs
INNER JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY ImprovementMeasure DESC;

-- Check fragmentation levels
SELECT
    OBJECT_NAME(ps.object_id) AS TableName,
    i.name AS IndexName,
    ps.avg_fragmentation_in_percent,
    ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.avg_fragmentation_in_percent > 10
ORDER BY ps.avg_fragmentation_in_percent DESC;

-- Reorganize index
ALTER INDEX IX_YourIndexName ON dbo.YourTableName REORGANIZE;

-- Rebuild index
ALTER INDEX IX_YourIndexName ON dbo.YourTableName REBUILD;

-- TempDB space usage
SELECT
    (SUM(unallocated_extent_page_count) * 1.0 / 128) AS [Free space (MB)],
    (SUM(version_store_reserved_page_count) * 1.0 / 128) AS [Version Store (MB)],
    (SUM(internal_object_reserved_page_count) * 1.0 / 128) AS [Internal Objects (MB)],
    (SUM(user_object_reserved_page_count) * 1.0 / 128) AS [User Objects (MB)]
FROM tempdb.sys.dm_db_file_space_usage;

-- Top wait types
SELECT
    wait_type,
    wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE',
                       'SLEEP_TASK','SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH',
                       'WAITFOR','LOGMGR_QUEUE','CHECKPOINT_QUEUE',
                       'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT',
                       'BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT',
                       'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE',
                       'FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT',
                       'XE_DISPATCHER_JOIN','SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
                       'ONDEMAND_TASK_QUEUE','BROKER_EVENTHANDLER',
                       'SLEEP_BPOOL_FLUSH','SLEEP_DBSTARTUP','DIRTY_PAGE_POLL',
                       'HADR_FILESTREAM_IOMGR_IOCOMPLETION','SP_SERVER_DIAGNOSTICS_SLEEP')
ORDER BY wait_time_ms DESC;

-- Recent backup history
SELECT
    d.name AS DatabaseName,
    b.backup_start_date,
    b.backup_finish_date,
    b.backup_size/1024/1024 AS BackupSizeMB,
    CASE b.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
    END AS BackupType
FROM msdb.dbo.backupset b
JOIN master.sys.databases d ON b.database_name = d.name
ORDER BY b.backup_finish_date DESC;

-- Database consistency check
DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- Check for suspect pages
SELECT * FROM msdb.dbo.suspect_pages;

-- Identify long-running transactions
SELECT
    transaction_id,
    name,
    transaction_state,
    transaction_begin_time
FROM sys.dm_tran_active_transactions
ORDER BY transaction_begin_time;

-- DBCC command for oldest active transaction
DBCC OPENTRAN;

-- Enable Query Store for a database
ALTER DATABASE YourDatabase SET QUERY_STORE = ON;

-- Configure Query Store settings
ALTER DATABASE YourDatabase SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB = 1024,
    INTERVAL_LENGTH_MINUTES = 60
);

-- Create session for long-running queries
CREATE EVENT SESSION [LongRunningQueries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    WHERE ([duration]>10000000)) -- 10 seconds
ADD TARGET package0.event_file(SET filename=N'C:\XEvents\LongRunningQueries.xel')
WITH (MAX_MEMORY=4096 KB, MAX_DISPATCH_LATENCY=30 SECONDS);
GO

ALTER EVENT SESSION [LongRunningQueries] ON SERVER STATE = START;