Sql-server – SQL Server – Do automatically created column statistics get dropped automatically after time

Nothing so easy. You would need to use DBCC SHOW_STATISTICS and look at the histogram, taking into account that the statistics might be filtered or multi column.

Probably easier to just let SQL Server do it and generate an estimated execution plan for the statement select * from foo where bar is null SQL Server will then use appropriate statistics if they exist or create them if they don't (assuming that the auto create statistics option is enabled).

You can then look at the estimated number of rows going into the SELECT iterator (This shows up as StatementEstRows in the XML for purposes of parsing programatically)

Example code

/*Create temporary stored procedure with query of interest*/
CREATE PROC dbo.#plan_test
AS
SELECT *
FROM master..spt_values
WHERE low IS NULL

GO

/*Generate an execution plan*/
SET FMTONLY ON
EXEC dbo.#plan_test
SET FMTONLY OFF;

/*Retrieve the plan and parse the estimated rows*/
WITH  XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql) 
SELECT  query_plan.value('(//sql:StmtSimple[1]/@StatementEstRows)[1]', 'float') AS EstimatedNumberOfRows
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
WHERE sys.dm_exec_sql_text.objectid=OBJECT_ID('tempdb.dbo.#plan_test') 
     AND sys.dm_exec_sql_text.dbid=2

DROP PROC dbo.#plan_test

I have checked on sql 2012 RTM and SP1, this trace flag TF 2317 is not enabled by default.

I have not seen this Trace Flag used widely, except on SQL Server running SAP. Also, if you are facing issues due to AUTO_UPDATE_STATISTICS then only you should consider enabling this trace flag.

My suggestion is that, monitor your statistics using sys.dm_db_stats_properties DMV to check the last time the statistics were updated, the number of rows in the table when the stats were updated along with the sampled rows. Its an interesting DMF to play around to better understand the statistics.

From Changes to automatic update statistics in SQL Server – traceflag 2371

When enabled -- With smaller tables, the threshold will still be around a 20% percentage. It only is when you exceed 25,000 rows per table where the dynamic rule will kick in and where, with an increasing # of rows, the percentage of records changed is becoming lower and lower. E.g. in a table with 100,000 rows, the threshold to trigger update statistics will already be down to 10%. In a table with 1,000,000 rows it only will take changes of around 3.2% to trigger automatic update statistics doing its job.

The only downside will be that updating stats will result in query recompilations occurring more frequently that are accessing the tables and hence a different plan is generated which might be good or bad.