SQL Server Query Tuning Series-Execution Plan Estimated No. of rows and RANGE_HI_KEY Direct Step Hit

Опубликовано: 30 Сентябрь 2024
на канале: JBSWiki

217

SQL Server Query Tuning Series - Estimated Number of rows in an Execution plan and Histogram's RANGE_HI_KEY Direct Step Hit

Restoring Required Databases for Query Tuning Session - • Video

use [JBSWIKI]
GO

--Enable execution plan and check estimated rows
select sno from color where color='red' -- WHere did we get 899 estimate???

-- Statistics for color?
SELECT [s].[object_id], [s].[name], [s].[auto_created],
COL_NAME([s].[object_id], [sc].[column_id]) AS [col_name]
FROM sys.[stats] AS [s]
INNER JOIN sys.[stats_columns] AS [sc]
ON [s].[stats_id] = [sc].[stats_id]
AND [s].[object_id] = [sc].[object_id]
WHERE [s].[object_id] = OBJECT_ID(N'dbo.color');

-- The 899 estimated rows for the Index Seek can be seen in stats data
DBCC SHOW_STATISTICS(N'dbo.color', ix_test);

-- Lets insert some rows and make sure autoupdate stats is not done.
-- Wtach for execution plan
set nocount on

insert into color values ('Red',351)
go 9100

-- Enable execution plan

select sno from color where color='red' option (recompile) -- Where did 980.08 came now?

-- Statistics for color?
SELECT [s].[object_id], [s].[name], [s].[auto_created],
COL_NAME([s].[object_id], [sc].[column_id]) AS [col_name]
FROM sys.[stats] AS [s]
INNER JOIN sys.[stats_columns] AS [sc]
ON [s].[stats_id] = [sc].[stats_id]
AND [s].[object_id] = [sc].[object_id]
WHERE [s].[object_id] = OBJECT_ID(N'dbo.color');

DBCC SHOW_STATISTICS(N'dbo.color', ix_test); --SQL knows that rows were inserted after last update stats, so estimate changes.

select 899.0/100899 --EQ Rows/total rows = selectivity 0.00890989

select 0.00890989 * (select count(1) from color) -- 980.07899011, this is same as what we saw.

update statistics color with fullscan
go
sp_recompile 'color'

select sno from color where color='red' option (recompile)

-- Where did 9999 came now?
DBCC SHOW_STATISTICS(N'dbo.color', ix_test); Disclaimer:
The views expressed on this Video are mine alone and do not reflect the views of my company or anyone else. All postings on this Video are provided “AS IS” with no warranties, and confers no rights.