Statistics for beginners Lies a blatant lie statistics

Statistics for beginners Lies, a blatant lie, statistics. Demystification of the statistics. Андрій Зробок azrobok@gmail. com

Agenda - CREATING STATISTICS: - CREATE STATISTICS (FULLSCAN, SAMPLE NNN PERCENT) CREATE INDEX AUTO-CREATING: EXECUTE SQL-QUERY STATISTICS ON SEVERAL COLUMNS (TWO FOR EXAMPLE) - UPDATING STATISTICS: - Automatic updates - Synchronous / Asynchronous - Manual updates - USAGE SAMPLES: - 2 | 9/18/2020 | WHERE COL_NAME = VALUE WHERE COL_NAME = VARIABLE (@ID) WHERE COL_NAME > VARIABLE (@ID) COMPUTED COLUMN FILTERED STATISTICS (SEVERAL COLUMNS) LIKE ‘%VAR%’ Statistics for beginners

Test database: Adventure. Works 2012 table: Person. Address table: dbo. Address 3 | 9/18/2020 | Statistics for beginners

Optimizer Server Query Optimizer - cost-based optimizer Cardinality estimation – number of record, will be returned Selectivity – percentage of rows from input that satisfy a predicate Memory inefficient plans negative impact on the performance incorrect cardinality and cost estimation Quality of the execution plans = accuracy of cost estimations 4 | 9/18/2020 | Statistics for beginners

CE: model assumptions SQL Server’s CE component makes certain assumptions based on typical customer database designs, data distributions, and query patterns. The core assumptions are: Independence: Data distributions on different columns are independent unless correlation information is available. Uniformity: Within each statistics object histogram step, distinct values are evenly spread and each value has the same frequency. Containment: If something is being searched for, it is assumed that it actually exists. For a join predicate involving an equijoin for two tables, it is assumed that distinct join column values from one side of the join will exist on the other side of the join. In addition, the smaller range of distinct values is assumed to be contained in the larger range. Inclusion: For filter predicates involving a column-equal-constant expression, the constant is assumed to actually exist for the associated column. If a corresponding histogram step is nonempty, one of the step’s distinct values is assumed to match the value from the predicate. Given the vast potential for variations in data distribution, volume and query patterns, there are circumstances where the model assumptions are not applicable. 5 | 9/18/2020 | Statistics for beginners

CE: under over estimating Under estimating rows can lead to memory spills to disk, for example, where not enough memory was requested for sort or hash operations. Under estimating rows can also result in: The selection of serial plan when parallelism would have been more optimal. Inappropriate join strategies. Inefficient index selection and navigation strategies. Inversely, over estimating rows can lead to: Selection of a parallel plan when a serial plan might be more optimal. Inappropriate join strategy selection. Inefficient index navigation strategies (scan versus seek). Inflated memory grants. Wasted memory and unnecessarily throttled concurrency. Improving the accuracy of row estimates can improve the quality of the query execution plan and, as a result, improve the performance of the query. 6 | 9/18/2020 | Statistics for beginners

Creating / updating statistics: SETs 7 | 9/18/2020 | Statistics for beginners

