Caching Plan of Stored Procedure

Last Post 04 Nov 2010 11:00 AM by russellb. 11 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
rerichards
New Member
New Member

--
29 Oct 2010 06:18 AM
Can anybody answer why my stored procedure is not being cached????

The server has 32 GB RAM
The Max Server Memory Setting = 26880 (26.25 GB)
The amount of memory SQL Server is using is 6157 MB.
The OS, as per Perfmon Available Mbytes averages over 1 GB
The CACHESTORE_OBJCP = 4135800 KB (3.9 GB)
 
Some more details:
1. it was created as dbo.NameOfMyStoredProcedure
2. The actual name of the stored procedure does NOT begin with sp_
3. When I execute it, the execution is like such, EXEC dbo. NameOfMyStoredProcedure
4. Profiler always shows a CacheMiss.
5. It does contain parameters, approximately 20.
6. It contains several Temp Tables created at the beginning
7. It contains a mix of normal SELECT and INSERT statements
8. It contains some Dynamic SQL executed like the following: EXEC sp_executeSQL @SQL
9. There are not any RECOMPILE hints
gunneyk
New Member
New Member

--
30 Oct 2010 04:59 AM
When you say it is being executed as EXEC dbo.xxx from what tool? Is this called from the client app? If so then it sounds like you are calling it initially as a batch instead of as an RPC. If for instance in .net you call a sp you don' use the text property but the sp instead and there is no need for an EXEC in the string that you send. So that is on possible reason you see the cache miss because you get two plans (really more since you have dynamic sql internally). One for the batch and one for the RPC that would get called when the actual sp gets run. But another and more likely scenario is that you are seeing recompiles due to the mixed DDL and DML or the temp tables. If you trace at the statement level you will see which line is causing the cache miss event. But to see if the sp is getting cached (and I am sure it is) just look in the dmv for that sp name and you should see the usecounts column increase each time you call it.

SELECT b.[bucketid], b.[cacheobjtype], b.[objtype], b.[refcounts], b.[usecounts]
, a.[dbid], a.[objectid], b.[size_in_bytes], a.[text]
FROM sys.dm_exec_cached_plans as b
CROSS APPLY sys.dm_exec_sql_text(b.[plan_handle]) AS a
ORDER BY [text]
--ORDER BY [usecounts] DESC
rerichards
New Member
New Member

--
30 Oct 2010 08:00 PM
Thanks for the reply.

The stored procedure gets called from a .Net application. I am not exactly sure how the code is constructed which executes the stored procedure within the application.

I am familiar with the script you sent. In fact, that is how I discovered this stored procedure was not getting cached.

When I could not find it in cache I executed it from SSMS. It did not get put into cache when executing it from SSMS either.

I executed it several times with Profiler running also and each time I recieve an SP:CacheMiss.

I am rather disturbed that this procedure is not getting cached. I am seeing SP:Recompiles for the various statements in Profiler, but each time I execute the stored procedure (EXEC dbo.xxx) I receive a CacheMiss, and the aforementioned script comes back empty for the stored procedure in question. It is simply not getting cached, whether called from the application or from SSMS.
gunneyk
New Member
New Member

--
31 Oct 2010 10:47 AM
Are you 100% sure there is no WITH RECOMPILE hint in the sp? I don't think I have ever actually seen a sp that didnt get put into cache that was not marked with RECOMPILE. Even if the contents make it such that it will get recompiled it still gets into the cache the first time you run it. Any chance you can post the actual code to the sp?
gunneyk
New Member
New Member

--
31 Oct 2010 12:18 PM
One more thing. Does the sp contain any security related commands or anything that deals with passwords? If so some security type commands will force a proc to not be cached.
rerichards
New Member
New Member

--
31 Oct 2010 12:52 PM
Here is the stored procedure code:

