RDS for SQL server 空间问题排查汇总-阿里云开发者社区

开发者社区> 马弓手三菜> 正文

RDS for SQL server 空间问题排查汇总

简介: SQL server的空间问题一直有客户在询问,今天就给大家汇总讲解下SQL server 的全部空间开销。 SQL server 的空间组成 从文件类型来看,SQL server 的文件类型分数据文件(MDF,NDF),日志文件(LDF) 从数据库来看分为系统数据库和用户数据库,其中系统数据库中,最容易出现空间问题的,就是临时数据库(tempdb) 下面我们将分别来研究下空间的常见问题和解决方法。
+关注继续查看

SQL server的空间问题一直有客户在询问,今天就给大家汇总讲解下SQL server 的全部空间开销。

SQL server 的空间组成

从文件类型来看,SQL server 的文件类型分数据文件(MDF,NDF),日志文件(LDF)

从数据库来看分为系统数据库和用户数据库,其中系统数据库中,最容易出现空间问题的,就是临时数据库(tempdb)

下面我们将分别来研究下空间的常见问题和解决方法。

 

用户数据库的数据文件

正常情况下,数据文件是随着数据库使用,正常增长的。

sp_spaceused

这里给大家解释下,这几个参数。假设我们只有一个MDF,和一个LDF。

那么MDF 的文件大小 = reserved(8142.5MB) + unallocated space (2250.37MB)

Reserved (8337992KB)= DATA(4248352KB) +INDEX_SIZE(4086384KB) + Unused(3256KB) 

Database size(10393.94MB) = MDF(10392.94MB) + LDF(1MB) 

但是有时候,因为频繁更改,会带来碎片(fragmentation),碎片度太高,会导致内部的空间浪费。同时,每个SQL 操作,因为碎片,可能要访问更多的页面(page),导致开销变大。 

可以通过这个命令查看下当前数据库的索引碎片。

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

 

通常情况下,碎片度大于30%的索引,我们会选择rebuild index

ALTER INDEX [idx_name] ON [dbo].[test] REBUILD 

当然rebuild 之后,这些碎片空间也不会直接被OS回收,而是作为数据文件的可重用空间。

 

如果特别想回收这部分空间,可以尝试下这个命令,回收下文件尾部的空间。

dbcc shrinkfile(N'testdb',0,truncateonly)

 

 

用户数据库的日志文件

SQL server的redo 段和 undo 段都是记录在 T-Log的,所以很容易被撑大。

我们的SQL Server都是使用 FULL RECOVERY 级别,所以日志空间并不会自动释放。

 

如何查看LOG 大小

 

dbcc sqlperf(logspace)

可以看见testdb 这个库,有15G的日志占用,切使用率99%

Log Reuse 的最常见的两种原因:

1) Log backup 

2)   Active transaction

 

那么,如何具体查看LOG 等待 reuse的原因?

 

select name,log_reuse_wait_desc,* from sys.databases where name='testdb'

 

原因一:日志产生过快,日志备份频率过低,等待日志备份

 

大部分情况,这里应该是log_backup, 这时候,如果日志占用很大且利用率很高,就可以考虑调整下日志备份策略。修改的地址在 备份恢复-> 备份设置-> 编辑,我们这里可以改成30分钟一次,以提高备份频率。

 

等到log使用率下降后,我们可以通过这个命令来shrink 日志文件,也可以通过控制台上“收缩事务日志”按钮来收缩日志。 

-- 注意,可以通过这里查询下当前数据库的日志文件名,替换下文中的test_log
-- select name,* from sys.database_files where name='dbname'

-- 此处,将尽可能的让testdb_log日志收缩到100MB
dbcc shrinkfile (N'test_log',100)

 

原因二:有活跃事务阻塞了日志空间释放

如果是Active transaction, 那么可以这么去查

dbcc opentran 

这里就能看见阻塞者的开始时间,已经SPID。根据SPID,可以再查到这个会话最后一条SQL语句是什么。

dbcc inputbuffer(64)

把session kill之后,再查下log_reuse_wait_desc ,如果变成log_backup,就可以尝试下shrink 了 

 

 

tempdb的空间问题

 

tempdb是一个非常特殊的db,每次实例启动的时候,都会根据tempdb的默认值大小,重建tempdb文件。所以tempdb的空间问题,都可以通过重启解决。重点在于,找到tempdb的开销来自哪里,从根源上优化。

 

