整理一些常用的SQL语句

1.设置数据库的基于快照的隔离级别，读操作不需要申请获得共享锁，即便是数据已经存在排他锁也不影响读操作。
ALTER DATABASE [DomainDB]
SET READ_COMMITTED_SNAPSHOT ON
GO

name：数据库名，is_read_committed_snapshot_on：0时是没有设置成读写分离；1时是已经设置成读写分离；
select name,is_read_committed_snapshot_on from sys.databases

2.查看数据库执行平均时间最长的10个SQL语句

SELECT top 10
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'执行语句'
,(total_elapsed_time / execution_count)/1000 N'平均时间ms'
,total_elapsed_time/1000 N'总花费时间ms'
,total_worker_time/1000 N'所用的CPU总时间ms'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
,execution_count N'执行次数'
,creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
FROM
sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like '?tch%'
ORDER BY
total_elapsed_time / execution_count DESC;

3.查看索引碎片状态信息的SQL语句

SELECT object_name(a.object_id) [TableName] ,a.index_id ,name [IndexName] ,avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats ( DB_ID() , NULL , NULL, NULL, NULL ) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id order by avg_fragmentation_in_percent desc;

返回结果集说明：

1） TableName:索引对应的数据库的表名称

2） IndexName: 索引名称

3） avg_fragmentation_in_percent:索引碎片比率：当此值在大于30时，需要重建索引，清理碎片，请参照执行步骤2的语句

4.查看需要重建索引（清理索引碎片）的表名SQL语句

select distinct [TableName] from (SELECT object_name(a.object_id) [TableName] ,a.index_id ,name [IndexName] ,avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats ( DB_ID() , NULL , NULL, NULL, NULL ) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id where avg_fragmentation_in_percent>=30) as needRebuildIndeTableName

返回结果集： TableName：满足条件（表中某一索引碎片比率>=30）的所有表名

5. 对指定数据库中的表重新生成所有索引，清理索引碎片的SQL语句

DBCC DBREINDEX ('TableName', ' ', 80)