IF OBJECT_ID('rprt_sel_AOS') IS NOT NULL DROP PROCEDURE dbo.rprt_sel_AOS GO CREATE PROCEDURE dbo.rprt_sel_AOS @BaseKey varchar(8000), @TeamList varchar(8000), @RankCode int, @dateLow varchar(10), @datehigh varchar(10), @ControllCodeFilter varchar(255), @PersonLow varchar(6), @PersonHigh varchar(6), @ClikLow varchar(2), @ClikHigh varchar(2), @FindLow varchar(15), @FindHigh varchar(15), @SpecFilter varchar(255), @mastLow varchar(6), @mastHigh varchar(6), @FactLow varchar(5), @FactHigh varchar(5), @DsDe char(1), @GroupBy char(1), @CodeType char(10), @UsePerson bit = 0, @Debug bit = 0, @DescInd char(1) = null AS SET NOCOUNT ON --Holds a list of Bot keys given the Base Bot key and Base Teams CREATE TABLE #TBaseKeys ( BaseKey int, BotName varchar(40) NOT NULL DEFAULT '', RowNumber int IDENTITY(1,1) ) -- Holds a list of Specs to include CREATE TABLE #TSpecList ( BaseKey int NOT NULL, SpecFID int NOT NULL ) -- Holds a list of Principle Specs per Base key / Setting CREATE TABLE #TPrincipleSpec( BaseKey int, SettingFID int, SpecFID int ) -- Holds a list of codes to include CREATE TABLE #TPCodeList( BaseKey int, PCodeFID int, PRIMARY KEY (BaseKey, PCodeFID) ) -- Holds the end results CREATE TABLE #TResult ( RowID int IDENTITY PRIMARY KEY, BaseKey int, FullName varchar(107), Mapping char(12), Placement char(10), Descrip varchar(100), Type char(1), DS char(10), DE char(10), Measure int, Person varchar(126), Controll money, Spare money, Waste money, Spec varchar(100), Factile varchar(58), TrimGrade varchar(45), Finder varchar(118), BotName varchar(40) ) DECLARE @CtDateLow datetime, @CtDateHigh datetime, @BotName varchar(255), @FilterBySpec bit, @FilterByFinder bit, @FilterByTrimGrade bit, @FilterByFactile bit, @FilterByMasterPerson bit, @CurrentBaseKey int, @CurrentBotName varchar(40), @BaseKeyCount int, @Counter int, @CodeFilter varchar(255), @SQL nvarchar(max) -- Determine the filtering criteria IF @mastLow IS NOT NULL OR @mastHigh IS NOT NULL SET @FilterByMasterPerson = 1 ELSE SET @FilterByMasterPerson = 0 IF (@SpecFilter IS NOT NULL OR (@GroupBy = 'C')) SET @FilterBySpec = 1 ELSE SET @FilterBySpec = 0 IF ((@FindLow IS NOT NULL OR @FindHigh IS NOT NULL) OR (@GroupBy = 'D')) SET @FilterByFinder = 1 ELSE SET @FilterByFinder = 0 IF ((@ClikLow IS NOT NULL OR @ClikHigh IS NOT NULL) OR (@GroupBy = 'F')) SET @FilterByTrimGrade = 1 ELSE SET @FilterByTrimGrade = 0 IF ((@FactLow IS NOT NULL OR @FactHigh IS NOT NULL) OR (@GroupBy = 'A')) SET @FilterByFactile = 1 ELSE SET @FilterByFactile = 0 -- Default unspecified filtering variables IF @mastLow IS NULL SET @mastLow = '' IF @mastHigh IS NULL SET @mastHigh = Replicate('z', 6) IF @FindLow IS NULL SET @FindLow = '' IF @FindHigh IS NULL SET @FindHigh = Replicate('z', 15) IF @ClikLow IS NULL SET @ClikLow = '' IF @ClikHigh IS NULL SET @ClikHigh = Replicate('z', 2) IF @FactLow IS NULL SET @FactLow = '' IF @FactHigh IS NULL SET @FactHigh = Replicate('z', 5) IF @PersonLow IS NULL SET @PersonLow = '' IF @PersonHigh IS NULL SET @PersonHigh = Replicate('z', 6) IF @DescInd IS NULL SET @DescInd = '0' -- set High Date IF IsDate(@dateHigh) = 1 BEGIN SET @CtDateHigh = dbo.udf_HighDate(Cast(@dateHigh AS datetime)) END ELSE BEGIN SET @CtDateHigh = GetDate() END -- set Low Date IF IsDate(@dateLow) = 1 BEGIN SET @CtDateLow = Cast(@dateLow AS datetime) -- Assure the low date is within 15 months of the high date IF DateDiff(m, @CtDateLow, @CtDateHigh) > 15 SET @CtDateLow = Cast(Convert(char(10), DateAdd(m, -15, @CtDateHigh), 120) AS datetime) END ELSE BEGIN -- Assure the low date is within 15 months of the high date SET @CtDateLow = Cast(Convert(char(10), DateAdd(m, -15, @CtDateHigh), 120) AS datetime) END --Get a Base key list according to the values of the keys AND Teams passed in INSERT #TBaseKeys (BaseKey) SELECT * FROM dbo.udf_BuildBKTable(@TeamList, @BaseKey) -- Retrieve the list of Specs matching the Spec filter INSERT #TSpecList SELECT * FROM dbo.udf_GetSpecsFromSpecFilterCBO(@BaseKey, @TeamList, @SpecFilter) --Add key after data has already been inserted ALTER TABLE #TSpecList ADD PRIMARY KEY CLUSTERED (SpecFID, BaseKey) --Get the BotName so we only need to look this up one time UPDATE #TBaseKeys SET BotName = mg.BotName FROM #TBaseKeys lk INNER JOIN dbo.mf_PGBot mg ON lk.BaseKey = mg.BaseKey AND Bot_UID > 1 --Find out how many Base keys there are and initialize counter SELECT @BaseKeyCount = MAX(RowNumber), @Counter = 1 FROM #TBaseKeys WHILE @Counter 0 THEN Car.SpecName + '' - '' + Car.SpecCode ELSE '''' END AS Spec, CASE WHEN @FilterByFactile = 1 AND Len(IsNull(Fact.FactileCode, '''')) > 0 THEN Fact.FactileCode + '' - '' + Fact.FactileName ELSE '''' END AS Factile, CASE WHEN @FilterByTrimGrade = 1 AND Len(IsNull(FC.TrimGradeCode, '''')) > 0 THEN FC.TrimGradeCode + '' - '' + FC.[Description] ELSE '''' END AS TrimGrade, CASE WHEN @FilterByFinder = 1 AND DC.FinderCode IS NOT NULL AND @DescInd = ''0'' THEN DC.FinderCode + '' - '' + DC.InnDescription WHEN @FilterByFinder = 1 AND DC.FinderCode IS NOT NULL AND @DescInd = ''1'' THEN DC.FinderCode + '' - '' + DC.StatementDescription ELSE '''' END AS Finder, @CurrentBotName AS BotName FROM dbo.prt_RelocInfo Pt INNER JOIN dbo.atv_ControllDetail CD ON Pt.Reloc_UID = CD.RelocFID AND Pt.BaseKey = CD.BaseKey AND CD.ControllDetail_UID > 1 INNER JOIN dbo.mp_ControllCodes PCC ON CD.ControllCodeFID = PCC.ControllCode_UID AND CD.BaseKey = PCC.BaseKey INNER JOIN #TPCodeList pcl ON pcl.PCodeFID = CD.ControllCodeFID AND pcl.BaseKey = CD.BaseKey INNER JOIN dbo.ppts_Settings ppt ON CD.VisorFID = ppt.Setting_UID AND CD.BaseKey = ppt.BaseKey INNER JOIN dbo.vw_WithPR pr ON ppt.PremFID = pr.Prem_UID AND ppt.BaseKey = pr.BaseKey LEFT JOIN dbo.mf_TrimGradees FC ON CD.TrimGradeFID = FC.TrimGrade_UID AND CD.Basekey = FC.BaseKey LEFT JOIN dbo.vw_mpPR mp ' IF @UsePerson = 0 SET @SQL = @SQL + 'ON mp.Person_UID = pr.PersonFID ' ELSE SET @SQL = @SQL + 'ON mp.Person_UID = pr.PRFID ' SET @SQL = @SQL + ' AND mp.BaseKey = pr.BaseKey LEFT JOIN dbo.m_Factiles Fact ON ppt.FactileFID = Fact.Factile_UID AND ppt.BaseKey = Fact.BaseKey LEFT JOIN #TPrincipleSpec fb ON ppt.Setting_UID = fb.SettingFID AND ppt.BaseKey = fb.BaseKey LEFT JOIN #TSpecList cl ON cl.SpecFID = fb.SpecFID AND cl.BaseKey = fb.BaseKey LEFT JOIN dbo.mf_Specs Car ON fb.SpecFID = Car.Spec_UID AND fb.BaseKey = Car.BaseKey LEFT JOIN dbo.atv_lnk_ControllDetail_FinderCodes ICDDC ON CD.ControllDetail_UID = ICDDC.ControllDetailFID AND CD.Basekey = ICDDC.BaseKey AND ICDDC.CDSeq = 1 LEFT JOIN dbo.mf_FinderCodes DC ON ICDDC.FinderCodeFID = DC.FinderCode_UID AND ICDDC.Basekey = DC.BaseKey WHERE Pt.BaseKey = @CurrentBaseKey AND Pt.Reloc_UID > 1 AND CD.BaseKey = @CurrentBaseKey AND CD.Delayed = 1 AND PCC.BaseKey = @CurrentBaseKey AND ppt.BaseKey = @CurrentBaseKey ' SELECT @SQL = @SQL + CASE @CodeType WHEN 'all' THEN 'AND PCC.ProControllCodeTypeFID IN (2, 10) ' WHEN 'Controll' THEN 'AND PCC.ProControllCodeTypeFID = 2 ' WHEN 'Deny' THEN 'AND PCC.ProControllCodeTypeFID = 10 ' ELSE '' END -- IF @CodeType = 'all' SET @SQL = @SQL + 'AND PCC.ProControllCodeTypeFID IN (2, 10) ' -- IF @CodeType = 'Controll' SET @SQL = @SQL + 'AND PCC.ProControllCodeTypeFID = 2 ' -- IF @CodeType = 'Deny' SET @SQL = @SQL + 'AND PCC.ProControllCodeTypeFID = 10 ' IF @UsePerson = 0 SET @SQL = @SQL + 'AND pr.PremCode BETWEEN @PersonLow AND @PersonHigh ' ELSE SET @SQL = @SQL + 'AND pr.PRCode BETWEEN @PersonLow AND @PersonHigh ' IF @DsDe = 'E' SET @SQL = @SQL + 'AND CD.PostingDate BETWEEN @CtDateLow AND @CtDateHigh ' IF @DsDe = 'S' SET @SQL = @SQL + 'AND CD.BeginDS BETWEEN @CtDateLow AND @CtDateHigh ' IF @FilterByFinder = 1 SET @SQL = @SQL + 'AND DC.FinderCode BETWEEN @Findlow AND @FindHigh AND DC.FinderCode IS NOT NULL AND PCC.ControllCode != ''#DEFAULT'' ' IF @FilterByTrimGrade = 1 SET @SQL = @SQL + 'AND FC.TrimGradeCode BETWEEN @Cliklow AND @Clikhigh AND FC.TrimGrade_UID IS NOT NULL ' IF @FilterByFactile = 1 SET @SQL = @SQL + 'AND Fact.FactileCode BETWEEN @FactLow AND @FactHigh AND CD.ControllDetail_UID IS NOT NULL AND ppt.Setting_UID IS NOT NULL ' IF @FilterByMasterPerson = 1 SET @SQL = @SQL + 'AND mp.PremCode BETWEEN @mastLow AND @mastHigh AND mp.Person_UID IS NOT NULL ' -- Include all Specs matching the list of Specs selected IF @SpecFilter IS NOT NULL SET @SQL = @SQL + 'AND cl.SpecFID IS NOT NULL ' EXEC sp_executesql @SQL, N'@CurrentBaseKey int, @PersonLow varchar(6), @PersonHigh varchar(6), @ClikLow varchar(2), @ClikHigh varchar(2), @FindLow varchar(15), @FindHigh varchar(15), @mastLow varchar(6), @mastHigh varchar(6), @FactLow varchar(5), @FactHigh varchar(5), @CtDateLow datetime, @CtDateHigh datetime, @UsePerson bit, @FilterBySpec bit, @FilterByFinder bit, @FilterByTrimGrade bit, @FilterByFactile bit, @CurrentBotName varchar(40), @DescInd char(1)', @CurrentBaseKey, @PersonLow, @PersonHigh, @ClikLow, @ClikHigh, @FindLow, @FindHigh, @mastLow, @mastHigh, @FactLow, @FactHigh, @CtDateLow, @CtDateHigh, @UsePerson, @FilterBySpec, @FilterByFinder, @FilterByTrimGrade, @FilterByFactile, @CurrentBotName, @DescInd END --Use a different variable for ControllCodeFilter because of the loop the original CodeFilter will be used each loop. SET @CodeFilter = @ControllCodeFilter -- Clear the code filter if @CodeType = all since only Controll codes are valid for this type --This allows for all Spare and Mark-off codes to be selected from this point on. If @CodeType = 'all' SET @CodeFilter = NULL -- Retrieve Spares If @CodeType = 'Spare' OR @CodeType = 'all' BEGIN -- Retrieve a list of Spare codes to include TRUNCATE TABLE #TPCodeList INSERT #TPCodeList SELECT * FROM dbo.udf_GetSpareCodesFromFilterString(@BaseKey, @TeamList, @CodeFilter) SET @SQL = ' INSERT #TResult SELECT Pt.BaseKey, Pt.FullName AS FullName, Pt.MappingNumber AS Mapping, PPC.SpareCode AS Placement, PPC.Description AS Descrip, (CASE PD.Replica WHEN 1 THEN ''V'' ELSE '' '' END) AS [Type], Convert(char(10), PD.EntryDate, 101) AS DS, Convert(char(10), PD.EntryDate, 101) AS DE, 0 AS Measure, CASE WHEN pr.Delayed = 0 THEN ''*DEL*'' ELSE RTrim(CASE WHEN @UsePerson = 0 THEN pr.PremCode ELSE pr.PRCode END) END + '' - '' + CASE WHEN @UsePerson = 0 THEN pr.PersonName ELSE pr.PRName END AS Person, 0 AS Controll, PD.SpareAmount AS Spare, 0 AS Waste, CASE WHEN @FilterBySpec = 1 AND Len(IsNull(Car.SpecCode, '''')) > 0 THEN Car.SpecName + '' - '' + Car.SpecCode ELSE '''' END AS Spec, CASE WHEN @FilterByFactile = 1 AND Len(IsNull(Fact.FactileCode, '''')) > 0 THEN Fact.FactileCode + '' - '' + Fact.FactileName ELSE '''' END AS Factile, CASE WHEN @FilterByTrimGrade = 1 AND Len(IsNull(FC.TrimGradeCode, '''')) > 0 THEN FC.TrimGradeCode + '' - '' + FC.[Description] ELSE '''' END AS TrimGrade, '''' AS Finder, @CurrentBotName AS BotName FROM dbo.prt_RelocInfo Pt INNER JOIN dbo.atv_Spares PD ON Pt.Reloc_UID = PD.RelocFID AND Pt.BaseKey = PD.BaseKey INNER JOIN dbo.mp_SpareCodes PPC ON PD.SpareCodeFID = PPC.SpareCode_UID AND PD.BaseKey = PPC.BaseKey INNER JOIN dbo.atv_SpareDetails PD ON PD.SpareFID = PD.Spare_UID AND PD.BaseKey = PD.BaseKey AND PD.SpareDetail_UID > 1 INNER JOIN dbo.vw_WithPR pr ON PD.PremFID = pr.Prem_UID AND PD.BaseKey = pr.BaseKey INNER JOIN #TPCodeList pcl ON pcl.PCodeFID = PD.SpareCodeFID AND pcl.BaseKey = PD.BaseKey LEFT JOIN dbo.atv_ControllDetail CD ON PD.ControllDetailFID = CD.ControllDetail_UID AND PD.BaseKey = CD.BaseKey LEFT JOIN dbo.mf_TrimGradees FC ON CD.TrimGradeFID = FC.TrimGrade_UID AND CD.Basekey = FC.BaseKey LEFT JOIN dbo.ppts_Settings ppt ON CD.VisorFID = ppt.Setting_UID AND CD.BaseKey = ppt.BaseKey LEFT JOIN dbo.m_Factiles Fact ON ppt.FactileFID = Fact.Factile_UID AND ppt.BaseKey = Fact.BaseKey LEFT JOIN dbo.vw_mpPR mp ' IF @UsePerson = 0 SET @SQL = @SQL + 'ON mp.Person_UID = pr.PersonFID ' ELSE SET @SQL = @SQL + 'ON mp.Person_UID = pr.PRFID ' SET @SQL = @SQL + ' AND mp.BaseKey = pr.BaseKey LEFT JOIN #TSpecList cl ON cl.SpecFID = PD.SpecFID AND cl.BaseKey = PD.BaseKey AND PD.SpecFID > 1 LEFT JOIN dbo.mf_Specs Car ON PD.SpecFID = Car.Spec_UID AND PD.BaseKey = Car.BaseKey AND PD.SpecFID > 1 WHERE Pt.BaseKey = @CurrentBaseKey AND Pt.Reloc_UID > 1 AND PD.BaseKey = @CurrentBaseKey AND PD.EntryDate BETWEEN @CtDateLow AND @CtDateHigh AND PPC.BaseKey = @CurrentBaseKey AND PD.BaseKey = @CurrentBaseKey ' IF @UsePerson = 0 SET @SQL = @SQL + 'AND pr.PremCode BETWEEN @PersonLow AND @PersonHigh ' ELSE SET @SQL = @SQL + 'AND pr.PRCode BETWEEN @PersonLow AND @PersonHigh ' IF @FilterByTrimGrade = 1 SET @SQL = @SQL + 'AND FC.TrimGradeCode BETWEEN @Cliklow AND @Clikhigh AND CD.ControllDetail_UID IS NOT NULL AND FC.TrimGrade_UID IS NOT NULL ' IF @FilterByFactile = 1 SET @SQL = @SQL + 'AND Fact.FactileCode BETWEEN @FactLow AND @FactHigh AND CD.ControllDetail_UID IS NOT NULL AND ppt.Setting_UID IS NOT NULL ' IF @FilterByMasterPerson = 1 SET @SQL = @SQL + 'AND mp.PremCode BETWEEN @mastLow AND @mastHigh AND mp.Person_UID IS NOT NULL ' -- Include all Specs matching the list of Specs selected IF @SpecFilter IS NOT NULL SET @SQL = @SQL + 'AND cl.SpecFID IS NOT NULL ' EXEC sp_executesql @SQL, N'@CurrentBaseKey int, @PersonLow varchar(6), @PersonHigh varchar(6), @ClikLow varchar(2), @ClikHigh varchar(2), @mastLow varchar(6), @mastHigh varchar(6), @FactLow varchar(5), @FactHigh varchar(5), @CtDateLow datetime, @CtDateHigh datetime, @UsePerson bit, @FilterBySpec bit, @FilterByTrimGrade bit, @FilterByFactile bit, @CurrentBotName varchar(40)', @CurrentBaseKey, @PersonLow, @PersonHigh, @ClikLow, @ClikHigh, @mastLow, @mastHigh, @FactLow, @FactHigh, @CtDateLow, @CtDateHigh, @UsePerson, @FilterBySpec, @FilterByTrimGrade, @FilterByFactile, @CurrentBotName END -- Retrieve Mark offs IF @CodeType ='Markoff' OR @CodeType = 'all' BEGIN -- Retrieve a list of Markoff codes to include TRUNCATE TABLE #TPCodeList INSERT #TPCodeList SELECT * FROM dbo.udf_GetMarkoffCodesFromFilterString(@BaseKey, @TeamList, @CodeFilter) SET @SQL = ' INSERT #TResult SELECT Pt.BaseKey, Pt.FullName AS FullName, Pt.MappingNumber AS Mapping, PWOC.MarkOffCode AS Placement, PWOC.Description AS Descrip, (CASE WOD.Replica WHEN 1 THEN ''V'' ELSE '' '' END) AS [Type], Convert(char(10), WO.EntryDate, 101) AS DS, Convert(char(10), WO.EntryDate, 101) AS DE, 0 AS Measure, CASE WHEN pr.Delayed = 0 THEN ''*DEL*'' ELSE RTrim(CASE WHEN @UsePerson = 0 THEN pr.PremCode ELSE pr.PRCode END) END + '' - '' + CASE WHEN @UsePerson = 0 THEN pr.PersonName ELSE pr.PRName END AS Person, 0 AS Controll, 0 AS Spare, WOD.MarkOffAmount AS Waste, CASE WHEN @FilterBySpec = 1 AND Len(IsNull(Car.SpecCode, '''')) > 0 THEN Car.SpecName + '' - '' + Car.SpecCode ELSE '''' END AS Spec, CASE WHEN @FilterByFactile = 1 AND Len(IsNull(Fact.FactileCode, '''')) > 0 THEN Fact.FactileCode + '' - '' + Fact.FactileName ELSE '''' END AS Factile, CASE WHEN @FilterByTrimGrade = 1 AND Len(IsNull(FC.TrimGradeCode, '''')) > 0 THEN FC.TrimGradeCode + '' - '' + FC.[Description] ELSE '''' END AS TrimGrade, '''' AS FinderCode, @CurrentBotName AS BotName FROM dbo.prt_RelocInfo Pt INNER JOIN dbo.atv_Markoffs WO ON Pt.Reloc_UID = WO.RelocFID AND Pt.BaseKey = WO.BaseKey INNER JOIN dbo.mp_MarkOffCodes PWOC ON WO.MarkOffCodeFID = PWOC.MarkOffCode_UID AND WO.BaseKey = PWOC.BaseKey INNER JOIN dbo.atv_MarkOffDetails WOD ON WOD.MarkOffFID = WO.MarkOffs_UID AND WOD.BaseKey = WO.BaseKey AND WOD.MarkOffDetail_UID > 1 INNER JOIN dbo.vw_WithPR pr ON WOD.PremFID = pr.Prem_UID AND WOD.BaseKey = pr.BaseKey INNER JOIN #TPCodeList pcl ON pcl.PCodeFID = WO.MarkOffCodeFID AND pcl.BaseKey = WO.BaseKey LEFT JOIN dbo.atv_ControllDetail CD ON WOD.ControllDetailFID = CD.ControllDetail_UID AND WOD.BaseKey = CD.BaseKey LEFT JOIN dbo.mf_TrimGradees FC ON CD.TrimGradeFID = FC.TrimGrade_UID AND CD.Basekey = FC.BaseKey LEFT JOIN dbo.vw_mpPR mp ' IF @UsePerson = 0 SET @SQL = @SQL + 'ON mp.Person_UID = pr.PersonFID ' ELSE SET @SQL = @SQL + 'ON mp.Person_UID = pr.PRFID ' SET @SQL = @SQL + ' AND mp.BaseKey = pr.BaseKey LEFT JOIN #TPrincipleSpec fb ON CD.VisorFID = fb.SettingFID AND CD.BaseKey = fb.BaseKey LEFT JOIN #TSpecList cl ON cl.SpecFID = fb.SpecFID AND cl.BaseKey = fb.BaseKey LEFT JOIN dbo.mf_Specs Car ON fb.SpecFID = Car.Spec_UID AND fb.BaseKey = Car.BaseKey LEFT JOIN dbo.ppts_Settings ppt ON ppt.Setting_UID = CD.VisorFID AND ppt.BaseKey = CD.BaseKey INNER JOIN dbo.m_Factiles Fact ON ppt.FactileFID = Fact.Factile_UID AND ppt.BaseKey = Fact.BaseKey WHERE Pt.BaseKey = @CurrentBaseKey AND Pt.Reloc_UID > 1 AND WO.BaseKey = @CurrentBaseKey AND WO.EntryDate BETWEEN @CtDateLow AND @CtDateHigh AND PWOC.BaseKey = @CurrentBaseKey AND WOD.BaseKey = @CurrentBaseKey ' IF @UsePerson = 0 SET @SQL = @SQL + 'AND pr.PremCode BETWEEN @PersonLow AND @PersonHigh ' ELSE SET @SQL = @SQL + 'AND pr.PRCode BETWEEN @PersonLow AND @PersonHigh ' IF @FilterByTrimGrade = 1 SET @SQL = @SQL + 'AND FC.TrimGradeCode BETWEEN @Cliklow AND @Clikhigh AND CD.ControllDetail_UID IS NOT NULL AND FC.TrimGrade_UID IS NOT NULL ' IF @FilterByFactile = 1 SET @SQL = @SQL + 'AND Fact.FactileCode BETWEEN @FactLow AND @FactHigh AND CD.ControllDetail_UID IS NOT NULL AND ppt.Setting_UID IS NOT NULL ' IF @FilterByMasterPerson = 1 SET @SQL = @SQL + 'AND mp.PremCode BETWEEN @mastLow AND @mastHigh AND mp.Person_UID IS NOT NULL ' -- Include all Specs matching the list of Specs selected IF @SpecFilter IS NOT NULL SET @SQL = @SQL + 'AND cl.SpecFID IS NOT NULL ' EXEC sp_executesql @SQL, N'@CurrentBaseKey int, @PersonLow varchar(6), @PersonHigh varchar(6), @ClikLow varchar(2), @ClikHigh varchar(2), @mastLow varchar(6), @mastHigh varchar(6), @FactLow varchar(5), @FactHigh varchar(5), @CtDateLow datetime, @CtDateHigh datetime, @UsePerson bit, @FilterBySpec bit, @FilterByTrimGrade bit, @FilterByFactile bit, @CurrentBotName varchar(40)', @CurrentBaseKey, @PersonLow, @PersonHigh, @ClikLow, @ClikHigh, @mastLow, @mastHigh, @FactLow, @FactHigh, @CtDateLow, @CtDateHigh, @UsePerson, @FilterBySpec, @FilterByTrimGrade, @FilterByFactile, @CurrentBotName END IF @CodeType = 'Deny' OR @CodeType = 'all' BEGIN -- Retrieve a list of Controll codes to include TRUNCATE TABLE #TPCodeList INSERT #TPCodeList SELECT * FROM dbo.udf_GetControllCodesFromFilterString(@BaseKey, @TeamList, @ControllCodeFilter) SET @SQL = ' INSERT #TResult SELECT Pt.BaseKey, Pt.FullName AS FullName, Pt.MappingNumber AS Mapping, PCC.ControllCode AS Placement, CASE @DescInd WHEN ''0'' THEN PCC.InnDescription ELSE PCC.StatementDescription END AS Descrip, (CASE WHEN CD.Replica = 0 THEN '' '' ELSE ''V'' END) AS [Type], Convert(char(10), CD.BeginDS, 101) AS DS, Convert(char(10), CD.PostingDate, 101) AS DE, CASE WHEN CD.ReplaceFID IS NULL THEN CD.Measure ELSE -1 * CD.Measure END AS Measure, CASE WHEN pr.Delayed = 0 THEN ''*DEL*'' ELSE RTrim(CASE WHEN @UsePerson = 0 THEN pr.PremCode ELSE pr.PRCode END) END + '' - '' + CASE WHEN @UsePerson = 0 THEN pr.PersonName ELSE pr.PRName END AS Person, 0 AS Controll, 0 AS Spare, ((IsNull(CD.RelocPortion, 0)+IsNull(CD.InnPortion, 0)) * -1) AS Waste, CASE WHEN @FilterBySpec = 1 AND Len(IsNull(Car.SpecCode, '''')) > 0 THEN Car.SpecName + '' - '' + Car.SpecCode ELSE '''' END AS Spec, CASE WHEN @FilterByFactile = 1 AND Len(IsNull(Fact.FactileCode, '''')) > 0 THEN Fact.FactileCode + '' - '' + Fact.FactileName ELSE '''' END AS Factile, CASE WHEN @FilterByTrimGrade = 1 AND Len(IsNull(FC.TrimGradeCode, '''')) > 0 THEN FC.TrimGradeCode + '' - '' + FC.[Description] ELSE '''' END AS TrimGrade, CASE WHEN @FilterByFinder = 1 AND DC.FinderCode IS NOT NULL AND @DescInd = ''0'' THEN DC.FinderCode + '' - '' + DC.InnDescription WHEN @FilterByFinder = 1 AND DC.FinderCode IS NOT NULL AND @DescInd = ''1'' THEN DC.FinderCode + '' - '' + DC.StatementDescription ELSE '''' END AS Finder, @CurrentBotName AS BotName FROM dbo.prt_RelocInfo Pt INNER JOIN dbo.atv_ControllDetail CD ON Pt.Reloc_UID = CD.RelocFID AND Pt.BaseKey = CD.BaseKey AND CD.ControllDetail_UID > 1 INNER JOIN dbo.mp_ControllCodes PCC ON CD.ControllCodeFID = PCC.ControllCode_UID AND CD.BaseKey = PCC.BaseKey INNER JOIN #TPCodeList pcl ON pcl.PCodeFID = CD.ControllCodeFID AND pcl.BaseKey = CD.BaseKey INNER JOIN dbo.ppts_Settings ppt ON CD.VisorFID = ppt.Setting_UID AND CD.BaseKey = ppt.BaseKey INNER JOIN dbo.vw_WithPR pr ON ppt.PremFID = pr.Prem_UID AND ppt.BaseKey = pr.BaseKey LEFT JOIN dbo.mf_TrimGradees FC ON CD.TrimGradeFID = FC.TrimGrade_UID AND CD.Basekey = FC.BaseKey LEFT JOIN dbo.vw_mpPR mp ' IF @UsePerson = 0 SET @SQL = @SQL + 'ON mp.Person_UID = pr.PersonFID ' ELSE SET @SQL = @SQL + 'ON mp.Person_UID = pr.PRFID ' SET @SQL = @SQL + ' AND mp.BaseKey = pr.BaseKey LEFT JOIN dbo.m_Factiles Fact ON ppt.FactileFID = Fact.Factile_UID AND ppt.BaseKey = Fact.BaseKey LEFT JOIN #TPrincipleSpec fb ON ppt.Setting_UID = fb.SettingFID AND ppt.BaseKey = fb.BaseKey LEFT JOIN #TSpecList cl ON cl.SpecFID = fb.SpecFID AND cl.BaseKey = fb.BaseKey LEFT JOIN dbo.mf_Specs Car ON fb.SpecFID = Car.Spec_UID AND fb.BaseKey = Car.BaseKey LEFT JOIN dbo.atv_lnk_ControllDetail_FinderCodes ICDDC ON CD.ControllDetail_UID = ICDDC.ControllDetailFID AND CD.Basekey = ICDDC.BaseKey AND ICDDC.CDSeq = 1 LEFT JOIN dbo.mf_FinderCodes DC ON ICDDC.FinderCodeFID = DC.FinderCode_UID AND ICDDC.Basekey = DC.BaseKey WHERE Pt.BaseKey = @CurrentBaseKey AND Pt.Reloc_UID > 1 AND CD.BaseKey = @CurrentBaseKey AND PCC.ProControllCodeTypeFID = 11 AND PCC.BaseKey = @CurrentBaseKey AND ppt.BaseKey = @CurrentBaseKey ' IF @UsePerson = 0 SET @SQL = @SQL + 'AND pr.PremCode BETWEEN @PersonLow AND @PersonHigh ' ELSE SET @SQL = @SQL + 'AND pr.PRCode BETWEEN @PersonLow AND @PersonHigh ' IF @DsDe = 'E' SET @SQL = @SQL + 'AND CD.PostingDate BETWEEN @CtDateLow AND @CtDateHigh ' IF @DsDe = 'S' SET @SQL = @SQL + 'AND CD.BeginDS BETWEEN @CtDateLow AND @CtDateHigh ' IF @FilterByFinder = 1 SET @SQL = @SQL + 'AND DC.FinderCode BETWEEN @Findlow AND @FindHigh AND DC.FinderCode IS NOT NULL AND PCC.ControllCode != ''#DEFAULT'' ' IF @FilterByTrimGrade = 1 SET @SQL = @SQL + 'AND FC.TrimGradeCode BETWEEN @Cliklow AND @Clikhigh AND FC.TrimGrade_UID IS NOT NULL ' IF @FilterByFactile = 1 SET @SQL = @SQL + 'AND Fact.FactileCode BETWEEN @FactLow AND @FactHigh AND CD.ControllDetail_UID IS NOT NULL AND ppt.Setting_UID IS NOT NULL ' IF @FilterByMasterPerson = 1 SET @SQL = @SQL + 'AND mp.PremCode BETWEEN @mastLow AND @mastHigh AND mp.Person_UID IS NOT NULL ' -- Include all Specs matching the list of Specs selected IF @SpecFilter IS NOT NULL SET @SQL = @SQL + 'AND cl.SpecFID IS NOT NULL ' EXEC sp_executesql @SQL, N'@CurrentBaseKey int, @PersonLow varchar(6), @PersonHigh varchar(6), @ClikLow varchar(2), @ClikHigh varchar(2), @FindLow varchar(15), @FindHigh varchar(15), @mastLow varchar(6), @mastHigh varchar(6), @FactLow varchar(5), @FactHigh varchar(5), @CtDateLow datetime, @CtDateHigh datetime, @UsePerson bit, @FilterBySpec bit, @FilterByFinder bit, @FilterByTrimGrade bit, @FilterByFactile bit, @CurrentBotName varchar(40), @DescInd char(1)', @CurrentBaseKey, @PersonLow, @PersonHigh, @ClikLow, @ClikHigh, @FindLow, @FindHigh, @mastLow, @mastHigh, @FactLow, @FactHigh, @CtDateLow, @CtDateHigh, @UsePerson, @FilterBySpec, @FilterByFinder, @FilterByTrimGrade, @FilterByFactile, @CurrentBotName, @DescInd END SET @Counter = @Counter + 1 END --While -- Return the final results SELECT tr.BotName, tr.BaseKey, FullName, Mapping, Placement, CASE pccc.Description WHEN '' THEN '* NO CATEGORY' ELSE pccc.Description END AS ControllCodeCategory, Descrip, [Type], DS, DE, tr.Measure, Person, Controll, Spare, Waste, CASE Spec WHEN '' THEN '* NO Spec' ELSE Spec END AS Spec, CASE Factile WHEN '' THEN '* NO Factile' ELSE Factile END AS Factile, TrimGrade, Finder, Convert(char(10), @CtDateLow, 101) AS DateLow, Convert(char(10), @CtDateHigh, 101) AS DateHigh FROM #TResult tr LEFT OUTER JOIN dbo.mp_ControllCodes pcc ON tr.Placement = pcc.ControllCode AND tr.BaseKey = pcc.BaseKey LEFT OUTER JOIN dbo.mp_ControllCodeCategories pccc ON pcc.ControllCodeCategoryFID = pccc.ControllCodeCategory_UID AND pcc.BaseKey = pccc.BaseKey ORDER BY tr.BaseKey, tr.BotName, FullName -- Debugging stuff IF @Debug = 1 BEGIN PRINT ' @FilterBySpec ' + Cast(@FilterBySpec AS char(1)) PRINT ' --> "' + IsNull(@SpecFilter, '*NULL*') + '"' PRINT ' @FilterByFinder ' + Cast(@FilterByFinder AS char(1)) PRINT ' --> "' + @FindLow + '" - "' + @FindHigh + '"' PRINT ' @FilterByTrimGrade ' + Cast(@FilterByTrimGrade AS char(1)) PRINT ' --> "' + @ClikLow + '" - "' + @ClikHigh + '"' PRINT ' @FilterByFactile ' + Cast(@FilterByFactile AS char(1)) PRINT ' --> "' + @FactLow + '" - "' + @FactHigh + '"' PRINT ' @FilterByMasterPerson ' + Cast(@FilterByMasterPerson AS char(1)) PRINT ' --> "' + @mastLow + '" - "' + @mastHigh + '"' PRINT ' @CtDateLow ' + Convert(char(10), @CtDateLow, 120) PRINT ' @CtDateHigh ' + Convert(char(10), @CtDateHigh, 120) END -- Drop the temp tables used in this Proedure to free up resources DROP TABLE #TBaseKeys DROP TABLE #TSpecList DROP TABLE #TPrincipleSpec DROP TABLE #TPCodeList DROP TABLE #TResult
rerichards
New Member
New Member

