访问 sp_ 过程时应该使用 master.dbo 吗? [英] Should you use master.dbo when accessing sp_ procedures?

问题描述

我 100% 确信这是重复的，但经过一个多小时的搜索，我似乎无法找到答案.

当使用特殊程序时(即 sp_ 程序，如 sp_executesql)，使用完整的 3 部分标识符 master.dbo 是否明智>(或 master..)还是直接使用它们?我正在寻找性能最优化的版本:

<代码>1.sp_executesql2. 掌握..sp_executesql3.master.dbo.sp_executesql

2 和 3 在性能方面是否完全相同(即引用 master)，并且对用户 master.. 是否安全，或者您不应该冒险即使在 master 上，因为有人仍然可以在某个时候在那里创建另一个模式?

非常感谢.

解决方案

TL;DR;
应该没有任何明显的性能差异.

长长的故事:
每当您执行以 sp_ 前缀开头的存储过程时，SQL Server 将首先在 master.dbo 中搜索它，因此所有三个选项都应该具有相同的性能.

摘自 Eric Cobb 于 2015 年发表的一篇题​​为 为什么不应该在存储过程前加上sp_"

<块引用>

每当 SQL Server 在存储过程的开头看到sp_"时，它首先会尝试在 master 数据库中查找该过程.如上面的 Microsoft 文档所述，SQL Server 使用此前缀来指定系统过程"，因此当 SQL Server 看到sp_"时，它开始寻找系统过程.只有在它搜索完主数据库中的所有过程并确定您的过程不存在后，它才会返回到您的数据库尝试定位存储过程.

此外，它引用了另一个官方文档(带有指向 2008 版本的链接，正在寻找当前版本):

<块引用>

与系统存储过程同名的用户定义存储过程，无论是非限定的还是在 dbo 模式中的，都将永远不会被执行；系统存储过程将始终执行.

那句话，即使我在当前版本的文档中找不到，我也可以轻松证明.
考虑以下脚本:

USE -- 当然，改为数据库的实际名称走创建程序 dbo.sp_who作为SELECT 'Zohar peled' 作为我的名字走-- 当然，改为数据库的实际名称EXEC <YourDatabaseNameHere>.dbo.sp_who执行 dbo.sp_whoEXEC sp_who走删除程序 dbo.sp_who -- 清理

在 2016 版本(这是我可用于测试的服务器)上进行测试时，所有三个 exec 语句都执行了系统过程.我找不到任何方法来执行我的程序.

现在我不能摆弄服务器上的 master 数据库，所以我只能证明它适用于现有系统过程，但我很漂亮确保任何以 sp_ 前缀开头的过程都是相同的，即使您自己将它写入主数据库和您自己的数据库，正如 Aaron Bertrand 在 他的文章，标题为另一个副作用:歧义

然而，即使情况并非如此，除非当前模式中有很多过程，并且在紧密循环中运行存储过程，否则我怀疑您会看到任何明显的性能差异.
稍后在同一篇文章中:

<块引用>

正如前一点所提到的，以sp_"命名的过程将执行得更慢.它可能并不总是引人注目，但它就在那里.连接到 DatabaseA，跳转到主数据库并扫描那里的每个存储过程，然后返回到 DatabaseA 并执行该过程总是比仅仅连接到 DatabaseA 并执行该过程花费更多的时间.

请注意，本段讨论的是执行具有 sp_ 前缀的用户定义存储过程的性能问题 - 所以让我们暂时颠倒这个过程:

假设 SQL Server 必须扫描当前架构中的所有存储过程，然后才会在未找到的情况下转到 Master.Dbo 并开始查找.
很容易看到架构中的过程越多，所需的时间就越长.但是 - 您有没有注意到 SQL Server 需要多长时间才能找到它需要运行的过程?自 SQL Server 2000 版本以来，我一直在使用它，并且我共享的包含数百个过程的数据库都挤在同一个架构中 - 但这从来都不是性能问题.
事实上，在超过 15 年的 SQL Server 使用经验中，我从未遇到过由于 SQL Server 需要时间来找到它需要运行的存储过程而导致的性能问题.

