Statistics And the Query Optimizer

Statistics and
the Query Optimizer
Grant Fritchey
Product Evangelist
Red Gate Software
www.ScaryDBA.com
Grant Fritchey | www.ScaryDBA.com

Goals
 Learn how SQL Server creates, stores and maintains
statistics
 Understand how the optimizer consumes statistics
to arrive at an execution plan
 Learn various methods for controlling statistics to
take more direct control of your queries


grant@scarydba.com www.scarydba.com

@gfritchey

Grant Fritchey
Product Evangelist, Red Gate Software


www.linkedin.com/in/s
carydba

STATISTICS IN ACTION


4

CREATE PROC dbo.spAddressByCity @City NVARCHAR(30)
AS
SELECT a.AddressID,
a.AddressLine1,
a.AddressLine2,
a.City,
sp.[Name] AS StateProvinceName,
a.PostalCode
FROM
Person.Address AS a
JOIN Person.StateProvince AS sp
ON a.StateProvinceID = sp.StateProvinceID
WHERE
a.City = @City;



6


7


8

WHAT ARE STATISTICS


9

DBCC
SHOW_STATISTICS('Person.Address',_WA_Sys_00000004_164452B1);


10

Key Statistic Terms
 Rows Sampled
 Steps
 Density
 Range_hi_key
 Range_rows
 Eq_rows
 Avg_range_rows
 Cardinality
 Cardinality
 Cardinality

11

Cardinality
 Selectivity
» Returned values / Total Values
 Histogram
 Density
» 1/distinct values
 Compound Columns
» Selectivity * Selectivity
» Density * Density
 + Trace Flags which we’ll talk about


12

Estimates vs. Cardinality
 Comparing variables
 Using != and NOT
 Predicates comparing columns within a table
 Functions w/o constant value
 Joins using arithmetic or string operations
 No statistics!
 Skewed distribution (eh)


13

Statistics are used to…
 Determine cardinality which is used to…
» Determine number of rows processed which is
used to…
— Determine cost of the operation in the plan which is
used to…
– Pick the operations used in the plan which is used to
» Return your data in an efficient manner which is used for
whatever the heck the business wants


14

CAPTURING BEHAVIOR


15

Capture Mechanisms
 Static
» DBCC SHOW_STATISTICS
» Execution Plans (sort of)
 Dynamic
» Trace Events
» Extended Events


16

Auto_stats


17

Missing_column_statistics


18

Query_optimizer_estimate
_cardinality


19

STATS ARE CREATED


20

SELECT

s.name,
s.auto_created,
s.user_created,
s.filter_definition,
sc.column_id,
c.name AS ColumnName
FROM
sys.stats AS s
JOIN sys.stats_columns AS sc ON sc.stats_id = s.stats_id
AND sc.object_id = s.object_id
JOIN sys.columns AS c ON c.column_id = sc.column_id
AND c.object_id = s.object_id
WHERE
s.object_id = OBJECT_ID('dbo.Address2')


21


22


23


24

What?
_WA_Sys_00000001_0A1E72EE


25

What?
_WA_Sys_00000001_0A1E72EE
Hexidecimal Object ID


26

What?
_WA_Sys_00000001_0A1E72EE

Table Column Number


27

What?
_WA_Sys_00000001_0A1E72EE

Table Column Number
System


28

What?
The US State of Washington… yes I’m serious
_WA_Sys_00000001_0A1E72EE

Table Column Number
System


29

YOU CAN CREATE STATS


30

CREATE STATISTICS MyStats
ON Person.Person (Suffix)
WITH FULLSCAN;


31

CREATE STATISTICS MyJrStats
ON Person.Person (Suffix)
WHERE Suffix = 'Jr.'
WITH FULLSCAN;


32

CREATE STATISTICS MyComboStats
ON Person.Person (Title,Suffix)
WHERE Suffix = 'PhD'
WITH FULLSCAN;


33

…Or Drop Them

DROP STATISTICS Person.Person.MyStats;
DROP STATISTICS Person.Person.MyJrStats;
DROP STATISTICS Person.Person.MyComboStats;


34

STATS ARE MAINTAINED


35

Dungeons and Dragons
 Add 1 Row when 0
 Add > 500 Rows when < 500
 Add 20% + 500 Rows when > 500


36

CREATE INDEX AddressCity
ON dbo.Address2 (City);

DBCC SHOW_STATISTICS(Address2,AddressCity)


37

SELECT * FROM dbo.Address2 AS a
WHERE City = 'Springfield';


38


39

Advanced D&D
 Trace Flag 2371
» SQL Sever 2008 R2 Sp1 and above
» After 25,000 rows
— Percentage changed based on number of rows
— Reducing as the number grows

 Trace Flag 4137
» Minimum selectivity instead of multiplication on
AND predicates
 Trace Flag 9471 (SQL Server 2014)
» Minimum selectivity on AND and OR predicates
 Trace Flag 9472 (SQL Server 2014)
» Independence (pre-2014 behavior)

40

YOU CAN SHOULD
MAINTAIN STATS
MANUALLY

41

Automatic Maintenance
 AUTO_CREATE_STATISTICS
 AUTO_UPDATE_STATISTICS
» Uses rules in previous section
 AUTO_UPDATE_STATISTICS_ASYNC
» Test, test, test


42

sp_updatestats
ALTER procedure [sys].[sp_updatestats]
@resample char(8)='NO'
As
…
if ((@ind_rowmodctr <> 0) or ((@is_ver_current is not
null) and (@is_ver_current = 0)))
…


43

sp_updatestats
ALTER procedure [sys].[sp_updatestats]
@resample char(8)='NO'
As
…

@ind_rowmodctr <> 0

if ((

)

or ((@is_ver_current is not null) and (@is_ver_current =
0)))
…


44

UPDATE STATISTICS

UPDATE STATISTICS Person.Address;


45

UPDATE STATISTICS

UPDATE STATISTICS Person.Address
WITH FULLSCAN;


46

UPDATE STATISTICS

UPDATE STATISTICS dbo.Address2
AddressCity;


47

UPDATE STATISTICS

UPDATE STATISTICS
dbo.Address2 AddressCity
WITH FULLSCAN,NORECOMPUTE;


48

STATISTICS AND
OPTIMIZER AT WORK

49

Statistics Matter


50

Compatibility Levels

ALTER DATABASE
AdventureWorks2012 SET
COMPATIBILITY_LEVEL = 120;


51

Optimizer Switches
OPTION

(QUERYTRACEON 2312);

DBCC TRACEON(4199);
DBCC FREEPROCCACHE();


52

Recommendations
 Compatibility setting
 Automatic creation
 Automatic update
 Update asynchronous where necessary
 Use appropriate sample rate


53

Resources
 Understanding SQL Server Cardinality Estimations
 Fixing Cardinality Estimation Errors
 Statistics Used by the Optimizer
 First look at the
query_optimizer_estimate_cardinality XE Event
 Changes to automatic update statistics inSQL
Server – traceflag 2371
 Cardinality Estimation for Multiple Predicates


55

Questions?


56

Statistics And the Query Optimizer

Recommended

Recommended

More Related Content

Viewers also liked

Viewers also liked (7)

Similar to Statistics And the Query Optimizer

Similar to Statistics And the Query Optimizer (20)

More from Grant Fritchey

More from Grant Fritchey (20)

Recently uploaded

Recently uploaded (20)

Statistics And the Query Optimizer

Editor's Notes