--
31 Oct 2010 12:58 PM
Can you give me an example of what you mean by security related commands? Are you meaning encrypting the stored procedure call?
gunneyk
New Member
New Member

--
31 Oct 2010 01:40 PM
Wow, I will have to spend a little time looking at it but I did find another potential cause. It seems there is a bug with SQL2008 (that wont be fixed until 2011 btw) that can cause a sp to not go into the cache. Here are the particulars from Connect:

You have a stored procedure which have these characteristics:
1) The procedure has a parameter of the type nvarchar(MAX), call it @p
2) There is a SELECT from a table in the procedure.
3) In the SELECT there is an expression like @p + ','
Under these cicrumstances the procedure will not be put into the procedure
cache.

gunneyk
New Member
New Member

--
31 Oct 2010 02:06 PM
OK the code is a bit long and unfortunately the web takes out all the formatting and it is simply too time consuming to go thru it all and try to make sense of it. But there are some things I can comment on. First off the security stuff I mentioned is not the encryption but more of when you use any command that would specify a password. Just as trace blanks out the text with any events that have the word password in them they won't be cached so that someone could just read it from the cache. I don't think that is the case here. You also don't have a MAX parameter as the connect item suggests for the bug but you do use a MAX variable that you pass to sp_executesql. Now I use that all the time and haven't had an issue but maybe you just hit the right (or wrong) combination of stuff and do hit this bug. I am told the work around for the bug is to use "@p + convert(nvarchar(MAX), N',')", rather than "@p + ','".
Having said all of that I wonder if this is worth all the effort. It appears to be a report that probably doesnt get run all that often but I can't tell for sure. The thing is there can be a lot of different combinations of parameters passed in that may affect the query plan so I am not sure it's such a bad thing in this case to compile each time. You might actually end up with a better plan for each run than just reusing the previous plan that may have had totally different parameters. The other thing is that it appears the meat of the sp is actually done in the sp_executesql call anyway. That will get it's own plan separate from the one for the sp it lives in. And that one if doe correctly will be cached and reused regardless of the outer one. If you search for the text for that dynamic sql you should see a plan in the cache for it.

Andy
rerichards
New Member
New Member

--
31 Oct 2010 02:54 PM
Do you have a link that refers to the bug you are referencing? I would like to explore this potentiality some more.
gunneyk
New Member
New Member

--
31 Oct 2010 04:49 PM
Well here it is but I believe you need an account with Connect to view it.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=473880&wa=wsignin1.0
russellb
New Member
New Member

--
04 Nov 2010 11:00 AM
With all that dynamic sql and string concatenation, it's a good thing it's not getting cached
You are not authorized to post a reply.

Acceptable Use Policy