This is fairly easy to test yourself. Let's create two very simple procedures:
CREATE PROCEDURE dbo.sp_mystuff
AS
SELECT 'x';
GO
CREATE PROCEDURE dbo.mystuff
AS
SELECT 'x';
GO
Now let's build a wrapper that executes them a number of times, with and without the schema prefix:
CREATE PROCEDURE dbo.wrapper_sp1
AS
BEGIN
SET NOCOUNT ON;
DECLARE @i INT = 1;
WHILE @i <= 1000
BEGIN
EXEC sp_mystuff;
SET @i += 1;
END
END
GO
CREATE PROCEDURE dbo.wrapper_1
AS
BEGIN
SET NOCOUNT ON;
DECLARE @i INT = 1;
WHILE @i <= 1000
BEGIN
EXEC mystuff;
SET @i += 1;
END
END
GO
CREATE PROCEDURE dbo.wrapper_sp2
AS
BEGIN
SET NOCOUNT ON;
DECLARE @i INT = 1;
WHILE @i <= 1000
BEGIN
EXEC dbo.sp_mystuff;
SET @i += 1;
END
END
GO
CREATE PROCEDURE dbo.wrapper_2
AS
BEGIN
SET NOCOUNT ON;
DECLARE @i INT = 1;
WHILE @i <= 1000
BEGIN
EXEC dbo.mystuff;
SET @i += 1;
END
END
GO
Results:
Conclusions:
- using sp_ prefix is slower
- leaving out schema prefix is slower
The more important question: why would you want to use the sp_ prefix? What do your co-workers expect to gain from doing so? This shouldn't be about you having to prove that this is worse, it should be about them justifying adding the same three-letter prefix to every single stored procedure in the system. I fail to see the benefit.
Also I performed some pretty extensive testing of this pattern in the following blog post:
http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix
I did a DuckDuckGo.com search for the like_i_sql_unicode_string
since there appears to be no documentation that describes it included with SQL Server, and came across this Connect item, https://connect.microsoft.com/SQLServer/feedback/details/699053/sql-batch-completed-event-returns-0-for-row-count#details - it contains a sample extended event in the "steps to reproduce" section of the Connect item. In the sample extended event is this text, which wraps the search terms in question in the standard SQL Server wildcard, %
.
WHERE ([sqlserver].[like_i_sql_unicode_string]([batch_text],N'%sys.objects%')))
I modified my extended event definition like:
DROP EVENT SESSION FindCaller ON SERVER;
CREATE EVENT SESSION [FindCaller] ON SERVER
ADD EVENT sqlserver.sql_batch_starting
(
ACTION
(
sqlserver.client_app_name
, sqlserver.client_hostname
, sqlserver.database_name
, sqlserver.nt_username
, sqlserver.session_id
, sqlserver.sql_text
)
WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ObjectInQuestion%'))
)
WITH
(
MAX_MEMORY=4096 KB
, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS
, MAX_DISPATCH_LATENCY=30 SECONDS
, MAX_EVENT_SIZE=0 KB
, MEMORY_PARTITION_MODE=NONE
, TRACK_CAUSALITY=OFF
, STARTUP_STATE=OFF
);
Now, when I 'Watch Live Data' I see the culprit statements, along with the actual SQL text being executed, the name of the application, the client machine name, etc. Quite useful.
The take-home for me is the Extended Event GUI needs better documentation!
Best Answer
Include the
module_start
event filtered on the stored procedure name and a histogram target bucket based on the object_name field. This will capture and aggregate the execution counts every time the stored procedure is executed, whether it is called directly or indirectly (i.e. called other stored procedures and triggers).Note that if the same stored procedure name exists in more than one database or schema, the different procs will be counted as one with this method. An additional filter on the database can be used to limit counting to only the desired database.