SQL DB Performance Tuning Scripts

 

1. Performance Tuning Scripts

a. Identify Missing Indexes

sql
SELECT 
    DB_NAME(database_id) AS DatabaseName,
    OBJECT_NAME(object_id, database_id) AS TableName,
    migs.*, 
    'CREATE INDEX [IX_' + OBJECT_NAME(object_id, database_id) + '_' + column_name + '] ON [' + 
    OBJECT_SCHEMA_NAME(object_id, database_id) + '].[' + OBJECT_NAME(object_id, database_id) + '] (' + column_name + ')' AS CreateIndexScript
FROM sys.dm_db_missing_index_details mid
CROSS APPLY (SELECT SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(object_id) AS TableName,
                    (SELECT column_name + ', ' AS [text()]
                     FROM sys.dm_db_missing_index_columns(mid.index_handle)
                     FOR XML PATH ('')) AS column_name) DerivedData
JOIN sys.dm_db_missing_index_group_stats migs 
    ON migs.group_handle = mid.index_group_handle
WHERE migs.avg_total_user_cost > 1000
ORDER BY migs.avg_total_user_cost DESC;

b. Index Fragmentation Analysis

sql
SELECT 
    dbschemas.[name] AS SchemaName,
    dbtables.[name] AS TableName,
    dbindexes.[name] AS IndexName,
    indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') indexstats
INNER JOIN sys.objects dbtables ON indexstats.object_id = dbtables.[object_id]
INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes dbindexes ON indexstats.index_id = dbindexes.index_id AND indexstats.object_id = dbindexes.[object_id]
WHERE indexstats.avg_fragmentation_in_percent > 10;

c. Expensive Queries by CPU

sql
SELECT TOP 10
    qs.execution_count,
    qs.total_logical_reads AS TotalReads,
    qs.total_worker_time AS TotalCPU,
    qs.total_elapsed_time / 1000 AS TotalElapsedTime_ms,
    SUBSTRING(st.text, qs.statement_start_offset / 2, 
    (CASE 
        WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2 
        ELSE qs.statement_end_offset 
    END - qs.statement_start_offset) / 2) AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;

---

2. Database Object Details

a. List All Tables and Row Counts

sql
SELECT 
    SCHEMA_NAME(schema_id) AS SchemaName,
    t.name AS TableName,
    p.rows AS RowCounts
FROM sys.tables t
INNER JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id < 2
ORDER BY p.rows DESC;

b. Find Object Dependencies

sql
SELECT 
    referencing.name AS ReferencingObject,
    referenced.name AS ReferencedObject,
    d.referenced_class_desc
FROM sys.sql_expression_dependencies d
JOIN sys.objects referencing ON d.referencing_id = referencing.object_id
JOIN sys.objects referenced ON d.referenced_id = referenced.object_id;

---

3. Search for Text in Database Objects

a. Search for Text in Stored Procedures, Views, and Functions

sql
SELECT 
    OBJECT_NAME(object_id) AS ObjectName,
    OBJECT_DEFINITION(object_id) AS ObjectText
FROM sys.sql_modules
WHERE OBJECT_DEFINITION(object_id) LIKE '%YourTextHere%';

b. Search for Text in All Columns of All Tables

sql
DECLARE @SearchText NVARCHAR(100) = 'YourTextHere';
EXEC sp_msforeachtable 'SELECT ''?'' AS TableName, * FROM ? WHERE ''?'' LIKE ''%' + @SearchText + '%''';

---

4. Other Useful Scripts

a. Check for Blocking Sessions

sql
SELECT 
    spid AS BlockingSPID,
    blocked AS BlockedSPID,
    DB_NAME(dbid) AS DatabaseName,
    cmd AS Command,
    waittype AS WaitType
FROM sys.sysprocesses
WHERE blocked > 0;

b. Identify Long-Running Queries

sql
SELECT 
    r.session_id,
    r.start_time,
    DB_NAME(r.database_id) AS DatabaseName,
    SUBSTRING(qt.text, r.statement_start_offset / 2, 
              (CASE 
                   WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                   ELSE r.statement_end_offset 
               END - r.statement_start_offset) / 2) AS QueryText
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) qt
WHERE r.status = 'running'
ORDER BY r.start_time ASC;


Database Object Details
a. List All Indexes with Columns
sql
SELECT 
    t.name AS TableName,
    ind.name AS IndexName,
    col.name AS ColumnName,
    ic.key_ordinal AS KeyOrder,
    ind.type_desc AS IndexType