I'm 100% convinced this is a duplicate but after more than an hour of searching I just can't seem to find the answer.

When using special procedures (i.e. the sp_ ones like sp_executesql), is it wise to use the full 3-part identifier master.dbo (or master..) or just use them as is? I'm looking for the most performance optimized version of this:

1. sp_executesql
2. master..sp_executesql
3. master.dbo.sp_executesql

Are 2 and 3 identical in terms of performance specifically regarding the above (i.e. referencing master) and is it safe to user master.. or should you not risk it even on master since someone could still create another schema there at some point?

Much appreciated.

解决方案

TL;DR;
Shouldn't be any noticeable performance difference.

The long story:
Whenever you are executing a stored procedure that starts with the sp_ prefix SQL Server will first search for it in master.dbo, so all three options should have the same performance.

From an article posted by Eric Cobb in 2015 entitled Why you should not prefix your stored procedures with "sp_"

Whenever SQL Server sees "sp_" at the beginning of a stored procedure, it first tries to find the procedure in the master database. As stated in the Microsoft documentation above, "This prefix is used by SQL Server to designate system procedures", so when SQL Server sees "sp_" it starts looking for system procedures. Only after it has searched through all of the procedures in the master database and determined that your procedure is not there will it then come back to your database to try to locate the stored procedure.

Also, it quotes another official documentation (with a link to 2008 version, working on finding current version):

A user-defined stored procedure that has the same name as a system stored procedure and is either nonqualified or is in the dbo schema will never be executed; the system stored procedure will always execute instead.

That quote, even though I couldn't find in the documentation of current version, I can easily prove.
Consider the following script:

USE <YourDatabaseNameHere> -- change to the actual name of the db, of course
GO

CREATE PROCEDURE dbo.sp_who
AS
    SELECT 'Zohar peled' as myName
GO

-- change to the actual name of the db, of course
EXEC <YourDatabaseNameHere>.dbo.sp_who
EXEC dbo.sp_who
EXEC sp_who

GO

DROP PROCEDURE  dbo.sp_who  -- cleanup

When tested on 2016 version (which is the server I've had available for testing), All three exec statements executed the system procedure. I couldn't find any way to execute my procedure.

Now I can't fiddle around with the master DB on my server, so I can only show that it's true for existing system procedures, but I'm pretty sure that it's going to be the same for any procedure that starts with the sp_ prefix, even if you wrote it yourself to both the master database and your own, as Aaron Bertrand illustrated on his article under the title Another side effect : Ambiguity

However, even if that wasn't the case, unless having many procedures in the current schema, and running the stored procedure in a tight loop, I doubt you'll see any noticeable performance difference.
Later on in the same article:

As alluded to in the previous point, procedures named with "sp_" are going to perform slower. It may not always be noticeable, but it is there. Connecting to DatabaseA, jumping over to the master database and scanning every stored procedure there, then coming back to DatabaseA and executing the procedure is always going to take more time than just connecting to DatabaseA and executing the procedure.

Note that this paragraph is talking about performance issues executing a user-defined stored procedure that has the sp_ prefix - so let's reverse this process for a moment:

Suppose SQL Server would have to scan all the stored procedures in the current schema, and only then, if not found, go to Master.Dbo and start looking there.
Easy to see the more procedures you have in the schema the longer it takes. However - have you ever noticed how long it takes SQL Server to find the procedure it needs to run? I've been working with SQL Server since it's 2000 version, and I've had my share of databases containing hundreds of procedures all cramped up in the same schema - but that was never a performance issue.
In fact, in over 15 years of experience with SQL Server, I've never encountered a performance issue caused by the time it takes SQL Server to find the stored procedure it needs to run.

这篇关于访问 sp_ 过程时应该使用 master.dbo 吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！