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')

traceflag_4136_1

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

traceflag_4136_2

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

traceflag_4136_3

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

One Comment on “sql server trace flag 4136”

Leave a Reply

Your email address will not be published. Required fields are marked *