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;