频道导航

SQLServer 2000 升级到 SQLServer 2008 性能之需要注意的地方之一

2019-01-04 MsSQL 前端之家

前端之家收集整理的这篇文章主要介绍了SQLServer 2000 升级到 SQLServer 2008 性能之需要注意的地方之一，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

测试sql：
<div class="codetitle"><a style="CURSOR: pointer" data="77849" class="copybut" id="copybut77849" onclick="doCopy('code77849')"> 代码如下:

<div class="codebody" id="code77849">
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT COUNT(1)
FROM dbo.tbtext a
INNER LOOP JOIN dbo.tbtext b
ON a.id = b.id option (maxdop 1)
SET STATISTICS IO Off
SET STATISTICS TIME Off

表结构：
<div class="codetitle"><a style="CURSOR: pointer" data="69564" class="copybut" id="copybut69564" onclick="doCopy('code69564')"> 代码如下:

<div class="codebody" id="code69564">
CREATE TABLE [dbo].[tbtext](
[id] [int] IDENTITY(1,1) NOT NULL,
[VALUE] [int] NULL
) ON [PRIMARY]

单这句测试，看执行计划根本看不出区别。
|--Compute Scalar(DEFINE:([Expr1006]=CONVERT_IMPLICIT(int,[Expr1009],0)))
|--Stream Aggregate(DEFINE:([Expr1009]=Count()))
|--Nested Loops(Inner Join,WHERE:([northwind].[dbo].[tbtext].[id] as [b].[id]=[northwind].[dbo].[tbtext].[id] as [a].[id]))
|--Table Scan(OBJECT:([northwind].[dbo].[tbtext] AS [a]))
|--Table Spool
|--Table Scan(OBJECT:([northwind].[dbo].[tbtext] AS [b]))
2008r2:
<div class="codetitle"><a style="CURSOR: pointer" data="47537" class="copybut" id="copybut47537" onclick="doCopy('code47537')"> 代码如下:

<div class="codebody" id="code47537">
/
警告: 由于使用了本地联接提示，联接次序得以强制实施。
表 'tbtext'。扫描计数 1，逻辑读取 46 次
(1 行受影响)
表 'Worktable'。扫描计数 1，逻辑读取 290098 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'tbtext'。扫描计数 2，逻辑读取 262 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
(1 行受影响)
sql Server 执行时间:
cpu 时间 = 32828 毫秒，占用时间 = 32846 毫秒。
sql Server 执行时间:
cpu 时间 = 0 毫秒，占用时间 = 0 毫秒。
/

2000sp4:
<div class="codetitle"><a style="CURSOR: pointer" data="87058" class="copybut" id="copybut87058" onclick="doCopy('code87058')"> 代码如下:

<div class="codebody" id="code87058">
/
警告: 由于使用了局部联接提示，所以联接次序得以强制实施。
表 'tbtext'。扫描计数 1，逻辑读 131 次，物理读 0 次，预读 0 次。
sql Server 执行时间:
cpu 时间 = 0 毫秒，耗费时间 = 0 毫秒。
表 'Worktable'。扫描计数 9999，逻辑读 180001 次，物理读 0 次，预读 0 次。
表 'tbtext'。扫描计数 2，逻辑读 262 次，物理读 0 次，预读 138 次。
sql Server 执行时间:
cpu 时间 = 17188 毫秒，耗费时间 = 17261 毫秒。
(1 行受影响)
sql Server 执行时间:
cpu 时间 = 0 毫秒，耗费时间 = 0 毫秒。
/

比较2000 和 2008的执行就能发现 2008 的cpu 时间明显比 2000 高，2008 的worktable 逻辑读取量，比2000的高，
这个有个worktable 的扫描技术，2000的是9999,2008的是1，这个让人难免有的疑惑是什么情况，都是nest loop，worktable 扫描不应该是1才对。
性能差怎么大会不会是 worktable 搞的鬼呢？
那么就开始调节，过滤id 会有啥发现呢？
<div class="codetitle"><a style="CURSOR: pointer" data="68495" class="copybut" id="copybut68495" onclick="doCopy('code68495')"> 代码如下:<div class="codebody" id="code68495">
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT COUNT(1)
FROM dbo.tbtext a
INNER LOOP JOIN dbo.tbtext b
ON a.id = b.id
WHERE a.id <= 1000 option (maxdop 1)
SELECT COUNT(1)
FROM dbo.tbtext a
SET STATISTICS IO Off
SET STATISTICS TIME Off

2008r2:
SELECT COUNT(1) FROM dbo.tbtext a INNER LOOP JOIN dbo.tbtext b ON a.id = b.id WHERE a.id <= 1000 option (maxdop 1)
|--Compute Scalar(DEFINE:([Expr1006]=CONVERT_IMPLICIT(int,WHERE:([northwind].[dbo].[tbtext].[id] as [b].[id]=[northwind].[dbo].[tbtext].[id] as [a].[id]))
|--Table Scan(OBJECT:([northwind].[dbo].[tbtext] AS [a]),WHERE:([northwind].[dbo].[tbtext].[id] as [a].[id]<=(1000)))
|--Table Spool
|--Table Scan(OBJECT:([northwind].[dbo].[tbtext] AS [b]),WHERE:([northwind].[dbo].[tbtext].[id] as [b].[id]<=(1000)))
<div class="codetitle"><a style="CURSOR: pointer" data="50742" class="copybut" id="copybut50742" onclick="doCopy('code50742')"> 代码如下:<div class="codebody" id="code50742">
表 'Worktable'。扫描计数 1，逻辑读取 6006 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'tbtext'。扫描计数 2，逻辑读取 262 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

2000sp4：
|--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1006])))
|--Stream Aggregate(DEFINE:([Expr1006]=Count()))
|--Nested Loops(Inner Join,WHERE:([b].[id]=[a].[id]))
|--Table Scan(OBJECT:([Northwind].[dbo].[tbtext] AS [a]),WHERE:([a].[id]<=1000))
|--Table Spool
|--Table Scan(OBJECT:([Northwind].[dbo].[tbtext] AS [b]))
<div class="codetitle"><a style="CURSOR: pointer" data="77445" class="copybut" id="copybut77445" onclick="doCopy('code77445')"> 代码如下:<div class="codebody" id="code77445">
表 'Worktable'。扫描计数 999，逻辑读 27001 次，物理读 0 次，预读 0 次。
表 'tbtext'。扫描计数 2，逻辑读 262 次，物理读 0 次，预读 0次。

进入 lazy spool的数据完全不一样了，2008 只是进入了1000 条数据，但是2000 全都进去了。
在逻辑读上面 2008 明显低于 2000. cpu时间也明显比2000少。
通过调节id 的值，2000 我推出了一个公式逻辑读= 10001+（17*n），
但是2008的算法十分奇怪，
当n < 386 时逻辑读=3+4（n-1）
当 386<=n<=770 逻辑读= 1932+5(n-386)
2000的逻辑读是线性增长，2008 是分段的线性增长，每个分段 f '(x) 都不一样。
2008 的lazy spool适合选择度高的，选择度低的时候完全不行。
从2000到2008 不单单是多了sqlos和表面上的一些功能，很多执行计划的操作符都被重写了，像lazy spool 。
所以在升级到2008 之前，
各位朋友，是否都应该重写一下sql 呢？单单优化索引已经解决不了根本问题了。

性能性能