当前位置:Gxlcms > 数据库问题 > SQL Server数据库优化实战(三)

SQL Server数据库优化实战(三)

时间:2021-07-01 10:21:17 帮助过:28人阅读

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


收缩日志文件

--利用

exec sp_spaceused

语句查看数据库大小

技术分享

--右键数据库属性

--查看选项

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

技术分享



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

技术分享



--文件类型选择日志

技术分享



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

技术分享



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

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

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

技术分享


表压缩

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

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

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


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

(标准版不可以进行表压缩)

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

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

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

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

2、 数据库性能下降。

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

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

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


操作方法:

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

技术分享


--压缩前表大小:

技术分享

技术分享

技术分享

技术分享

--查看一下表大小:

技术分享

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

压缩后的表大小一般会是原来表大小的1/4左右

技术分享

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

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

技术分享

现在我们对于数据库应用的瓶颈,主要还是在磁盘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   数据库优化   表压缩   收缩日志   

人气教程排行