开发程序员快速优化数据库脚本

程序员辛苦了

以下内容一针见效 简单粗暴直接有效

菜鸟：昨天跑的好好sql，今天怎么就慢了呢？DBA抓到慢查询了，在群里嗷嗷叫，真烦！
老鸟：小乖乖，加索引啊~
菜鸟：怎么加啊，人家看不懂执行计划啊~
老鸟：其他人都闪开，让我告诉你怎么快速建索引

快速建索引 TSQL

WITH MissingIndex AS
( SELECT db_name(c.database_id) AS DBName,c.statement,e.name AS SchemaName
,d.name AS TableName,a.user_seeks, avg_user_impact,c.equality_columns
, inequality_columns,c.included_columns
, IndexID=replace(newid(),'-',),CheckDate=getdate()
 FROM sys.dm_db_missing_index_group_stats AS a 
 join sys.dm_db_missing_index_groups 
 AS b ON a.group_handle=b.index_group_handle 
 join sys.dm_db_missing_index_details AS c ON b.index_handle=c.index_handle j
 oin sys.tables AS d ON c.[object_id]=d.[object_id] join sys.schemas e ON d.schema_id=e.schema_id )

SELECT *, "Create Index"= CASE 
WHEN equality_columns is not null 
THEN ('CREATE INDEX '+'IX_'+ TableName+'_'+indexID +' ON ['+SchemaName+'].[' +TableName+'] ('+equality_columns+ isnull(','+inequality_columns,) +')' +isnull(' INCLUDE ('+included_columns+')',)) 
WHEN equality_columns is null THEN ('CREATE INDEX '+'IX_'+ TableName+'_'+indexID +' ON ['+SchemaName+'].['+TableName+'] ('+inequality_columns+')' +isnull(' INCLUDE ('+included_columns+')',)) END
, dropIndex= ('DROP INDEX ['+SchemaName+'].['+TableName+'].'+'IX_'+TableName+'_'+indexID) 
FROM MissingIndex ORDER BY user_seeks DESC

老鸟：此脚本一出，谁与争锋！大家看好了，我连创建索引的脚本都run出来了，copy出来就可以直接创建了。是不是很开森~~

想学摄影吗？—— 一键解决读写互斥

正所谓，两攻相遇必有一受 —— 近排吃瓜
with (nolock)， 与READ_COMMITTED_SNAPSHOT 不可不说的二三事

Round One —— WITH (NOLOCK)
with(nolcok),不去请求锁，允许一定程度的脏读。实验代码如下：

CREATE DATABASE LockTestDB

USE LockTestDB 
--生成测试表
CREATE TABLE LockTest
(
ID INT,
Value VARCHAR(20)
)
--插入测试数据
INSERT INTO LockTest VALUES (1,'Version1')

--而后新开一个Session1,执行语句
--执行更新，但是不提交
BEGIN TRAN
UPDATE LockTest SET Value='Version2' WHERE ID=1

--此时，新开一个Session2,执行语句
--无法查询TABLE1数据，因为Session 1没有提交
SELECT * FROM LockTest

--此时，新开一个Session3,执行语句
--可以查出
--ID  Value
--————————
--1      Version2
-- 这就是为什么生产环境大多要求脚本中一定带有with(nolcok),不去请求锁，允许一定程度的脏读
SELECT * FROM LockTest with(nolock)

Round Two —— READ_COMMITTED_SNAPSHOT
READ_COMMITTED_SNAPSHOT，等同于orcale默认隔离级别
READ_COMMITTED_SNAPSHOT 一开，瞬间可以解决百万并发阻塞
但是，对TempDB数据库落磁盘性能要求高。如果TempDB所在磁盘性能垃圾，那么不如不开

实验如下：

--开启行版本隔离机制，乐观并发已提交读。等同于orcale默认隔离级别
--READ_COMMITTED_SNAPSHOT一开，瞬间可以解决百万并发阻塞

ALTER DATABASE LockTestDB SET READ_COMMITTED_SNAPSHOT ON

--而后新开一个Session1,执行语句
--执行更新，但是不提交
BEGIN TRAN
UPDATE LockTest SET Value='Version2' WHERE ID=1

--此时，新开一个Session2,执行语句
--可以查询出TABLE1数据
SELECT * FROM LockTest

--此时，虽然Session 1没有提交，但是照样可以查出
--ID  Value
--————————
--1      Version1
--并且这个查询结果是干净的（Session1没有提交，所以Value当然是Version1）

穿上马甲照样认识你 —— 一键参数化

不加绑定变量的SQL是可怕的SQL
菜鸟：呵呵，我配置牛逼，不怕~
老鸟：呵呵哒，蚂蚁咬死象，晓得不~~
菜鸟：但是，如果现在改代码，来不及啊，还要release。
老鸟：可以一键参数化，先救火

实验如下：

CREATE DATABASE ParTest
USE ParTest

SELECT * FROM sys.objects WHERE object_id=1
SELECT * FROM sys.objects WHERE object_id=2

可以看到tsql分别编译执行了

接下来，一键参数化

-- PARAMETERIZATION 参数化
ALTER DATABASE ParTest SET PARAMETERIZATION FORCED WITH NO_WAIT

SELECT * FROM sys.objects WHERE object_id=3
SELECT * FROM sys.objects WHERE object_id=4

再次查看，可以看到，编译参数化了，可以在缓存中击中

一键参数化是灵丹妙药吗？
我们没有贤者之石，所以能量是守恒的。
1 一键参数化并不是无敌的，他也有不能参数化的。比如，动态sql。
2 某个时间点会出现参数嗅探，但是并不是参数化的锅