SQL Server Long running query

Question

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;

UDFs are famous both for being slow to run and for interfering with the optimizer's ability to run the query. — Gordon Linoff
– Gordon Linoff, Commented Mar 11, 2017 at 2:26
One way is to try not apply a function to your column. What does your sys.fn_hadr_backup_is_preferred_replica do? Is it deterministic so it can be indexed? Could you show it in your question? — Pham X. Bach
– Pham X. Bach, Commented Mar 11, 2017 at 2:29
what version of sql server? : connect.microsoft.com/SQLServer/feedback/details/783011/… — Mitch Wheat
– Mitch Wheat, Commented Mar 11, 2017 at 2:44

Gordon Linoff · Accepted Answer · 2017-03-11 02:27:57Z

1

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.

answered Mar 11, 2017 at 2:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

paparazzo · Accepted Answer · 2017-03-11 15:00:45Z

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());

Collectives™ on Stack Overflow

SQL Server Long running query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related