Database Reference
In-Depth Information
The session output doesn't contain any SQL activity representing a statistics update because the number of
changes fell below the threshold where any table that has more than 500 rows must have an increase of 500 rows plus
20 percent of the number of rows.
To understand the effect of large data modification on statistics update, add 1,500 rows to the table.
SELECT TOP 1500
IDENTITY( INT,1,1 ) AS n
INTO #Nums
FROM Master.dbo.SysColumns scl,
Master.dbo.SysColumns sC2;
INSERT INTO dbo.Test1
(C1)
SELECT 2
FROM #Nums;
DROP TABLE #Nums;
Now, if you reexecute the
SELECT
statement, like so, a large result set (1,502 rows out of 3,001 rows) will be
retrieved:
SELECT *
FROM dbo.Test1
WHERE C1 = 2;
Since a large result set is requested, scanning the base table directly is preferable to going through the
nonclustered index to the base table 1,502 times. Accessing the base table directly will prevent the overhead cost
of bookmark lookups associated with the nonclustered index. This is represented in the resultant execution plan
(see Figure
12-3
).
Figure 12-3.
Execution plan for a large result set
Figure
12-4
shows the resultant session output.
