Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Try shortening the parameter names? –  Jon Seigel Jul 13 '12 at 22:40
    
Yeah, that's probably what we're going to have to do. I am concerned since the real parameter list is well over 4000 characters (somewhere around 5800) and keeps growing as more functionality is needed. At some point the list of data types might overflow the size by themselves. –  Tadmas Jul 13 '12 at 22:44
    
Sorry, I misunderstood what was the problem here. My first comment still applies; either way, definitely get rid of the extraneous whitespace. –  Jon Seigel Jul 13 '12 at 22:57
    
Can you explain how the app calls this? Does it only specify, say, @paramWubble if 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... –  Aaron Bertrand Jul 14 '12 at 1:54
    
...but it depends on that and if you can explain how a few of these parameters are actually used (I don't see them referenced in @select at all). –  Aaron Bertrand Jul 14 '12 at 2:00

2 Answers 2

Considering how many parameters of the same type you have, the best workaround that I can think of would be to just use a temporary table to pass in all of those nullable varchar(100) parameters.

The following demonstrates how this would be done with the example that you gave:

alter PROCEDURE [dbo].ObviouslyAnonymizedProcedure (
      @SchemaId int = Null
    , @TypeDesc varchar(60) = Null
    , @paramFoo varchar(100) = null
    , @paramBar varchar(100) = null
    , @paramBaz varchar(100) = null
    , @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

CREATE TABLE #tmpParms(pName SYSNAME, pValue varchar(100) null)
INSERT INTO #tmpParms
          SELECT 'paramFoo', @paramFoo
UNION ALL SELECT 'paramBar', @paramBar
UNION ALL SELECT 'paramBaz', @paramBaz
UNION ALL SELECT 'paramQux', @paramQux
UNION ALL SELECT 'paramQuux', @paramQuux
UNION ALL SELECT 'paramCorge', @paramCorge
UNION ALL SELECT 'paramGrault', @paramGrault
UNION ALL SELECT 'paramGarply', @paramGarply
UNION ALL SELECT 'paramWaldo', @paramWaldo
UNION ALL SELECT 'paramFred', @paramFred
UNION ALL SELECT 'paramPlugh', @paramPlugh
UNION ALL SELECT 'paramXyzzy', @paramXyzzy
UNION ALL SELECT 'paramThud', @paramThud
UNION ALL SELECT 'paramWibble', @paramWibble
UNION ALL SELECT 'paramWobble', @paramWobble
UNION ALL SELECT 'paramWubble', @paramWubble
UNION ALL SELECT 'paramTimey', @paramTimey
UNION ALL SELECT 'paramWimey', @paramWimey
UNION ALL SELECT 'paramFlob', @paramFlob
UNION ALL SELECT 'paramAlpha', @paramAlpha
UNION ALL SELECT 'paramBeta', @paramBeta
UNION ALL SELECT 'paramGamma', @paramGamma
UNION ALL SELECT 'paramDelta', @paramDelta
UNION ALL SELECT 'paramEpsilon', @paramEpsilon
UNION ALL SELECT 'paramZeta', @paramZeta
UNION ALL SELECT 'paramEta', @paramEta
UNION ALL SELECT 'paramTheta', @paramTheta
UNION ALL SELECT 'paramIota', @paramIota
UNION ALL SELECT 'paramKappa', @paramKappa
UNION ALL SELECT 'paramLambda', @paramLambda
UNION ALL SELECT 'paramMu', @paramMu
UNION ALL SELECT 'paramNu', @paramNu
UNION ALL SELECT 'paramXi', @paramXi
UNION ALL SELECT 'paramOmicron', @paramOmicron
UNION ALL SELECT 'paramPi', @paramPi
UNION ALL SELECT 'paramRho', @paramRho
UNION ALL SELECT 'paramSigma', @paramSigma
UNION ALL SELECT 'paramTau', @paramTau
UNION ALL SELECT 'paramUpsilon', @paramUpsilon
UNION ALL SELECT 'paramPhi', @paramPhi
UNION ALL SELECT 'paramChi', @paramChi
UNION ALL SELECT 'paramPsi', @paramPsi
UNION ALL SELECT 'paramOmega', @paramOmega
UNION ALL SELECT 'paramAlfa', @paramAlfa
UNION ALL SELECT 'paramBravo', @paramBravo
UNION ALL SELECT 'paramCharlie', @paramCharlie
UNION ALL SELECT 'paramEcho', @paramEcho
UNION ALL SELECT 'paramFoxtrot', @paramFoxtrot
UNION ALL SELECT 'paramGolf', @paramGolf
UNION ALL SELECT 'paramHotel', @paramHotel
UNION ALL SELECT 'paramIndia', @paramIndia
UNION ALL SELECT 'paramJuliet', @paramJuliet
UNION ALL SELECT 'paramKilo', @paramKilo
UNION ALL SELECT 'paramLima', @paramLima
UNION ALL SELECT 'paramMike', @paramMike
UNION ALL SELECT 'paramNovember', @paramNovember
UNION ALL SELECT 'paramOscar', @paramOscar
UNION ALL SELECT 'paramPapa', @paramPapa
UNION ALL SELECT 'paramQuebec', @paramQuebec
UNION ALL SELECT 'paramRomeo', @paramRomeo
UNION ALL SELECT 'paramSierra', @paramSierra
UNION ALL SELECT 'paramTango', @paramTango
UNION ALL SELECT 'paramUniform', @paramUniform
UNION ALL SELECT 'paramVictor', @paramVictor
UNION ALL SELECT 'paramWhiskey', @paramWhiskey
UNION ALL SELECT 'paramXray', @paramXray
UNION ALL SELECT 'paramYankee', @paramYankee
UNION ALL SELECT 'paramZulu', @paramZulu
UNION ALL SELECT 'paramAdam', @paramAdam
UNION ALL SELECT 'paramBoy', @paramBoy
UNION ALL SELECT 'paramCharles', @paramCharles
UNION ALL SELECT 'paramDavid', @paramDavid
UNION ALL SELECT 'paramEdward', @paramEdward
UNION ALL SELECT 'paramFrank', @paramFrank
UNION ALL SELECT 'paramGeorge', @paramGeorge
UNION ALL SELECT 'paramHenry', @paramHenry
UNION ALL SELECT 'paramIda', @paramIda
UNION ALL SELECT 'paramJohn', @paramJohn
UNION ALL SELECT 'paramKing', @paramKing
UNION ALL SELECT 'paramLincoln', @paramLincoln
UNION ALL SELECT 'paramMary', @paramMary
UNION ALL SELECT 'paramNora', @paramNora
UNION ALL SELECT 'paramOcean', @paramOcean
UNION ALL SELECT 'paramPaul', @paramPaul
UNION ALL SELECT 'paramQueen', @paramQueen
UNION ALL SELECT 'paramRobert', @paramRobert
UNION ALL SELECT 'paramSam', @paramSam
UNION ALL SELECT 'paramTom', @paramTom
UNION ALL SELECT 'paramUnion', @paramUnion
UNION ALL SELECT 'paramWilliam', @paramWilliam
UNION ALL SELECT 'paramYoung', @paramYoung
UNION ALL SELECT 'paramZebra', @paramZebra
UNION ALL SELECT 'paramAlf', @paramAlf
UNION ALL SELECT 'paramBet', @paramBet
UNION ALL SELECT 'paramGaml', @paramGaml
UNION ALL SELECT 'paramDelt', @paramDelt
UNION ALL SELECT 'paramHe', @paramHe
UNION ALL SELECT 'paramWau', @paramWau
UNION ALL SELECT 'paramZai', @paramZai
UNION ALL SELECT 'paramHet', @paramHet
UNION ALL SELECT 'paramTet', @paramTet
UNION ALL SELECT 'paramYod', @paramYod
UNION ALL SELECT 'paramKaf', @paramKaf
UNION ALL SELECT 'paramLamd', @paramLamd
UNION ALL SELECT 'paramMem', @paramMem
UNION ALL SELECT 'paramNun', @paramNun
UNION ALL SELECT 'paramSemk', @paramSemk
UNION ALL SELECT 'paramAin', @paramAin
UNION ALL SELECT 'paramPe', @paramPe
UNION ALL SELECT 'paramSade', @paramSade
UNION ALL SELECT 'paramQof', @paramQof
UNION ALL SELECT 'paramRosh', @paramRosh
UNION ALL SELECT 'paramShin', @paramShin
UNION ALL SELECT 'paramAlaph', @paramAlaph
UNION ALL SELECT 'paramBeth', @paramBeth
UNION ALL SELECT 'paramGamal', @paramGamal
UNION ALL SELECT 'paramDalath', @paramDalath
UNION ALL SELECT 'paramWaw', @paramWaw
UNION ALL SELECT 'paramZain', @paramZain
UNION ALL SELECT 'paramHeth', @paramHeth
UNION ALL SELECT 'paramTeth', @paramTeth
UNION ALL SELECT 'paramYudh', @paramYudh
UNION ALL SELECT 'paramKaph', @paramKaph
UNION ALL SELECT 'paramLamadh', @paramLamadh

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 

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

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]'

Select @Select = @Select + ';

    SELECT COUNT(*) FROM #tmpParms;
    '

    Execute sp_executesql 
        @Select
        , N'@SchemaId int, @TypeDesc varchar(36), @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, @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
share|improve this answer
    
I believe the compilation problems are due solely to binding all those parameters, so I have a low level of confidence that dumping all the parameters with that big statement will help. In fact I think the bulk that adds to the procedure body alone will only increase the size of the plan in cache and increase the risk of compilation issues, never mind we're not really sure that the parameter values will be of much use stored in that way (granted, I made the same assumption that they could still be useful in my answer). –  Aaron Bertrand Jul 14 '12 at 3:03
    
I probably shouldn't have made all the types the same. They are wildly different: ints, datetimes, decimal, money, etc. It was just easier to copy & paste varchar(100) over and over. I chose 100 because it was 3 digits and made it easier to get to 4000 characters. :) –  Tadmas Jul 14 '12 at 5:28
    
@AaronBertrand Even with all the parameters on the outer procedure, as long as I shortened the parameters on the sp_executesql call, it cached the plan. –  Tadmas Jul 14 '12 at 5:49
    
@Aaron Bertrand: As Tadmas indicates here, it was my understanding that the problem was coming from binding the parameters in the sp_executesql call, which is why I took this approach and rather than one more like your answer. –  RBarryYoung Jul 14 '12 at 12:07
    
@Tadmas: If the dataTypes are that different, then you could use sql_variant's in the #temp table definition instead. They're not usually recommended, but should be OK for this situation. –  RBarryYoung Jul 14 '12 at 12:10

I suggest a different tack altogether. Instead of naming 18,000 parameters why not make use of table-valued parameters? I'm making some leaps here about what exactly you're using all these parameters for (since you so handily anonymized them for us :-)), but if you create these types:

