MS SQL - I am boggled

[KIAman]

What does the name of the stored procedure have ANYTHING to do with execution performance even though the contents of both are identical?

Please, someone tell me this is real.

For example.

exec spRunMe - this takes 2 minutes to run

exec spRunMe_Test - this takes 1 second to run

Server: Windows 2003 RC2 (NT 5.2 3790)
SQL: MS SQL 2005 (9.00.4035.00)

Any ideas?

 

[KLin]

Look at the estimated execution plan for both statements in separate query windows. ctrl-L or click query menu, and find the option in sql server management studio.

 

[KIAman]

Thanks for responding. I am twisting my team's arm to grant me SHOWPLAN access so I can see the execution paths.

But, that still begs the question, what does the name have ANYTHING to do with execution plan (assuming they are different)?!?

 

[Train]

my initial guess is that Sql Server created a cached index or cached result set, on the first run, then used that on the 2nd.

Had you run them the other way around it would still be 2 minutes, then 1 second.

 

[Cogman]

my initial guess is that Sql Server created a cached index or cached result set, on the first run, then used that on the 2nd.

Had you run them the other way around it would still be 2 minutes, then 1 second.

My guess exactly. SQL server likes to do things like caching results of recent executions.

 

[KIAman]

Wow, I took lunch and still haven't been granted SHOWPLAN.

I tried running the longer query and it still takes 2 minutes regardless. I even swapped the names and the one with _TEST is still blazingly fast. It just doesn't make any sense.

 

[Evadman]

Wow, I took lunch and still haven't been granted SHOWPLAN.

I tried running the longer query and it still takes 2 minutes regardless. I even swapped the names and the one with _TEST is still blazingly fast. It just doesn't make any sense.

Add 'WITH RECOMPILE'. (right after the 'as' in create procedure). or run this code:

execute sp_execresultset N'select ''execute sp_recompile '' +  quotename(routine_name) from information_schema.routines where  routine_type = ''procedure'' and objectproperty(object_id(routine_schema  + ''.'' + quotename(routine_name)), ''IsMSShipped'') = 0'
go

If that doesn't work because the statement is too large, run this one:

declare @FieldHolder sysname
declare @SQlToExecute nvarchar(4000)
declare Cursor_Proc cursor local fast_forward for
    select  routine_name from information_schema.routines where routine_type =  'procedure' and objectproperty(object_id(routine_schema + '.' +  quotename(routine_name)), 'IsMSShipped') = 0

open Cursor_Proc

while 1 = 1 [COLOR=green]/* do until we get an error on fetch */[/COLOR]
begin
fetch next from Cursor_Proc into @FieldHolder
    if @@error <> 0 or @@fetch_status <> 0 break
    set @SQlToExecute = N'execute sp_recompile ' + quotename(@FieldHolder)
    execute sp_executesql @SQlToExecute
end

close Cursor_Proc
deallocate Cursor_Proc
go

Dollars to donuts says the query plan is stale and recompiling it will solve the problem. Check here for the plan:

SELECT [cp].[refcounts], [cp].[usecounts], [cp].[objtype], [st].[dbid], [st].[objectid], [st].[text], [qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st
CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp

Save the queryplan XML text from that resultset with an extension of .sqlplan and you can display a graphical plan even without the SHOWPLAN access. You need VIEW SERVER STATE access to run the query I put in above.

 

[KentState]

Wow, I took lunch and still haven't been granted SHOWPLAN.

I tried running the longer query and it still takes 2 minutes regardless. I even swapped the names and the one with _TEST is still blazingly fast. It just doesn't make any sense.

Are you running both under different sessions or within the same transaction? Each will have their own query plan which depends on the name, but you can also be benefiting from cached results. Are you positive that the stored procedures are 100% identical besides the name or is that perceived by manually looking at the code?

 

[uclabachelor]

What does the name of the stored procedure have ANYTHING to do with execution performance even though the contents of both are identical?

Please, someone tell me this is real.

For example.

exec spRunMe - this takes 2 minutes to run

exec spRunMe_Test - this takes 1 second to run

Server: Windows 2003 RC2 (NT 5.2 3790)
SQL: MS SQL 2005 (9.00.4035.00)

Any ideas?

There is something wrong with the way you are testing the stored procedure.

1. Try connecting with a new connection, create the procedure, run it. Note the time.

2. Repeat #1 but give the procedure a new name.

3. Repeat 1 and 2 however many times you need to convince yourself that the name of a stored procedure has absolutely nothing to do with query times.

 

[KIAman]

Add 'WITH RECOMPILE'. (right after the 'as' in create procedure). or run this code:

execute sp_execresultset N'select ''execute sp_recompile '' +  quotename(routine_name) from information_schema.routines where  routine_type = ''procedure'' and objectproperty(object_id(routine_schema  + ''.'' + quotename(routine_name)), ''IsMSShipped'') = 0'
go

If that doesn't work because the statement is too large, run this one:

declare @FieldHolder sysname
declare @SQlToExecute nvarchar(4000)
declare Cursor_Proc cursor local fast_forward for
    select  routine_name from information_schema.routines where routine_type =  'procedure' and objectproperty(object_id(routine_schema + '.' +  quotename(routine_name)), 'IsMSShipped') = 0
 
open Cursor_Proc
 
while 1 = 1 [COLOR=green]/* do until we get an error on fetch */[/COLOR]
begin
fetch next from Cursor_Proc into @FieldHolder
    if @@error <> 0 or @@fetch_status <> 0 break
    set @SQlToExecute = N'execute sp_recompile ' + quotename(@FieldHolder)
    execute sp_executesql @SQlToExecute
end
 
close Cursor_Proc
deallocate Cursor_Proc
go

Dollars to donuts says the query plan is stale and recompiling it will solve the problem. Check here for the plan:

SELECT [cp].[refcounts], [cp].[usecounts], [cp].[objtype], [st].[dbid], [st].[objectid], [st].[text], [qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st
CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp

Save the queryplan XML text from that resultset with an extension of .sqlplan and you can display a graphical plan even without the SHOWPLAN access. You need VIEW SERVER STATE access to run the query I put in above.

Sorry it took so long to respond but Evad hit it on the head. We have several people operating on the data with frequent update statistics and the SPs required a recompile.

The "with recompile" didn't seem to resolve it while sp_recompile spRunMe did. Looking in the code for sp_recompile is the magic command "dbcc LockObjectSchema (@objname)"

I can't find a single "official" reference on what this does. The comments simply say -- BUMP SCHEMA FOR RECOMPILE --