SQL SERVER STATS CHANGE AND IMPACT ON OPTIMIZER DEMO

1.Summary

This article is to describe how sql server automatically update statistics and how statistics influence execution plan .

2.When sql server automatically update statistics ?

SQL server automatically update statistics when change of rows reach 20%+500rows in a table .

3.Demo for statistics and execution plan change in table

--Check database statistic setting

SELECT name

       ,is_auto_create_stats_on

       ,is_auto_create_stats_incremental_on

       ,is_auto_update_stats_on

FROM sys.databases

WHERE name = db_name();

/*

is_auto_create_stats_on=1,is_auto_update_stats_on=1

indicate database "sample_db" auto create and update stats are on .

*/

 

--Create demo table

create table t1 (col1 int identity,col2 int) ;

 

--Insert 1500 records into table

 

select top 1500 identity (int,1,1) as n into #num from adventureworks2014.sales.SalesOrderDetail;

INSERT INTO t1 (col2)

SELECT n

FROM #num

 

DROP TABLE #num

GO

 

select min(col1),max(col1),min(col2),max(col2) from t1;

 

/*All the data in table t1 is even distributed .

total record in t1 is 1500 rows .

*/

--Display table t1 statistic infomation

 

EXEC sp_autostats 'sample_db.dbo.t1'

 

SELECT object_name(o.object_id)

       ,auto_created

       ,user_created

       ,stats_date(o.object_id, stats_id) AS StatisticUpdateDate

FROM sys.objects o

       ,sys.stats s

WHERE o.name = 't1'

       AND o.object_id = s.object_id;

--Create non-clustered index on column col2

create nonclustered index ind_t1_col2 on t1(col2) ;

 

--Retrieve data from table t1

select * from t1 where col2=2;

--Display execution plan for query

/*

Query access table t1 through index [ind_t1_col2]

Estimate number of row returned 1 .

This is the correct estimate number of row returned and nothing wrong with the execution plan .

*/

 

--How many rows needed for sql server to auto update stats?

--Modification of rows need to reach 20%+500rows to auto update stats.In this case ,need 800rows for sql server auto update stats.

select (1500*0.2)+500;

 

--Insert data into table t1 and see how stats change

--In this case ,insert 799 rows into table t1 and column col2 value is 2.

SELECT TOP 799 identity(INT, 1, 1) AS n

INTO #num

FROM adventureworks2014.sales.SalesOrderDetail;

 

INSERT INTO t1 (col2)

SELECT 2

FROM #num;

 

DROP TABLE #num;

 

--Check the table stats

 

EXEC sp_autostats 'sample_db.dbo.t1'

SELECT object_name(o.object_id),stats_id,s.name

       ,auto_created

       ,user_created

       ,stats_date(o.object_id, stats_id) AS StatisticUpdateDate

FROM sys.objects o

       ,sys.stats s

WHERE o.name = 't1'

       AND o.object_id = s.object_id;

SELECT obj.name

       ,obj.object_id

       ,stat.name

       ,stat.stats_id

       ,last_updated

       ,modification_counter

FROM sys.objects AS obj

INNER JOIN sys.stats AS stat ON stat.object_id = obj.object_id

CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp

WHERE obj.name = 't1'

/*Stats is not updated as modification_counter didn't reach 800*/

 

--Retrieve data from table t1

select * from t1 where col2=2;

 

--Display execution plan

/*

Same query executed .

Since stats is out of date and optimizer have no idea about the data change .

So the optimizer just blinded reuse the old execution plan which cached in database .

 

From the execution plan , we can see that the estimate return rows is 1 ,

in reality actual return rows are 800.

Moreover , it has to perform 800 times table lookup and this will

lead to serious performance issue .

*/

 

--Let's insert one more record to the table t1 and see how's going ?

 

SELECT TOP 1 identity(INT, 1, 1) AS n

INTO #num

FROM adventureworks2014.sales.SalesOrderDetail;

 

INSERT INTO t1 (col2)

SELECT 2

FROM #num;

 

DROP TABLE #num;

 

--Retrieve data from table t1

select * from t1 where col2=2;

 

--Check the table stats

 

EXEC sp_autostats 'sample_db.dbo.t1'

 

 

SELECT object_name(o.object_id),stats_id,s.name

       ,auto_created

       ,user_created

       ,stats_date(o.object_id, stats_id) AS StatisticUpdateDate

FROM sys.objects o

       ,sys.stats s

WHERE o.name = 't1'

       AND o.object_id = s.object_id;

 

 

SELECT obj.name

       ,obj.object_id

       ,stat.name

       ,stat.stats_id

       ,last_updated

       ,modification_counter

FROM sys.objects AS obj

INNER JOIN sys.stats AS stat ON stat.object_id = obj.object_id

CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp

WHERE obj.name = 't1'

/*

What happened when insert one more record into table t1?

When insert one row to table t1, the modification counter reached to 800 <threhold of auto stats update>

system automatically update table statistics .

 

When perform select query this time , optimizer is able to pickup the correct execution plan as stats

is up to date .

 

From the execution plan we can see the estimate return rows is 801 which is same as actual return rows

and number of execution  is 1 and table access by full table scan .

*/

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28412973/viewspace-2152849/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28412973/viewspace-2152849/