大神求教:SQL Server 2012 总是阻塞,并且回滚特别慢,以及索引优化的问题

handsomenku 2014-07-22 04:38:36
帖子比较长,还请各位大神受累不吝赐教,跪求~~

前几天发了个帖子,也是关于2012数据库总是卡的问题,当时加了一些索引数据库很长时间没卡。今天数据库又卡的非常厉害,而且非常频繁。
现在状况是:数据库70多个G,数据库卡一次之后回滚需要30分钟以上,半小时左右卡一次,完全没法干活~~o(>_<)o ~~
这是查看的数据库阻塞的进程,是Ghost cleanup 阻塞了数据库吗?如果是的话为什么会阻塞这么长时间?


另外关于回滚的问题,数据库每次卡的时候基本上都是在回滚,如果是回滚的话为什么会这么长时间,回滚不应该是比较快的吗?

最后是数据库索引的一些信息,求大神指导哪些需要优化,以及索引碎片是否太高,如何优化?
表: 'UMS_MERCHANT' (521117693);索引 ID: 1,数据库 ID: 7
已执行 TABLE 级别的扫描。
- 扫描页数................................: 26520
- 扫描区数..............................: 3383
- 区切换次数..............................: 18676
- 每个区的平均页数........................: 7.8
- 扫描密度 [最佳计数:实际计数].......: 17.75% [3315:18677]
- 逻辑扫描碎片 ..................: 66.38%
- 区扫描碎片 ..................: 22.55%
- 每页的平均可用字节数.....................: 2663.0
- 平均页密度(满).....................: 67.10%
DBCC SHOWCONTIG 正在扫描 'UMS_MERCHANT' 表...
表: 'UMS_MERCHANT' (521117693);索引 ID: 2,数据库 ID: 7
已执行 LEAF 级别的扫描。
- 扫描页数................................: 967
- 扫描区数..............................: 131
- 区切换次数..............................: 163
- 每个区的平均页数........................: 7.4
- 扫描密度 [最佳计数:实际计数].......: 73.78% [121:164]
- 逻辑扫描碎片 ..................: 5.69%
- 区扫描碎片 ..................: 14.50%
- 每页的平均可用字节数.....................: 239.2
- 平均页密度(满).....................: 97.04%
DBCC SHOWCONTIG 正在扫描 'UMS_MERCHANT' 表...
表: 'UMS_MERCHANT' (521117693);索引 ID: 38,数据库 ID: 7
已执行 LEAF 级别的扫描。
- 扫描页数................................: 1286
- 扫描区数..............................: 170
- 区切换次数..............................: 283
- 每个区的平均页数........................: 7.6
- 扫描密度 [最佳计数:实际计数].......: 56.69% [161:284]
- 逻辑扫描碎片 ..................: 11.28%
- 区扫描碎片 ..................: 12.94%
- 每页的平均可用字节数.....................: 465.4
- 平均页密度(满).....................: 94.25%
DBCC SHOWCONTIG 正在扫描 'UMS_MERCHANT' 表...
表: 'UMS_MERCHANT' (521117693);索引 ID: 48,数据库 ID: 7
已执行 LEAF 级别的扫描。
- 扫描页数................................: 1031
- 扫描区数..............................: 140
- 区切换次数..............................: 213
- 每个区的平均页数........................: 7.4
- 扫描密度 [最佳计数:实际计数].......: 60.28% [129:214]
- 逻辑扫描碎片 ..................: 9.99%
- 区扫描碎片 ..................: 27.86%
- 每页的平均可用字节数.....................: 419.9
- 平均页密度(满).....................: 94.81%
DBCC SHOWCONTIG 正在扫描 'UMS_MERCHANT' 表...
表: 'UMS_MERCHANT' (521117693);索引 ID: 49,数据库 ID: 7
已执行 LEAF 级别的扫描。
- 扫描页数................................: 1970
- 扫描区数..............................: 255
- 区切换次数..............................: 414
- 每个区的平均页数........................: 7.7
- 扫描密度 [最佳计数:实际计数].......: 59.52% [247:415]
- 逻辑扫描碎片 ..................: 9.64%
- 区扫描碎片 ..................: 8.24%
- 每页的平均可用字节数.....................: 413.2
- 平均页密度(满).....................: 94.90%



