SQL 性能学习笔记(持续更新中)

学习目录:

01-性能概述
02-手工性能收集
03-自动性能收集
04-索引1
05-并发访问1
06-并发访问2
07-并发访问3
08-索引2
09-索引3
10-执行计划
11-其他性能实践

-----------------------------------------------------------

一.性能概述
虚拟内存做1到1.5倍
策略->计算机配置=>Windows设置->本地策略->审核策略:关闭审计
性能监视器:
a.内存瓶颈分析,主要内存计算器:
1.Available Bytes:越大越好
2.Page/Sec:值应该持续小于20
3.Buffer Cache Hit Radio:值应该持续大于99%
b.磁盘瓶颈分析,
1.%Disk Time:值应该持续小于55%
2.Avg.Disk Queue Length:值应该每个磁盘持续小于2
3.Buffer Cache Hit Radio:值应该持续大于99%
c.处理器瓶颈分析,主要处理器计数器
1.%Processer Time:值应该持续小于60%
2.Processer Queue Length:值应该持续小于2
d.网络瓶颈分析
1.Bytes Total/sec:平均值应该小于网卡容量50%
2.%Net Utilization:平均值应该小于80%

二.手工性能收集

打开性能监视器:perfmon.msc
建立监视:
1.%Processer Time
2.%Disk Time
3.Pages/sec
动态管理视图

三.自动性能收集

四.索引1
聚集索引查询的速度比非聚集索引要快
先创建聚集索引,再创建非聚集索引
一般为主键列创建聚集索引,为条件列创建非聚集索引
分类汇总时,用视图,针对视图,创建唯一聚集索引(unique clustered index),索引视图,做在变更不大的表里面

create nonclustered index nc_ColName
on Orders(IndexColName) --创建非聚集索引
include(ShowColName1,ShowColName2) --include 覆盖聚集索引
with(online=on,MAXDOP=2) --创建索引时,用户无法访问堆,所以需要用with(online=on)
where IsValid=1 --过滤索引

五.并发访问1
当多个线程访问同一个资源,会产生并发性问题
乐观并发控制
悲观并发控制

begin tran
update employee set age = age + 1 where age > 30
rollback tran --此句占不执行
select name,age from employee -- 不能执行

create nonclustered index on nc_Employee(age) include(name)

事务尽量简短

事务尽量只包含必要的语句,验证与查询等语句放置事务之外
避免事务需要事务的交互

锁避免的问题
1.丢失的问题
2.脏读
3.不一致性分析
4.幻象集

select name,age from employee with(Readpast)  --Readpast:跳过阻塞行(age = 34)
where age < 30 or age = 34

六.并发访问2

a.悲观并发访问
b.乐观并发访问
c.通过T_SQL或.Net代码控制事务

1.读提交:默认行为,读取时请求共享锁S
2.脏读:读取时不请求共享锁S,不会受到其他锁X限制
3.已提交快照读:更新时将老的数据复制到Tempdb:read_committed_snapshot
4.可重复读:事务结束前,不释放获取的共享锁S,可能会形成死锁
5.串行化:访问的行和按顺序下一行放置范围锁,放置不必要操作与插入数据
6.快照:比已提交快照读更严格,试图对修改数据应用X,如果已发生改变,事务失败 allow_snapshot_isolation
7.索引对隔离级别的影响,阻塞的监视

减少阻塞与死锁建议:
1.合适的索引
2.合适的分区
3.调整合适的隔离级别
4.查询条件的有效性
5.相同的顺序操作资源
6.短的事务

七.索引2
查询优化:
创建非聚集索引的时候,一般创建覆盖索引,建立覆盖索引时:频率大的放前面
如果有覆盖索引,但是条件里面没有相关字段,就会执行表扫描
当有窄列和宽列时,一般对窄列建立聚集索引
过滤索引:业务上,肯定会有此条件的时候

索引设计建议:
Where字句与连接条件列
使用窄索引
选择性高的列应该创建索引,如果有多列筛选,并尽量放置经常筛选的列和低密度的列到组合索引的前面
int类型上创建索引与char类型上创建索引
非聚集索引:RIDz指针指向堆的行标识符或聚集索引的键值
如果有非聚集索引,一定要创建一个聚集索引
先创建聚集索引,再创建非聚集索引

使用聚集索引时机:
Group By 列
Order By 列
没有针对某个筛选条件的非聚集索引

不适合使用聚集索引
索引列值频繁更新
并发的大量插入

如果非聚集索引需要书签查找,则建议通过聚集索引查找
建议创建覆盖索引,不用单一的非聚集索引
不适合使用非聚集索引:
需要获取大量的行
需要获取大量的字段

交叉索引:针对筛选条件分别建立非聚集索引,在查询时,获得两个子集的索引交叉,解决覆盖索引非常宽的问题
建议使用过滤索引:针对查询必然需要筛选掉的条件做成索引的过滤条件
恰当使用索引视图使连接与聚合实物化,平衡查询性能提升与维护视图性能开销