1. 简介
Microsoft SQL Server（MSSQL）是一种广泛使用的关系型数据库管理系统。在使用MSSQL运营企业数据库时，有必要了解如何优化数据库性能并收缩数据库大小，以确保良好的性能和管理数据库将使用的存储空间。
2. 优化MSSQL数据库
2.1 索引优化
索引在MSSQL数据库中扮演着极其重要的角色，它是将数据库查询速度提升到更高水平的主要因素之一。通过优化索引，我们可以缩短查询执行的时间，加快数据库查询的速度。
一种常见的优化索引的方法是分析查询，了解查询实际的操作，找到查询过程中缺失或未充分利用的索引，并创建索引来支持查询。
-- 查看缺少索引的查询
SELECT
'SELECT' AS [QUERY TYPE],
DB_NAME(database_id) AS [DATABASE NAME],
OBJECT_NAME(s.object_id) AS [OBJECT NAME],
statement_text AS [QUERY TEXT],
execution_count AS [EXECUTION COUNT],
total_worker_time/execution_count AS [AVG CPU TIME],
(total_logical_reads + total_logical_writes)/execution_count AS [AVG IO]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WITH (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp WITH (NOLOCK)
CROSS APPLY(SELECT 1 UNION ALL
SELECT cp.usecounts
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
WHERE cp.plan_handle = qs.plan_handle) AS ca (usecounts)
CROSS APPLY(SELECT plan_type FROM qp.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') AS batch(statement)) AS sqb ([plan_type])
CROSS APPLY(SELECT plan_type FROM qp.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') AS batch(statement) WHERE sqb.plan_type = 'SELECT' AND query_hash = qp.query_plan.value('(//BatchSequence/Batch/Statements/QueryPlan/@QueryHash)[1]', 'nvarchar(4000)') AND query_plan = qp.query_plan.value('(//BatchSequence/Batch/Statements/QueryPlan)[1]', 'nvarchar(max)')) AS sqs ([plan_type])
CROSS APPLY(SELECT CONVERT(xml,sqs.query_plan).value('(//MissingIndexes)[1]','nvarchar(max)')) AS mi ([MissingIndexInfo])
CROSS APPLY(SELECT CONVERT(xml,sqs.query_plan).value('(//StmtSimple)' + CASE WHEN sqs.[plan_type] = 'SELECT' THEN '[1]' ELSE '' END + '/QueryPlan/MissingIndexes[1]','nvarchar(max)')) AS temp ([MissingIndexInfo2])
CROSS APPLY(SELECT [MissingIndexInfo],[MissingIndexInfo2]) AS mi2 ([MissingIndexInfo],[MissingIndexInfo2])
WHERE sqb.plan_type = 'SELECT'
AND qs.last_execution_time > DATEADD(dd,-7,GETDATE())
AND ISNULL(mi.MissingIndexInfo,'') != ''
--AND ISNULL(mi2.MissingIndexInfo2,'') != ''
ORDER BY [AVG IO] DESC;
以上SQL语句可以找出运行次数最多，最消耗CPU和I/O资源的查询，以便找到应该优化的查询。同时，查询将显示具有缺少标记的仅适用于当前查询的索引的建议，并且还将显示在缺少标记下若干默认索引。其建议的索引内容可以存储在 MissingIndexInfo 和 MissingIndexInfo2 列中，便于后续优化。
2.2 缓存优化
在MSSQL数据库中，查询缓存可以避免查询的执行成本，提高查询性能。通过将查询内容存储在缓存中，MSSQL可以在下一次询问时快速返回结果，从而提高性能。但是，由于缓存不会自动更新，因此我们需要对其进行优化。
要优化MSSQL查询缓存，请修改服务器级别的‘max server memory’ 高级选项配置项，以确保 SQL Server 实例具有足够的系统内存以缓存查询结果。但是，当访问后台系统资源时，应该适当减少SQL Server 实例可使用的系统内存，以避免操作无响应。
-- 显示所有可用高级选项配置项
SELECT * FROM sys.configurations WHERE is_advanced = 1;
查找 max server memory 选项的配置
-- 查找max server memory高级选项的设置：
sp_configure 'max server memory';
优化后，执行以下语句重新加载新高级选项的值
-- 重新加载新高级选项的值：
RECONFIGURE;
3. 收缩MSSQL数据库大小
3.1 数据库浏览缩小
对于无法调整数据文件大小的情况，可以尝试压缩数据库。通过压缩数据库，可以减少数据库的磁盘占用。可以使用以下SQL语句压缩右键单击“数据库”，然后单击“压缩”。
-- 压缩数据库数据文件：
DBCC SHRINKFILE('LOGICAL_FIEL_NAME',SIZE);
可以使用以下SQL语句压缩右键单击“数据库” ，然后单击“任务” 、 “缩小” 。
-- 在 SQL Server Management Studio 中压缩数据库：
USE databasename
GO
DBCC SHRINKDATABASE(N'databasename')
GO
3.2 逐步采取措施
根据需要，可以采取以下逐步措施来收缩SQL Server数据库。
1.重组索引或重新创建索引，以减少索引的碎片。
2.转储事务日志，这会释放未使用的空间，并防止日志文件过大。
3.使用备份和恢复操作。此方法可以生成一个新的数据库，该数据库只具有数据和索引。
3.3 限制数据库大小自动增长
限制数据库大小可以帮助管理数据库使用的磁盘空间。通过设置大小自动增长选项，可以确保数据库不会无限制地增长。
以下SQL语句可以将数据库大小限制为50MB，并将增量设置为10MB。
-- 修改数据库的大小以及每次增长的大小：
ALTER DATABASE databasename MODIFY FILE ( NAME = database_file01 , SIZE = 50000KB , FILEGROWTH = 10000KB );
3.4 删除不需要的数据
删除不需要的数据是另一种减小数据库大小的方法。可以使用以下SQL语句删除表中的记录。
-- 从表中删除记录：
DELETE FROM tablename WHERE condition;
4. 总结
MSSQL数据库管理需要注意性能和空间的平衡。优化索引和缓存可以加快数据库查询的速度，使用以上方法也可以帮助减少数据库所占用的空间。