分析和清理SQL Server数据表和存储过程

发表于： 2021-05-02 分类于： Misc 阅读：≈ 2分钟浏览：评论：

生产环境有个SQL Server云数据库，花钱不少，性能却很差。最近发现跑一些并不太复杂的存储过程却需要好几分钟。其中一个常用的存储过程是将几张表通过一个键值关联并返回结果，这几张表都在千万级大小。分析了下执行计划，发现index seek花去了90%以上的时间，进而发现这些表的索引碎片化都极为严重。由于这个老DB几经易手，没人知道这些表和存储过程是做什么的，于是需要手动分析这些表的schema和存储过程所依赖的表。基于这些结果再对索引重建提升性能。下面就是用来分析DB的一些重要Query。

获取各表的磁盘使用量

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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

结果示例:

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

获取各存储过程的用量

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
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

结果示例:

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

获取存储过程的上次执行时间

1
2
3
4
5
6
7
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 

结果示例:

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

获取存储过程依赖哪些表

1
2
3
4
5
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

结果示例:

proc_name	table_name
P1	T1
P2	T1
P2	T2

分析各表索引的碎片化程度

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
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

重建索引

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

重构索引

1
2
3
ALTER INDEX [PK_dbo.T1]
    ON [dbo].[T1]
    REORGANIZE;