sqlworkshops

Flying with Plan Freezing – Mostly you experience thrust or stay afloat, but rarely this add-on can be a drag

Plan freezing is an interesting feature, targeting plan stability, performance predictability. We all want stability, especially during the good times. There are exceptions, there are always exceptions, that’s what software testing is all about, there is no software without flaws, you have to learn to identify and navigate around the flaws. Most of the time these flaws will be eventually corrected (sometimes they do get marked ‘By Design/Feature’!), but a mission critical application can’t wait for the service pack or hotfix, it needs to perform today, now!

As I mentioned in the previous article ‘Prefetch – Querying at the speed of SAN’, plan freezing will not help you with estimation. SQL Server will still estimate based on current set of parameter values (compile time parameter values) and not based on the plan you use for freezing.

The best way to learn is to practice. To create the below tables and reproduce the behavior, join the mailing list by using this link: www.sqlworkshops.com/ml and I will send you the table creation script.

Optimized Bitmap Filtering is an interesting feature, if you execute the below query with MAXDOP 1, Bitmap Filtering will not happen and the query will consume more CPU resource.

You have to execute all the below example queries with parallelism enabled (with MAXDOP 0 or > 1). I did not use hint OPTION (MAXDOP 0) as my SQL Server Configuration Parameter ‘max degree of parallelism’ is set to 0.

Here is a query that uses the Optimized Bitmap Filter (that’s new in SQL Server 2008):

set statistics time on

go

--Example provided by www.sqlworkshops.com

declare @i int

select @i = f1.c1 from Fact1 f1

      inner join Dim1 d1 on (f1.c2 = d1.c2)

      inner join Dim2 d2 on (f1.c3 = d2.c2)

      where d1.c3 between 100000 and 110000 and d2.c3 between 200000 and  210000

go

--The above query takes 201ms of CPU

--There are 2 Optimized Bitmap Filters

--eliminating non qualifying rows from

--table Dim1(Fact1.c2) and Dim2(Fact1.c3)

--Hash join bottom input has only 63 rows

You can test Plan Freezing this with sp_create_plan_guide or sp_create_plan_guide_from_handle or simply with USE PLAN hint. We will use ‘USE PLAN’ hint.

You need to get the XML plan from the above query and then use than XML Plan in the below query. Make sure you disable 'Include Actual Execution Plan' in SQL Server Management Studio to get the XML plan.

--Example provided by www.sqlworkshops.com

--You need to turn off 'Include Actual Execution Plan'

--or Graphical plan and then execute

--set set statistics xml on

--to get the xml plan

--in SQL Server Management Studio.

set statistics xml on

go

--Example provided by www.sqlworkshops.com

--let’s execute the above query again

declare @i int

select @i = f1.c1 from Fact1 f1

      inner join Dim1 d1 on (f1.c2 = d1.c2)

      inner join Dim2 d2 on (f1.c3 = d2.c2)

      where d1.c3 between 100000 and 110000 and d2.c3 between 200000 and  210000

go

Right click on the XML Plan, do not left click, right click and choose Copy and paste the XML Plan in the OPTION (USE PLAN ‘<...XMLPlan...>’) clause between ‘<...XMLPlan...>’.

--Example provided by www.sqlworkshops.com

--Cut and paste the above xml plan here

declare @i int

select @i = f1.c1 from Fact1 f1

      inner join Dim1 d1 on (f1.c2 = d1.c2)

      inner join Dim2 d2 on (f1.c3 = d2.c2)

      where d1.c3 between 100000 and 110000 and d2.c3 between 200000 and  210000

      option (use plan '<...XMLPlan...>')

go

--The above query takes 331ms of CPU

--Notice There is only one Bitmap Filter

--not the 2008 Optimized Bitmap Filter

--eliminating non qualifying rows only

--from table Dim2(c3) and not from

--table Dim1(c2)

--Hash join bottom input has only 212835 rows

--to join instead of 63 rows like before

Duration may not be much different in our example even though we are joining less rows. This is because we have not that many rows like a real world OLAP database and we are using integer columns for the join and not large columns.

Remember the last time, you found something good, you liked it, you froze it and then it didn’t taste as good as it was before. Well this can happen with real world SQL Server queries as well, keep your eyes open and keep learning.

I recommend you to watch my webcasts (www.sqlworkshops.com/webcasts). The best way to learn is to practice. To create the above tables and reproduce the behavior, join the mailing list at www.sqlworkshops.com/ml and I will send you the relevant SQL Scripts.