CREATE TYPE dbo.VarcharParameters AS TABLE
(
  ParamName  SYSNAME,
  ParamValue VARCHAR(100)
);

CREATE TYPE dbo.BitParameters AS TABLE
(
  ParamName  SYSNAME,
  ParamValue BIT
);

Then change the procedure as follows (please note the comments inline about how to deal with stuff in the TVPs):

CREATE PROCEDURE dbo.ObviouslyAnonymizedProcedure2
  @SchemaID      INT                   = NULL,
  @TypeDesc      NVARCHAR(60)          = NULL,
  @VCParams      dbo.VarcharParameters READONLY,
  @BitParams     dbo.BitParameters     READONLY,
  @paramStartRow INT,
  @paramMaxRows  INT
AS
BEGIN
  SET NOCOUNT ON;
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

  DECLARE 
    @sql      NVARCHAR(MAX) = N'',
    @From     NVARCHAR(MAX) = N'',
    @Where    NVARCHAR(MAX) = N'',
    @LF       CHAR(2)       = CHAR(13) + CHAR(10),
    @Tab      CHAR(1)       = CHAR(9),
    @FLOuter  NVARCHAR(MAX),
    @FLInner  NVARCHAR(MAX);

  DECLARE @LFTab CHAR(3) = @LF + @Tab;

  SET @FLOuter = @LFTab + '  t1.name' 
        + @LFTab + ', t1.object_id'
        + @LFTab + ', SCHEMA_NAME(t1.schema_id) AS schema_name'
        + @LFTab + ', t1.type_desc' 
        + @LF; 

  SET @FLInner = @LFTab + '  t0.name'
        + @LFTab + ', t0.object_id'
        + @LFTab + ', t0.schema_id'
        + @LFTab + ', t0.type_desc'
        + @LF; 

  SET @From = N' From sys.objects as t0 with(nolock) ' + @LF;

  IF @SchemaId IS NOT NULL
  BEGIN
    SET @Where = @Where + ' AND t0.schema_id = @SchemaId';
  END 

  IF @TypeDesc IS NOT NULL
  BEGIN
    SET @Where = @Where + ' AND t0.type_desc = @TypeDesc'
  END

  -- obviously you need a bunch more of these, and I'm making
  -- a half-educated guess about how the bit params are used:
  IF EXISTS (SELECT 1 FROM @BitParams WHERE ParamName = 'paramIsView' AND ParamValue = 1)
  BEGIN
    SET @Where += ' AND t0.type_desc = ''VIEW'''
  END

  -- and I'm not clear exactly what you're doing with the varchar params,
  -- but if you give some more clues I'm sure we can work that out too.
  -- It may be very simple to build a string from those, without having to
  -- reference every single one of them by name, depending on what they do.

  SET @sql = 'SELECT ' + @FLOuter + ' FROM ( SELECT ROW_NUMBER() OVER 
      (ORDER BY t0.[object_id]) AS rn, ' + @FLInner +
    @From + ' WHERE 1 = 1 ' + @Where + ') AS t1 
      WHERE t1.rn BETWEEN @paramStartRow + 1 
      AND @paramStartRow + @paramMaxRows ORDER BY rn;'

  EXEC sp_executesql @sql,
    N'@SchemaId INT,@TypeDesc NVARCHAR(60),@paramStartRow INT,@paramMaxRows INT',
    @SchemaID, @TypeDesc, @paramStartRow, @paramMaxRows;
END
GO

Now you can call it like this:

DECLARE @x dbo.VarcharParameters;

INSERT @x VALUES
  ('paramFoo',   'wuzzuh'),
  ('paramGamma', 'foobar');

DECLARE @y dbo.BitParameters;

INSERT @y VALUES
  ('paramIsView',  0),
  ('paramIsTable', 0);

EXEC dbo.ObviouslyAnonymizedProcedure2 
  @SchemaId      = 1, 
  @TypeDesc      = NULL, 
  @VCParams      = @x, 
  @BitParams     = @y, 
  @paramStartRow = 1, 
  @ParamMaxRows  = 20;

I won't show my results, because they'll be different from yours, but I bet the massive reduction in parameters will eliminate the compilation problems you have.

Also this is how you call this procedure from T-SQL; in order to call it from, say, C#, you'll need to use a DataTable or List or something compatible. I have an example here.

This is also much more flexible in terms of adding new parameters - you don't have to change the interface to the stored procedure, just add them to the procedure body (where relevant) and to the code that populates the data table.

Now just fill us in on what all the varchar parameters do and you might be one step closer to a solution. :-)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.