Maciej Pilecki Project Botticelli Ltd LIES DAMN LIES

Maciej Pilecki | Project Botticelli Ltd. LIES, DAMN LIES AND STATISTICS MAKING THE MOST OUT OF SQL SERVER STATISTICS

SELECT Bio FROM Speakers WHERE Full. Name=‘Maciej Pilecki’; Microsoft Certified Trainer since 2001 � SQL Server MVP since Jan 2006 � Specializing in SQL Server database development and administration � Delivering training and consulting services around the world � Frequent speaker at many international conferences and UG meetings �

Agenda � Introduction to statistics � Statistics internals � Creating statistics � Maintaining statistics

How the Query Optimizer Works Source: SQL 2008 Books Online

How the Query Optimizer Works SQL Server uses "cost-based" optimizer � Execution plan is chosen based on the ESTIMATED cost of execution � Cost estimation based on the number of rows processed (cardinality estimation) � Cardinality estimation based on � ○ Number of rows in a table ○ Data distribution statistics � Optimizer estimates selectivity of a query

What are Statistics? � Statistics hold data distribution information about of values in a column or a set of columns � Statistics contain: �Distribution for leading column �Density information for column combinations � Every index has associated statistics

Statistics internals � Viewing statistics: �sys. stats_columns �DBCC SHOW_STATISTICS

Creating statistics � Automatically: �Depends on AUTO_CREATE_STATISTICS database option �For every index �For each non-indexed column used in the query predicate � Manually: �CREATE STATISTICS �sp_createstats

Maintaining statistics � Outdated stats can lead to suboptimal execution plans � Stats have to be refreshed

Maintaining statistics � Automatically, depending on: ○ AUTO_UPDATE_STATISTICS database setting ○ STATISTICS_NORECOMPUTE option of the index ○ NORECOMPUTE option of the statistics ○ sp_autostats ○ AUTO_UPDATE_STATISTICS_ASYNC database option � Manually: ○ UPDATE STATISTICS ○ sp_updatestats ○ Index rebuild

Auto-updates � Thresholds for auto-updates: • For temp table with less than 6 rows – after every 6 updates • For table with less than 500 rows - after 500 updates • For larger table - after 500 + 20% of rows • For table variables – NEVER Described in KB 195565 • Updates tracked in rowmodctrs sysindexes •

Best practices � Don't turn off: �AUTO_CREATE_STATISTICS �AUTO_UPDATE_STATISTICS � Rebuild your indexes from time to time �Performs a full-scan update of statistics � Run sp_updatestats after any major update

Summary � Introduction to statistics � Statistics internals � Creating statistics � Maintaining statistics

Related Content � Statistics Used by the Query Optimizer in Microsoft SQL Server 2008 http: //msdn. microsoft. com/en-us/library/dd 535534. aspx � Elisabeth Redei – Lies, Damn Lies and Statistics (in two parts): http: //sqlblog. com/blogs/elisabeth_redei/archive/2009/03/01/lies-damned -lies-and-statistics-part-i. aspx � SQL Server Internals and Query Tuning class: Mainz, 22 -26 November http: //entwickler-akademie. de/codecamps/workshop-maciej-pilecki

Q&A maciej@projectbotticelli. com