DBCC SHOWCONTIG 正在扫描 'UMS_TERMINAL' 表...
表: 'UMS_TERMINAL' (686559617);索引 ID: 1,数据库 ID: 7
已执行 TABLE 级别的扫描。
- 扫描页数................................: 9785
- 扫描区数..............................: 1235
- 区切换次数..............................: 1474
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 82.98% [1224:1475]
- 逻辑扫描碎片 ..................: 59.44%
- 区扫描碎片 ..................: 3.24%
- 每页的平均可用字节数.....................: 407.6
- 平均页密度(满).....................: 94.96%
DBCC SHOWCONTIG 正在扫描 'UMS_TERMINAL' 表...
表: 'UMS_TERMINAL' (686559617);索引 ID: 2,数据库 ID: 7
已执行 LEAF 级别的扫描。
- 扫描页数................................: 5321
- 扫描区数..............................: 680
- 区切换次数..............................: 1681
- 每个区的平均页数........................: 7.8
- 扫描密度 [最佳计数:实际计数].......: 39.60% [666:1682]
- 逻辑扫描碎片 ..................: 21.93%
- 区扫描碎片 ..................: 26.76%
- 每页的平均可用字节数.....................: 909.4
- 平均页密度(满).....................: 88.76%
DBCC SHOWCONTIG 正在扫描 'UMS_TERMINAL' 表...
表: 'UMS_TERMINAL' (686559617);索引 ID: 3,数据库 ID: 7
已执行 LEAF 级别的扫描。
- 扫描页数................................: 5316
- 扫描区数..............................: 677
- 区切换次数..............................: 1690
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 39.33% [665:1691]
- 逻辑扫描碎片 ..................: 21.93%
- 区扫描碎片 ..................: 23.93%
- 每页的平均可用字节数.....................: 902.6
- 平均页密度(满).....................: 88.85%
DBCC SHOWCONTIG 正在扫描 'UMS_TERMINAL' 表...
表: 'UMS_TERMINAL' (686559617);索引 ID: 4,数据库 ID: 7
已执行 LEAF 级别的扫描。
- 扫描页数................................: 7369
- 扫描区数..............................: 938
- 区切换次数..............................: 2737
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 33.67% [922:2738]
- 逻辑扫描碎片 ..................: 27.97%
- 区扫描碎片 ..................: 26.33%
- 每页的平均可用字节数.....................: 1157.8
- 平均页密度(满).....................: 85.70%
DBCC SHOWCONTIG 正在扫描 'UMS_TERMINAL' 表...
表: 'UMS_TERMINAL' (686559617);索引 ID: 5,数据库 ID: 7
已执行 LEAF 级别的扫描。
- 扫描页数................................: 7271
- 扫描区数..............................: 924
- 区切换次数..............................: 2711
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 33.52% [909:2712]
- 逻辑扫描碎片 ..................: 28.58%
- 区扫描碎片 ..................: 23.92%
- 每页的平均可用字节数.....................: 1197.9
- 平均页密度(满).....................: 85.20%
DBCC SHOWCONTIG 正在扫描 'UMS_TERMINAL' 表...
表: 'UMS_TERMINAL' (686559617);索引 ID: 6,数据库 ID: 7
已执行 LEAF 级别的扫描。
- 扫描页数................................: 6640
- 扫描区数..............................: 847
- 区切换次数..............................: 2054
- 每个区的平均页数........................: 7.8
- 扫描密度 [最佳计数:实际计数].......: 40.39% [830:2055]
- 逻辑扫描碎片 ..................: 20.87%
- 区扫描碎片 ..................: 20.19%
- 每页的平均可用字节数.....................: 870.9
- 平均页密度(满).....................: 89.24%
DBCC SHOWCONTIG 正在扫描 'UMS_TERMINAL' 表...
表: 'UMS_TERMINAL' (686559617);索引 ID: 7,数据库 ID: 7
已执行 LEAF 级别的扫描。
- 扫描页数................................: 5814
- 扫描区数..............................: 737
- 区切换次数..............................: 2648
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 27.44% [727:2649]
- 逻辑扫描碎片 ..................: 37.39%
- 区扫描碎片 ..................: 28.09%
- 每页的平均可用字节数.....................: 1518.8
- 平均页密度(满).....................: 81.24%
DBCC SHOWCONTIG 正在扫描 'UMS_TERMINAL' 表...
表: 'UMS_TERMINAL' (686559617);索引 ID: 8,数据库 ID: 7
已执行 LEAF 级别的扫描。
- 扫描页数................................: 6231
- 扫描区数..............................: 794
- 区切换次数..............................: 1996
- 每个区的平均页数........................: 7.8
- 扫描密度 [最佳计数:实际计数].......: 39.01% [779:1997]
- 逻辑扫描碎片 ..................: 22.44%
- 区扫描碎片 ..................: 21.28%
- 每页的平均可用字节数.....................: 924.8
- 平均页密度(满).....................: 88.57%
DBCC SHOWCONTIG 正在扫描 'UMS_TERMINAL' 表...
表: 'UMS_TERMINAL' (686559617);索引 ID: 9,数据库 ID: 7
已执行 LEAF 级别的扫描。
- 扫描页数................................: 6349
- 扫描区数..............................: 803
- 区切换次数..............................: 2404
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 33.01% [794:2405]
- 逻辑扫描碎片 ..................: 28.79%
- 区扫描碎片 ..................: 26.40%
- 每页的平均可用字节数.....................: 1183.8
- 平均页密度(满).....................: 85.37%
DBCC SHOWCONTIG 正在扫描 'UMS_TERMINAL' 表...
表: 'UMS_TERMINAL' (686559617);索引 ID: 161,数据库 ID: 7
已执行 LEAF 级别的扫描。
- 扫描页数................................: 5311
- 扫描区数..............................: 673
- 区切换次数..............................: 973
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 68.17% [664:974]
- 逻辑扫描碎片 ..................: 6.61%
- 区扫描碎片 ..................: 6.98%
- 每页的平均可用字节数.....................: 289.6
- 平均页密度(满).....................: 96.42%
DBCC SHOWCONTIG 正在扫描 'UMS_TERMINAL' 表...
表: 'UMS_TERMINAL' (686559617);索引 ID: 246,数据库 ID: 7
已执行 LEAF 级别的扫描。
- 扫描页数................................: 896
- 扫描区数..............................: 117
- 区切换次数..............................: 276
- 每个区的平均页数........................: 7.7
- 扫描密度 [最佳计数:实际计数].......: 40.43% [112:277]
- 逻辑扫描碎片 ..................: 20.54%
- 区扫描碎片 ..................: 12.82%
- 每页的平均可用字节数.....................: 867.1
- 平均页密度(满).....................: 89.29%
...全文
527 27 打赏 收藏 转发到动态 举报
写回复
用AI写文章
27 条回复
切换为时间正序
请发表友善的回复…
发表回复
發糞塗牆 2014-07-23
  • 打赏
  • 举报
