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

发表于 更新于 分类于 评论： 阅读次数：

生产环境有个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;