Need help optimizing a stored procedure

Question

Recently started working at a company and found a stored procedure which was crawling. Nobody who works here at the moment knows how to optimise it but it just looks funny to me. Wondering if anyone see's anything obvious:

--------- [Debug Block] ------------
SET @DebugMessage = 'Execution Started'

EXEC [dbo].[MarkInProcessDebugPoint] 
     @ProcId = @@PROCID
    ,@DebugType = 0
    ,@Message = @DebugMessage
--------- [Debug Block] ------------

BEGIN TRY

DECLARE @tbl_Downloads  as TABLE (
    Revision bigint,
    AssignedStores varchar (max),
    Id varchar (64),
    DownloadId int
)

-- Get Id, and legacy Id
INSERT @tbl_Downloads (Revision, DownloadId, Id)
   SELECT 
      isnull(DCG.[DOWNLOAD_ID], 0), [Value], 
      isnull(DCG.[GUID], newid())
   FROM 
      dbo.fn_Valid_Split(@Downloads, ';') AS CD 
   LEFT JOIN
      [DIGITAL_CONTENT_GUID] DCG ON CD.Value = DCG.[DOWNLOAD_ID]

INSERT [DIGITAL_CONTENT_GUID]
   SELECT 
      DownloadId, 1, max(Id)
   FROM 
      @tbl_Downloads
   WHERE 
      Revision = 0 and DownloadId > 0
   GROUP BY 
      DownloadId    

-- Create track GUID values if the album is in the list
INSERT [DIGITAL_CONTENT_GUID] (DOWNLOAD_ID, CONTENT_TYPE_ID, [GUID]) 
   SELECT 
      A.DOWNLOAD_ID, 1, NEWID()
   FROM
      (SELECT DISTINCT 
          P.DOWNLOAD_ID
       FROM 
          DOWNLOAD_PACK P (NOLOCK)
       INNER JOIN
          @tbl_Downloads T ON P.DOWNLOAD_PACK_ID = T.DownloadId
       WHERE 
          NOT EXISTS (SELECT 1 
                      FROM [DIGITAL_CONTENT_GUID] G (nolock) 
                      WHERE G.DOWNLOAD_ID = P.DOWNLOAD_ID)
      ) A

--------- [Debug Block] ------------
SELECT @DebugMessage = 'Get Id, and legacy Id' 

EXEC [dbo].[MarkInProcessDebugPoint] 
     @ProcId = @@PROCID
    ,@DebugType = 0
    ,@Message = @DebugMessage
--------- [Debug Block] ------------

SELECT 
    --TA.Revision, 
    [dbo].[Fn_ContentExport_GetAssignedStores_Json]  (TA.DownloadId, 1) AS AssignedStores,
    TA.Id, 
    TA.DownloadId As LegacyId, 
    [dbo].[Fn_ContentExport_GetContentNameSpace]  (CD.Download_Type_Id) AS [Namespace],
    [dbo].[Fn_ContentExport_GetContentTypeName]  (CD.Download_Type_Id) AS [Type],
    T.TITLE AS Title,
    T.DISPLAY_ARTIST_NAME AS Subtitle,
    CD.DESCRIPTION AS [Description],
    [dbo].[Fn_ContentExport_GetContentArtworks]  (TA.DownloadId, CD.Download_Type_Id, 0) AS Artwork,
    [dbo].[Fn_ContentExport_GetTitleArtists_Json]  (CD.TITLE_ID, 0) AS Artists,
    [dbo].[Fn_ContentExport_GetContentTypeData]  (TA.DownloadId, CD.Download_Type_Id, 2240) AS TypeEntity,
    '' AS PublicTags,
    [dbo].[Fn_ContentExport_GetInternalTags_Json] (CD.Global_Content_Id) AS InternalTags,
    CD.SUPPLIER_ID AS SupplierId,
    S.NAME AS SupplierName,
    CD.RELEASE_DATE AS [ReleaseDateTime2],
    CD.VALID_TO_DATE As [ExpiryDateTime2],
    CASE
         WHEN CD.IS_DELETED = 1 THEN 1 
         WHEN CD.IS_DELETED = 0 AND [dbo].[Fn_ContentExport_GetContentIsSearchable] (TA.DownloadId) = 0 THEN 1
         ELSE 0
    END As Archived,

    CD.DATE_CREATED AS CreationDateTime,
    CD.DATE_MODIFIED AS ModifiedDateTime,
    [dbo].[Fn_ContentExport_GetContentTierPriceId]  (CD.Download_Type_Id, TA.DownloadId) As [PricingTierId],
    CD.TITLE_ID As [LegacyTitleId]
    -- [dbo].[Fn_ContentExport_GetContentIsSearchable]  (TA.DownloadId) AS IsSearchable
FROM 
    @tbl_Downloads AS TA
INNER JOIN
    CONTENT_DOWNLOAD CD (NOLOCK) ON TA.DownloadId = CD.DOWNLOAD_ID
INNER JOIN
    TITLE T (NOLOCK) ON T.TITLE_ID = CD.TITLE_ID
INNER JOIN
    SUPPLIER S (NOLOCK) ON CD.SUPPLIER_ID = s.SUPPLIER_ID
WHERE
    CD.DOWNLOAD_TYPE_ID <> 125

END TRY
BEGIN CATCH
    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    DECLARE @ErrorMessage NVARCHAR(2048);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    --------- [Debug Block] ------------
    SET @DebugMessage = CAST(@ErrorSeverity AS VARCHAR(100)) + ':' +        CAST(@ErrorState AS VARCHAR(100)) + ':' + @ErrorMessage

    EXEC [dbo].[MarkInProcessDebugPoint] 
         @ProcId = @@PROCID
        ,@DebugType = 1
        ,@Message = @DebugMessage
    --------- [Debug Block] ------------

    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );

      END CATCH

Many thanks

Answer 1

Contents of fn_Valid_Split and @Downloads are missing, so just assuming there's few items (10-20 or something like that) in a delimited list.

Few things that I would check:

• Check from plan cache that which of these statements is actually the one causing problems (high I/O, high CPU time). You should also check what the query returns after "set statistics io" is on. Statistics I/O will not show I/O made in functions, so you'll need the plan cache for that too. Look also at least the last select if all the tables joined have correct indexes in place for the joins.

• Definitely look into the user defined functions in the last select. It would be good to get rid of them, if not, check if you can change them into inline table valued functions.

• If fn_Valid_Split gets called more than it should (e.g. more than once per execution) take it out of the join and fill a temp table (or table variable) with the contents it returns. You should also check DelimitedSplit8k by Jeff Moden, in case your implementation isn't as good: http://www.sqlservercentral.com/articles/Tally+Table/72993/

• If there's a lot of rows in @tbl_Downloads try to change it to temp table, because then it will contain statistics. You can also add a (clustered?) index to DownloadId when it's a temp table. That might help the joins.

• I would also definitely recommend removing nolock. It can cause a lot more headache than what it "fixes". If blocking exists, I would try to fix it properly.