SQL Server proc运行速度比普通查询慢5倍

I have the following query:

我有以下查询:

DECLARE @DaysNotUsed int = 14
DECLARE @DaysNotPhoned int = 7

--Total Unique Students
DECLARE @totalStudents TABLE (SchoolID uniqueidentifier, TotalUniqueStudents int)
INSERT INTO @totalStudents
SELECT 
        SSGG.School,
        COUNT(DISTINCT S.StudentID)
    FROM Student S
        INNER JOIN StudentStudents_GroupGroups SSGG ON (SSGG.Students = S.StudentID AND SSGG.School = S.School)
        INNER JOIN [Group] G ON (G.GroupID = SSGG.Groups AND G.School = SSGG.School)
        INNER JOIN SessionHistory SH ON (SH.Student = S.StudentID AND SH.School = S.School AND SH.StartDateTime > GETDATE() - @DaysNotUsed)
    WHERE G.IsBuiltIn = 0
        AND S.HasStartedProduct = 1
    GROUP BY SSGG.School

--Last Used On
DECLARE @lastUsed TABLE (SchoolID uniqueidentifier, LastUsedOn datetime)
INSERT INTO @lastUsed
SELECT
        vi.SchoolID,
        MAX(sh.StartDateTime)
    FROM View_Installation as vi
        INNER JOIN SessionHistory as sh on sh.School = vi.SchoolID
    GROUP BY vi.SchoolID

SELECT 
        VI.SchoolID, 
        INS.DateAdded,
        INS.Removed,
        INS.DateRemoved,
        INS.DateToInclude,
        VI.SchoolName AS [School Name], 
        VI.UsersLicensed AS [Licenses],
        ISNULL(TS.TotalUniqueStudents, 0) as [Total Unique Students],
        ISNULL(TS.TotalUniqueStudents, 0) * 100 / VI.UsersLicensed as [% of Students Using],
        S.State,
        LU.LastUsedOn,
        DATEDIFF(DAY, LU.LastUsedOn, GETDATE()) AS [Days Not Used],
        SI.AreaSalesManager AS [Sales Rep],
        SI.CaseNumber AS [Case #],
        SI.RenewalDate AS [Renewal Date],
        SI.AssignedTo AS [Assigned To],
        SI.Notes AS [Notes]
    FROM View_Installation VI
        INNER JOIN School S ON S.SchoolID = VI.SchoolID
        LEFT OUTER JOIN @totalStudents TS on TS.SchoolID = VI.SchoolID
        INNER JOIN @lastUsed LU on LU.SchoolID = VI.SchoolID
        LEFT OUTER JOIN InactiveReports..SchoolInfo SI ON S.SchoolID = SI.SchoolID
        LEFT OUTER JOIN InactiveReports..InactiveSchools INS ON S.SchoolID = INS.SchoolID
    WHERE VI.UsersLicensed > 0
        AND VI.LastPhoneHome > GETDATE() - @DaysNotPhoned
        AND
        (
            (
                SELECT COUNT(DISTINCT S.StudentID)
                    FROM Student S
                        INNER JOIN StudentStudents_GroupGroups SSGG ON (SSGG.Students = S.StudentID AND SSGG.School = S.School)
                        INNER JOIN [Group] G ON (G.GroupID = SSGG.Groups AND G.School = SSGG.School)
                    WHERE G.IsBuiltIn = 0
                        AND S.School = VI.SchoolID
            ) * 100 / VI.UsersLicensed < 50
            OR
            VI.SchoolID NOT IN 
            (
                SELECT DISTINCT SH1.School
                FROM SessionHistory SH1
                WHERE SH1.StartDateTime > GETDATE() - @DaysNotUsed
            ) 
        )
    ORDER BY [Days Not Used] DESC

Running just plain sql like this in SSMS take about 10 seconds to run. When I created a stored procedure with exactly the same code, the query takes 50 seconds instead. The only difference in the actual code of the proc is a SET NOCOUNT ON that the IDE put in by default, but adding that line to the query doesn't have any impact. Any idea what would cause such a dramatic slow down like this?

在SSMS中运行这样的普通sql需要大约10秒才能运行。当我使用完全相同的代码创建存储过程时,查询需要50秒。 proc的实际代码中唯一的区别是IDE默认放入的SET NOCOUNT ON,但是将该行添加到查询中没有任何影响。有什么想法会导致如此戏剧性的减速吗?

EDIT I neglected the declare statements at the beginning. These are not in the proc, but are parameters to it. Could this be the difference?

编辑我在开头忽略了声明语句。这些不在proc中,而是它的参数。这可能是区别吗?

4 个解决方案

#1

I agree about the potential parameter sniffing issue, but I would also check these settings.

我同意潜在的参数嗅探问题,但我也会检查这些设置。

For the procedure:

对于程序:

SELECT uses_ansi_nulls, uses_quoted_identifier
  FROM sys.sql_modules
  WHERE [object_id] = OBJECT_ID('dbo.procedure_name');

For the SSMS query window where the query is running fast:

对于查询运行速度快的SSMS查询窗口:

SELECT [ansi_nulls], [quoted_identifier]
  FROM sys.dm_exec_sessions
  WHERE session_id = @@SPID;

If either of these don't match, you might consider dropping the stored procedure and re-creating it with those two settings matching. For example, if the procedure has uses_quoted_identifier = 0 and the session has quoted_identifier = 1, you could try:

如果其中任何一个不匹配,您可以考虑删除存储过程并使用这两个匹配的设置重新创建它。例如,如果过程具有uses_quoted_identifier = 0且会话的quoted_identifier = 1,则可以尝试:

DROP PROCEDURE dbo.procedure_name;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.procedure_name
AS
BEGIN
    SET NOCOUNT ON;
    ...
END
GO

Ideally all of your modules will be created with the exact same QUOTED_IDENTIFIER and ANSI_NULLS settings. It's possible the procedure was created when the settings were off (the default is on for both), or it's possible that where you are executing the query, one or both options are off (you can change this behavior in SSMS under Tools/Options/Query Execution/SQL Server/ANSI).

理想情况下,所有模块都将使用完全相同的QUOTED_IDENTIFIER和ANSI_NULLS设置创建。设置关闭时可能会创建该过程(两者都默认为打开),或者您执行查询的位置可能会关闭一个或两个选项(您可以在工具/选项/下的SSMS中更改此行为)查询执行/ SQL Server / ANSI)。

