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;