回复
SQL Server 2012管理高级教程
handsomenku 2014-07-23
  • 打赏
  • 举报
回复
引用 20 楼 ap0405140 的回复:
建议: 1.需定期做索引重建或重整,特别是针对大表. 语法: alter index [索引名] on [表名] rebuild 2.查看SQL日志,spid=31的进程在执行什么? 3.鉴于LZ问题的复杂度,建议找本DBA的书看看比较好,在这里应该无法帮你彻底的解决问题.
多谢,能推荐本DBA的书不~
handsomenku 2014-07-23
  • 打赏
  • 举报
回复
引用 19 楼 DBA_Huangzj 的回复:
看来你的问题描述和你的日志没有做好管理有非常大甚至直接关系,还是先做好一些常规维护再考虑优化方面吧
恩,清理了一下日志,感觉好多了
唐诗三百首 2014-07-23
  • 打赏
  • 举报
回复
建议: 1.需定期做索引重建或重整,特别是针对大表. 语法: alter index [索引名] on [表名] rebuild 2.查看SQL日志,spid=31的进程在执行什么? 3.鉴于LZ问题的复杂度,建议找本DBA的书看看比较好,在这里应该无法帮你彻底的解决问题.
發糞塗牆 2014-07-23
  • 打赏
  • 举报
回复
看来你的问题描述和你的日志没有做好管理有非常大甚至直接关系,还是先做好一些常规维护再考虑优化方面吧
handsomenku 2014-07-23
  • 打赏
  • 举报
