Proc Name changes runtime

[MartinCroft]

Hi

This is a most bizzare one! A procedure called smABC019 runs for over an hour but if you rename it to anthing else
smABC020 or smABC019db it runs in under 10 seconds.

The following things have been tried

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

sp_recompile (procedure)

Droping and recompiling, creating a new procedure with the same name that just returns SELECT 1, this run instantly then drop buffers and cache. The syscacheobjects table is being cleared out. The sql server instance has been stopped and re-started all to no avail

The easy answer is obviouly to change the name but I want to know why its doing this and it must be storing the table name / object name somewhere even when caches cleared and instance restarted. The execution display existmated plan does not bring anything back untoward, comparing SHOWPLAN_ALL etc shows no differnces

any idea's

Mart

 

[SQLDenis]

Can you post the code?

you can also create the proc like this

CREATE PROCEDURE ProcName WITH RECOMPILE
AS
.....
instead of

CREATE PROCEDURE ProcName AS
.....




Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[MartinCroft]

Unfortunetly I can't post the code, but the nothing changes but the name of the procedure, if I open the procedure that has an issue and add an x to the end of its name and compile and execute it runs ok. Potentially the code could be written different but it works for one name and not the other. there does not seem to be any logical explanation.

 

[SQLDenis]

Without changing the name try something like this

If your proc is this

create proc ProcName
@id int
as
select * from table1 where ID =@id

try changing that to

create proc ProcName
@id int
as
declare @id2 int
select @id2 =@id
select * from table1 where ID =@id2

let me know if that helps (this is called parameter sniffing and should not happen here but it's worth to try it out just in case)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[MartinCroft]

Funnily we do get issues with parameter sniffing on the odd occassion,but it wasn't that in this occasion it was a collegues who was looking at the code, I did not delve too in depth but maybe I should have as within the code it joined on a table dependant on the procedure name, the value was held in a table therefore changing the name did change how the procedure worked it wasn't noticeable in the showplan , estimated qurey plan.

Thanks for the suggestions anyhow

Mart