This is my 43rd blog on SQL Server Trace Flag 4136 in the series of One Trace Flag a Day. You can go to all blogs under that series by clicking here.
To understand the use of this trace flag, we should be aware about statistics in SQL Server. In one of my previous blog I describe about the use of statistics histogram. You can go that blog by clicking here. I am using the same code here which I used in that blog.
use master go --drop test db if already exist if DB_ID('STATSTEST') IS NOT NULL Drop database STATSTEST; --create a new test db create database STATSTEST; go use STATSTEST; go --create a new table create table xtstats ( custid int identity(1000,1), bal int ) go --enter 1000 random values for bal column insert into xtstats values (RAND()*786) go 1000 --create a non clustered index on bal as leading column --it will also create statistics with same name as index create nonclustered index IX_xtstats_bal_id on xtstats(bal,custid) go --check the created stats DBCC SHOW_STATISTICS('xtstats','IX_xtstats_bal_id')
Now if I’ll ran the below query with Actual execution plan then it will estimate the number of rows base on above histogram.
USE STATSTEST go Select custid,bal from xtstats where bal=542
Till now we have seen the use of Histogram to estimate the number of rows. You can verify it from histogram shown in first figure. By using Trace Flag 4136 we can force the optimizer to use density rather than using histogram for cardinality estimation. Let me ran the same query with Actual execution plan after enabling the trace flag 4136.
use master go --Enable the trace flag DBCC TRACEON(4136,-1) go --To remove the execution plan saved in plan cache during previous execution --Do not run this in Prod environment DBCC FREEPROCCACHE() go USE STATSTEST go Select custid,bal from xtstats where bal=542 go
From the above execution plan you can see that now estimated number of rows is 1.75439. Which came from (density of bal from density vector * number of rows) i.e. (0.001754386 * 1000 = 1.754386). so we have seen the use of trace flag 4136.
And finally do not forget to turn off the trace flag.
use master go DBCC TRACEOFF(4136,-1)
PS: Do not use trace flags in production environment without testing it on non production environments and without consulting because everything comes at a cost.
Prince Kumar Rastogi