Database Reference
In-Depth Information
Benefits of Updated Statistics
The benefits of performing an auto update usually outweigh its cost on the system resources. If you have large tables
and I mean hundreds of gigabytes for a single table, you may be in a situation where letting the statistics update
automatically is less beneficial. In this case, you may want to try using the sliding scale, or you may be in a situation
where automatic statistics maintenance doesn't work well. But this is an edge case, and even here, you may find that
an auto update of the statistics doesn't negatively impact your system.
To more directly control the behavior of the data, instead of using the tables in
AdventureWorks2012
, for
this set of examples, you will create one manually. Specifically, create a test table with only three rows and a
nonclustered index.
IF (SELECT OBJECT_ID('Test1')
) IS NOT NULL
DROP TABLE dbo.Test1;
GO
CREATE TABLE dbo.Test1 (C1 INT, C2 INT IDENTITY);
SELECT TOP 1500
IDENTITY( INT,1,1 ) AS n
INTO #Nums
FROM Master.dbo.SysColumns sC1,
Master.dbo.SysColumns sC2;
INSERT INTO dbo.Test1
(C1)
SELECT n
FROM #Nums;
DROP TABLE #Nums;
CREATE NONCLUSTERED INDEX i1 ON dbo.Test1 (C1) ;
If you execute a
SELECT
statement with a selective filter criterion on the indexed column to retrieve only one row,
as shown in the following line of code, then the optimizer uses a nonclustered index seek, as shown in the execution
plan in Figure
12-1
.
SELECT *
FROM dbo.Test1
WHERE C1 = 2;
Figure 12-1.
Execution plan for a small result set
