August 30, 2011 at 6:50 am
Is this possible? If so, is that the "sql_handle" parameter?
August 30, 2011 at 7:03 am
Sorry if I am being stupid but why not just run sp_recompile for just that stored proc? It will recreate the plan.
-Roy
August 30, 2011 at 7:29 am
gregory.anderson (8/30/2011)
Is this possible? If so, is that the "sql_handle" parameter?
That's it.
-- Gianluca Sartori
August 30, 2011 at 7:30 am
Or execute it with the recompile option.
exec dbo.MyProc @MyParam=MyValue WITH RECOMPILE;
Will that do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 30, 2011 at 7:31 am
Roy Ernest (8/30/2011)
Sorry if I am being stupid but why not just run sp_recompile for just that stored proc? It will recreate the plan.
Quite true, indeed. Maybe freeproccache with @plan_handle was meant for ad-hoc sql and not for stored procedures.
Thanks for pointing it out, Roy.
-- Gianluca Sartori
August 30, 2011 at 7:38 am
We upgraded to SQL Server 2008 a couple weeks ago and we flagged all stored procedures to recompile with the 'exec sp_recompile' command and it didn't help anything. Also, we updated statistics and still nothing. After doing a reboot of the SQL Server (and thus doing what dbcc freeproccache does) all of the stored procedures started performing normal again.
Was there something else going on that I'm not aware of that caused the procs to start performing like normal again?
August 30, 2011 at 7:43 am
To be more exact in what I'm working on....
We still have 1 stored procedure that is not performing well (just noticed it). It's a straight-forward procedure, just a select with about 12 columns, with 4 inner joins and 5 left outer joins. Two of the columns call a function to get their value. If I run the proc as-is, it takes over 2.5 minutes with no results (because it's still running). We have a 30-second timeout in our system so there's no point in letting it run. If I change it to do a Select *, it runs in a split second. My first thought is it's one of the functions that's causing the issue, but don't know for sure.
Here's what the code looks like:
ALTER Procedure [dbo].[RPTGetCustomerRegistrationSummary]
(
@Cust_Noint,
@fromDate datetime,
@toDate datetime
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF (@fromDate IS NULL)
SET @fromDate= DBO.MinDateTime()
IF (@toDate IS NULL)
SET @toDate = DBO.MaxDateTime()
declare @currentDate datetime
DECLARE @CustType_Individual int
DECLARE @CustNameType_Legal int
DECLARE @AddrType_Residency int
DECLARE @VestmentType_Owner int
set @currentDate = GetDate()
SET @CustType_Individual = dbo.CDV('CustType', 'Individual')
SET@CustNameType_Legal = dbo.CDV('CustNameType', 'Legal')
SET@AddrType_Residency = dbo.CDV('AddrType', 'Residency')
SET@VestmentType_Owner = dbo.CDV('VestmentType', 'Owner')
SELECT
VR.Veh_Reg_ID as [Veh_Reg_ID],
VR.Reg_Start_Dtas [RegStartDt],
VR.Reg_End_Dt as [RegEndDt],
VRDT.Val as [RegStatus],
VR.Disp_Dtas [StatusDate],
Veh.VIN as [VIN],
VehT.Val as [VehicleType],
Veh.Year as [Year],
Make.Val as [Make],
Model.Val as [Model],
[PrimaryOwner] = dbo.GetCustomerLegalName (vp.cust_no, getdate()),
[CustAddr] = dbo.GetCustomerResidencyAddress (vp.cust_no, getdate()),
VR.Reg_Month as [RegMonth],
RIV.Val as [RegInterval],
VR.Updated_By AS [UpdatedBy],
VR.Updated_DtAS [UpdatedDt]
FROM
Veh_Reg VR
INNER JOIN Veh_Ownsp VO on VR.Veh_Ownsp_ID = VO.Veh_Ownsp_ID
LEFT OUTER JOIN V_Reg_Disp_Type VRDT on VRDT.V_Reg_Disp_Type_ID = VR.V_Reg_Disp_Type_ID
INNER JOIN Vested_Party VP on VP.Veh_Ownsp_ID = VR.Veh_Ownsp_ID
AND vp.Vestment_Type_Id = @VestmentType_Owner
AND vp.Priority = 1
AND vp.End_DT IS NULL
INNER JOIN Vestment_Type vt ON vt.Vestment_Type_Id = vp.Vestment_type_ID
LEFT OUTER JOIN Reg_Interval RIV ON VR.Reg_Interval_ID = RIV.Reg_Interval_ID
LEFT OUTER JOINVeh ON VO.Veh_No = Veh.Veh_No
AND Veh.End_DT IS NULL
INNER JOIN Veh_Type VehT ON VehT.Veh_Type_Id = Veh.Veh_Type_Id
LEFT OUTER JOIN Make On Veh.Make_Id = Make.Make_ID
LEFT OUTER JOIN Model On Veh.Model_Id = Model.Model_ID
WHERE
VR.Reg_Start_DT <= @toDate
AND (VR.Reg_End_DT IS NULL OR VR.Reg_End_DT >= @fromDate)
AND @Cust_No in (Select svp.cust_no from Vested_Party svp where svp.veh_ownsp_id = vo.veh_ownsp_id)
ORDER BY
VR.Reg_Start_DT
August 30, 2011 at 8:13 am
Can you check what is the plan difference when it is Select * vs Select column list? That should point you in the right directions.
-Roy
September 1, 2011 at 11:21 pm
I would recommand you to Comment the two function calls from the selecting resultset and see the result how much time ti takes to execute. Probabaly it might due to the function calls. Not sure as the execution plan is missing in you post.
September 2, 2011 at 1:39 am
Use the following command to list all the plans:
SELECT [text], cp.size_in_bytes, plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype = N'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;
If you want to remove some plan from cache, then you can find the plan_handle from aboe and drop it by usign the following command:
DBCC FREEPROCCACHE(plan_handle)
Thanks
Chandan
September 2, 2011 at 2:14 am
so many non answers! Yes Greg you can do exactly what you want like this:
--
-- get proc handle
--
SELECT plan_handle, st.text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'%dbo.name_of_the_proc_here';
GO
--
-- now drop this proc from cache dbcc freeproccache(plan_handle);
-- put your own handle here - no quotes
--
dbcc freeproccache(0x0500070002F70F35404120D4090000000000000000000000);
go
incidentally undocumented dbcc flushprocindb(db_id); will remove all plans for a specific database id.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 2, 2011 at 12:28 pm
Sounds suspiciously like a parameter sniffing option. Check out this article.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 13, 2012 at 9:10 am
Much easier method (SQL 2005 and on):
-- Clear procedure from the cache
sp_recompile 'schema.ObjectName'
Clears plans from the cache for specified stored procedure or trigger. If table or view is specified, all procedures and triggers that reference this table/view are cleared from the cache. Object is searched in current db only.
Reference: http://msdn.microsoft.com/en-us/library/ms181647.aspx
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply