Statistics, Data Distribution, and Cardinality - SQL Server Query Performance Tuning - page 224

While analyzing an execution plan for a query, look for the following points to ensure a cost-effective processing
strategy:

• Indexes are available on the columns referred to in the filter and join criteria.

• In the case of a missing index, statistics should be available on the columns with no index. It is

preferable to have the index itself.

• Since outdated statistics are of no use and can even be misleading, it is important that the

estimates used by the optimizer from the statistics are up-to-date.

You analyzed the use of a proper index in Chapter 6. In this section, you will analyze the effectiveness of statistics
for a query.

Resolving a Missing Statistics Issue
To see how to identify and resolve a missing statistics issue, consider the following example. To more directly control
the data, I'll use a test table instead of one of the AdventureWorks2012 tables. First disable both auto create statistics
and auto update statistics using the ALTER DATABASE command.

ALTER DATABASE AdventureWorks2012 SET AUTO_CREATE_STATISTICS OFF;
ALTER DATABASE AdventureWorks2012 SET AUTO_UPDATE_STATISTICS OFF;

Create a test table with a large number of rows and a nonclustered index.

IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.Test1') )
DROP TABLE [dbo].[Test1] ;
GO
CREATE TABLE dbo.Test1 (C1 INT, C2 INT, C3 CHAR(50)) ;
INSERT INTO dbo.Test1
(C1, C2, C3)
VALUES (51, 1, 'C3') ,
(52, 1, 'C3') ;
CREATE NONCLUSTERED INDEX iFirstIndex ON dbo.Test1 (C1, C2) ;

SELECT TOP 10000
IDENTITY( INT,1,1 ) AS n
INTO #Nums
FROM Master.dbo.SysColumns scl,
Master.dbo.SysColumns sC2 ;
INSERT INTO dbo.Test1
(C1, C2, C3)
SELECT n % 50,
n,
'C3'
FROM #Nums ;
DROP TABLE #Nums ;