SQL Server数据库优化实战（三）

本章主要来介绍一下收缩日志和表的压缩。

收缩日志文件

--利用

exec sp_spaceused

语句查看数据库大小

--右键数据库属性

--查看选项

--将恢复模式设置成简单

--右键数据库-任务-收缩-文件

--文件类型选择日志

--查看收缩后数据库大小

--右键数据库属性-选项

--将恢复模式设置成完整

--注意：此时需要进行一次数据库完整备份

表压缩

--SQL Server 2005及以上版本支持表分区

表分区具体操作详见以下网址：

http://blog.csdn.net/yole_grise/article/details/18658949

-- SQL Server 2008及以上版本支持表压缩

（标准版不可以进行表压缩）

--对于我们的主流客户来说，随着时间的积累、经营规模的扩大，总部数据库越来越大，产生很多负面影响。

例如，一个100家门店的连锁公司，三年下来数据库可能会达到100G至200G。

由于数据库增大导致的主要影响如下：

１、 导致磁盘存储成本增高；200G的数据库加上备份需要，至少得1T磁盘才勉强够用。

２、 数据库性能下降。

３、 数据库出故障的潜在风险增加。（虽然没有直接的证据，但简单的推理就可得出该结论）

４、 备份一次数据库的时间太长。备份文件的复制和还原比较麻烦。

像现在我们有很多客户数据库都在100G以上了。在这种背景下，“表压缩”闪亮登场。

操作方法：

--右键表-存储-管理压缩

--压缩前表大小：

--查看一下表大小：

--表大小由81344KB压缩到21328KB。

--索引压缩也是同样的道理

--右键索引-存储-管理压缩

现在我们对于数据库应用的瓶颈，主要还是在磁盘IO上面。也就是读写磁盘的效率。

而通过表压缩，恰好能减轻磁盘的负担，所以通过实际应用来看，表压缩的效果非常明显。

当数据库超过100G时，通过压缩最大的几个表（和索引），可以将数据库减至30G左右，

速度明显提升，管理起来也更方便。

但另一方面，压缩也有负作用。

压缩会增加CPU的开销，因为要不断地进行压缩算法和解压算法。

问题来了：

什么样的表需要进行压缩？

--查看表大小
IF OBJECT_ID('tempdb..#TB_TEMP_SPACE') IS NOT NULL DROP TABLE #TB_TEMP_SPACE
GO
CREATE TABLE #TB_TEMP_SPACE(
NAME VARCHAR(500)
,ROWS INT
,RESERVED VARCHAR(50)
,DATA VARCHAR(50)
,INDEX_SIZE VARCHAR(50)
,UNUSED VARCHAR(50)
)
GO
SP_MSFOREACHTABLE 'INSERT INTO #TB_TEMP_SPACE exec sp_spaceused ''?'''
GO
SELECT *,'ALTER TABLE [dbo].['+NAME+'] REBUILD PARTITION = ALL
WITH 
(DATA_COMPRESSION = PAGE
)' as sql
FROM #TB_TEMP_SPACE
ORDER BY REPLACE(DATA,'KB','')+0 DESC

答案：

1、查询频率小的。

2、占用空间大的

这样的表需要进行表压缩。

典型代表表：u_sale_c

一般来说，表大于1G或索引大于1G的，都是需要压缩的。

注：聚集索引是不需要进行压缩的。因为聚集索引本身是不占用空间的。

压缩和收缩的区别：

压缩，是指通过算法和规则，减少数据库的数据大小。

而收缩，是指将数据库的可用空间释放出来，变成操作系统的可用空间。

例子：

一个数据库有100G，我们进行表压缩，压缩后只有40G了；但此时mdf文件仍然是100G。

执行sp_spaceused会发现， unallocated space中有60G，这个就是未分配空间。

所以，我们必须通过收缩数据库，才能将这60G空间释放掉。

SO，表压缩操作与收缩数据库是紧密相连的两个操作；只有这样才能达到我们预期的效果。

---------------------------华丽的分割线-----------------------------------

SQL Server数据库优化实战（三）

标签：sql server   数据库优化   表压缩   收缩日志