回复
引用 25 楼 DBA_Huangzj 的回复:
1、日志过大会导致恢复很慢。 2、日志太大的时候,vlfs会很多,逻辑碎片也大。 3、checkpoint的东西也会跟着多。 当然,日志大并不一定造成性能问题,但是日志大的话,回滚时需要搜索的范围就很大,你想像一下在一个小房间(日志小)里面找一个东西和在一栋楼里面(日志大)找一个东西耗费时间是不一样的。
恩,再看看系统运行状况吧,最近弄数据库都是找到哪地方不对劲改哪地方,没有策略性和方向性。 所以很多时候会怀疑到底是不是刚改的地方导致的系统故障,还是很感谢你~thx
發糞塗牆 2014-07-23
  • 打赏
  • 举报
回复
总之,日志如果因为缺乏维护导致那么大,对你的数据库没有任何一点好处
發糞塗牆 2014-07-23
  • 打赏
  • 举报
回复
1、日志过大会导致恢复很慢。 2、日志太大的时候,vlfs会很多,逻辑碎片也大。 3、checkpoint的东西也会跟着多。 当然,日志大并不一定造成性能问题,但是日志大的话,回滚时需要搜索的范围就很大,你想像一下在一个小房间(日志小)里面找一个东西和在一栋楼里面(日志大)找一个东西耗费时间是不一样的。
handsomenku 2014-07-23
  • 打赏
  • 举报
回复
引用 23 楼 DBA_Huangzj 的回复:
SQL Server 2012管理高级教程
500G的日志文件会影响系统的性能吗,造成阻塞? 网上说日志文件大一般不会影响性能,昨天清理了一下日志,数据库一直没阻塞。但是我感觉上数据库阻塞应该不是日志文件得原因,您觉得哪?
handsomenku 2014-07-22
  • 打赏
  • 举报
回复
引用 17 楼 DBA_Huangzj 的回复:
略微暴力的做法,算了,做一次完整备份,然后如果你不想管日志就设简单模式,但是生产环境用简单模式风险很大,如果用完整模式,要有常规的日志备份
恩 好的 多谢指导,我明天来单位再试试,太感谢了~
發糞塗牆 2014-07-22
  • 打赏
  • 举报
回复
略微暴力的做法,算了,做一次完整备份,然后如果你不想管日志就设简单模式,但是生产环境用简单模式风险很大,如果用完整模式,要有常规的日志备份
handsomenku 2014-07-22
  • 打赏
  • 举报
回复
引用 15 楼 DBA_Huangzj 的回复:
当然会,你没做日志备份?500g太不正常了
好像没有,我用了个日志清理代码,清理完之后由500G变为了1M,这样会不会影响数据库的回滚和恢复啊 USE [master] GO ALTER DATABASE DNName SET RECOVERY SIMPLE WITH NO_WAIT GO ALTER DATABASE DNName SET RECOVERY SIMPLE GO USE DNName GO DBCC SHRINKFILE (N'LogFileName' , 0,TRUNCATEONLY) GO USE [master] GO ALTER DATABASE DNName SET RECOVERY FULL WITH NO_WAIT GO ALTER DATABASE DNName SET RECOVERY FULL GO
發糞塗牆 2014-07-22
  • 打赏
  • 举报
回复
当然会,你没做日志备份?500g太不正常了
handsomenku 2014-07-22
  • 打赏
  • 举报
回复
引用 9 楼 DBA_Huangzj 的回复:
2012用extended event,也可以用DMV。这是查碎片的:
SELECT  '[' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME(ddips.[object_id],
                                                     DB_ID()) + '].['
        + OBJECT_NAME(ddips.[object_id], DB_ID()) + ']' AS [statement] ,
        i.[name] AS [index_name] ,
        ddips.[index_type_desc] ,
        ddips.[partition_number] ,
        ddips.[alloc_unit_type_desc] ,
        ddips.[index_depth] ,
        ddips.[index_level] ,
        CAST(ddips.[avg_fragmentation_in_percent] AS SMALLINT) AS [avg_frag_%] ,
        CAST(ddips.[avg_fragment_size_in_pages] AS SMALLINT) AS [avg_frag_size_in_pages] ,
        ddips.[fragment_count] ,
        ddips.[page_count]
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'limited') ddips
        INNER JOIN sys.[indexes] i ON ddips.[object_id] = i.[object_id]
                                      AND ddips.[index_id] = i.[index_id]