FROM sys.indexes ind
INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id AND ind.index_id = ic.index_id
INNER JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
INNER JOIN sys.tables t ON ind.object_id = t.object_id
ORDER BY t.name, ind.name, ic.key_ordinal;
b. Find All User-Defined Tables and Their Sizes
sql
SELECT 
    t.name AS TableName,
    SUM(p.rows) AS TotalRows,
    SUM(a.total_pages) * 8 / 1024 AS TotalSize_MB
FROM sys.tables t
INNER JOIN sys.partitions p ON t.object_id = p.object_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE p.index_id IN (0, 1)
GROUP BY t.name
ORDER BY TotalSize_MB DESC;
2. Memory Usage
a. Current Memory Usage by Database
sql
SELECT 
    DB_NAME(database_id) AS DatabaseName,
    COUNT(*) * 8 / 1024 AS MemoryUsage_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY MemoryUsage_MB DESC;
b. Buffer Pool Usage
sql
SELECT 
    DB_NAME(database_id) AS DatabaseName,
    COUNT(*) AS PagesInMemory,
    COUNT(*) * 8 / 1024 AS MemoryUsage_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY MemoryUsage_MB DESC;
3. CPU Usage
a. Current CPU Usage by Session
sql
SELECT 
    r.session_id,
    r.cpu_time AS CPUTime_ms,
    r.reads AS LogicalReads,
    r.writes AS LogicalWrites,
    DB_NAME(r.database_id) AS DatabaseName,
    SUBSTRING(qt.text, r.statement_start_offset / 2, 
              (CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
                    ELSE r.statement_end_offset END - r.statement_start_offset) / 2) AS QueryText
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) qt
ORDER BY r.cpu_time DESC;
4. Duplicate Handling
a. Remove Duplicates While Keeping Original
sql
WITH CTE AS (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY [ColumnToCheck] ORDER BY [PrimaryKeyColumn]) AS RowNum
    FROM YourTable
)
DELETE FROM CTE WHERE RowNum > 1;
5. Index Optimization
a. Detect Missing Indexes
sql
SELECT 
    migs.group_handle,
    mid.index_handle,
    DB_NAME(mid.database_id) AS DatabaseName,
    OBJECT_NAME(mid.object_id, mid.database_id) AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.avg_total_user_cost,
    migs.avg_user_impact
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 migs.avg_user_impact DESC;
6. Server Usage
a. Server Resource Usage
sql
SELECT 
    sqlserver_start_time AS StartTime,
    cpu_ticks / ms_ticks AS CPUUsage_ms,
    physical_memory_kb / 1024 AS TotalMemory_MB
FROM sys.dm_os_sys_info;
7. Network Usage
a. Current Network Statistics
sql
SELECT 
    net_transport,
    auth_scheme,
    client_net_address,
    local_net_address,
    connection_id
