学习目标

熟悉SQL Server参数优化
熟悉SQL Server索引调优
设置索引重建作业
有阻塞时，查询阻塞
查询最耗资源的SQL语句
收缩日志

业务场景

数据库新上线
数据库运行一段时间了，但未优化过数据库设置

一、修改服务器参数

数据库在安装之后，或者在新增加数据库后，需要修改数据库参数，实例参数、服务器参数

sp_configure 'show advanced options', 1;
go
reconfigure with override;
go

调整针对即席工作负荷进行优化=true：
sp_configure 'optimize for ad hoc workloads', 1;
go
调整并行度：
sp_configure 'max degree of parallelism', 1;

go
reconfigure with override;

二、限制最小最大内存值

三、锁定内存页

更改“锁定内存页”，用户为启动SQL Server的用户

四、更改隔离级别

设置读提交快照处于打开状态=True

也可以用语句来实现：

查询隔离级别：
select name,user_access,user_access_desc, snapshot_isolation_state, snapshot_isolation_state_desc,is_read_committed_snapshot_on
from sys.databases;

更改隔离级别：
alter database test set single_user with rollback immediate
alter database test set allow_snapshot_isolation on
alter database test set read_committed_snapshot on
alter database test set multi_user

注意把test改成实际数据库名

五、查杀死锁或阻塞

使用查询分析器，全选脚本，然后执行

use master
go
declare @spid int,@bl int,@intTransactionCountOnEntry int,        @intRowcount int,
        @intCountProperties int,        @intCounter int

create table #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint)

insert into #tmp_lock_who(spid,bl) select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0

-- 找到临时表的记录数
select @intCountProperties = Count(*),@intCounter = 1 from #tmp_lock_who
if @intCountProperties=0
select N'现在没有阻塞和死锁信息' as message
-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
select @spid = spid,@bl = bl from #tmp_lock_who where Id = @intCounter
begin
if @spid =0
            select N'引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + N'进程号,其执行的SQL语法如下'
else
            select N'进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ N'被' + N'进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +N'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end
-- 循环指针下移
set @intCounter = @intCounter + 1
end
drop table #tmp_lock_who

--附:杀死相关会话的脚本为 kill SPID 注意SPID为常量，不能为变量，要用变量，请用动态语句

六、查询执行最慢的SQL语句（可查N天前信息）

使用查询分析器执行

SELECT
(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'执行次数'
,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'执行语句'
,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;
结果类似下面图：

查CPU 平均占用率最高的前 50 个 SQL 语句

SELECT TOP 50
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, *
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESC;

结果类似下面图：

七、活动监视器优化索引

活动监视器最近耗费大量资源的查询选中SQL，右键显示执行计划，如果缺索引，它会告诉我们当前sql应该在哪些列上建索引

会出现以下界面：

绿色的字部分是优化建议，可根据优化建议生成索引

八、设置重新生成索引、碎片整理、更新统计信息作业

按下图顺序设置作业
1）

2）

3）

4）

5）

6）

7）

8）

9）

10）

11）

12) 选择空闲时间点运行优化任务，按确定，点击关闭，保存

九、日志文件收缩

了解SQL Server数据库维护最佳实践

恭喜您！