SQL Server存储过程优化-网站名

数据准备优化必须有数据量。只有几十行数据时很多慢 SQL 问题不会暴露。建议准备一张订单表和一张用户表用来模拟真实业务查询。建表CREATE TABLE dbo.Users ( UserId INT IDENTITY(1,1) PRIMARY KEY, UserName NVARCHAR(50) NOT NULL, Phone VARCHAR(20) NULL, CreateTime DATETIME NOT NULL DEFAULT GETDATE() ); CREATE TABLE dbo.Orders ( OrderId BIGINT IDENTITY(1,1) PRIMARY KEY, UserId INT NOT NULL, Status TINYINT NOT NULL, OrderAmount DECIMAL(18,2) NOT NULL, CreateTime DATETIME NOT NULL, Remark NVARCHAR(500) NULL );插入数据INSERT INTO dbo.Users(UserName, Phone) SELECT TOP (100000) NUser_ CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NVARCHAR(20)), CAST(13000000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(20)) FROM sys.all_objects a CROSS JOIN sys.all_objects b; INSERT INTO dbo.Orders(UserId, Status, OrderAmount, CreateTime, Remark) SELECT TOP (1000000) ABS(CHECKSUM(NEWID())) % 100000 1, ABS(CHECKSUM(NEWID())) % 5, ABS(CHECKSUM(NEWID())) % 10000 / 10.0, DATEADD(MINUTE, -ABS(CHECKSUM(NEWID())) % 1000000, GETDATE()), N测试订单 FROM sys.all_objects a CROSS JOIN sys.all_objects b CROSS JOIN sys.all_objects c;开启观察标SET STATISTICS IO ON; SET STATISTICS TIME ON;看逻辑读取logical reads 逻辑读越高说明扫描的数据页越多。CPU 时间CPU time CPU消耗占用时间elapsed time 实际的执行时间执行SQL后在“消息”这里会出现以下内容SQL Server 分析和编译时间这个阶段主要做以下三件事情解析SQL语法、检查对象和字是否存在、生成或服用执行计划行数和IO信息这是查询实际访问数据的情况比如下图100000行受影响说明这条SQL返回或影响了100000行数据表‘user说明统计的是user表扫描计数对这个表/索引扫描1次逻辑读取721次从内存缓存里读取了721数据页每页8KB721*8K5768KB≈5.6m物理读取2次有2页是从磁盘中读取预读724次说明SQL Server判断接下来要读这些页于是提前从磁盘预读了7245页执行时间:CPU真正执行的耗时SQL Server数据页表和索引在磁盘/内存里的最下存储单元1、数据页大小固定8KB2、一行数据也会放进数据页3、SQL Server不是按行读取而是按页读取比如查一行数据它会把所在的数据页读出来4、页组成区Extent1页8KB1区8页64KB5、索引也是由页组成的索引不是一个抽象的目录它本身也存放在8KB的页里面索引结构类似B树从根页开始找--找到中间页--找到叶子页--定位到数据行FAQ:Q1:表数据怎么分页存①如果有主键主键会创建聚集索引数据按聚集索引建组织简单说就是表数据页会按照id顺序排列比如Page 1: id 1 - 100Page 2: id 101 - 200Page 3: id 201 - 300如果查 id 150 SQL Server可以通过B树快速定位到Page2.②没有聚集索引这个表叫堆表Heap,堆表数据没有明确的顺序比如Page A: id 900, 12, 300Page B: id 5, 8000, 21Page C: id 100, 77, 600如果查id 150 可能就要扫描很多页一、不要先优化先测量没有测量就没有优化。一个存储过程慢可能是缺索引。索引用不上。返回列太多。排序或聚合太重。参数嗅探。被其他事务阻塞。统计信息过旧。创建一个存储过程Create or alter PROC dbo.GetOrdersByUser UserId INT AS BEGIN select * from dbo.Orders where UserId UserId; end;运行这个存储过程exec dbo.GetOrdersByUser UserId 10011;查看运行情况如图执行计划中的逻辑读取logical reads 很高6475次记录本次执行内容执行耗时9ms CPU 时间78ms Orders 表 logical reads6475FAQ为什么会产生两次分析和两次执行日志执行的是存储过程不是一条单独的 SELECTSQL Server会把不用层级/语句的时间分别打印出来所以会有两组第一组外层exec命令本身的解析/编译时间几乎没成本第二组存储过程中真正SQL语句的编译时间后面两个执行时间也类似第一个是SQL的执行时间第二个是存储过程内部查询语句的执行时间二、select * 问题select * 的问题不只是返回的列多还会影响索引设计。如果查询只需要5个字段却返回30个字段会导致IO增加网络传输增加内存消耗增加更容易产生Key Lookup很难用覆盖索引优化去掉*优化create or alter proc GetUserOrders_Good UserId int AS begin select OrderId,UserId,Status,OrderAmount,CreateTime from dbo.Orders where UserId UserId; end; -- 执行语句 exec dbo.GetUserOrders_Good UserId 10011;执行结果记录本次执行内容执行耗时34ms CPU 时间0ms Orders 表 logical reads6475FAQQ1为什么不加*指明列名逻辑读取数量还是一样的它们现在大概率都在扫描同一张orders表/同一个聚集索引虽然返回列不同但为了找到目标值行读取的数据页是一样的Q2不加*怎么执行耗时还变长了一次执行的“占用时间”会波动尤其现在是毫秒级查询不能直接说明不加*反而更慢创建非聚合索引创建非聚集索引create index IX_Orders_UserId on dbo.Orders(UserId);调用存储过程结果记录本次执行结果执行耗时2ms CPU 时间0ms Orders 表 logical reads36FAQ聚集索引和非聚集索引区别聚集索引决定表数据物理/逻辑存放顺序数据表本身就会按照 id 组织在聚集索引的叶子节点上一张表通常只能有一个聚集索引因为数据只能按一种方式组织适合场景主键、范围查询、排序写入影响有影响非聚集索引额外建的一份“目录”适合场景高频查询条件、关联字段、覆盖查询写入影响索引越多写入越慢创建覆盖索引create index IX_Orders_UserId_Cover on dbo.Orders(UserId) include(Status,OrderAmount,CreateTime);执行结果记录本次执行结果执行耗时0ms CPU 时间0ms Orders 表 logical reads3FAQQ1include 是什么意思?include 里的列不参与索引排序只存放在索引叶子节点上。以上这句的意思是按UserId创建目录叶子节点上额外带上Status,OrderAmount,CreateTimeUserId用于where查询Status,OrderAmount,CreateTime用于select返回Q2非集合索引和覆盖索引对比非集合索引覆盖索引索引UserIdUserId包含列无Status,OrderAmount,CreateTime能否按UserId查找可以可以是否覆盖查询不一定可以覆盖指定查询是否容易Key Lookup容易不容易占用空间小大写入维护成本较低较高适合场景只过滤或返回少量键列高频查询固定返回这些列三、索引的核心让查询少读数据索引优化的本质不是让SQL用上索引而是让SQl少读数据。常见索引类型聚集索引决定数据物理组织方式一张表通常一个非聚合索引额外的数据查找结构组合索引多个字段组成的索引覆盖索引索引中包含查询需要的所有列先删除之前创建的索引drop index IX_Orders_UserId on dbo.Orders; drop index IX_Orders_UserId_Cover on dbo.Orders;示例查询语句SELECT OrderId, UserId, Status, OrderAmount, CreateTime FROM dbo.Orders WHERE UserId 23093 AND Status 0 AND CreateTime 2024-01-06 AND CreateTime 2026-08-06 ORDER BY CreateTime DESC;结果记录本次结果执行耗时29ms CPU 时间62ms Orders 表 logical reads6475FAQ为什么日志中会出现表Worktableworktable是SQL Server在执行查询时临时创建的内部工作表通常放在tempdb中。常见的触发场景order by、group by、distinct、union、hash join / hash aggregate、spool、游标、复杂查询中间结果推荐索引CREATE INDEX IX_Orders_User_Status_CreateTime ON dbo.Orders(UserId, Status, CreateTime DESC) INCLUDE(OrderAmount);示例语句执行结果记录本次执行结果执行耗时0ms CPU 时间0ms Orders 表 logical reads3为什么这样设计UserId等值过滤放前面Status等值过滤继续放前面CreateTime范围过滤按照倒序存放同时满足order byOrderAmount只返回不过滤放 include删除IX_Orders_User_Status_CreateTime索引分别建立以下两个索引查看结果。CREATE INDEX IX_Orders_UserId_Test ON dbo.Orders(UserId); 记录本次执行结果执行耗时0ms CPU 时间0ms Orders 表 logical reads54 CREATE INDEX IX_Orders_Status_Test ON dbo.Orders(Status); 记录本次执行结果执行耗时10ms CPU 时间0ms Orders 表 logical reads6475注意每次训练完毕后请删除索引四、组合索引顺序组合索引不是字段越多越好字段顺序非常重要一般原则等值查询列优先范围查询列放在等值查询之后排序列尽量和索引顺序一致只返回但不筛选的列放include比较两种不同顺序的索引CREATE INDEX IX_Test_A ON dbo.Orders(UserId, Status, CreateTime); 记录本次执行结果执行耗时0ms CPU 时间0ms Orders 表 logical reads18 CREATE INDEX IX_Test_B ON dbo.Orders(CreateTime, UserId, Status); 记录本次执行结果执行耗时44ms CPU 时间47ms Orders 表 logical reads3363可通过逻辑读取来看按照原则顺序来查询的数据页越少五、避免函数包字段如果在字段外面套函数SQL Server往往无法直接利用索引范围查找简单说用函数会使索引失效。先加索引CREATE INDEX IX_Orders_CreateTime ON dbo.Orders(CreateTime) INCLUDE(OrderAmount);用函数写法 select OrderId,CreateTime,OrderAmount from dbo.Orders where CONVERT(date,CreateTime) 2026-08-10; 记录本次执行结果执行耗时268ms CPU 时间0ms Orders 表 logical reads12 优化写法不使用函数 select OrderId,CreateTime,OrderAmount from dbo.Orders where CreateTime 2026-08-10 and CreateTime 2026-08-11 记录本次执行结果执行耗时0ms CPU 时间134ms Orders 表 logical reads6六、避免隐式转换参数类型和字段类型不一致会导致隐式转换可能会让索引失效Users表字段 Phone的类型是VARCHAR(20)比较下面两个存储-- 创建一个不匹配类型的存储 CREATE OR ALTER PROC dbo.GetUserByPhone_A Phone bigint AS BEGIN SELECT UserId, UserName FROM dbo.Users WHERE Phone Phone; END; -- 执行存储过程 exec dbo.GetUserByPhone_A Phone 13000096415 记录本次执行结果执行耗时16ms CPU 时间16ms Users 表 logical reads721 -- 创建一个类型匹配的存储 CREATE OR ALTER PROC dbo.GetUserByPhone_B Phone VARCHAR(20) AS BEGIN SELECT UserId, UserName FROM dbo.Users WHERE Phone Phone; END; -- 执行存储过程 exec dbo.GetUserByPhone_B Phone 13000096415; 记录本次执行结果执行耗时1ms CPU 时间0ms Users 表 logical reads6查看逻辑读取发现隐式转换会使索引失效七、Key Lookup优化Key Lookup:索引里字段不够SQL Server 再按主键回主表取缺少的字段。少量Ket Lookup可以接受大量key lookup会很慢--添加UserId索引 CREATE INDEX IX_Orders_UserId ON dbo.Orders(UserId); --示例SQL SELECT OrderId, UserId, OrderAmount, CreateTime FROM dbo.Orders WHERE UserId 1001; 记录本次执行结果执行耗时8ms CPU 时间0ms Orders 表 logical reads30 --添加覆盖索引 CREATE INDEX IX_Orders_UserId_Cover2 ON dbo.Orders(UserId) INCLUDE(OrderAmount, CreateTime); --示例SQL SELECT OrderId, UserId, OrderAmount, CreateTime FROM dbo.Orders WHERE UserId 1001; 记录本次执行结果执行耗时0ms CPU 时间0ms Orders 表 logical reads3减少key lookup可以提升查询掉率注意不要把大字段放进include如果不是高频查询的必要字段不建议放入覆盖索引八、OR条件优化or容易让优化器难以选择索引尤其两个条件对应不用字段时-- 创建索引 CREATE INDEX IX_Orders_UserId ON dbo.Orders(UserId); -- exists select u.UserId,u.UserName from dbo.Users u where exists ( select 1 from dbo.Orders o where o.UserId u.UserId ); 记录本次执行结果执行耗时935ms CPU 时间109ms Orders 表 logical reads2252 Users 表 logical reads721 -- join select u.UserId,u.UserName from dbo.Users u join dbo.Orders o on o.UserId u.UserId; 记录本次执行结果执行耗时9172ms CPU 时间967ms Orders 表 logical reads2310 Users 表 logical reads757 -- in select u.UserId,u.UserName from dbo.Users u where u.UserId in ( select o.UserId from dbo.Orders o ); 记录本次执行结果执行耗时934ms CPU 时间63ms Orders 表 logical reads2252 Users 表 logical reads721结果如下FAQQ1:出现的Workfile是什么Workfile也是SQL Server内部临时文件通常也在tempdb中触发的场景Hash joinHash AggregateSort 溢出并行查询中间数据Q2Worktable为什么出现两次一个用于union去重一个用于并行/中间结果/排序注意union 会默认去重等价于union distinct如果不需要去重可以使用union all不去重通常更快九、Exists、in、join只判断是否存在优先考虑exists需要返回关联表字段时用join判断值是否在集合中子查询返回单列用in下面比较判断是否存在-- 创建索引 CREATE INDEX IX_Orders_UserId ON dbo.Orders(UserId); -- exists select u.UserId,u.UserName from dbo.Users u where exists ( select 1 from dbo.Orders o where o.UserId u.UserId ); -- join select u.UserId,u.UserName from dbo.Users u join dbo.Orders o on o.UserId u.UserId; -- in select u.UserId,u.UserName from dbo.Users u where u.UserId in ( select o.UserId from dbo.Orders o );EXISTS 和 IN 基本等价JOIN 明显更慢是因为它返回了重复数据。十、大分页优化传统的分页越往后越慢例如offset 90000 rows fetch next 20 rows only这意味着前90000行也要被扫描、排序、跳过。-- 创建索引 CREATE INDEX IX_Orders_CreateTime_OrderId ON dbo.Orders(CreateTime DESC, OrderId DESC) INCLUDE(OrderAmount); -- 使用分页逻辑 select OrderId,CreateTime,OrderAmount from dbo.Orders o order by CreateTime desc offset 90000 rows fetch next 20 rows only; 记录本次执行结果执行耗时50ms CPU 时间0ms Orders 表 logical reads364 -- 使用创建时间进行查询 SELECT TOP (20) OrderId, CreateTime, OrderAmount FROM dbo.Orders WHERE CreateTime 2026-06-08 20:49:08.033 ORDER BY CreateTime DESC; 记录本次执行结果执行耗时0ms CPU 时间0ms Orders 表 logical reads3如果是大分页会导致逻辑读取增多可以使用时间进行约束十一、临时表拆分复杂查询复杂的SQL不一定要写一条到底对于大数据查询可以先过滤再关联再聚合。临时表的优点可以缩小数据查询范围可以给中间结果加索引SQL Server可以为临时表生成统计信息比如以下示例select u.UserId,u.UserName,SUM(o.OrderAmount) as totalAmount from dbo.Users u inner join dbo.Orders o on o.UserId o.UserId where o.CreateTime 2022-01-01 and o.CreateTime 2024-12-31 group by u.UserId,u.UserName 记录本次执行结果执行耗时1130ms CPU 时间327ms Orders 表 logical reads6475 Users 表 logical reads757后面拆分成临时表并加索引-- 创建临时表#FilteredOrders select OrderId,UserId,OrderAmount into #FilteredOrders from dbo.Orders where CreateTime 2022-01-01 and CreateTime 2024-12-31; -- 在临时表#FilteredOrders加UserId索引 create index IX_FilteredOrders_UserId on #FilteredOrders(UserId); -- 查询 select u.UserId,u.UserName,SUM(f.OrderAmount) as totalAmount from dbo.Users u inner join #FilteredOrders f on u.UserId f.OrderId group by u.UserId,u.UserName 记录本次执行结果执行耗时319ms CPU 时间0ms #FilteredOrders 表 logical reads584 Users 表 logical reads757两次结果对比拆分临时表后逻辑读取变少内存消耗减少十二、表变量和临时表小数据量可以使用表变量大数据量优先使用临时表创建表变量它不是普通变量而是一张临时的小表。-- 创建表变量 DECLARE OrderIds TABLE ( OrderId BIGINT PRIMARY KEY ); -- 在变中将查询的id放入表变量中 INSERT INTO OrderIds(OrderId) SELECT OrderId FROM dbo.Orders WHERE UserId 10011;表变量的逻辑是创建一个临时表变量OrderIds里面只有一列OrderId之后可以将查到的orderid放入表变量中十三、参数嗅探SQL Server 会缓存存储过程执行计划第一次执行时的参数可能会影响后续执行如果不同参数对应的数据量差异巨大就可能出现小数据参数编译出来的计划用在大数据参数上很慢大数据参数编译出来的计划用在小数据参数上也可能不理想示例SQLCREATE OR ALTER PROC dbo.GetOrderByStatus Status VARCHAR(50) AS BEGIN SELECT OrderId, UserId, Status, OrderAmount FROM dbo.Orders WHERE Status IN ( SELECT TRY_CAST(value AS TINYINT) FROM STRING_SPLIT(Status, ,) WHERE TRY_CAST(value AS TINYINT) IS NOT NULL ); END;查询状态Status0,1,2,3 有80%的数据Status4有20%的数据这时同一个执行计划就不适用所有参数方案一重新编译在最后加入OPTION (RECOMPILE); 让其每次执行SQL会重新编译执行计划正常情况下SQL Server会把执行计划缓存起来第一次执行编译计划 -- 执行 -- 缓存计划第二次执行复用上次计划加入OPTION (RECOMPILE);后每次执行重新根据当前参数编译计划 -- 执行如果第一次执行Status4 会生成一个适合小数据量的计划之后执行Status0,1,2,3却复用这个小数据量的计划可能就很慢加入OPTION (RECOMPILE);后让其每次执行重新编译计划以上这种情况就会消除CREATE OR ALTER PROC dbo.GetOrderByStatus Status VARCHAR(50) AS BEGIN SELECT OrderId, UserId, Status, OrderAmount FROM dbo.Orders WHERE Status IN ( SELECT TRY_CAST(value AS TINYINT) FROM STRING_SPLIT(Status, ,) WHERE TRY_CAST(value AS TINYINT) IS NOT NULL ) -- 每次执行都会重新编译 OPTION (RECOMPILE); END;方案二指定优化参数可以指定参数进行使用OPTION (OPTIMIZE FOR (Status 0,1,2,3,4))它的作用是参数嗅探让执行计划更稳定之后每次运行都会按照Status 0,1,2,3,4的计划去执行CREATE OR ALTER PROC dbo.GetOrderByStatus Status VARCHAR(50) AS BEGIN SELECT OrderId, UserId, Status, OrderAmount FROM dbo.Orders WHERE Status IN ( SELECT TRY_CAST(value AS TINYINT) FROM STRING_SPLIT(Status, ,) WHERE TRY_CAST(value AS TINYINT) IS NOT NULL ) -- 每次执行都会按照Status 0,1,2,3,4的编译计划取运行 OPTION (OPTIMIZE FOR (Status 0,1,2,3,4)) END;方案三动态SQL动态SQL作用是让SQL条件更灵活让不同参数生成不同的SQL计划能改善参数嗅探CREATE OR ALTER PROC dbo.GetOrderByStatus Status VARCHAR(50) AS BEGIN SET NOCOUNT ON; DECLARE sql NVARCHAR(MAX) N SELECT OrderId, UserId, Status, OrderAmount FROM dbo.Orders WHERE Status IN ( SELECT TRY_CAST(value AS TINYINT) FROM STRING_SPLIT(Status, ,) WHERE TRY_CAST(value AS TINYINT) IS NOT NULL );; EXEC sp_executesql sql, NStatus VARCHAR(50), Status Status; END;对比三种方案优点缺点适用场景重新编译1、计划更贴合当前参数2、处理参数差异大的查询很有效1、每次都编译会增加CPU2、高频接口慎用1、报表查询2、复杂查询3、参数差异大4、执行频率不高指定优化参数1、执行计划稳定2、避免第一次参数影响后续执行1、实际参数和指定参数差异大不适用1、大多数请求都是一个值2、不想每次重新编译3、希望计划稳定动态SQL1、适合多条件查询2、避免可选参数导致低效3、不同查询可以生成不用的计划1、拼接不当会有SQL注入风险2、计划缓存会变多3、调试不如静态SQL直观多条件搜索十四、分批更新和删除一次更新或删除几百万行会带来大事务大量日志长时间锁表或锁页阻塞其他业务先创建一个备份表SELECT * INTO dbo.Orders_Bak FROM dbo.Orders;全表删除delet from dbo.Orders 记录本次执行结果执行耗时5614ms CPU 时间5250ms Orders 表 logical reads3238038恢复数据-- 因为有自增列需要开启允许手动插入 SET IDENTITY_INSERT dbo.Orders ON; INSERT INTO dbo.Orders ( OrderId, UserId, Status, OrderAmount, CreateTime ) SELECT OrderId, UserId, Status, OrderAmount, CreateTime FROM dbo.Orders_Bak; SET IDENTITY_INSERT dbo.Orders OFF;分批次删除分5000行while 1 1 begin delete top(5000) from dbo.Orders; if ROWCOUNT 0 break; end; 记录本次执行结果每次平均执行耗时91ms 每次平均CPU 时间87ms 总执行耗时18234 ms 总cpu时间17374ms Orders 表 logical reads3214112分批次删除分10000行while 1 1 begin delete top (10000) from dbo.Orders; if ROWCOUNT 0 break; end; 每次平均执行耗时207ms 每次平均CPU 时间184ms 总执行耗时20657 ms 总cpu时间18407ms Orders 表 logical reads9071443分批次后耗时会增加cpu时间会增加逻辑查询会增加是因为每次都会查询虽然时间上涨但是分批次处理每次处理的时间会减少可大大减少风险十五、避免游标和逐行处理SQL Server擅长集合操作不擅长一行一行处理游标可以理解成把查询结果一行一行拿出来处理游标示例-- 声明一个变量后面游标每取一行订单就放在这个变量中 declare OrderId bigint; --声明一个游标cur,取游标的数据来源 --那么游标cur结果是 --1001 --1002 --1003 --... declare cur cursor for select OrderId from dbo.Orders where Status 0; -- 打开游标从游标cur取下一行数据放进OrderId中 open cur; fetch next from cur into OrderId; -- 开始循环FETCH_STATUS表示上一次fetch是否成功 -- 常见值 -- 0取值成功 -- -1取数据失败或没有下一行 -- -2取到的行不存在 WHILE FETCH_STATUS 0 begin update dbo.Orders set Status 1 where OrderId OrderId; -- 再从游标取下一行OrderId fetch next from cur into OrderId; end; -- 关闭游标并释放资源 close cur; deallocate cur; 每次平均执行耗时0ms 每次平均CPU 时间0ms 总执行耗时5472ms 总cpu时间44841ms Orders 表 logical reads1408928这里注意恢复数据先前已经备份了order表数据请先进行恢复优化写法这个表的数据有10万可以用分批更新的方法WHILE 1 1 begin update top (5000) dbo.Orders set Status 1 where Status 0; if ROWCOUNT 0 BREAK; END; 每次平均执行耗时283ms 每次平均CPU 时间275ms 总执行耗时11594ms 总cpu时间11279ms Orders 表 logical reads109561一行一行执行更新一行一次日志一行一次锁操作一行一次执行开销会浪费很多资源FAQselect、update、delete、insert分别是什么锁锁类型更新锁U Lock、排他锁X Lock、共享锁S Lockselect共享锁正常update一行select会等待正在select一行update会等待。update排他锁、更新锁先找要更新的行加排他锁真正修改适时加更新锁delete排他锁找到删除的行加排他锁insert排他锁防止别人同时修改同一行或相关索引结构十六、事务范围要小事务越大锁持有时间越长越容易阻塞别人事务里只放必须包怎一致性的写操作示例差写法先创建一个orderlog表create table dbo.OrderLog ( OrderId bigint, Content NVARCHAR(200) );-- 开启事务 begin tran; select * from dbo.Orders where OrderId 10011; update dbo.Orders set Status 1 where OrderId 10011; insert into dbo.OrderLog (OrderId,Content) values (10011,N订单状态变更); -- 提交事务 commit;优化写法select * from dbo.Orders where OrderId 10011; -- 开启事务 begin tran; update dbo.Orders set Status 1 where OrderId 10011; insert into dbo.OrderLog (OrderId,Content) values (10011,N订单状态变更); -- 提交事务 commit;把无关select放在事务外是保证事务一致性写操作原则十七、锁等待和阻塞如果SQL本身逻辑读不高但执行很慢可能不是查询问题而是被锁住了1、先开启一个SSMS查询窗口1begin tran; update dbo.Orders set Status 4 where OrderId 10012; --注意这里先不要提交事务 --commit;这时窗口1已经更新这行数据但事务没提交它会持有这行的排他锁2、在开启一个查询窗口2SET STATISTICS IO ON; SET STATISTICS TIME ON; UPDATE dbo.Orders SET Status 3 WHERE OrderId 10012;这条SQL理论上只更新一行逻辑读取不高但是它会一直等待窗口1释放锁3、再开启一个查询窗口3查看阻塞SELECT session_id, blocking_session_id, wait_type, wait_time, wait_resource FROM sys.dm_exec_requests WHERE blocking_session_id 0;结果这里每个字段的意思session_id被阻塞的会话blocking_session_id阻塞它的会话wait_type等待锁wait_tiem已经等待的时间wait_resource正在等待哪个锁资源这里的LCK_M_X是等待排他锁之后在窗口1提交事务窗口2会立即执行完毕查看执行记录会发现执行时间很长执行耗时461669ms cpu时间16ms Orders 表 logical reads6逻辑读不高但执行慢这是可以查阻塞/锁等待十八、统计信息和索引维护优化器依赖统计信息估算行数统计信息过旧时会导致执行计划错误。SQL Server在执行SQL前会先估算每个条件大概能查出多少行数据这个估算依赖于统计信息如果统计信息不准会影响执行计划。更新统计信息UPDATE STATISTICS dbo.Orders;全库更新更新库中所有的统计信息EXEC sp_updatestats;统计信息是SQL Server用来估算行数的依据比如数据分布是否均匀最大值、最小值每个范围大概有多少行这个字段有多少不用值查看索引碎片SELECT OBJECT_NAME(object_id) AS TableName, index_id, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats( DB_ID(), NULL, NULL, NULL, LIMITED ) WHERE avg_fragmentation_in_percent 10;查看当前数据库中索引碎片大于10%的索引可以理解为索引页顺序乱不乱如果碎片高范围查询扫描、排序可能会变慢5% 以下通常不用管 10% - 30%可以考虑重组 30% 以上可以考虑重建重组索引:将索引页稍微整理顺一点ALTER INDEX IX_Orders_UserId ON dbo.Orders REORGANIZE;重建索引将索引重新创建一遍ALTER INDEX IX_Orders_UserId ON dbo.Orders REBUILD;注意重建索引会消耗资源生产环境要安排维护时间。十九、总结先测试再优化着重看哪些表的逻辑读取很多再看用了什么索引索引不是越多越好能少逻辑读取才好优化关键减少逻辑读取

资讯详情

SQL Server存储过程优化