Plan Freezing feature in sql 2k8

generic

Hello I read that sql 2k8 has a new feature called "Plan Freezing". SQL Server 2008 enables greater query performance stability and predictability by providing new functionality to lock down query plans.

I was wondering if anybody has implemented this for a NAV db. thanks.

stryk

Hi!

Well, "Plan Freezing" is actually just another term for "Plan Guides" - which were already introduced with SQL Server 2005.
So, yes, once I used indeed "Plan Guides" with NAV, e.g. to apply RECOMPILE hints.

For example:

EXEC sp_create_plan_guide 
  @name =  N'SSI_Guide1',
  @stmt = N'SELECT  * FROM "navision"."dbo"."CRONUS$Document Dimension" WITH (UPDLOCK, ROWLOCK)   WHERE (("Table ID"=@P1 OR "Table ID"=@P2)) AND (("Document Type"=@P3)) AND (("Document No_"=@P4)) ORDER BY "Table ID","Document Type","Document No_","Line No_","Dimension Code" OPTION (FAST 25)',
  @type = N'SQL',
  @module_or_batch = NULL,
  @params = N'@P1 int,@P2 int,@P3 int,@P4 varchar(20)',
  @hints = N'OPTION (RECOMPILE)'

The default NAV way of RECOMPILE-hinting is to apply the required parameters in "$ndo$sbconfig", then the C/SIDE client will send the query hint. But unfortunately the "dbconfig" thing is not very specific, actually you have to hint on table "granularity". Using RECOMPILE hints on heavily used tables on systems with small CPU capacity could cause problems ...
With Plan Guides you could be more specific - on query "granularity", so to speak - you apply a hint for a very specific query pattern. Thus, the advantage is to limit the amount of recompiles, avoiding too much pressure on the CPU. But the disadvantage is, that if the query changes - e.g. because a user changed a filter - the Plan Guide does not match anymore.
And if you have too many Plan Guides it's not easy to maintain all that stuff ...

Nowadays SQL Servers are (should be, at least) mostly sufficiently sized so that the "normal" way of RECOMPILE hinting via "$ndo$dbconfig" should do ...

I didn't use Plan Guides for anything else .. yet ... maybe with SQL 2008 this has been further improved ... I should look into that, again ... 8)

Regards,
Jörg

stryk

P.S.: You could also check this one out: http://blogs.msdn.com/sqlblog/archive/2009/02/19/plan-guides-plan-freezing-in-sql-server-2005-2008.aspx