Forcing hints when using sp_executesql

Question

Post reply

Forcing hints when using sp_executesql

dcristini-633990

SSChasing Mays

Points: 603
More actions
April 7, 2008 at 7:33 pm

#188785

Hi all, I got a question regarding adding plan guides
I am using Microsoft's Dynamics CRM 3.0 and of course I cannot modify the queries, now a particular query does a join which while has a smaller cost performs slower than using hash joins, I'd like to try forcing it to do hash joins, however I don't seem to be able to force a plan guide (with sp_create_plan_guide) since the CRM makes the queries through sp_executesql I am trying to create a plan guide for an object, like this:
exec sp_create_plan_guide
@name = N'Plan_Codigo_MIS_250',
@stmt = N'select top 251 contact.FullName as ''fullname'',contact.Telephone1 as ''telephone1'',contact.OwnerId as ''ownerid'',contact.OwnerIdDsc as
''owneriddsc'',contact.OwnerIdName as ''owneridname'',contact.OwnerIdType as ''owneridtype'',contact.OwningTeam as ''owningteam'',contact.OwningUser as
''owninguser'',contact.New_noid as ''new_noid'',contact.New_MIS as ''new_mis'',contact.New_Territorio as ''new_territorio'',contact.New_EstadoCliente as
''new_estadocliente'',contact.New_TipodeID as ''new_tipodeid'',contact.New_Oficina as ''new_oficina'',contact.New_Vinculacion as ''new_vinculacion'',''new_vinculacionname'' =
case contact.New_Vinculacion when 1 then @P1 when 2 then @P2 when 3 then @P3 when 4 then @P4 when 5 then @P5 else null end ,contact.New_Clasificacion as
''new_clasificacion'',''new_clasificacionname'' = case contact.New_Clasificacion when 1 then @P6 when 2 then @P7 when 3 then @P8 when 4 then @P9 when 5 then @P10 when 6 then
@P11 when 7 then @P12 when 8 then @P13 when 9 then @P14 when 10 then @P15 when 11 then @P16 when 12 then @P17 else null end ,contact.ContactId as ''contactid'' from Contact as
contact where contact.DeletionStateCode in (0) and ((contact.StateCode = @P18) and (contact.New_noid like @P19 or contact.New_MIS = @P20)) AND (((contact.OwningUser is null)
OR(contact.OwningUser = @P21) OR (contact.OwningBusinessUnit is not null) OR ( contact.ContactId in (select POA.ObjectId from PrincipalObjectAccess POA join SystemUserPrincipals
sup on POA.PrincipalId = sup.PrincipalId where sup.SystemUserId = @P22 and POA.ObjectTypeCode = @P23 and ((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1)=1)))) order
by contact.FullName asc , contact.ContactId asc',
@type = N'OBJECT',
@module_or_batch = N'sp_executesql',
@params = NULL,
@hints = N'OPTION (HASH JOIN)';
SQL Server (2005 SP2) claims that I can't access the stored procedure sp_executesql due to permissions or that it doesn't exist. The docs don't say anything about it, anybody got any ideas?

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Try a planguide for adhoc code

@type = 'SQL', @module_or_batch = NULL

sp_executesql's just executing a string passed to it. The @type=object is for a query that's within a stored procedure.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dcristini Valued Member Points: 67 More actions · Answer 2

I tried that, it does create the plan, however when I execute the query with exec sp_execsql it doesn't use it.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Hmm. Never used plan guides, so I'm guessing.

How about the template option? That's for use by parameterised queries, so maybe...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dcristini-633990 SSChasing Mays Points: 603 More actions · Answer 4

The template option only allows me to define the form of parametrization, it doesn't allow me to include hints......

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 5

Does it work if you use a hash join hint on the appropriate joins?

dcristini-633990 SSChasing Mays Points: 603 More actions · Answer 6

I'm afraid I don't follow you, if I do the mentioned query using SQLServer Management studio and I include the hint by hand (using option (hash join)), it works fine, (and it's twice as fast as the normal query).

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 7

The version you are using now applies hash joins to the whole query, I was referring to the ability to apply the hint to a single join. Looking through the query again, I can only see one join (I had originally thought there were more) - so it makes no difference.

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 8

You've got a list of parameters inside the query. You should run your query through sp_get_query_template which will give you the correct output and the list of parameters as it would be seen within the execution plan. It looks like you've already done that for the TSQL, but then you're not showing that list of parameters in the guide definition. You have to do both.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

dcristini-633990 SSChasing Mays Points: 603 More actions · Answer 9

When creating the plan guide for an object, the parameter list must be null, I of course have the parameters since this query is sent by the aplication through sp_execsql, I used it when making the plan guide for a SQL statement.

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 10

You will have to pass them in for a SQL guide.

Here's an example:

EXEC sp_create_plan_guide @name = N'APlanGuide',

@stmt = N'SELECT * FROM [Person].[Address] WHERE [City] = @0',

@type = N'SQL',

@module_or_batch = NULL,

@params = N'@0 VARCHAR(8000)',

@hints = N'OPTION(OPTIMIZE FOR (@0 = ''Newark''))'

Since your query is going to have parameters, you'll need to pass them in the @params in order for the guide to be used appropriately.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

dcristini-633990 SSChasing Mays Points: 603 More actions · Answer 11

That was my first attempt, here it is:

exec sp_create_plan_guide

@name = N'Plan_Codigo_MIS_250',

@stmt = N'select top 251 contact.FullName as ''fullname'',contact.Telephone1 as ''telephone1'',contact.OwnerId as ''ownerid'',contact.OwnerIdDsc as

''owneriddsc'',contact.OwnerIdName as ''owneridname'',contact.OwnerIdType as ''owneridtype'',contact.OwningTeam as ''owningteam'',contact.OwningUser as

''owninguser'',contact.New_noid as ''new_noid'',contact.New_MIS as ''new_mis'',contact.New_Territorio as ''new_territorio'',contact.New_EstadoCliente as

''new_estadocliente'',contact.New_TipodeID as ''new_tipodeid'',contact.New_Oficina as ''new_oficina'',contact.New_Vinculacion as ''new_vinculacion'',''new_vinculacionname'' =

case contact.New_Vinculacion when 1 then @P1 when 2 then @P2 when 3 then @P3 when 4 then @P4 when 5 then @P5 else null end ,contact.New_Clasificacion as

''new_clasificacion'',''new_clasificacionname'' = case contact.New_Clasificacion when 1 then @P6 when 2 then @P7 when 3 then @P8 when 4 then @P9 when 5 then @P10 when 6 then

@P11 when 7 then @P12 when 8 then @P13 when 9 then @P14 when 10 then @P15 when 11 then @P16 when 12 then @P17 else null end ,contact.ContactId as ''contactid'' from Contact as

contact where contact.DeletionStateCode in (0) and ((contact.StateCode = @P18) and (contact.New_noid like @P19 or contact.New_MIS = @P20)) AND (((contact.OwningUser is null)

OR(contact.OwningUser = @P21) OR (contact.OwningBusinessUnit is not null) OR ( contact.ContactId in (select POA.ObjectId from PrincipalObjectAccess POA join SystemUserPrincipals

sup on POA.PrincipalId = sup.PrincipalId where sup.SystemUserId = @P22 and POA.ObjectTypeCode = @P23 and ((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1)=1)))) order

by contact.FullName asc , contact.ContactId asc',

@type = N'SQL',

@module_or_batch = NULL,

@params = N'@P1 nvarchar(9),@P2 nvarchar(11),@P3 nvarchar(6),@P4 nvarchar(8),@P5 nvarchar(9),@P6 nvarchar(16),@P7 nvarchar(17),@P8

nvarchar(18),@P9 nvarchar(19),@P10 nvarchar(19),@P11 nvarchar(19),@P12 nvarchar(19),@P13 nvarchar(14),@P14 nvarchar(11),@P15 nvarchar(15),@P16 nvarchar(16),@P17 nvarchar(7),@P18

int,@P19 nvarchar(7),@P20 int,@P21 uniqueidentifier,@P22 uniqueidentifier,@P23 int',

@hints = N'OPTION (HASH JOIN)';

the guide is created. However, When the app makes the query it comes out like this (I got it through SQL Profiler):

exec sp_executesql N' select top 251 contact.FullName as ''fullname'',contact.Telephone1 as ''telephone1'',contact.OwnerId as ''ownerid'',contact.OwnerIdDsc as