WHERE   ddips.[avg_fragmentation_in_percent] > 15
        AND ddips.[page_count] > 500
ORDER BY ddips.[avg_fragmentation_in_percent] ,
        OBJECT_NAME(ddips.[object_id], DB_ID()) ,
        i.[name]
数据日志文件500多个G,会影响sql server启动的速度和性能吗?
俺是大菠萝 2014-07-22
  • 打赏
  • 举报
回复
这个问题比较麻烦,分析点也比较多,update执行慢,先抓一下缓存计划,看看哪些语句慢,执行计划是否正常,看一下当前执行语句中等待类型都是什么,基本上可以判断出是内存不足导致,还是IO性能影响的;
handsomenku 2014-07-22
  • 打赏
  • 举报
回复
引用 11 楼 u013226693 的回复:
不明白你所谓的卡是什么意思,数据访问不了?为什么还回滚了?重启服务了么?先看一下sp_readerrorlog里有什么问题;另外抓一下当前执行的sql,看看是不是有大查询导致IO性能很差
数据库卡的现象就是可以select 但是update 会非常慢,以至于无法执行。 回滚是看到有个进程在running,执行的是类似于查找snapshot, get rid of 之类的语句,感觉上是在回滚。 2012重启服务数据库就会自动回滚啊,因为以前重启服务回滚要20分钟,所以没有重启,一直等到数据库自己恢复。 不过我下午3:50试了试重启服务,结果到现在都没有卡,感觉很奇怪,我什么都没做,就是重启了一下服务,不知道是不是重启服务的原因还是别的原因?
俺是大菠萝 2014-07-22
  • 打赏
  • 举报
回复
不明白你所谓的卡是什么意思,数据访问不了?为什么还回滚了?重启服务了么?先看一下sp_readerrorlog里有什么问题;另外抓一下当前执行的sql,看看是不是有大查询导致IO性能很差
Q315054403 2014-07-22
  • 打赏
  • 举报
回复
不建议玩DTA,经手过数个系统,多次用DTA产生大量无用的索引,不客气地说,DTA比较弱智 优化这个东西并不是魔术,也不神奇,需要有比较好的基础知识综合运用而已 若有优化预算,欢迎加我QQ合作
發糞塗牆 2014-07-22
  • 打赏
  • 举报
回复
2012用extended event,也可以用DMV。这是查碎片的:
SELECT  '[' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME(ddips.[object_id],
                                                     DB_ID()) + '].['
        + OBJECT_NAME(ddips.[object_id], DB_ID()) + ']' AS [statement] ,
        i.[name] AS [index_name] ,
        ddips.[index_type_desc] ,
        ddips.[partition_number] ,
        ddips.[alloc_unit_type_desc] ,
        ddips.[index_depth] ,
        ddips.[index_level] ,
        CAST(ddips.[avg_fragmentation_in_percent] AS SMALLINT) AS [avg_frag_%] ,
        CAST(ddips.[avg_fragment_size_in_pages] AS SMALLINT) AS [avg_frag_size_in_pages] ,
        ddips.[fragment_count] ,
        ddips.[page_count]
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'limited') ddips
        INNER JOIN sys.[indexes] i ON ddips.[object_id] = i.[object_id]
                                      AND ddips.[index_id] = i.[index_id]
WHERE   ddips.[avg_fragmentation_in_percent] > 15
        AND ddips.[page_count] > 500
ORDER BY ddips.[avg_fragmentation_in_percent] ,
        OBJECT_NAME(ddips.[object_id], DB_ID()) ,
        i.[name]
handsomenku 2014-07-22
  • 打赏
  • 举报
回复
引用 4 楼 DBA_Huangzj 的回复:
从你这些数据来看,是spid=31的阻塞了,不是ghost cleanup
我用的这个语句查的索引,dbcc showcontig(ums_terminal) with all_indexes 还有其它的吗,求指导? 另外确实是spid=31阻塞了,我用下面的语句查31的详细信息,但是查不出来,难道是系统进程的原因吗? declare @spid int; declare @sql_handle binary(20); set @spid = 31 SELECT @sql_handle = sql_handle FROM sysprocesses As A with (nolock) where spid = @spid select text from ::fn_get_sql(@sql_handle)
加载更多回复(7)

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