SQL Server CleanUp Database Queries

We have a costly SQL server database with bad performance. Specifically, some store procedures (join several tables on primary key, each table has ~10M rows) were executed for several miniutes. The execution plan showed that the index seek costs 90% of the total time. Finally we found the root cause is the indexes have very high degree of fragmentation. Since its DBA had changed many times, we need to analyze the database schemas, table disk usage and storage procedure dependency tables. Based on these results, we cleanup tables, store procedures and rebuild the indexes to improve the DB performance. Here are the queries to accomplish these tasks.

Get Disk Usage By Table

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB,
    SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 /1024 AS UnusedSpaceMB
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
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 OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    UsedSpaceMB DESC

Sample result:

TableName SchemaName RowCounts TotalSpaceMB UsedSpaceMB UnusedSpaceMB
T1 dbo 25609971 97735 97366 369
T2 dbo 157963374 76543 76341 201
T3 dbo 10714232 56383 53113 3269
T4 dbo 47533799 26000 25310 690
T5 dbo 15956528 17750 17742 8

Get Storage Procedure Usage

DECLARE @dbname varchar(30) = '';

select
    db_name(st.dbid) as database_name,
    object_name(st.objectid) as name,
    p.size_in_bytes / 1024 as size_in_kb,
    p.usecounts,
    st.text
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle) st
where p.objtype = 'proc'
and st.dbid = db_id(@dbname)
order by p.usecounts desc

Sample result:

database_name name size_in_kb usecounts text
D Proc_Upsert 8800 39675743 xxx
D Proc_Update 1368 2216916 xxx
D Proc_Select 280 1641531 xxx

Get Storage Procedure Last Execution Time

SELECT DB_NAME(database_id)
    ,OBJECT_NAME(object_id,database_id) as procname
    ,cached_time
    ,last_execution_time
    ,execution_count
FROM sys.dm_exec_procedure_stats
order by procname

Sample result:

procname cached_time last_execution_time execution_count
CleanUpArticles 2021-04-20 17:00:02.710 2021-04-20 17:00:02.710 3788
CleanUpTables 2021-04-22 12:51:17.807 2021-04-22 12:51:17.807 6019
Proc_Select 2021-04-19 02:09:41.830 2021-04-19 02:09:41.830 3827

Get Storage Procedure Dependency Table

SELECT DISTINCT p.name AS proc_name, t.name AS table_name
FROM sys.sql_dependencies d
INNER JOIN sys.procedures p ON p.object_id = d.object_id
INNER JOIN sys.tables     t ON t.object_id = d.referenced_major_id
ORDER BY proc_name, table_name

Sample result:

proc_name table_name
P1 T1
P2 T1
P2 T2

Get Amount of Index Fragmentation

Refer to: # Resolve index fragmentation by reorganizing or rebuilding indexes

DECLARE @dbname varchar(30) = '';
DECLARE @tablename varchar(30) = '';

SELECT a.object_id, object_name(a.object_id) AS TableName,
    a.index_id, name AS IndedxName, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
    (DB_ID(@dbname)
        , OBJECT_ID(@tablename)
        , NULL
        , NULL
        , NULL) AS a
INNER JOIN sys.indexes AS b
    ON a.object_id = b.object_id
    AND a.index_id = b.index_id;
Sample result:

object_id TableName index_id IndedxName avg_fragmentation_in_percent
1733581214 T1 1 PK_dbo.T1 23.53719
1733581214 T1 3 key 22.5999

Rebuild Index

ALTER INDEX [PK_dbo.T1]
    ON [dbo].[T1]
    REBUILD WITH (ONLINE = ON);

Reorganize Index

ALTER INDEX [PK_dbo.T1]
    ON [dbo].[T1]
    REORGANIZE;