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.

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.

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.

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.

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