FROM sys.dm_exec_connections;
8. sp_who Replacement
a. Enhanced Session Management Query
sql
SELECT 
    session_id,
    login_name,
    status,
    DB_NAME(database_id) AS DatabaseName,
    wait_time,
    wait_type,
    cpu_time,
    memory_usage,
    logical_reads,
    reads,
    writes
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;
9. Concurrent Connection Monitoring
a. Active Connections by Database
sql
SELECT 
    DB_NAME(dbid) AS DatabaseName,
    COUNT(dbid) AS ConnectionCount
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid
ORDER BY ConnectionCount DESC;
10. Expensive Query Detection
a. Identify Top CPU-Consuming Queries
sql
SELECT TOP 10
    qs.execution_count,
    qs.total_worker_time / qs.execution_count AS AvgCPUTime_ms,
    DB_NAME(qp.dbid) AS DatabaseName,
    SUBSTRING(st.text, qs.statement_start_offset / 2, 
              (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2 
                    ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) qp
ORDER BY AvgCPUTime_ms DESC;




1. Database Management
a. List All Databases with Recovery Model
sql
SELECT 
    name AS DatabaseName, 
    recovery_model_desc AS RecoveryModel
FROM sys.databases
ORDER BY name;
b. Change Recovery Model to Simple
sql
ALTER DATABASE [YourDatabaseName]
SET RECOVERY SIMPLE;
c. Database Backup Script
sql
BACKUP DATABASE [YourDatabaseName]
TO DISK = 'C:\\Backup\\YourDatabaseName.bak'
WITH FORMAT, INIT, 
     NAME = 'Full Backup of YourDatabaseName', 
     SKIP, NOREWIND, NOUNLOAD, STATS = 10;
2. Database Design
a. List Tables Without Primary Key
sql
SELECT 
    t.name AS TableName
FROM sys.tables t
LEFT JOIN sys.key_constraints kc ON t.object_id = kc.parent_object_id AND kc.type = 'PK'
WHERE kc.name IS NULL;
b. Identify Foreign Keys Without Indexes
sql
SELECT 
    fk.name AS ForeignKeyName,
    OBJECT_NAME(fk.parent_object_id) AS TableName,
    COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ColumnName
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
WHERE NOT EXISTS (
    SELECT 1
    FROM sys.index_columns ic
    INNER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    WHERE i.is_unique = 1 AND ic.column_id = fkc.parent_column_id
);
3. Query Optimization
a. Identify Queries with High Logical Reads
sql
SELECT TOP 10
    total_logical_reads AS LogicalReads,
    execution_count AS ExecutionCount,
    total_logical_reads / execution_count AS AvgLogicalReads,
    SUBSTRING(st.text, qs.statement_start_offset / 2,
              (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2
                    ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_logical_reads DESC;
4. Performance Monitoring
a. Monitor Index Usage
sql
SELECT 
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    user_seeks, 
    user_scans, 
    user_lookups, 
    user_updates
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.database_id = DB_ID('YourDatabaseName')
ORDER BY user_seeks DESC;
b. Check Wait Statistics
sql
SELECT 
    wait_type,
    wait_time_ms / 1000 AS WaitTimeSeconds,
    signal_wait_time_ms / 1000 AS SignalWaitTimeSeconds,
    waiting_tasks_count AS WaitingTasks
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;
5. Troubleshooting
a. Find Blocked and Blocking Sessions
sql
SELECT 
    spid AS SessionID, 
    blocked AS BlockingSessionID, 
    DB_NAME(dbid) AS DatabaseName, 
    waittype AS WaitType, 
    cmd AS Command
FROM sys.sysprocesses
WHERE blocked > 0;
b. Detect Deadlocks
sql
SELECT 
    XEventData.value('(event/action[@name=\"session_id\"]/value)[1]', 'INT') AS SessionID,
    XEventData.value('(event/data[@name=\"database_name\"]/value)[1]', 'NVARCHAR(MAX)') AS DatabaseName,
    XEventData.value('(event/data[@name=\"object_name\"]/value)[1]', 'NVARCHAR(MAX)') AS ObjectName
FROM (SELECT CAST(target_data AS XML) AS TargetData
      FROM sys.dm_xe_session_targets st
      JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
      WHERE st.target_name = 'ring_buffer' AND s.name = 'system_health') AS XEvent
CROSS APPLY TargetData.nodes('//event[@name=\"deadlock\"]/data') AS XEventData(event);
6. Session Management
a. Kill a Session
sql
KILL [SessionID];
b. List Active Sessions
sql
SELECT 
    session_id, 
    login_name, 
    status, 
    DB_NAME(database_id) AS DatabaseName, 
    cpu_time, 
    memory_usage, 
    logical_reads, 
    reads, 
    writes
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
ORDER BY cpu_time DESC;
7. Advanced Analysis
a. Identify Most Used Tables
sql
SELECT 
    OBJECT_NAME(p.object_id) AS TableName,
    i.name AS IndexName,
    dm_logical_reads + dm_physical_reads AS TotalReads
FROM sys.dm_db_index_usage_stats us
INNER JOIN sys.partitions p ON us.object_id = p.object_id
INNER JOIN sys.indexes i ON us.index_id = i.index_id
ORDER BY TotalReads DESC;
b. Analyze IO Bottlenecks
sql
SELECT 
    database_id, 
    file_id, 
    num_of_reads, 
    num_of_writes, 
    io_stall_read_ms, 
    io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL);
8. User Management
a. List All Users and Roles
sql
SELECT 
    u.name AS UserName, 
    r.name AS RoleName
FROM sys.database_principals u
LEFT JOIN sys.database_role_members rm ON u.principal_id = rm.member_principal_id
LEFT JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
WHERE u.type IN ('S', 'U') AND u.name NOT LIKE '##%';
b. Grant Permissions to a User
sql
GRANT SELECT, INSERT, UPDATE, DELETE ON [YourTable] TO [YourUser];
9. Backup and Restore Management
a. View Backup History
sql
SELECT 
    database_name,
    backup_start_date,
    backup_finish_date,
    backup_size / 1024 / 1024 AS BackupSize_MB,
    physical_device_name
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
ORDER BY backup_start_date DESC;
b. Restore Database
sql
RESTORE DATABASE [YourDatabaseName]
FROM DISK = 'C:\\Backup\\YourDatabaseName.bak'
WITH MOVE 'LogicalDataFileName' TO 'C:\\Data\\YourDatabaseName.mdf',
     MOVE 'LogicalLogFileName' TO 'C:\\Data\\YourDatabaseName.ldf';




10. Extended Database Management
a. Check Disk Space Used by Database Files
sql
SELECT 
    DB_NAME(database_id) AS DatabaseName,
    type_desc AS FileType,
    name AS LogicalFileName,
    physical_name AS PhysicalFileName,
    size * 8 / 1024 AS Size_MB,
    size * 8 / 1024 / 1024 AS Size_GB
FROM sys.master_files
ORDER BY DatabaseName;
b. Shrink Database Log File
sql
DBCC SHRINKFILE ('LogicalLogFileName', 10); -- Shrink to 10MB
c. Rebuild All Indexes in a Database
sql
DECLARE @TableName NVARCHAR(MAX);
DECLARE TableCursor CURSOR FOR
SELECT '[' + SCHEMA_NAME(schema_id) + '].[' + name + ']' AS TableName
FROM sys.tables;

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('ALTER INDEX ALL ON ' + @TableName + ' REBUILD');
    FETCH NEXT FROM TableCursor INTO @TableName;
END;

CLOSE TableCursor;
DEALLOCATE TableCursor;
11. Security and User Management
a. Check User Permissions
sql
SELECT 
    grantee.name AS UserName,
    permission_name AS Permission,
    class_desc AS ObjectType,
    major_id AS ObjectID,
    OBJECT_NAME(major_id) AS ObjectName
FROM sys.database_permissions p
JOIN sys.database_principals grantee ON p.grantee_principal_id = grantee.principal_id;
b. Disable User Account
sql
ALTER LOGIN [UserName] DISABLE;
c. Identify Orphaned Users
sql
SELECT 
    dp.name AS UserName
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U') AND sp.sid IS NULL;
12. Query Optimization
a. Identify Queries with High Execution Times
sql
SELECT TOP 10
    total_elapsed_time / 1000 AS TotalElapsedTime_ms,
    execution_count,
    SUBSTRING(qt.text, qs.statement_start_offset / 2,
              (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                    ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY TotalElapsedTime_ms DESC;
b. Detect Implicit Conversions in Queries
sql
SELECT 
    OBJECT_NAME(st.objectid) AS ObjectName,
    st.text AS QueryText,
    cp.usecounts AS PlanReuseCount
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.text LIKE '%CONVERT%';
13. Troubleshooting
a. Identify Unused Indexes
sql
SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    s.user_seeks + s.user_scans + s.user_lookups AS TotalAccesses,
    s.user_updates AS TotalUpdates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.user_seeks + s.user_scans + s.user_lookups = 0
      AND i.is_primary_key = 0
      AND i.is_unique_constraint = 0;
b. Identify High Disk IO Queries
sql
SELECT TOP 10
    total_physical_reads AS TotalReads,
    execution_count,
    SUBSTRING(qt.text, qs.statement_start_offset / 2,
              (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                    ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY TotalReads DESC;
14. Session and Connection Management
a. List Open Transactions
sql
DBCC OPENTRAN;
b. Detect Active Locks
sql
SELECT 
    resource_type,
    resource_description,
    request_mode,
    request_status,
    request_session_id
FROM sys.dm_tran_locks;
c. Disconnect All Sessions from a Database
sql
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
15. Backup and Restore
a. Differential Backup
sql
BACKUP DATABASE [YourDatabaseName]
TO DISK = 'C:\\Backup\\YourDatabaseName_Diff.bak'
WITH DIFFERENTIAL, INIT, STATS = 10;
b. Restore with Standby
sql
RESTORE DATABASE [YourDatabaseName]
FROM DISK = 'C:\\Backup\\YourDatabaseName.bak'
WITH STANDBY = 'C:\\Backup\\YourDatabaseName_Standby.ldf';
16. Advanced Analysis
a. Table Statistics
sql
DBCC SHOW_STATISTICS ('YourTable', 'YourIndexName');
b. Identify Tables Without Statistics
sql
SELECT 
    t.name AS TableName
FROM sys.tables t
WHERE NOT EXISTS (
    SELECT 1 
    FROM sys.stats s 
    WHERE s.object_id = t.object_id);
c. Monitor TempDB Usage
sql
SELECT 
    session_id,
    user_objects_alloc_page_count AS UserObjectsPages,
    internal_objects_alloc_page_count AS InternalObjectsPages
FROM sys.dm_db_task_space_usage;
17. Maintenance
a. Update Statistics
sql
EXEC sp_updatestats;
b. Reorganize Indexes
sql
DECLARE @TableName NVARCHAR(MAX);
DECLARE TableCursor CURSOR FOR
SELECT '[' + SCHEMA_NAME(schema_id) + '].[' + name + ']' AS TableName
FROM sys.tables;

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('ALTER INDEX ALL ON ' + @TableName + ' REORGANIZE');
    FETCH NEXT FROM TableCursor INTO @TableName;
END;

CLOSE TableCursor;
DEALLOCATE TableCursor;