I am seeing blocking due to locks on compiling a particular stored procedure (as described in KB 263889). Basically, several processes are waiting with LCK_M_X on the same resource "TAB: 8:1044511100:0 [COMPILE]", and if I look up the object it is one of my stored procedures.
Trying to narrow down why the execution plan for that procedure isn't being cached, I have found that the size of the @params parameter to sp_executesql is a factor: if @params is over 4000 characters, sometimes the execution plan is cached, but if @params is 4000 characters or less the plan is cached every time.
This can be reproduced with the following procedure:
CREATE PROCEDURE [dbo].ObviouslyAnonymizedProcedure (
@SchemaId int = Null
, @TypeDesc varchar(60) = Null
, @paramFoo varchar(100) = null
, @paramBar datetime2(4) = null
, @paramBaz numeric(4,3) = null
-- Parameter names have been changed. I added a "param" prefix and set type
-- varchar(100) to more easily get up to 4000 characters in this example.
, @paramQux varchar(100) = null
, @paramQuux varchar(100) = null
, @paramCorge varchar(100) = null
, @paramGrault varchar(100) = null
, @paramGarply varchar(100) = null
, @paramWaldo varchar(100) = null
, @paramFred varchar(100) = null
, @paramPlugh varchar(100) = null
, @paramXyzzy varchar(100) = null
, @paramThud varchar(100) = null
, @paramWibble varchar(100) = null
, @paramWobble varchar(100) = null
, @paramWubble varchar(100) = null
, @paramTimey varchar(100) = null
, @paramWimey varchar(100) = null
, @paramFlob varchar(100) = null
, @paramAlpha varchar(100) = null
, @paramBeta varchar(100) = null
, @paramGamma varchar(100) = null
, @paramDelta varchar(100) = null
, @paramEpsilon varchar(100) = null
, @paramZeta varchar(100) = null
, @paramEta varchar(100) = null
, @paramTheta varchar(100) = null
, @paramIota varchar(100) = null
, @paramKappa varchar(100) = null
, @paramLambda varchar(100) = null
, @paramMu varchar(100) = null
, @paramNu varchar(100) = null
, @paramXi varchar(100) = null
, @paramOmicron varchar(100) = null
, @paramPi varchar(100) = null
, @paramRho varchar(100) = null
, @paramSigma varchar(100) = null
, @paramTau varchar(100) = null
, @paramUpsilon varchar(100) = null
, @paramPhi varchar(100) = null
, @paramChi varchar(100) = null
, @paramPsi varchar(100) = null
, @paramOmega varchar(100) = null
, @paramAlfa varchar(100) = null
, @paramBravo varchar(100) = null
, @paramCharlie varchar(100) = null
, @paramEcho varchar(100) = null
, @paramFoxtrot varchar(100) = null
, @paramGolf varchar(100) = null
, @paramHotel varchar(100) = null
, @paramIndia varchar(100) = null
, @paramJuliet varchar(100) = null
, @paramKilo varchar(100) = null
, @paramLima varchar(100) = null
, @paramMike varchar(100) = null
, @paramNovember varchar(100) = null
, @paramOscar varchar(100) = null
, @paramPapa varchar(100) = null
, @paramQuebec varchar(100) = null
, @paramRomeo varchar(100) = null
, @paramSierra varchar(100) = null
, @paramTango varchar(100) = null
, @paramUniform varchar(100) = null
, @paramVictor varchar(100) = null
, @paramWhiskey varchar(100) = null
, @paramXray varchar(100) = null
, @paramYankee varchar(100) = null
, @paramZulu varchar(100) = null
, @paramAdam varchar(100) = null
, @paramBoy varchar(100) = null
, @paramCharles varchar(100) = null
, @paramDavid varchar(100) = null
, @paramEdward varchar(100) = null
, @paramFrank varchar(100) = null
, @paramGeorge varchar(100) = null
, @paramHenry varchar(100) = null
, @paramIda varchar(100) = null
, @paramJohn varchar(100) = null
, @paramKing varchar(100) = null
, @paramLincoln varchar(100) = null
, @paramMary varchar(100) = null
, @paramNora varchar(100) = null
, @paramOcean varchar(100) = null
, @paramPaul varchar(100) = null
, @paramQueen varchar(100) = null
, @paramRobert varchar(100) = null
, @paramSam varchar(100) = null
, @paramTom varchar(100) = null
, @paramUnion varchar(100) = null
, @paramWilliam varchar(100) = null
, @paramYoung varchar(100) = null
, @paramZebra varchar(100) = null
, @paramAlf varchar(100) = null
, @paramBet varchar(100) = null
, @paramGaml varchar(100) = null
, @paramDelt varchar(100) = null
, @paramHe varchar(100) = null
, @paramWau varchar(100) = null
, @paramZai varchar(100) = null
, @paramHet varchar(100) = null
, @paramTet varchar(100) = null
, @paramYod varchar(100) = null
, @paramKaf varchar(100) = null
, @paramLamd varchar(100) = null
, @paramMem varchar(100) = null
, @paramNun varchar(100) = null
, @paramSemk varchar(100) = null
, @paramAin varchar(100) = null
, @paramPe varchar(100) = null
, @paramSade varchar(100) = null
, @paramQof varchar(100) = null
, @paramRosh varchar(100) = null
, @paramShin varchar(100) = null
, @paramAlaph varchar(100) = null
, @paramBeth varchar(100) = null
, @paramGamal varchar(100) = null
, @paramDalath varchar(100) = null
, @paramWaw varchar(100) = null
, @paramZain varchar(100) = null
, @paramHeth varchar(100) = null
, @paramTeth varchar(100) = null
, @paramYudh varchar(100) = null
, @paramKaph varchar(100) = null
, @paramLamadh varchar(100) = null
, @paramIsAggregateFunction bit = null
, @paramIsCheckConstraint bit = null
, @paramIsDefaultConstraint bit = null
, @paramIsForeignKeyConstraint bit = null
, @paramIsScalarFunction bit = null
, @paramIsCLRScalarFunction bit = null
, @paramIsCLRTableValuedFunction bit = null
, @paramIsTableValuedFunction bit = null
, @paramIsInlineTableValuedFunction bit = null
, @paramIsInternalTable bit = null
, @paramIsStoredProcedure bit = null
, @paramIsCLRProcedure bit = null
, @paramIsPlanGuide bit = null
, @paramIsPrimaryKeyConstraint bit = null
, @paramIsRule bit = null
, @paramIsReplicationFilterProcedure bit = null
, @paramIsSystemTable bit = null
, @paramIsSynonym bit = null
, @paramIsSequenceObject bit = null
, @paramIsServiceQueue bit = null
, @paramIsCLRDMLTrigger bit = null
, @paramIsDMLTrigger bit = null
, @paramIsTableType bit = null
, @paramIsTable bit = null
, @paramIsUniqueConstraint bit = null
, @paramIsView bit = null
, @paramIsExtendedStoredProcedure bit = null
, @paramStartRow int
, @paramMaxRows int
)
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @Select nvarchar(max)
DECLARE @From varchar(max)
DECLARE @Where varchar(max) = ''
DECLARE @Concat varchar(9) = ''
DECLARE @LF char(2) = char(13) + char(10)
DECLARE @Tab char(1) = char(9)
DECLARE @FieldListOuter varchar(max)
DECLARE @FieldListInner varchar(max)
Set @FieldListOuter = @LF
+ @Tab + ' t1.name'+ @LF
+ @Tab + ', t1.object_id'+ @LF
+ @Tab + ', SCHEMA_NAME(t1.schema_id) AS schema_name'+ @LF
+ @Tab + ', t1.type_desc'+ @LF
Set @FieldListInner = @LF
+ @Tab + ' t0.name'+ @LF
+ @Tab + ', t0.object_id'+ @LF
+ @Tab + ', t0.schema_id'+ @LF
+ @Tab + ', t0.type_desc'+ @LF
-- Actual FROM clause joins a few tables. This is a simplified version
-- to serve as an example.
Set @From = N' From sys.objects as t0 with(nolock) ' + @LF
If @SchemaId is NOT NULL
Begin
Set @Where = @Where + @Concat + 't0.schema_id = @SchemaId'
Set @Concat = ' And '
End
If @TypeDesc is NOT NULL
Begin
Set @Where = @Where + @Concat + 't0.type_desc = @TypeDesc'
Set @Concat = ' And '
End
-- etc...
-- Set @Where = @Where + @Concat + 't0.bar_data = @paramBar'
-- Set @Where = @Where + @Concat + 't0.baz_data = @paramBaz'
-- Set @Where = @Where + @Concat + 't0.is_table = @paramIsTable'
-- Set @Where = @Where + @Concat + 'other_table_alias.value = @param...'
If @Where <> ''
Set @Where = 'WHERE ' + @Where
Set @Select =
'SELECT ' + @FieldListOuter +
' FROM ( SELECT ROW_NUMBER() OVER (ORDER BY t0.[object_id]) AS [ROW_NUMBER], ' + @FieldListInner +
@From + @Where+') '+
' AS t1 ' +
' WHERE t1.[ROW_NUMBER] BETWEEN @paramStartRow + 1 and @paramStartRow + @paramMaxRows ORDER BY [ROW_NUMBER]'
Execute sp_executesql
@Select
, N'@SchemaId int, @TypeDesc varchar(36), @paramFoo varchar(100), @paramBar datetime2(4), @paramBaz decimal(4,3), @paramQux varchar(100), @paramQuux varchar(100), @paramCorge varchar(100), @paramGrault varchar(100), @paramGarply varchar(100), @paramWaldo varchar(100), @paramFred varchar(100), @paramPlugh varchar(100), @paramXyzzy varchar(100), @paramThud varchar(100), @paramWibble varchar(100), @paramWobble varchar(100), @paramWubble varchar(100), @paramTimey varchar(100), @paramWimey varchar(100), @paramFlob varchar(100), @paramAlpha varchar(100), @paramBeta varchar(100), @paramGamma varchar(100), @paramDelta varchar(100), @paramEpsilon varchar(100), @paramZeta varchar(100), @paramEta varchar(100), @paramTheta varchar(100), @paramIota varchar(100), @paramKappa varchar(100), @paramLambda varchar(100), @paramMu varchar(100), @paramNu varchar(100), @paramXi varchar(100), @paramOmicron varchar(100), @paramPi varchar(100), @paramRho varchar(100), @paramSigma varchar(100), @paramTau varchar(100), @paramUpsilon varchar(100), @paramPhi varchar(100), @paramChi varchar(100), @paramPsi varchar(100), @paramOmega varchar(100), @paramAlfa varchar(100), @paramBravo varchar(100), @paramCharlie varchar(100), @paramEcho varchar(100), @paramFoxtrot varchar(100), @paramGolf varchar(100), @paramHotel varchar(100), @paramIndia varchar(100), @paramJuliet varchar(100), @paramKilo varchar(100), @paramLima varchar(100), @paramMike varchar(100), @paramNovember varchar(100), @paramOscar varchar(100), @paramPapa varchar(100), @paramQuebec varchar(100), @paramRomeo varchar(100), @paramSierra varchar(100), @paramTango varchar(100), @paramUniform varchar(100), @paramVictor varchar(100), @paramWhiskey varchar(100), @paramXray varchar(100), @paramYankee varchar(100), @paramZulu varchar(100), @paramAdam varchar(100), @paramBoy varchar(100), @paramCharles varchar(100), @paramDavid varchar(100), @paramEdward varchar(100), @paramFrank varchar(100), @paramGeorge varchar(100), @paramHenry varchar(100), @paramIda varchar(100), @paramJohn varchar(100), @paramKing varchar(100), @paramLincoln varchar(100), @paramMary varchar(100), @paramNora varchar(100), @paramOcean varchar(100), @paramPaul varchar(100), @paramQueen varchar(100), @paramRobert varchar(100), @paramSam varchar(100), @paramTom varchar(100), @paramUnion varchar(100), @paramWilliam varchar(100), @paramYoung varchar(100), @paramZebra varchar(100), @paramAlf varchar(100), @paramBet varchar(100), @paramGaml varchar(100), @paramDelt varchar(100), @paramHe varchar(100), @paramWau varchar(100), @paramZai varchar(100), @paramHet varchar(100), @paramTet varchar(100), @paramYod varchar(100), @paramKaf varchar(100), @paramLamd varchar(100), @paramMem varchar(100), @paramNun varchar(100), @paramSemk varchar(100), @paramAin varchar(100), @paramPe varchar(100), @paramSade varchar(100), @paramQof varchar(100), @paramRosh varchar(100), @paramShin varchar(100), @paramAlaph varchar(100), @paramBeth varchar(100), @paramGamal varchar(100), @paramDalath varchar(100), @paramWaw varchar(100), @paramZain varchar(100), @paramHeth varchar(100), @paramTeth varchar(100), @paramYudh varchar(100), @paramKaph varchar(100), @paramLamadh varchar(100), @paramIsAggregateFunction bit, @paramIsCheckConstraint bit, @paramIsDefaultConstraint bit, @paramIsForeignKeyConstraint bit, @paramIsScalarFunction bit, @paramIsCLRScalarFunction bit, @paramIsCLRTableValuedFunction bit, @paramIsTableValuedFunction bit, @paramIsInlineTableValuedFunction bit, @paramIsInternalTable bit, @paramIsStoredProcedure bit, @paramIsCLRProcedure bit, @paramIsPlanGuide bit, @paramIsPrimaryKeyConstraint bit, @paramIsRule bit, @paramIsReplicationFilterProcedure bit, @paramIsSystemTable bit, @paramIsSynonym bit, @paramIsSequenceObject bit, @paramIsServiceQueue bit, @paramIsCLRDMLTrigger bit, @paramIsDMLTrigger bit, @paramIsTableType bit, @paramIsTable bit, @paramIsUniqueConstraint bit, @paramIsView bit, @paramIsExtendedStoredProcedure bit, @paramStartRow int, @paramMaxRows int '
, @SchemaId, @TypeDesc, @paramFoo, @paramBar, @paramBaz, @paramQux, @paramQuux, @paramCorge, @paramGrault, @paramGarply, @paramWaldo, @paramFred, @paramPlugh, @paramXyzzy, @paramThud, @paramWibble, @paramWobble, @paramWubble, @paramTimey, @paramWimey, @paramFlob, @paramAlpha, @paramBeta, @paramGamma, @paramDelta, @paramEpsilon, @paramZeta, @paramEta, @paramTheta, @paramIota, @paramKappa, @paramLambda, @paramMu, @paramNu, @paramXi, @paramOmicron, @paramPi, @paramRho, @paramSigma, @paramTau, @paramUpsilon, @paramPhi, @paramChi, @paramPsi, @paramOmega, @paramAlfa, @paramBravo, @paramCharlie, @paramEcho, @paramFoxtrot, @paramGolf, @paramHotel, @paramIndia, @paramJuliet, @paramKilo, @paramLima, @paramMike, @paramNovember, @paramOscar, @paramPapa, @paramQuebec, @paramRomeo, @paramSierra, @paramTango, @paramUniform, @paramVictor, @paramWhiskey, @paramXray, @paramYankee, @paramZulu, @paramAdam, @paramBoy, @paramCharles, @paramDavid, @paramEdward, @paramFrank, @paramGeorge, @paramHenry, @paramIda, @paramJohn, @paramKing, @paramLincoln, @paramMary, @paramNora, @paramOcean, @paramPaul, @paramQueen, @paramRobert, @paramSam, @paramTom, @paramUnion, @paramWilliam, @paramYoung, @paramZebra, @paramAlf, @paramBet, @paramGaml, @paramDelt, @paramHe, @paramWau, @paramZai, @paramHet, @paramTet, @paramYod, @paramKaf, @paramLamd, @paramMem, @paramNun, @paramSemk, @paramAin, @paramPe, @paramSade, @paramQof, @paramRosh, @paramShin, @paramAlaph, @paramBeth, @paramGamal, @paramDalath, @paramWaw, @paramZain, @paramHeth, @paramTeth, @paramYudh, @paramKaph, @paramLamadh, @paramIsAggregateFunction, @paramIsCheckConstraint, @paramIsDefaultConstraint, @paramIsForeignKeyConstraint, @paramIsScalarFunction, @paramIsCLRScalarFunction, @paramIsCLRTableValuedFunction, @paramIsTableValuedFunction, @paramIsInlineTableValuedFunction, @paramIsInternalTable, @paramIsStoredProcedure, @paramIsCLRProcedure, @paramIsPlanGuide, @paramIsPrimaryKeyConstraint, @paramIsRule, @paramIsReplicationFilterProcedure, @paramIsSystemTable, @paramIsSynonym, @paramIsSequenceObject, @paramIsServiceQueue, @paramIsCLRDMLTrigger, @paramIsDMLTrigger, @paramIsTableType, @paramIsTable, @paramIsUniqueConstraint, @paramIsView, @paramIsExtendedStoredProcedure, @paramStartRow, @paramMaxRows
END
If you execute the following, you will see a cached plan for the procedure:
DECLARE @SchemaId int = SCHEMA_ID('dbo')
exec dbo.ObviouslyAnonymizedProcedure
@paramStartRow=0
,@paramMaxRows=10
,@SchemaId=@SchemaId
,@TypeDesc=N'SQL_STORED_PROCEDURE'
SELECT OBJECT_NAME(CONVERT(int, pvt.objectid)) AS name
, pvt.refcounts
, pvt.usecounts
, pvt.set_options
, pvt.plan_handle
FROM (SELECT cp.plan_handle, cp.refcounts, cp.usecounts, a.attribute, a.value
FROM sys.dm_exec_cached_plans cp
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS a
WHERE cp.objtype = 'Proc' AND cp.cacheobjtype = 'Compiled Plan') AS cpa
PIVOT (MAX(cpa.value) FOR cpa.attribute IN (objectid, dbid, set_options)) AS pvt
WHERE pvt.dbid = DB_ID()
ORDER BY name;
But, if you then go into the sp_executesql call and add spaces to the end of the parameter list string constant and drop/add the procedure, there will (usually) not be a plan in the cache. I have seen it cache sometimes, but not reliably.
If it matters, I'm running SQL Server 2008 R2 SP1 (10.50.2500). That should have the fix on KB 2380435, which is what got me thinking to look at the size of the sp_executesql parameter list.
We are running a report that really does need this many parameters. Is this a documented limitation of SQL Server, or is there a known way to work around this?
@paramWubbleif it actually includes the parameter, or does it always mention the parameter (just might pass it as NULL)? I think there's a clever way to clean this up a lot...@selectat all).