I'm not going to make any disclaimers about the behavior of the stored procedure with the different settings (for example you may have wanted ANSI_NULLS off so you could compare NULL = NULL), that you'll have to test, but at least you'll be comparing queries that are being run with the same options, and it will help narrow down potential parameter sniffing issues. If you're intentionally using SET ANSI_NULLS OFF, however, I caution you to find other approaches as that behavior will eventually be unsupported.

我不打算用不同的设置制作关于存储过程行为的任何免责声明(例如,你可能希望ANSI_NULLS关闭以便你可以比较NULL = NULL),你必须测试,但至少你将使用相同的选项比较正在运行的查询,这将有助于缩小潜在的参数嗅探问题。但是,如果您故意使用SET ANSI_NULLS OFF,我提醒您找到其他方法,因为最终将不支持该行为。

Other ways around parameter sniffing:

参数嗅探的其他方法:

• make sure you don't inadvertently compile the procedure with atypical parameters

确保不会无意中使用非典型参数编译过程

• use the recompile option either on the procedure or on the statement that seems to be the victim (I'm not sure if all of these are valid, because I can only tell that you are using SQL Server 2005 or greater, and some of these were introduced in 2008)

在程序或似乎是受害者的语句上使用重新编译选项(我不确定所有这些是否有效,因为我只能告诉您使用的是SQL Server 2005或更高版本,以及其中一些于2008年推出)

• declare local variables similar to your input parameters, and pass the input parameter values to them, using the local variables later in the prodedure and ignoring the input parameters

声明类似于输入参数的局部变量,并将输入参数值传递给它们,稍后使用文档中的局部变量并忽略输入参数

The last option is my least favorite, but it's the quickest / easiest fix in the midst of troubleshooting and when users are complaining.

最后一个选项是我最不喜欢的,但它是在故障排除和用户抱怨时最快/最简单的解决方案。

#2

Also, in addition to everything else mentioned, if you are on SQL Server 2008 and up, have a look at OPTIMIZE FOR UNKNOWN http://blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx

此外,除了提到的所有内容之外,如果您使用的是SQL Server 2008及更高版本,请查看OPTIMIZE FOR UNKNOWN http://blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-对于未知-A-鲜为人知-SQL服务器2008 feature.aspx

#3

I would recommend recompiling the execution plan for the stored procedure.

我建议重新编译存储过程的执行计划。

usage: sp_recompile '[target]'

用法:sp_recompile'[target]'

example: sp_recompile 'dbo.GetObject'

示例:sp_recompile'dbo.GetObject'

When you execute a query from SSMS the query plan is automatically redone every time its executed. However with stored procs, sql server caches execution plans for stored procedures, and its this execution plan that gets used everytime the stored proc is called.

当您从SSMS执行查询时,查询计划会在每次执行时自动重做。但是,对于存储过程,sql server会缓存存储过程的执行计划,以及每次调用存储过程时都会使用的执行计划。

Link for sp_recompile.

sp_recompile的链接。

You can also change the proc to use with WITH RECOMPILE clause within the stored proc.

您还可以在存储过程中更改要与WITH RECOMPILE子句一起使用的过程。

Example:

CREATE PROCEDURE dbo.GetObject
(
    @parm1 VARCHAR(20)
)
WITH RECOMPILE
AS
BEGIN 
  -- Queries/work here.
END

However this will force the execution plan to be recompiled every time the stored proc is called. This is good for dev/testing where the proc and/or data changes quite frequently. Make sure you remove it when you deploy it to production, as this can have a performance hit.

但是,这将强制每次调用存储过程时重新编译执行计划。这对于开发/测试,其中proc和/或数据经常变化很有用。确保在将其部署到生产环境时将其删除,因为这可能会影响性能。

sp_recompile only recompiles the execution plan once. If you need to do it again at a later date, you will need to make the call again.

sp_recompile仅重新编译执行计划一次。如果您需要在以后再次进行,则需要再次拨打电话。

Good luck!

#4

OK, thank you all for your help. Turns out it was a terribly stupid rookie mistake. The first time I created the proc, it created it under my user's schema instead of the dbo schema. When I called the proc I was simply doing 'exec proc_name', which I'm realizing now was using the version of the proc under my user's schema. Running 'exec dbo.proc_name' ran as expected.

好的,谢谢大家的帮助。原来这是一个非常愚蠢的菜鸟错误。我第一次创建proc时,它是在我的用户架构而不是dbo架构下创建的。当我调用proc时,我只是在做'exec proc_name',我现在意识到它是在我的用户模式下使用proc的版本。运行'exec dbo.proc_name'按预期运行。