Início / dba / Perguntas / 255401
Accepted
Fza
Asked: 2019-12-12 06:08:19 +0800 CST

Problema de desempenho de proc armazenado e TVF do SQL Server 2017

  • 772

Eu tenho uma instância do SQL Server 2017 Enterprise Edition em que um procedimento armazenado estava demorando aprox. cinco minutos para executar. Depois de revisar o código do procedimento armazenado, pude ver que havia um UDF escalar embutido referenciado várias vezes na lista SELECT e também na cláusula WHERE do predicado do corpo do procedimento armazenado.

Aconselhei a equipe de aplicativos proprietária do código que eles deveriam refatorar seu proc armazenado para não usar uma UDF em linha que eles incorporaram e substituíram por um TVF. Enquanto eles estavam fazendo isso, notei que o banco de dados do aplicativo ainda tinha nível de compatibilidade de banco de dados 100, então elevei isso para o nível mais recente de 140 depois de executar o banco de dados por meio do Data Migration Assistant para verificar se há recursos obsoletos e alterações importantes.

Após a substituição do UDF por um TVF e aumentando o nível de compatibilidade do banco de dados de 100 para 140, o desempenho aumentou muito e o proc armazenado agora é executado em menos de um minuto, mas o desempenho ainda não está onde eu gostaria. Espero que alguém possa aconselhar sobre algo óbvio que estou perdendo ou me apontar na direção certa de qualquer outra coisa que eu possa fazer para otimizar ainda mais o código ou obter um melhor desempenho? O plano de execução está aqui: https://www.brentozar.com/pastetheplan/?id=ByrsEdRpr

O código para o procedimento armazenado e função são como abaixo e o procedimento armazenado é chamado pelo aplicativo como tal: "EXEC dbo.CAOT_GetApplicationQueue;1"

