sql server trace flag 4136

Hi Friends,

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.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook