July 9, 2008 at 10:25 am
Weird behavior going on here. I create some T-sql to delete some hundreds of rows from some tables, execute it, it runs fast. I put it in a stored proc. The stored proc will go on for hours. I go to edit the stored proc, highlight code that does the work, execute, finishes in a second or two at most. Then I run the stored proc again with same parameters and it finishes fast and deletes no rows because I already deleted them. So the T-SQL inside stored proc works great, but the proc does not unless there are no rows to delete.
Anyone have any idea what is going on? BTW, this is sql server 2000.
Thanks
Peter
July 9, 2008 at 12:41 pm
Your description is opposite to my experiences. Did you check whether or not there are any other tasks running against this server?
July 9, 2008 at 2:22 pm
It is opposite of mine usually too. There are no other major tasks running. Sql Server takes 50% CPU when the proc is running. As soon as i kill it, the system is back to 98% idle.
Whatever is happening, it does not interfere the t-sql functioning outside the stored pocedure. Literally, i can run the t-sql side by side the stored proc with same parameters and the stored proc will never finish but the t-sql will finish in a second or two.
I just tried deleting the proc and recreating it. It did not help. The first time I ran the proc, it worked great. After that it has not ever finished even after several hours. Very strange. I think i am going to restart the server. I am executing as with a login that is in the System Adminstrators role so permissions are not the issue.
Thanks
July 9, 2008 at 2:58 pm
July 9, 2008 at 3:08 pm
Parameter Sniffing? I'll google it. In the meantime Here is my stored proc. It is very basic. Thanks for the help.
ALTER PROCEDURE [dbo].[usp_DeleteIlvsLmgrBatch]
@LmgrBatchId int,
@InsUser varchar(10) = Null
AS
BEGIN
SET NOCOUNT ON;
If @InsUser Is Null
Set @InsUser = [Ins Master Files].dbo.ufn_GetInsUser()
/*Declare @InsUser varchar(10)
Declare @LmgrBatchId Int
Set @InsUser = 'Amd01'
Set @LmgrBatchId = 90
*/
Declare @SeqStr varchar(20)
Set @SeqStr = '00000' + cast(@LmgrBatchId as varchar(10))
Set @SeqStr = Right(@SeqStr, 6) + '%ILVS'
Select 'Deleting For Process Sequence Matching:', @SeqStr as SeqPattern, @InsUser as InsUser
-- Clear out mfg queues
Delete
from [Ins Data Files].dbo.[mfg queue header]
where InsUser = @InsUser
AND BatchId in (
select distinct BatchId
from [mfg queue detail]
where InsUser = @InsUser AND
reference in (
Select OemSequence from [broadcast queue]
where InsUser = @InsUser AND
processsequence like @SeqStr
)
)
Select 'Count Deleted From [Mfg Queue Header]', @@RowCount
delete
from [Ins Data Files].dbo.[mfg queue detail]
where InsUser = @InsUser AND
reference in (
Select OemSequence from [broadcast queue]
where InsUser = @InsUser AND
processsequence like @SeqStr
)
Select 'Count Deleted From [Mfg Queue Detail]', @@RowCount
-- Clear out broadcast queue so load manager does not balk
delete
from [Ins Data Files].dbo.[broadcast queue]
where InsUser = @InsUser AND
processsequence like @SeqStr
Select 'Count Deleted From [Broadcast Queue]', @@RowCount
-- Clear out load header and detail so load manager does not balk
delete
from [Ins Data Files].dbo.[load header]
Where InsUser = @InsUser AND
loadid in (
select distinct ld.loadid from [load detail] ld
where InsUser = @InsUser AND
ld.processsequence like @SeqStr
)
Select 'Count Deleted From [Load Header]', @@RowCount
-- delete load details for a batch
delete
from [Ins Data Files].dbo.[load detail]
Where InsUser = @InsUser AND
processsequence like @SeqStr
Select 'Count Deleted From [Load Detail]', @@RowCount
Return 0
END
July 9, 2008 at 3:15 pm
The query optimizer is 'smart' enough to create the execution plan for a query based off of the actual data within a parameter. It can use the value of the data to create the plan based on the dispursment and selectivity of the data values in the table that the parameter will be compared against. Sometimes this causes a less than optimal plan to be generated.
Here's a quick workaround to test. In your code, assign the parameter to a local variable and then use the local variable in the DML statements. If this improves the SP, then this was a case of parameter sniffing!!
July 9, 2008 at 3:16 pm
Yep. Parameter Sniffing was it i guess. I executed it "with Recompile" and it finished right away. Great. Thank you. I will create these with Recompile option as they are not run very often and are just utility procedures.
Thanks again.
July 9, 2008 at 3:25 pm
John Rowan (7/9/2008)
The query optimizer is 'smart' enough to create the execution plan for a query based off of the actual data within a parameter. It can use the value of the data to create the plan based on the dispursment and selectivity of the data values in the table that the parameter will be compared against. Sometimes this causes a less than optimal plan to be generated.Here's a quick workaround to test. In your code, assign the parameter to a local variable and then use the local variable in the DML statements. If this improves the SP, then this was a case of parameter sniffing!!
John, I just tried this too. It fixed it as well. Less than optimal is an understatement in my case. The parameter was part of a clustered primary key in every table.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply