December 17, 2010 at 3:15 am
Hi
I have a proc as pasted below. When I execute it, I can see that plan_handle is generated using this query:
select * from sys.dm_exec_cached_plans cross apply sys.dm_exec_sql_text(plan_handle) where dbid=db_id()
The generated plan_handle is:0x05002100D9F11B1FB8C1B83D000000000000000000000000
Now I try to see the corresponding plan using:
select * from sys.dm_exec_query_plan(0x05002100D9F11B1FB8C1B83D000000000000000000000000)
But I get query_plan as NULL. I get it for rest of procs, but for this particular proc, I dont get any plan. I believe if plan handle is there , then there should be a corresponding plan as well. I checked in profiler and there are no recompiles.
The proc is sth like this:
ALTER PROCEDURE [dbo].[p_Get_MyName]
@MyClient int = 0,
@Pvd1 int = -1,
@p_Last_Name VARCHAR(35) = '',
@p_First_Name VARCHAR(25) = '',
@Page_Number INT = 1,
@Page_Size INT = 10 ,
@Sort_Order int = 1 ,
@Sort_Direction BIT = 1
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @Last_Name VARCHAR(35),
@First_Name VARCHAR(25),
@StartRowIndex INT
SET @Last_Name = @p_Last_Name + '%'
SET @First_Name = @p_First_Name + '%'
SET @StartRowIndex = ((@Page_Number * @Page_Size) - @Page_Size)
;WITH List_Account_Card AS (SELECT CASE
WHEN @Sort_Order = 5 AND @Sort_Direction = 1 THEN ROW_NUMBER() OVER(ORDER BY RIGHT(LTRIM(RTRIM(Card_nbr)),4) DESC)
WHEN @Sort_Order = 6 AND @Sort_Direction = 0 THEN ROW_NUMBER() OVER(ORDER BY RIGHT(LTRIM(RTRIM(Card_nbr)),4) ASC )
END AS Row,
COLA,
COLB
FROM dbo.MyTable c
WHERE CASE @MyClient WHEN 0 THEN @MyClient ELSE c.Client_ID END = @MyClient
AND
case @Pvd1
when -1 then @Pvd1
else c.Pvd_1
end = @Pvd1
AND
c.Last_Name LIKE @Last_Name
AND
c.First_Name LIKE @First_Name
SELECT
Row,
ColA
FROM
List_Account_Card
WHERE
Row BETWEEN (@StartRowIndex +1) AND (@StartRowIndex + @Page_Size)
ORDER BY Row
RETURN
Any inputs are appreciated.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
December 17, 2010 at 4:04 am
Some query plans can get out of the cache when the server is under memory pressure.
It could be something to check.
-- Gianluca Sartori
December 17, 2010 at 4:20 am
There is no memory pressure. Its a development box and I am the only person doing work on it. And also if I execute other procs and check their plans I can see them. I even tried getting its plan after doing a dbcc freeproccache and dbcc dropcleanbuffers, but no luck....
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply