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

Question

sql server 2012优化索引阻塞

Answer 1

重建索引能缓解读取效率，优化（可能涉及改造SQL指令）是正道

Answer 2

用了Full-text？都2012了，换些语句去查索引碎片吧。。

Answer 3

从你这些数据来看，是spid=31的阻塞了，不是ghost cleanup

Answer 4

如何优化哪？如果优化数据库表结构设计的话不太现实，数据库900多个表，而且不是我做的。有没有比较方便可行的优化，比如通过工具分析，然后优化什么的？懒人方式——DTA

Answer 5

我用的这个语句查的索引，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)

Answer 6

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]

Answer 7

不建议玩DTA，经手过数个系统，多次用DTA产生大量无用的索引，不客气地说，DTA比较弱智
优化这个东西并不是魔术，也不神奇，需要有比较好的基础知识综合运用而已
若有优化预算，欢迎加我QQ合作

Answer 8

不明白你所谓的卡是什么意思，数据访问不了？为什么还回滚了？重启服务了么？先看一下sp_readerrorlog里有什么问题；另外抓一下当前执行的sql，看看是不是有大查询导致IO性能很差

Answer 9

数据库卡的现象就是可以select    但是update 会非常慢，以至于无法执行。
回滚是看到有个进程在running，执行的是类似于查找snapshot， get rid of 之类的语句，感觉上是在回滚。
2012重启服务数据库就会自动回滚啊，因为以前重启服务回滚要20分钟，所以没有重启，一直等到数据库自己恢复。
不过我下午3:50试了试重启服务，结果到现在都没有卡，感觉很奇怪，我什么都没做，就是重启了一下服务，不知道是不是重启服务的原因还是别的原因？

Answer 10

这个问题比较麻烦，分析点也比较多，update执行慢，先抓一下缓存计划，看看哪些语句慢，执行计划是否正常，看一下当前执行语句中等待类型都是什么，基本上可以判断出是内存不足导致，还是IO性能影响的；

Answer 11

数据日志文件500多个G，会影响sql server启动的速度和性能吗？

Answer 12

好像没有，我用了个日志清理代码，清理完之后由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

Answer 13

建议:
 1.需定期做索引重建或重整,特别是针对大表.
     语法:  alter index [索引名] on [表名] rebuild 2.查看SQL日志,spid=31的进程在执行什么? 3.鉴于LZ问题的复杂度,建议找本DBA的书看看比较好,在这里应该无法帮你彻底的解决问题.

Answer 14

多谢，能推荐本DBA的书不~

Answer 15

SQL Server 2012管理高级教程

Answer 16

1、日志过大会导致恢复很慢。
2、日志太大的时候，vlfs会很多，逻辑碎片也大。
3、checkpoint的东西也会跟着多。当然，日志大并不一定造成性能问题，但是日志大的话，回滚时需要搜索的范围就很大，你想像一下在一个小房间（日志小）里面找一个东西和在一栋楼里面（日志大）找一个东西耗费时间是不一样的。

Answer 17

恩，再看看系统运行状况吧，最近弄数据库都是找到哪地方不对劲改哪地方，没有策略性和方向性。
所以很多时候会怀疑到底是不是刚改的地方导致的系统故障，还是很感谢你~thx