Lies , Damn Lies and Statistics Making the Most Out of SQL Server Statistics

Maciej Pilecki | Project Botticelli Ltd. Lies, DamnLies and StatisticsMakingthe Most Out of SQL Server Statistics

SELECT Bio FROM Speakers WHERE FullName=‘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

HowtheQuery Optimizer Works Source: SQL 2008 Books Online

How theQuery Optimizer Works • SQL Server uses "cost-based" optimizer • Execution plan is chosen based on theESTIMATED 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 columnor 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 • 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 • Statshave 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 KB195565 • Updatestrackedinrowmodctrssysindexes

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

RelatedContent • Statistics Used by the Query Optimizer in Microsoft SQL Server 2008 http://msdn.microsoft.com/en-us/library/dd535534.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 QueryTuningclass: Mainz, 22-26 November http://entwickler-akademie.de/codecamps/workshop-maciej-pilecki

Q & Amaciej@projectbotticelli.com

Lies , Damn Lies and Statistics Making the Most Out of SQL Server Statistics