Creating statistics: Test table USE [Adventure. Works 2012] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo]. [Address]( [Address. ID] [int] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL, [Address. Line 1] [nvarchar](60) NOT NULL, [Address. Line 2] [nvarchar](60) NULL, [City] [nvarchar](30) NOT NULL, [State. Province. ID] [int] NOT NULL, [Postal. Code] [nvarchar](15) NOT NULL, [Spatial. Location] [geography] NULL, [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL, [Modified. Date] [datetime] NOT NULL, CONSTRAINT [PK_Address. ID] PRIMARY KEY CLUSTERED ( [Address. ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo]. [Address] ADD GO 8 | 9/18/2020 | CONSTRAINT [DF_dbo_Address_rowguid] DEFAULT (newid()) FOR [rowguid] CONSTRAINT [DF_dbo_Address_Modified. Date] Statistics for beginners DEFAULT (getdate()) FOR [Modified. Date]

Creating statistics: data loading set nocount on declare @i table (i int), @j int = 1 while @j <200 begin insert into @i (i) values (@j) set @j =@j +1 end Statistics are updated (created) when need (not immediately after data loading / updating ): INSERT INTO [dbo]. [Address] ([Address. Line 1] , [Address. Line 2] , [City] , [State. Province. ID] , [Postal. Code] , [Spatial. Location] ) SELECT [Address. Line 1] , [Address. Line 2] , [City] , [State. Province. ID] +i , [Postal. Code] , [Spatial. Location] FROM [Person]. [Address], @i Statistics is empty 9 | 9/18/2020 | DBCC SHOW_STATISTICS ('[dbo]. [Address]', [PK_Address. ID]) Statistics for beginners

Creating statistics: primary key select * from [dbo]. [Address] where [Address. ID] = 1 DBCC SHOW_STATISTICS ('[dbo]. [Address]', PK_Address. ID) 10 | 9/18/2020 | Statistics for beginners

Creating statistics: definition Density is calculated based on the formula: (1 / frequency), where frequency indicates the average number of the duplicates per key value All density is calculated based on (1 / number of distinct values) formula, and it indicates how many rows on average every combination of key values has The RANGE_HI_KEY column stores the sample value of the key. This value is the upper-bound key value for the range defined by histogram step. The RANGE_ROWS column estimates the number of rows within the interval EQ_ROWS indicates how many rows have a key value equal to the RANGE_HI_KEY upper-bound value DISTINCT_RANGE_ROWS indicates how many distinct values of the keys are within the interval AVG_RANGE_ROWS indicates the average number of rows per distinct key value in the interval. 11 | 9/18/2020 | Statistics for beginners

Creating statistics: auto-creating select distinct Postal. Code from [dbo]. [Address] where city = 'Concord‘ (1 row(s) affected) select * from sys. stats where object_id = object_id('[dbo]. [Address]', 'U') What does mean WA in the name of statistic? DBCC SHOW_STATISTICS ('[dbo]. [Address]', _WA_Sys_00000004_041093 DD) WITH STAT_HEADER go DBCC SHOW_STATISTICS ('[dbo]. [Address]', _WA_Sys_00000006_041093 DD) WITH STAT_HEADER go SQL Server stores additional information in the statistics for the string values called Trie Trees 12 | 9/18/2020 | Statistics for beginners

Creating statistics: auto-creating DBCC SHOW_STATISTICS ('[dbo]. [Address]', _WA_Sys_00000004_041093 DD) WITH DENSITY_VECTOR go DBCC SHOW_STATISTICS ('[dbo]. [Address]', _WA_Sys_00000006_041093 DD) WITH DENSITY_VECTOR go 13 | 9/18/2020 | Lies, damned lies, and statistics

Creating statistics: auto-creating DBCC SHOW_STATISTICS ('[dbo]. [Address]', _WA_Sys_00000004_041093 DD) WITH HISTOGRAM go DBCC SHOW_STATISTICS ('[dbo]. [Address]', _WA_Sys_00000006_041093 DD) WITH HISTOGRAM go 14 | 9/18/2020 | Statistics for beginners

Creating statistics: index SET ANSI_PADDING ON GO CREATE NONCLUSTERED INDEX [IDX_State. Province. ID] ON [dbo]. [Address] ( [State. Province. ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) GO DBCC SHOW_STATISTICS ('[dbo]. [Address]', IDX_State. Province. ID) 15 | 9/18/2020 | Statistics for beginners

Creating statistics: index (two columns) CREATE NONCLUSTERED INDEX [idx_city_postalcode] ON [dbo]. [Address] ( [City] ASC, [Postal. Code] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO DBCC SHOW_STATISTICS ('[dbo]. [Address]', idx_city_postalcode) Histogram is creating for first column only 16 | 9/18/2020 | Statistics for beginners

Creating statistics: two columns CREATE STATISTICS City. Province ON dbo. Address(City, State. Province. ID) GO DBCC SHOW_STATISTICS ('[dbo]. [Address]', City. Province) WITH STAT_HEADER CREATE STATISTICS City. Province ON dbo. Address(City, State. Province. ID) WITH FULLSCAN GO DBCC SHOW_STATISTICS ('[dbo]. [Address]', City. Province) WITH STAT_HEADER CREATE STATISTICS City. Province ON dbo. Address(City, State. Province. ID) WITH SAMPLE 50 PERCENT GO DBCC SHOW_STATISTICS ('[dbo]. [Address]', City. Province) WITH STAT_HEADER 17 | 9/18/2020 | Statistics for beginners

Updating statistics - Auto - Rules: - More then 500 records : 20% + 500 records are modified - Less then 500 records : 500 modifications - Amount of records are change from 0 - Temp tables: after every 6 modification - Filtered statistics – the same algorithm (as for usual statistics) - Sp_autostats (ON; OFF auto-updating statistics for particular objects) - NORECOMPUTE (Create Statistics OPTION) - STATISTICS_NORECOMPUTE (Create Index OPTION) - Synchronous / Asynchronous - Manual - UPDATE STATISTICS - Sp_Update. Stats (will update all statistics that have experienced the change of at least one underlying row since the last statistics update) - Index rebuild operation - Does not automatically deleted (in case of index creating) Updating statistics will result in cached plan invalidations. Auto - Updating statistics: sampling 20% rows 18 | 9/18/2020 | Statistics for beginners

Updating statistics: information SELECT OBJECT_NAME([sp]. [object_id]) AS "Table", [sp]. [stats_id] AS "Statistic ID", [s]. [name] AS "Statistic", [sp]. [last_updated] AS "Last Updated", [sp]. [rows], [sp]. [rows_sampled], [sp]. [unfiltered_rows], [sp]. [modification_counter] AS "Modifications" FROM [sys]. [stats] AS [s] OUTER APPLY sys. dm_db_stats_properties ([s]. [object_id], [s]. [stats_id]) AS [sp] WHERE [s]. [object_id] = OBJECT_ID(N'dbo. Address'); 19 | 9/18/2020 | Statistics for beginners

New 2014 CE Trace Flag 9481 reverts query compilation and execution to the pre-SQL Server 2014 legacy CE behavior for a specific statement. Trace Flag 2312 enables the new SQL Server 2014 CE for a specific query compilation and execution. --SQL Server 2014 compatibility level - New Cardinality Estimator ALTER DATABASE [Adventure. Works 2012] SET COMPATIBILITY_LEVEL = 120 select city, count(*) from [Person]. [Address] group by city OPTION (QUERYTRACEON 9481) --SQL Server 2012 compatibility level - Old Cardinality Estimator ALTER DATABASE [Adventure. Works 2014] SET COMPATIBILITY_LEVEL = 110 select city, count(*) from [Person]. [Address] group by city OPTION (QUERYTRACEON 2312) 20 | 9/18/2020 | Statistics for beginners

Undocumented (8666) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; GO DBCC TRACEON (8666); GO WITH XMLNAMESPACES ('http: //schemas. microsoft. com/sqlserver/2004/07/showplan' as p) SELECT qt. text AS SQLCommand, qp. query_plan, Stats. Used. XMLCol. value('@Field. Value', 'NVar. Char(500)') AS Stats. Name FROM sys. dm_exec_cached_plans cp CROSS APPLY sys. dm_exec_query_plan(cp. plan_handle) qp CROSS APPLY sys. dm_exec_sql_text (cp. plan_handle) qt CROSS APPLY query_plan. nodes('//p: Field[@Field. Name="wsz. Stat. Name"]') Stats. Used(XMLCol) WHERE qt. text LIKE '%SELECT%' AND qt. text LIKE '%addressline 1%'; GO DBCC TRACEOFF(8666); GO 21 | 9/18/2020 | Statistics for beginners

Undocumented (rowcount, pagecount) <update_stats_stream_option> : : = [ STATS_STREAM = stats_stream ] [ ROWCOUNT = numeric_constant ] [ PAGECOUNT = numeric contant ] <update_stats_stream_option> This syntax is for internal use only and is not supported. Microsoft reserves the right to change this syntax at any time. use tempdb go create table t 1(i int, j int) go create table t 2(h int, k int) go 22 | 9/18/2020 | Statistics for beginners

Undocumented (rowcount, pagecount) select distinct(i) from t 1 select * from t 1, t 2 where i = k order by j + k update statistics t 1 with rowcount = 10000, pagecount = 10000 update statistics t 2 with rowcount = 100000, pagecount = 100000 23 | 9/18/2020 | Statistics for beginners

Undocumented (rowcount, pagecount) select distinct(i) from t 1 select * from t 1, t 2 where i = k order by j + k 24 | 9/18/2020 | Statistics for beginners

Statistics for beginners SAMPLES 25 | 9/18/2020 | Statistics for beginners

Sample 1: constant = select addressid, Address. Line 1, addressline 2, city from [dbo]. [Address] where [State. Province. ID] = 17 Go 8429 row(s) affected 26 | 9/18/2020 | Statistics for beginners

Sample 2: local variable = declare @id int = 17 select addressid, Address. Line 1, addressline 2, city from [dbo]. [Address] where [State. Province. ID] = @id Go 8429 row(s) affected 27 | 9/18/2020 | Statistics for beginners

Sample 2 a: local variable = constant create table #t (id int not null identity(1, 1) primary key, descr varchar(20) not null) go insert into #t (descr) values ('descr 0'), ('descr 1'), ('descr 2'), ('descr 3'), ('descr 4'), ('descr 5'), ('descr 6'), ('descr 7'), ('descr 8'), ('descr 9') go 100 select * from #t where descr = 'descr 2' go declare @descr varchar(20) = 'descr 2' select * from #t where descr = @descr go 28 | 9/18/2020 | Statistics for beginners

Sample 3: local variable < (>) delete top (5) percent from [dbo]. [Address] go declare @id int = 9 select addressid, Address. Line 1, addressline 2, city from [dbo]. [Address] where [State. Province. ID] < @id go 0 row(s) affected DBCC SHOW_STATISTICS ('[dbo]. [Address]', IDX_State. Province. ID) 29 | 9/18/2020 | Lies, damned lies, and statistics

Sample 4: like % ; with rs as (select addressid, Address. Line 1, addressline 2, city, State. Province. ID from [dbo]. [Address] where Address. Line 1 like '%Monti%') select distinct rs. city, p. State. Province. Code, p. Name from rs inner join [Person]. [State. Province] p on rs. State. Province. ID = p. State. Province. ID go ; with rs as (select addressid, Address. Line 1, addressline 2, city, State. Province. ID from [dbo]. [Address] where Address. Line 1 like '%Circle') select distinct rs. city, p. State. Province. Code, p. Name from rs inner join [Person]. [State. Province] p on rs. State. Province. ID = p. State. Province. ID go DBCC SHOW_STATISTICS ('[dbo]. [Address]', _WA_Sys_00000004_041093 DD) WITH STAT_HEADER 30 | 9/18/2020 | Statistics for beginners

Sample 4: like % (with statistics) (1244 row(s) affected) (23321 row(s) affected) 31 | 9/18/2020 | Statistics for beginners

Sample 4: like % (with statistics) (23321 row(s) affected) (1244 row(s) affected) 32 | 9/18/2020 | Statistics for beginners

Sample 4: like % (without statistics) drop statistics dbo. address. _WA_Sys_00000004_041093 DD go USE [master] GO ALTER DATABASE [Adventure. Works 2012] SET AUTO_UPDATE_STATISTICS OFF GO ALTER DATABASE [Adventure. Works 2012] SET AUTO_CREATE_STATISTICS OFF GO 33 | 9/18/2020 | Statistics for beginners

Sample 4: like % (without statistics) 34 | 9/18/2020 | Statistics for beginners

Sample 4: like % (without statistics) Estimated With statistics 1078 vs Without statistics 575316 35 | 9/18/2020 | Statistics for beginners Estimated With statistics 121907 vs Without statistics 239715

Sample 5: computed columns SELECT (count(*)/100. 0)*30 as _30_percent FROM Sales. Order. Detail go SET STATISTICS PROFILE ON GO SELECT * FROM Sales. Order. Detail WHERE GO SET STATISTICS PROFILE OFF GO 36 | 9/18/2020 | Unit. Price * Order. Qty Statistics for beginners > 30000

Sample 5: computed columns ALTER TABLE Sales. Order. Detail ADD total AS Unit. Price * Order. Qty DBCC SHOW_STATISTICS ('[Sales]. [Sales. Order. Detail]', _WA_Sys_0000000 F_44 CA 3770) ALTER TABLE Sales. Order. Detail DROP COLUMN total 37 | 9/18/2020 | Statistics for beginners

Sample 7: two condition (2012; independent) SELECT [Address. Line 1] , [Address. Line 2] , [City] , [State. Province. ID] , [Postal. Code] FROM [Person]. [Address] where city = 'Melbourne' and stateprovinceid = 77 DBCC SHOW_STATISTICS ('[Person]. [Address]', IX_Address_State. Province. ID) DBCC SHOW_STATISTICS ('[Person]. [Address]', _WA_Sys_00000004_164452 B 1) Select ((901. 0/count(*)) * (110. 0/count(*))) * count(*) as Estimated. Numberof. Rows from [Person]. [Address] 38 | 9/18/2020 | Statistics for beginners

Sample 7: two condition (2014; selectivity) SELECT [Address. Line 1] , [Address. Line 2] , [City] , [State. Province. ID] , [Postal. Code] FROM [Person]. [Address] where city = 'Melbourne' and stateprovinceid = 77 39 | 9/18/2020 | Statistics for beginners

Sample 7: filtered staistics CREATE STATISTICS Victoria ON Person. Address(City) WHERE State. Province. ID = 77 40 | 9/18/2020 | Statistics for beginners

Sample 7: filtered statistics DBCC FREEPROCCACHE GO SELECT [Address. Line 1] , [Address. Line 2] , [City] , [State. Province. ID] , [Postal. Code] FROM [Person]. [Address] where city = 'Melbourne' and stateprovinceid = 77 Partition table 41 | 9/18/2020 | Statistics for beginners

Sample 8: out of date statistics DBCC SHOW_STATISTICS ('[dbo]. [Address]', Postal. Code) INSERT INTO [dbo]. [Address] ([Address. Line 1] , [Address. Line 2], [City], [State. Province. ID], [Postal. Code]) VALUES ('Address. Line 1', 'Address. Line 2', 'City', 5, N'YO 16') GO 100000 select Address. Line 1, Address. Line 2, city from [dbo]. [Address] where postalcode = N'YO 16' logical reads 300279 42 | 9/18/2020 | Statistics for beginners

Sample 8: out of date statistics (memory) select city, count(*) from [dbo]. [Address] where postalcode = N'YO 16' group by city 43 | 9/18/2020 | Statistics for beginners

Sample 8: corrected out of date statistics update statistics [dbo]. [Address] [postalcode] with fullscan select Address. Line 1, Address. Line 2, city from [dbo]. [Address] where postalcode = N'YO 16' logical reads 86357 44 | 9/18/2020 | Statistics for beginners

Sample 8: corrected out of date statistics select city, count(*) from [dbo]. [Address] where postalcode = N'YO 16' group by city 45 | 9/18/2020 | Statistics for beginners

Sample 9: auto – increment column (2012 vs 2014) create table dbo. Address_1 ( [Address. ID] [int] IDENTITY NOT NULL PRIMARY KEY, [Address. Line 1] [nvarchar](60) NOT NULL, [Address. Line 2] [nvarchar](60) NULL, [City] [nvarchar](30) NOT NULL, [State. Province. ID] [int] NOT NULL, [Postal. Code] [nvarchar](15) NOT NULL ) insert into dbo. Address_1 ([Address. Line 1] , [Address. Line 2] , [City] , [State. Province. ID] , [Postal. Code] ) select top 500 [Address. Line 1] , [Address. Line 2] , [City] , [State. Province. ID] , [Postal. Code] from person. address select [Address. Line 1] , [Address. Line 2] , [City] , [State. Province. ID] , [Postal. Code] from person. address SELECT * FROM dbo. Address_1 where addressid =1 46 | 9/18/2020 | insert into dbo. Address_1 ([Address. Line 1] , [Address. Line 2] , [City] , [State. Province. ID] , [Postal. Code] ) Statistics for beginners

Sample 9: auto – increment column (2012 vs 2014) 47 | 9/18/2020 | Statistics for beginners

Sample 9: auto – increment column (2012 vs 2014) dbcc freeproccache go SELECT * FROM go 48 | 9/18/2020 | dbo. Address_1 where addressid between 19800 and 19950 Statistics for beginners

Question: variation between estimated and actual row There is no hard-coded variance that is guaranteed to indicate an actionable cardinality estimate problem. Instead, there are several overarching factors to consider beyond just differences between estimated and actual row counts: Does the row estimate skew result in excessive resource consumption? For example, spills to disk because of underestimates of rows or wasteful reservation of memory caused by row overestimates. Does the row estimate skew coincide with specific query performance problems (e. g. , longer execution time than expected)? 49 | 9/18/2020 | Statistics for beginners

Statistics for beginners - Model assumptions are differ from real world Statistics are approximate Performance depends on up-to-date statistics Statistics on non-indexed column make sense Q&A 50 | 9/18/2020 | Statistics for beginners The end