/****** Object: StoredProcedure [dbo].[CAOT_GetApplicationQueue] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CAOT_GetApplicationQueue]
(@userID VARCHAR(50)='', @showComplete CHAR(1)='N', @JustMyQueue BIT=0, @ChannelId VARCHAR(10) = NULL )
AS
BEGIN
SELECT App.pkApplication ,
COALESCE(ApplicationReference, AlternateApplicationReference) AS ApplicationReference ,
ApplicationDate ,
Name ,
Telephone ,
[Address] ,
Email ,
CIN ,
Dob ,
CreatedDate ,
BusinessPhone ,
PostCode ,
MobilePhone ,
[Action] ,
ActionStatus ,
branchNumber ,
AccountNumber ,
AccountType ,
act.accountDescription,
IsNull( appstatus.DESCRIPTION ,'-- CREATED --') As LastStatus,
IsNull(appstatus.DAYS,'0') DaysSinceLastStatus ,
DATEDIFF(d,ApplicationDate, GETDATE()) DaysCreated,
InitialUserID,
IsNull(appstatus.STATUS,'-- MADE --') APPLICATIONSTATUS
FROM dbo.CAOT_Application (NOLOCK) app
LEFT OUTER JOIN dbo.CAOT_AccountType (NOLOCK) act
ON app.AccountType = act.AccountTypecode
LEFT OUTER JOIN [CAOT_GetAllApplicationStatus]() appstatus
ON app.pkApplication = appstatus.[PKAPPLICATION]
WHERE (IsNull(appstatus.STATUSCODE,'MADE') NOT IN ('CANCELLED','DECLINED','COMPLETE','EXPIRED')
OR @showComplete='Y') AND
(@JustMyQueue = 0 OR InitialUserID = @userID) AND
(@ChannelId IS NULL OR ChannelID = @ChannelId OR (@ChannelId = 'CBU' AND ChannelID IS NULL AND isCAO='N'))
ORDER BY CASE WHEN InitialUserID = @userid THEN 10 ELSE 900 END, ApplicationDate DESC
END
GO

/****** Object: UserDefinedFunction [dbo].[CAOT_GetAllApplicationStatus] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[CAOT_GetAllApplicationStatus]() RETURNS
@return TABLE
(
[PKAPPLICATION] [int] NOT NULL,
[PKAPPLICATIONEVENT] INT,
[EVENTCREATEDDATE] [DATETIME] NULL,
[KEY] VARCHAR(12) NULL,
[DESCRIPTION] VARCHAR(200) NULL,
[CODE] VARCHAR(20) NULL,
[DAYS] VARCHAR(20) NULL,
[STATUS] VARCHAR(200) NULL,
[STATUSCODE] VARCHAR(50) NULL
)

AS
BEGIN

Declare @AppStatus table
(
[PKAPPLICATION] [int] NOT NULL,
[PKAPPLICATIONEVENT] INT,
[EVENTCREATEDDATE] [DATETIME] NULL,
[KEY] VARCHAR(12) NULL,
[DESCRIPTION] VARCHAR(200) NULL,
[CODE] VARCHAR(20) NULL,
[DAYS] VARCHAR(20) NULL,
[STATUS] VARCHAR(200) NULL,
[STATUSCODE] VARCHAR(50) NULL
)

INSERT INTO @AppStatus

SELECT
fkApplication,
ev.pkApplicationEvent As pkApplicationEvent,
ev.CreateDate As 'EventCreatedDate',
CONVERT(VARCHAR(12), evt.fkApplicationStatus) As 'KEY',
evt.EventDescription As 'DESCRIPTION',
evt.EventCode As 'CODE' ,
CONVERT(VARCHAR(20), DATEDIFF(d, ev.createdate, GETDATE()) ) As 'DAYS',
apps.StatusDescription As 'STATUS' ,
apps.StatusCode As 'STATUSCODE'
FROM dbo.CAOT_ApplicationEvent (NOLOCK) ev
INNER JOIN dbo.CAOT_EventType (NOLOCK) evt ON ev.fkEventType = evt.pkEventType
INNER JOIN dbo.CAOT_ApplicationStatus (NOLOCK) apps ON evt.fkApplicationStatus = apps.pkApplicationStatus

ORDER BY ev.CreateDate DESC, ev.pkApplicationEvent DESC

INSERT INTO @return
Select * from @AppStatus AllStatus
Where AllStatus.EVENTCREATEDDATE = ( Select Max(LatestAppStatus.EVENTCREATEDDATE) from @AppStatus LatestAppStatus where LatestAppStatus.PKAPPLICATION =AllStatus.PKAPPLICATION ) --Z On X.PKAPPLICATION = Z.PKAPPLICATION

RETURN
END

GO
sql-server optimization

3 respostas

  1. Best Answer

    Você pode substituir seu TVF por uma visualização (ou manter o TVF, mas use a visualização para seu sproc de desempenho crítico):

    CREATE VIEW CAOT_AllApplicationStatuses AS
  SELECT
    fkApplication,
    ev.pkApplicationEvent AS pkApplicationEvent,
    ev.CreateDate AS EventCreatedDate,
    CONVERT(VARCHAR(12), evt.fkApplicationStatus) As 'KEY',
    evt.EventDescription AS 'DESCRIPTION',
    evt.EventCode AS 'CODE',
    CONVERT(VARCHAR(20), DATEDIFF(d, ev.createdate, GETDATE())) AS 'DAYS',
    apps.StatusDescription AS 'STATUS',
    apps.StatusCode AS 'STATUSCODE'
  FROM
    dbo.CAOT_ApplicationEvent (NOLOCK) ev
    INNER JOIN dbo.CAOT_EventType (NOLOCK) evt ON ev.fkEventType = evt.pkEventType
    INNER JOIN dbo.CAOT_ApplicationStatus (NOLOCK) apps ON evt.fkApplicationStatus = apps.pkApplicationStatus
  WHERE
    NOT EXISTS
      (
      SELECT * FROM dbo.CAOT_ApplicationEvent AS LaterEvent WHERE EV.pkApplication = LaterEvent.pkApplication AND LaterEvent.pkApplication.CreateDate > EV.CreateDate
      )
  ORDER BY
    ev.CreateDate DESC, ev.pkApplicationEvent DESC

    SELECTEste é simplesmente o conteúdo da consulta principal do TVF , com a WHEREcláusula da segunda SELECTincorporada como um arquivo NOT EXISTS. Estou confiando que todos os registros em CAOT_ApplicationEventtêm registros em CAOT_EventTypee CAOT_ApplicationStatus; se não for esse o caso, você precisará adicionar essas junções na NOT EXISTSconsulta.

    Apenas usar uma visualização em vez de um TVF pode ajudar, pois o analisador irá incorporar a visualização na consulta final e descartar partes não utilizadas; essas chamadas para CONVERT(), por exemplo, provavelmente serão relativamente caras, mas parecem não ser utilizadas. No entanto, os predicados complexos em seu sproc de nível superior podem exigir uma verificação de tabela. Vamos dar uma chance e ver se precisa de mais trabalho!

    • 2

  2. Principal área de preocupação

    1. Falta SET NOCOUNT ON no proc.
    2. Não use MTVF :Sua UDF é Multi statement TVF(MTVF) Atualmente ela busca todas as linhas desnecessariamente. Dá uma estimativa errada para o otimizador.
    3. MTVF não é necessário. Crie Inline TVF (ITVF) ou use Outer Apply. Se você usar Inline TVF, use-os dentro de OUTER APPLY em vez de LEFT JOIN. Passe pkApplicationStatus e @showComplete em Inline TVF como parâmetro. t executará menos que MTVF.
    4. Alternativamente, você escreve a lógica ITVF na tabela temporária. Também funcionará bem.
    5. Condição usando muita condição OR são de mau desempenho. (@JustMyQueue = 0 OR InitialUserID = @userID). Tente convertê-los em UNION ALL ou colocá-los na tabela #temp e JOIN.

    Exemplo,

    create table #Status(Status varchar(15))
if(@showComplete='Y')
begin
insert into #Status
end
else
begin
insert into #Status
end

    Agora é só aderir #Status

    Editar 1:

    Você deve ter notado a melhoria de desempenho depois de passar o parâmetro em sua UDF. Pode melhorar ainda mais. Se possível, reduza RETURNS VARCHAR(100) para VARCHAR(50) ou algo assim.

    ALTER FUNCTION [dbo].[CAOT_GetApplicationStatus]
  (@pkApplication INT, @returnType varchar(20))
  RETURNS VARCHAR(100)

AS
--DECLARE @status VARCHAR(100)
RETURN(
SELECT TOP 1

    CASE @returntype
      WHEN 'KEY' THEN CONVERT(VARCHAR(12), isnull(evt.fkApplicationStatus,'1'))
      WHEN 'DESCRIPTION' THEN isnull(evt.EventDescription,'-- CREATED --')
      WHEN 'CODE' THEN isnull(evt.EventCode,'CREATE')
      WHEN 'DAYS' THEN CONVERT(VARCHAR(20), isnull(DATEDIFF(d, ev.createdate, GETDATE()),'0') )
      WHEN 'STATUS' THEN isnull(apps.StatusDescription,'-- MADE --')
      WHEN 'STATUSCODE' THEN isnull(apps.StatusCode,'MADE')
    ELSE isnull(evt.EventDescription,'')
  END
FROM dbo.CAOT_ApplicationEvent (NOLOCK) ev
    INNER JOIN dbo.CAOT_EventType (NOLOCK) evt
        ON ev.fkEventType = evt.pkEventType
    INNER JOIN dbo.CAOT_ApplicationStatus (NOLOCK) apps
        ON evt.fkApplicationStatus = apps.pkApplicationStatus
WHERE fkApplication = @pkApplication
ORDER BY ev.CreateDate DESC, ev.pkApplicationEvent DESC
)

GO

    Não se esqueça do truque #Status join. Você deve atualizar seu script e plano de consulta.

    • 0

  3. Depois de ler http://sommarskog.se/dyn-search.html

    você pode dividir a consulta em várias partes, algo como:

    create procedure [dbo].[CAOT_GetApplicationQueue] ( @userID varchar(50) = '', @showComplete char(1) = 'N', @JustMyQueue bit = 0, @ChannelId varchar(10) = null )
as begin
    select case when InitialUserID = @userID then 10 else 900 end as SortCol, app.pkApplication, coalesce(ApplicationReference, AlternateApplicationReference) as ApplicationReference, ApplicationDate, Name, Telephone, [Address], Email, CIN, Dob, CreatedDate, BusinessPhone, PostCode, MobilePhone, [Action], ActionStatus, branchNumber, AccountNumber, AccountType
         , act.accountDescription, isnull(appstatus.DESCRIPTION, '-- CREATED --') as LastStatus, isnull(appstatus.DAYS, '0') DaysSinceLastStatus, datediff(d, ApplicationDate, getdate()) DaysCreated, InitialUserID, isnull(appstatus.STATUS, '-- MADE --') APPLICATIONSTATUS
    from dbo.CAOT_Application  app
    left outer join dbo.CAOT_AccountType  act on app.AccountType = act.AccountTypecode
    left outer join [CAOT_GetAllApplicationStatus]() appstatus on app.pkApplication = appstatus.[PKAPPLICATION]
    where ( isnull(appstatus.STATUSCODE, 'MADE') not in ( 'CANCELLED', 'DECLINED', 'COMPLETE', 'EXPIRED' ) or @showComplete = 'Y' )
    and   ( @JustMyQueue = 0 /* or InitialUserID = @userID */)
    and   ( @ChannelId is null or ChannelID = @ChannelId or ( @ChannelId = 'CBU' and ChannelID is null and isCAO = 'N' ))
    union all 
    select 10 as SortCol, app.pkApplication, coalesce(ApplicationReference, AlternateApplicationReference) as ApplicationReference, ApplicationDate, Name, Telephone, [Address], Email, CIN, Dob, CreatedDate, BusinessPhone, PostCode, MobilePhone, [Action], ActionStatus, branchNumber, AccountNumber, AccountType
         , act.accountDescription, isnull(appstatus.DESCRIPTION, '-- CREATED --') as LastStatus, isnull(appstatus.DAYS, '0') DaysSinceLastStatus, datediff(d, ApplicationDate, getdate()) DaysCreated, InitialUserID, isnull(appstatus.STATUS, '-- MADE --') APPLICATIONSTATUS
    from dbo.CAOT_Application  app
    left outer join dbo.CAOT_AccountType  act on app.AccountType = act.AccountTypecode
    left outer join [CAOT_GetAllApplicationStatus]() appstatus on app.pkApplication = appstatus.[PKAPPLICATION]
    where ( isnull(appstatus.STATUSCODE, 'MADE') not in ( 'CANCELLED', 'DECLINED', 'COMPLETE', 'EXPIRED' ) or @showComplete = 'Y' )
    and   ( @JustMyQueue <> 0)
    and   ( InitialUserID = @userID)
    and   ( @ChannelId is null or ChannelID = @ChannelId or ( @ChannelId = 'CBU' and ChannelID is null and isCAO = 'N' ))
    order by SortCol, ApplicationDate desc
    option (recompile)
end;

    Isso pode pesquisar menos linhas da sua tabela CAOT_Application.

    • 0

relate perguntas