快捷導(dǎo)航

SQLServer 2000 升級(jí)到 SQLServer 2008 性能之需要注意的地方之一

更新時(shí)間：2012年02月15日 22:13:44 作者：

今天在相同環(huán)境測(cè)試 2000 和 2008 性能讓我意外的是 2008 明顯比2000 慢很多，因?yàn)椴荒芎?jiǎn)單的升級(jí)，sql語(yǔ)句也需要優(yōu)化

測(cè)試sql：

 
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 

表結(jié)構(gòu)：

復(fù)制代碼代碼如下:

 
CREATE TABLE [dbo].[tbtext]( 
[id] [int] IDENTITY(1,1) NOT NULL, 
[VALUE] [int] NULL 
) ON [PRIMARY] 

單這句測(cè)試，看執(zhí)行計(jì)劃根本看不出區(qū)別。
|--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:

復(fù)制代碼代碼如下:

 
/* 
警告: 由于使用了本地聯(lián)接提示，聯(lián)接次序得以強(qiáng)制實(shí)施。 
表 'tbtext'。掃描計(jì)數(shù) 1，邏輯讀取 46 次 
(1 行受影響) 
表 'Worktable'。掃描計(jì)數(shù) 1，邏輯讀取 290098 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。 
表 'tbtext'。掃描計(jì)數(shù) 2，邏輯讀取 262 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。 
(1 行受影響) 
SQL Server 執(zhí)行時(shí)間: 
CPU 時(shí)間 = 32828 毫秒，占用時(shí)間 = 32846 毫秒。 
SQL Server 執(zhí)行時(shí)間: 
CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 0 毫秒。 
*/ 

2000sp4:

復(fù)制代碼代碼如下:

 
/* 
警告: 由于使用了局部聯(lián)接提示，所以聯(lián)接次序得以強(qiáng)制實(shí)施。 
表 'tbtext'。掃描計(jì)數(shù) 1，邏輯讀 131 次，物理讀 0 次，預(yù)讀 0 次。 
SQL Server 執(zhí)行時(shí)間: 
CPU 時(shí)間 = 0 毫秒，耗費(fèi)時(shí)間 = 0 毫秒。 
表 'Worktable'。掃描計(jì)數(shù) 9999，邏輯讀 180001 次，物理讀 0 次，預(yù)讀 0 次。 
表 'tbtext'。掃描計(jì)數(shù) 2，邏輯讀 262 次，物理讀 0 次，預(yù)讀 138 次。 
SQL Server 執(zhí)行時(shí)間: 
CPU 時(shí)間 = 17188 毫秒，耗費(fèi)時(shí)間 = 17261 毫秒。 
(1 行受影響) 
SQL Server 執(zhí)行時(shí)間: 
CPU 時(shí)間 = 0 毫秒，耗費(fèi)時(shí)間 = 0 毫秒。 
*/ 

比較2000 和 2008的執(zhí)行就能發(fā)現(xiàn) 2008 的cpu 時(shí)間明顯比 2000 高，2008 的worktable 邏輯讀取量，比2000的高，
這個(gè)有個(gè)worktable 的掃描技術(shù)，2000的是9999,2008的是1，這個(gè)讓人難免有的疑惑是什么情況，都是nest loop，worktable 掃描不應(yīng)該是1才對(duì)。
性能差怎么大會(huì)不會(huì)是 worktable 搞的鬼呢？
那么就開(kāi)始調(diào)節(jié)，過(guò)濾id 會(huì)有啥發(fā)現(xiàn)呢？

復(fù)制代碼代碼如下:

 
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,[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]), 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)))

復(fù)制代碼代碼如下:

 
表 'Worktable'。掃描計(jì)數(shù) 1，邏輯讀取 6006 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。 
表 'tbtext'。掃描計(jì)數(shù) 2，邏輯讀取 262 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 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]))

復(fù)制代碼代碼如下:

 
表 'Worktable'。掃描計(jì)數(shù) 999，邏輯讀 27001 次，物理讀 0 次，預(yù)讀 0 次。 
表 'tbtext'。掃描計(jì)數(shù) 2，邏輯讀 262 次，物理讀 0 次，預(yù)讀 0次。 

進(jìn)入 lazy spool的數(shù)據(jù)完全不一樣了，2008 只是進(jìn)入了1000 條數(shù)據(jù)，但是2000 全都進(jìn)去了。
在邏輯讀上面 2008 明顯低于 2000. cpu時(shí)間也明顯比2000少。
通過(guò)調(diào)節(jié)id 的值，2000 我推出了一個(gè)公式邏輯讀= 10001+（17*n），
但是2008的算法十分奇怪，
當(dāng)n < 386 時(shí) 邏輯讀=3+4（n-1）
當(dāng) 386<=n<=770 邏輯讀= 1932+5(n-386)
2000的邏輯讀是線性增長(zhǎng)，2008 是分段的線性增長(zhǎng)，每個(gè)分段 f '(x) 都不一樣。
2008 的lazy spool適合選擇度高的，選擇度低的時(shí)候完全不行。
從2000到2008 不單單是多了sqlos和表面上的一些功能，很多執(zhí)行計(jì)劃的操作符都被重寫(xiě)了，像lazy spool 。
所以在升級(jí)到2008 之前，
各位朋友，是否都應(yīng)該重寫(xiě)一下sql 呢？單單優(yōu)化索引已經(jīng)解決不了根本問(wèn)題了。

您可能感興趣的文章: