CODEWITHSUNDEEP
sqlsql-servert-sqlquery-optimization
carolcastelli
carolcastelli

Reputation: 21

SQL Server Long running query

Can somebody help me to understand why the query below is taking more than a minute to run?

When I comment the below line: 

AND sys.fn_hadr_backup_is_preferred_replica(d.name) = 1

the query runs in less than a second.

I'm looking for some alternatives to rewrite the query.

SELECT
DISTINCT @@SERVERNAME AS ServerName ,
d.name AS DatabaseName ,
'Diff Backups Not Performed Recently' AS Finding ,
'Last differential backup: ' + COALESCE(CAST(MAX(b.backup_finish_date) AS VARCHAR(25)),'never') AS Details
FROM master.sys.databases d
    LEFT OUTER JOIN msdb.dbo.backupset b ON d.name COLLATE SQL_Latin1_General_CP1_CI_AS = b.database_name COLLATE SQL_Latin1_General_CP1_CI_AS
    AND b.type = 'I'
    AND b.server_name = SERVERPROPERTY('ServerName') /*Backupset ran on current server */
WHERE d.database_id <> 2 
    AND d.state NOT IN(1, 6, 10) /* Not currently offline or restoring, like log shipping databases */
    AND d.is_in_standby = 0 /* Not a log shipping target database */
    AND d.source_database_id IS NULL /* Excludes database snapshots */
    AND b.user_name = 'tsmadmin'
    AND sys.fn_hadr_backup_is_preferred_replica(d.name) = 1
GROUP BY d.name
HAVING MAX(b.backup_finish_date) <= DATEADD(dd, -2, GETDATE())
    OR MAX(b.backup_finish_date) IS NULL;

Upvotes: 1

Views: 202

Answers (2)

paparazzo
paparazzo

Reputation: 45096

This turns it into a an innner join AND b.user_name = 'tsmadmin' so might as well use a join

HAVING OR may be the problem

DISTINCT may be a problem 

SELECT @@SERVERNAME AS ServerName;
SELECT d.name AS DatabaseName  
     , COALESCE(CAST(MAX(b.backup_finish_date) AS VARCHAR(25)), 'never') AS Details
FROM master.sys.databases d
JOIN msdb.dbo.backupset b 
  ON d.name COLLATE SQL_Latin1_General_CP1_CI_AS = b.database_name COLLATE SQL_Latin1_General_CP1_CI_AS
 AND b.type = 'I'
 AND b.server_name = SERVERPROPERTY('ServerName') /*Backupset ran on current server */
 AND b.user_name = 'tsmadmin'
 AND d.database_id <> 2 
 AND d.state NOT IN(1, 6, 10)     /* Not currently offline or restoring, like log shipping databases */
 AND d.is_in_standby = 0          /* Not a log shipping target database */
 AND d.source_database_id IS NULL /* Excludes database snapshots */
 AND sys.fn_hadr_backup_is_preferred_replica(d.name) = 1
GROUP BY d.name
HAVING isnull(MAX(b.backup_finish_date), DATEADD(dd, -2, GETDATE())) 
       <= DATEADD(dd, -2, GETDATE());

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270021

This might be a case where running the function after the group by is a good idea. Try removing the function call from the where and adding it to the having clause:

HAVING (MAX(b.backup_finish_date) <= DATEADD(day, -2, GETDATE()) OR
        MAX(b.backup_finish_date) IS NULL
       ) AND
       sys.fn_hadr_backup_is_preferred_replica(d.name) = 1

I'm only suggesting this because you are aggregating by d.name. Normally, filtering before the aggregation would be better. But, if the operation is really expensive, it might be better to filter after the aggregation.

Upvotes: 1

Related Questions