在 SQL Server 中使用 sp_ 作为用户存储过程的前缀导致性能影响 [英] Using sp_ as prefix for user stored procedures in SQL server causing performance impact

问题描述

我们使用的是 SQL Server 2014 SP2 CU4，用户定义的存储过程以 sp_ 为前缀而不是 usp_ 对性能有什么影响.如何量化，因为我们无法使用 sql 分析工具衡量影响.我读过这个链接，它确实有影响://sqlperformance.com/2012/10/t-sql-queries/sp_prefix

请求 SQL 服务器专家对此发表评论

谢谢，妮维达

解决方案

sp_prefix 是为系统存储过程保留的，不应该用于用户存储过程..

即使你有一个带有 sp_prefix 的本地存储过程，SQL 也会首先检查 master 数据库..

Aaron Bertrand 在此处对此进行了深入介绍:

we are using SQL server 2014 SP2 CU4, what is the performance impact of having the user defined stored procedures prefixed with sp_ instead of usp_. How can it be quantified, since we are unable to measure the impact using the sql profiling tools. I had read on this link , it does have an impact ://sqlperformance.com/2012/10/t-sql-queries/sp_prefix

Would request SQL server experts to comment on it

Thanks, Nivedita

解决方案

sp_prefix is reserved for system stored procedures.It should not be used for user stored procedures..

Even though you have a local stored proc with sp_prefix,SQL will check master database first..

This has been covered in depth by Aaron Bertrand here :Is the sp_ prefix still a no-no?

Quote from article on impact:

The performance issue comes from the fact that master might be checked for an equivalent stored procedure, depending on whether there is a local version of the procedure, and whether there is in fact an equivalent object in master. This can lead to extra metadata overhead as well as an additional SP:CacheMiss event.

He also did some tests..below are the results

这篇关于在 SQL Server 中使用 sp_ 作为用户存储过程的前缀导致性能影响的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！