tempdb的空间,主要分为三块:

1) user objects

2)   internal objects

3)   versioning objects 

 

想要看下当前tempdb的大小,可以试下这个语句

 

Select 'Tempdb' as DB, getdate() as Time,

    SUM (user_object_reserved_page_count)*8 as user_objects_kb, 

    SUM (internal_object_reserved_page_count)*8 as internal_objects_kb, 

    SUM (version_store_reserved_page_count)*8  as version_store_kb,      

    SUM (unallocated_extent_page_count)*8 as freespace_kb

From sys.dm_db_file_space_usage

Where database_id = 2 

 

这个命令可以查看到当前tempdb的空间开销

如果是user objects占用多,就要考虑下是否使用了大量的临时表,表变量等自建对象。

如果是 internal objects占用多,就要去检查自己的排序内存,hash内存是否不够

如果是versioning 占用多,就要去检查自己是否打开了versioning相关的设置,这个并发程度是否是预期内的。

如果是freespace 最大,就说明问题已经发生过了,需要定期抓取这个数据,等待问题重现来判断是上述三个中的哪一个导致的。

 

关于versioning可以查询下这里:

select is_read_committed_snapshot_on,snapshot_isolation_state_desc,* 
from sys.databases 

 

以上就是本期的全部内容,如果有任何疑问,可以在下方留言。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【转】Oracle 查看表空间使用率 SQL 脚本
文章转自:http://blog.csdn.net/tianlesoftware/article/details/7619732 1 /* Formatted on 2012/5/31 14:51:13 (QP5 v5.
658 0
wamp You don't have permission to access / on this server等问题的解决.
原文: wamp You don't have permission to access / on this server等问题的解决. 安装完wamp之后,安装网上的教程设置虚拟路径,出现了问题,同样的问题由不同的原因导致。
885 0
MySQL:互联网公司常用分库分表方案汇总
不管是IO瓶颈,还是CPU瓶颈,最终都会导致数据库的活跃连接数增加,进而逼近甚至达到数据库可承载活跃连接数的阈值。在业务Service来看就是,可用数据库连接少甚至无连接可用。接下来就可以想象了吧(并发量、吞吐量、崩溃)。
1351 0
【巡检问题分析与最佳实践】MongoDB 空间使用问题
阿里云数据库MongoDB的空间使用率是一个非常重要的监控指标,如果实例的存储空间完全打满,将会直接导致实例不可用。一般来说,当一个MongoDB实例的存储空间使用比例达到80-85%以上时,就应及时进行处理,要么降低数据库实际占用空间的大小,要么对存储空间进行扩容,以避免空间打满的风险。 然而,阿里云数据库MongoDB的空间使用情况分析并不简单,本文将由浅入深帮您查看,分析和优化云数据库MongoDB的空间使用。
238 0
SQLServer 2012 登陆异常问题
## 背景 RDS FOR SQLServer 2012 已经上线一段时间了,从反馈来看大家遇到了很多权限相关的问题,所以建议把[RDS FOR SQLServer 2012权限提升](https://yq.aliyun.com/articles/71919?spm=5176.100240.searchblog.8.LWPvRT)这篇文章作为基础知识先能够理解;再之后针对大家经常遇到的CASE我
1665 0
解决Setting property 'source' to 'org.eclipse.jst.jee.server的问题
<span style="color:rgb(51,102,255); font-size:24px"><span style="font-size:18px">对于这个问题,我相信我的方法已经能帮90%的人解决了!<br> 当你用Eclipse运行web项目的时候,你就会看到控制台出现:<br></span><span style="color:#ff0000"><span style
1072 0
sql2008发布订阅 问题汇总
 1.如何修改一个已经发布的表的属性?   将发布所有订阅删除,(发布不用删除),就可以在发布属性的项目中取消表,然后就   可以修改该表了,修改后,再将表加入发布内就可以了.   2.常用的操作界面:   (1)一般都在监视器--发布项目,可以看到快照 分发和订阅的即时信息.
977 0
+关注
马弓手三菜
酒且斟下,某去便来。
4
文章
0
问答
来源圈子
更多
作为全球云计算的领先者,阿里云为全球230万企业提供着云计算服务,服务范围覆盖200多个国家和地区。我们致力于为企业、政府等组织机构提供安全可靠的云计算服务,给用户带来极速愉悦的服务体验。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载