SQL Server Statistics DEMO SREENI JULAKANTI MCTS MCITP

SQL Server Statistics DEMO SREENI JULAKANTI , MCTS. MCITP, MCP. SQL SERVER Database Administration SQL Server Statistics

AGENDA SQL Server STATISTICS �SQL Server Statistics object. �Statistics Creation. �Statistics Update and Auto update scenarios. �Execution Plans with Data Skew Issues. �Solutions for Skewed Data Distribution. SQL Server Statistics

What are SQL Server STATISTICS? �Statistics for query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table. �Statistics Header �Describes the properties of the statistics object. �Density Vector �The query optimizer uses densities to enhance cardinality estimates for queries that return multiple columns from the same table. SQL Server Statistics

What are SQL Server STATISTICS? �Histogram �A histogram measures the frequency of occurrence for each distinct value in a data set. The query optimizer computes a histogram on the column values in the first key column of the statistics object. Histogram has limitation of 200 steps. �Cardinality : �The total number of rows processed at each level of a query plan operator. Cardinality estimator only interested in predicates SQL Server Statistics

Histogram SQL Server Statistics

Statistics Stats header Vector density Histogram SQL Server Statistics

Histogram SQL Server Statistics

STMT Syntax check (Parsing) Logical tree (Binding) Estimated Row Count Hardware Configuration Query Hints Optimizer Indexes Partitioning Filegroups/Files SQL Server Statistics Plan Execution Result

Creating Statistics The Different way stats being created � The query optimizer creates statistics on key column for indexes on tables when the index is created. �The query optimizer creates statistics for single columns in query predicates. �Composite indexes creates Multi column statistics. �sp_createstats stored procedure �Create Statistics Statement �Note: Auto create will not work on Read-only DB’s SQL Server Statistics

How SQL Server STATISTICS Impacts? �Detailed look at the utilization of statistics. �How SQL Server Thinks about Stats? �How SQL Server Comes to a conclusion? DEMO -1 SQL Server Statistics

Updating Statistics When STASTS Updated • SQL Server update stats on default threshold (20%+500) • A query compiles for the first time, • A query has an existing query plan, but a statistic in the plan is out of date • Auto_update_statistics • Sp_Updatestats stored procedure • Update statistics Command • Note: Index reorg will not Update index linked stats SQL Server Statistics

When AUTO Update Kicks off Demo - 2 SQL Server Statistics

Skewed data Distribution Even Distribution is easy ( it is fairly consistent ) Un-even distribution is HARDER E This is all over the place and varies over time as well The Histogram does a much better job having steps and average distribution per step but what if there are well over 200 distinct values and millions of rows with heavy skew between steps Siply put the averages just aren’t going to cut it anymore Un-even distribution is HARDER This is all over the place and varies over time as well The Histogram does a much better job having steps and average distribution per step but what if there are well over 200 distinct values and millions of rows with heavy skew between steps Simply put the averages just aren’t going to cut it anymore

Data Distribution Matters Impact of Skewed estimates 1. Spills to Disk(Under estimates) 2. Index. Scan Vs. Index Seek Decisions 3. Inflated Memory Grants 4. Serial vs. Parallel Operations 5. Least Selective tables Joined first 6. outer/inner choice 7. Inappropriate Join algorithm selected SQL Server Statistics

Statistics Best Practices DEMO -3 SQL Server Statistics

Skewed data Distribution • Some solutions for Skewed data Distribution • • • Optimize for (Unknown) Recompile Plan guides filtered statistics Declare local Variables. SQL Server Statistics

Q&A SQL Server Statistics