Sql-server – How tomprove Performance of Query in SQL Server

performancequery-performancesql server

I have a query and currently it's taking 10 seconds for only 324 records. Is there any way to improve this performance?

I have tried using SET NOCOUNT ON in the SP as I have read that it improves performance and even have used an alias for each table.

Execution Plan (Paste The Plan)

SET NOCOUNT ON  
 DECLARE @vRequestedBy VARCHAR(2000) = CASE WHEN @RequestedBy <> '' THEN @RequestedBy END,  
   @vJobType NVARCHAR(2000) = CASE WHEN @JobType <> '' THEN @JobType END;  


 SELECT  distinct ts.JobID,   
  TCC.Category,   
  ts.JobType,   
  ttj.JobStatus,   
  wc.Name "ContactName",   
  ts.Created,   
  wb.Name AS BuildingName,   
  --dbo.TSP_TSR_Job.JobStatusID,   
  --dbo.TSP_TSR_Job.BuildingID,   
  --dbo.TSP_TSR_Job.TenancyID,   
  --dbo.TSP_TSR_Job.CategoryID,   
  ts.Contact,   
 ts.CreatedBy,   
  ts.ContactEmail,   
  wc.TradingAs,  
  --wsm_Contact_User.UserId "RequestedByUserId",  
  c2.Name "RequestedByUser",  
  wc.ContactID  
  FROM   
   dbo.TSP_TSR_Job ts  
  LEFT OUTER JOIN   
   dbo.wsm_Ref_Buildings wb ON ts.BuildingID = wb.BuildingId   
  LEFT OUTER JOIN   
   dbo.wsm_Contact wc ON ts.TenancyID = wc.ContactID   
  LEFT OUTER JOIN   
   dbo.TSP_TSR_JobStatus TTJ ON ts.JobStatusID = TTJ.JobStatusID   
  LEFT OUTER JOIN   
   dbo.TSP_CAT_Category TCC ON ts.CategoryID = TCC.CategoryID  
  LEFT OUTER JOIN   
   dbo.wsm_Contact_User WCU ON UserID = ts.ContactEmail COLLATE SQL_Latin1_General_CP1_CI_AS  
  LEFT OUTER JOIN   
   dbo.wsm_Contact c2 ON c2.ContactID = WCU.ContactID  

  WHERE     
   -- JobId criteria  
    (@JobID = 0 OR JobID = @JobId)  
   -- Tenancy criteria   
   AND (@TenancyId = '0' OR TenancyId in (select Item from Split_fn(@TenancyID,',')))  
   --TradingAs criteria  
   AND (@TradingAs = '0' OR wc.ContactID in (select Item from Split_fn(@TradingAs,',') ))  
   --RequestedBy    
   AND (@vRequestedBy IS NULL OR @vRequestedBy = '0' OR ts.ContactEmail in (Select distinct Email from dbo.wsm_Contact WHere Email in (select Item from Split_fn(@vRequestedBy,',')) ))  
   -- Job Category  
   AND (@CategoryId = '0' OR ts.CategoryID in (select Item from Split_fn(@CategoryId,',') ))  
   -- Contact Id (always filter on this, enough security?!)    
   AND ts.BuildingID IN (SELECT distinct b.BuildingId    
            FROM   
             wsm_ContactSite s   
            INNER JOIN   
             wsm_Contact c ON c.ContactID = s.ContactID  
            INNER JOIN   
             wsm_Ref_Buildings b ON b.SiteId = s.SiteID  
            WHERE   
             c.ContactID = @ContactUserId)  

   AND wc.FloorID  IN     (SELECT t.FloorID   
              FROM wsm_Contact_Tenancy t  
             WHERE t.ContactID = @ContactUserId)  

   AND wc.OCCPSTAT NOT IN ('I', 'P')  

   AND (@vJobType IS NULL OR ts.JobType in (select Item from Split_fn(@vJobType,',')))  
   AND (ts.Created between @CreatedFrom and DATEADD(DD,1,@CreatedTo))  
   ORDER BY   
    JobID

STATS:

 SQL Server parse  SQL S and compile time: 
       CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server parse and compile time: 
       CPU time = 0 ms, elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.
    Table 'TSP_CAT_Category'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'wsm_Contact_Tenancy'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'wsm_Contact'. Scan count 2, logical reads 3822, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'wsm_ContactSite'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'wsm_Ref_Buildings'. Scan count 3, logical reads 2811, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 2, logical reads 341364, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#AFEC4F2F'. Scan count 2, logical reads 524444, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'TSP_TSR_Job'. Scan count 3, logical reads 58210, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'wsm_Contact_User'. Scan count 2, logical reads 2300, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'TSP_TSR_JobStatus'. Scan count 2, logical reads 650, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '1159564537'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#BB5E01DB'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#BA69DDA2'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#B1D497A1'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#B0E07368'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

     SQL Server Execution Times:
       CPU time = 8391 ms,  elapsed time = 5792 ms.

     SQL Server Execution Times:
       CPU time = 8391 ms,  elapsed time = 5793 ms.

Best Answer

You can add OPTION (RECOMPILE) at the end of your query if you can permit recompilations, or you can rewrite your query constructing it dynamically by analyzing your search conditions and adding them to your query only if they are NOT NULL.

Your sp now depends on the first input parameters that were sniffed, i.e. the plan that is used was built based on the parameters passed to this sp at the first execution, i.e. it's not optimal if the first time some parameters passed were null and now they are not null, or other parameter are null.

You can learn more about it here: Dynamic Search Conditions in T‑SQL.