Test sql:
Copy Code code as follows:
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
Table structure:
Copy Code code as follows:
CREATE TABLE [dbo]. [Tbtext] (
[ID] [int] IDENTITY (1,1) not NULL,
[VALUE] [INT] Null
) on [PRIMARY]
Single this test, see the implementation plan can not see the difference.
|--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:
Copy Code code as follows:
/*
Warning: The join order is enforced because the local join hint is used.
Table ' Tbtext '. Scan count 1, logical read 46 times
(1 rows affected)
Table ' worktable '. Scan count 1, logical read 290,098 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' Tbtext '. Scan count 2, logical read 262 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
(1 rows affected)
SQL Server Execution Time:
CPU time = 32828 milliseconds, elapsed time = 32846 milliseconds.
SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 0 milliseconds.
*/
2000SP4:
Copy Code code as follows:
/*
Warning: The join order is enforced because the local join hint is used.
Table ' Tbtext '. Scan count 1, logical read 131 times, physics read 0 times, pre-read 0 times.
SQL Server Execution Time:
CPU time = 0 milliseconds, time consuming = 0 milliseconds.
Table ' worktable '. Scan count 9999, logical read 180,001 times, physics read 0 times, pre-read 0 times.
Table ' Tbtext '. Scan count 2, logical read 262 times, physics read 0 times, pre-read 138 times.
SQL Server Execution Time:
CPU time = 17188 milliseconds, time consuming = 17261 milliseconds.
(1 rows affected)
SQL Server Execution Time:
CPU time = 0 milliseconds, time consuming = 0 milliseconds.
*/
Comparing 2000 and 2008 can be found 2008 CPU time is significantly higher than 2000, 2008 worktable logical reads, than 2000 of the higher,
This has a worktable scanning technology, 2000 is 9999,2008 is 1, this lets the person unavoidably some doubts is what situation, all is Nest loop,worktable scan should not be 1 just right.
Performance difference how big will be worktable?
So start adjusting, what's the filter ID going to find out?
Copy Code code as follows:
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))]
Copy Code code as follows:
Table ' worktable '. Scan count 1, logical read 6,006 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' Tbtext '. Scan count 2, logical read 262 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
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]))
Copy Code code as follows:
Table ' worktable '. Scan count 999, logical read 27,001 times, physics read 0 times, pre-read 0 times.
Table ' Tbtext '. Scan count 2, logical read 262 times, physics read 0 times, pre-read 0 times.
The data entering the lazy spool is completely different, with 2008 entering 1000 data, but 2000 all in.
In the logical reading above 2008 is obviously less than 2000. CPU time is also significantly less than 2000.
By adjusting the value of the ID, 2000 I released a formula logic read = 10001+ (17*n),
But the 2008 algorithm is very strange,
When n < 386 logic reads =3+4 (n-1)
When 386<=n<=770 logic reads = 1932+5 (n-386)
The logical reading of 2000 is linear growth, 2008 is piecewise linear growth, and each subsection F ' (x) is different.
2008 of the Lazy spool suitable for the choice of high, the choice of low when the total No.
From 2000 to 2008 more than just Sqlos and surface features, many execution plan operators have been rewritten, like lazy spool.
So before you upgrade to 2008,
Friends, should you rewrite SQL? Optimizing indexes alone does not solve the fundamental problem.
