MSSQL数据库实现轻量级瘦身-猿码集

1. 前言

在现实世界中，由于一些原因，例如硬件条件限制、数据管理需求降低等，数据库通常不得不承担越来越多的工作负担。随着数据不断增加和应用程序复杂性增加，数据库在处理查询和维护数据时变得更加缓慢。

为了改善这种情况，我们建议对MS SQL Server数据库进行“瘦身”。

2. 何为“瘦身”？

“瘦身”指的是在不影响功能和数据完整性的情况下，尽可能缩小数据库的体积。这也有助于提高数据库的性能和响应时间。

3. 清理不必要的对象

3.1 清理无用的表

在数据库中，通常会有一些表长期没有使用，或者已经被删除和替换，但它们的数据对象仍然占用着硬盘空间。这些表并不会影响数据库的功能和数据完整性，但会使数据库变得庞大，降低性能。

我们可以通过以下查询来识别无用的表：

SELECT  OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS TableName,
        i.name AS IndexName,
        i.index_id AS IndexID,
        ddps.row_count AS [RowCount]
FROM    sys.indexes AS i
        INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
        INNER JOIN sys.dm_db_partition_stats AS ddps ON o.OBJECT_ID = ddps.OBJECT_ID
                                                   AND i.index_id = ddps.index_id
WHERE   i.type_desc <> N'HEAP'
        AND OBJECTPROPERTY(i.OBJECT_ID, N'IsMsShipped') = 0
        AND i.is_disabled = 0
        AND i.is_hypothetical = 0
        AND ddps.row_count = 0
ORDER BY TableName ASC;

以上的查询会返回查询结果为空的表。如果您确定这些表不再使用，可以将其删除，从而节省空间。我们一般建议将这些表备份后删除。

3.2 清理无效的索引

索引可以提高数据库查询的速度。但是，如果索引无效或根本没有使用，它们将消耗额外的空间和资源，损害数据库的性能。

可以使用以下查询来识别哪些索引被使用或无效：

SELECT  OBJECT_SCHEMA_NAME(i.OBJECT_ID) + '.' + OBJECT_NAME(i.OBJECT_ID) AS TableName,
        i.name AS IndexName,
        i.index_id AS IndexID,
        i.is_disabled,
        i.is_hypothetical,
        i.is_primary_key,
        i.is_unique,
        i.fill_factor,
        i.is_padded,
        i.is_clustered,
        i.is_identity,
        i.ignore_dup_key,
        i.has_filter,
        i.fill_factor,
        ddps.row_count AS [RowCount],
        us.user_scans,
        us.user_lookups,
        us.user_seeks
FROM    sys.indexes AS i
        INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
        INNER JOIN sys.dm_db_partition_stats AS ddps ON o.OBJECT_ID = ddps.OBJECT_ID
                                                   AND i.index_id = ddps.index_id
        LEFT JOIN ( SELECT   object_id,
                            index_id,
                            SUM(user_scans) AS user_scans,
                            SUM(user_lookups) AS user_lookups,
                            SUM(user_seeks) AS user_seeks
                    FROM     sys.dm_db_index_usage_stats
                    GROUP BY object_id,
                            index_id ) AS us ON i.OBJECT_ID = us.object_id
                                                AND i.index_id = us.index_id
WHERE   i.type_desc <> N'HEAP'
        AND OBJECTPROPERTY(i.OBJECT_ID, N'IsMsShipped') = 0
        AND i.is_disabled = 0
        AND i.is_hypothetical = 0
        AND ddps.row_count > 0
ORDER BY us.user_seeks + us.user_scans + us.user_lookups DESC, TableName ASC;

这个查询将索引按使用顺序列出。如果一个索引未使用，可以使用以下查询删除它：

DROP INDEX [IndexName] ON [TableName] WITH ( ONLINE = OFF );

3.3 清理无用的存储过程和视图

与无用表和无效索引类似，存储过程和视图也可能存在没有使用的问题，为了节省数据库空间，应该清理。

您可以使用以下查询查找出没有使用的存储过程：

SELECT  [name] AS [SPName],
        [create_date] AS [CreatedDate],
        [modify_date] AS [ModifiedDate],
        [type_desc] AS [TypeDescription]
FROM    sys.procedures
WHERE   OBJECTPROPERTYEX(OBJECT_ID([name]), N'ExecIsTriggerDeferred') = 0
        AND OBJECTPROPERTYEX(OBJECT_ID([name]), N'IsMapped') = 0
        AND OBJECTPROPERTYEX(OBJECT_ID([name]), N'IsExecuted') = 0
        AND OBJECTPROPERTY([object_id], N'IsSystemObject') = 0
ORDER BY ModifyDate DESC;

如果一个存储过程长时间未使用，可以将其备份后删除：

DROP PROCEDURE [SPName];

以下是查找无效视图的示例查询：

SELECT  [name] AS [ViewName],
        [create_date] AS [CreatedDate],
        [modify_date] AS [ModifiedDate]
FROM    sys.views
WHERE   OBJECTPROPERTY([object_id], N'IsView') = 1
        AND OBJECTPROPERTY([object_id], N'IsSystemObject') = 0
        AND [isdate]([modify_date]) = 0
        AND NOT EXISTS ( SELECT  [schema_id],
                                    [name]
                         FROM   sys.tables AS t
                                INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id]
                         WHERE  t.[object_id] = v.[object_id] )
 ORDER BY ModifyDate DESC;

同样，如果一个视图长时间未使用，可以将其备份后删除：

DROP VIEW [ViewName];

4. 数据表处理

通过优化和精简数据表，数据库可以更快地响应查询。在创建表时请考虑以下要点：

4.1 选择正确的数据类型

选择正确的数据类型可以显著提高数据库性能，也可以节省磁盘空间。因此，请使用最适合您的数据类型。

例如，将使用TinyInt而不是Boolean，使用BigInt而不是Int，使用Varchar（max）而不是Text、Ntext或Image。

4.2 清理数据表

在不断的使用过程中，我们一些表中存储了大量的历史数据，这些数据可能不再有用，删除他们可以显著减少表的体积。例如，我们可以清理掉长时间不活跃的用户、已经处理完成的业务记录等。

尽管这些数据不会影响查询速度、数据完整性等，但我们仍然建议将其删除，从而加速数据库的响应时间。

除了删除历史数据，您还可以缩小表，使用以下查询可以将数据表缩小到最小值：

DBCC CLEANTABLE ([database_name], [table_name]);

使用上面的查询，将会去除数据表中的任何空间或修剪空间，缩小数据表大小。

5. 结论

通过瘦身，MS SQL Server数据库的大小可以被大大降低，从而提高响应速度和效率，而这不会影响数据库的功能和数据完整性。实现这些瘦身技巧只需一些时间和注意力，仅需进行清理、缩小和精简性操作即可。

我们建议您在保证数据库安全性的情况下，以上技巧可以作为对SQL Server建立公用的最佳实践。如果您估计每个步骤执行所需的时间，每个步骤在几分钟到几小时之间，整个过程也会花费更少的时间，从而让您的SQL Server运行更加高效。

MSSQL数据库实现轻量级瘦身