Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query plan - largest cost Expand / Collapse
Author
Message
Posted Friday, September 14, 2012 10:13 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 13, 2013 7:56 AM
Points: 939, Visits: 754
This query takes about 45 seconds to complete (down from 10+ minutes by adding clustered index to table). Looking at the query plan, the largest cost is a clustered index seek -- which, as I understand it, is what I'm supposed to be getting rather than table or index scans.

What other indexes may be missing to speed this up?

I know it's a big topic, where should I start reading for more information on how to read & understand a query plan and tune a query.

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT DISTINCT vwea.FranchiseNumber,
vwea.CustomerID,
vwea.EmailBefore,
vwea.EmailAfter,
vwea.ChangeDate,
j.ScheduledEndDate AS MoveDate,
CASE
WHEN j.ScheduledEndDate < vwea.ChangeDate THEN 'After Move Change'
ELSE 'Before move change'
END AS Change
FROM dbo.tblJob j
INNER JOIN dbo.tblWorkOrder wo ON j.DatabaseInfoID = wo.DatabaseInfoID
AND j.WorkOrderID = wo.WorkOrderID
INNER JOIN dbo.EmailAudit vwea ON wo.CustomerID = vwea.CustomerID
INNER JOIN dbo.tblDatabaseInfo ON j.DatabaseInfoID = dbo.tblDatabaseInfo.DatabaseInfoID
INNER JOIN dbo.tblFranchise ON j.DatabaseInfoID = dbo.tblFranchise.DatabaseInfoID
AND vwea.FranchiseNumber = dbo.tblFranchise.FranchiseNumber
WHERE (vwea.FranchiseNumber = 253)
AND j.ScheduledEndDate < vwea.ChangeDate
ORDER BY vwea.ChangeDate DESC;

SET STATISTICS IO OFF;
GO
SET STATISTICS TIME OFF;
GO

Thanks,
Rob

(Query plan attached)





  Post Attachments 
AuditSQLplan.sqlplan (22 views, 296.21 KB)
Job & work order tables.txt (12 views, 15.17 KB)
Post #1359483
Posted Friday, September 14, 2012 11:53 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:11 PM
Points: 15,517, Visits: 27,898
Looking at this, the first thing I'd suggest is you update your statistics. They're pretty badly out of wack. That might be why you're getting such an expensive plan. You're processing huge amounts of rows to only return 144. Also, I only see five tables the query, but, quick count, eight access points in the plan. Are you dealing with views or nested views here? That could also be problematic.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1359537
Posted Friday, September 14, 2012 1:03 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 13, 2013 7:56 AM
Points: 939, Visits: 754
I'm updating statistics on those tables right now; dbo.EmailAudit is a view. I'll look into that.

Thanks,
Rob



Post #1359586
Posted Friday, September 14, 2012 1:34 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:11 PM
Points: 15,517, Visits: 27,898
The simplification step in the optimization process can clean up issues with views, but not always and not always all the way. Depending on what's there, you might be better off accessing those tables more directly through this query.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1359608
Posted Sunday, September 16, 2012 7:45 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 13, 2013 7:56 AM
Points: 939, Visits: 754
I did an update of the statistics on all of the related tables. That didn't change the execution time.

The view (EmailAudit) duplicates many of the tables in the original query. Just running the view takes about 25 seconds.

The view definition is:

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO

SET STATISTICS IO ON
SET STATISTICS TIME ON

--CREATE VIEW [dbo].[EmailAudit]
--AS
SELECT COALESCE(f.FranchiseNumber, di.FranchiseNumber) AS FranchiseNumber,
a.[CustomerID],
a.[EmailAddress] AS [EmailBefore],
c.EmailAddress AS [EmailAfter],
a.[ChangeDate]
FROM [dbo].[tblEmailAudit] a
INNER JOIN dbo.tblCustomer c ON C.DatabaseInfoID = a.DatabaseInfoID
AND c.CustomerID = a.CustomerID
AND ISNULL(c.FranUniqueID, c.DatabaseInfoID) = ISNULL(a.FranUniqueID, a.DatabaseInfoID)
LEFT JOIN dbo.tblFranchise f ON a.DatabaseInfoID = f.DatabaseInfoID
AND ISNULL(f.FranUniqueID, f.DatabaseInfoID) = ISNULL(a.FranUniqueID, a.DatabaseInfoID)
LEFT JOIN dbo.tblDatabaseInfo di ON a.DatabaseInfoID = di.DatabaseInfoID
WHERE ISNULL(a.EmailAddress,'') <> ISNULL(c.EmailAddress,'');

SET STATISTICS IO OFF;
GO
SET STATISTICS TIME OFF;
GO


So I rewrote the original query removing the view and I'm still getting about the same time (40-45 seconds).

-- original query rewritten to remove EmailAudit view --
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT DISTINCT COALESCE(f.FranchiseNumber, di.FranchiseNumber) AS FranchiseNumber,
a.CustomerID,
a.EmailAddress AS EmailBefore,
c.EmailAddress AS EmailAfter,
a.ChangeDate,
j.ScheduledEndDate AS MoveDate,
CASE
WHEN j.ScheduledEndDate < a.ChangeDate THEN 'After Move Change'
ELSE 'Before move change'
END AS Change
FROM dbo.tblEmailAudit a
INNER JOIN dbo.tblCustomer c ON c.CustomerID = a.CustomerID
AND c.DatabaseInfoID = a.DatabaseInfoID
INNER JOIN dbo.tblWorkOrder wo ON c.CustomerId = wo.CustomerId
AND wo.DatabaseInfoId = a.DatabaseInfoId
INNER JOIN dbo.tblJob j ON j.WorkOrderID = wo.WorkOrderID
AND j.DatabaseInfoID = a.DatabaseInfoID
INNER JOIN dbo.tblDatabaseInfo di ON di.DatabaseInfoID = a.DatabaseInfoID
LEFT JOIN dbo.tblFranchise f ON a.DatabaseInfoID = f.DatabaseInfoID
AND ISNULL(f.FranUniqueID, f.DatabaseInfoID) = ISNULL(a.FranUniqueID, a.DatabaseInfoID)
WHERE (COALESCE(f.FranchiseNumber, di.FranchiseNumber) = 253)
AND j.ScheduledEndDate < a.ChangeDate
AND ISNULL(a.EmailAddress,'') <> ISNULL(c.EmailAddress,'')
ORDER BY a.ChangeDate DESC;

SET STATISTICS IO OFF;
GO
SET STATISTICS TIME OFF;
GO

The two biggest costs in the query plan are both index seeks (one clustered and the other non-clustered). The third largest cost is an index scan on dbo.tblJob.





  Post Attachments 
NewExecPlan.sqlplan (0 views, 212.71 KB)
Post #1359944
Posted Sunday, September 16, 2012 8:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 36,755, Visits: 31,212
rgtft (9/16/2012)

The two biggest costs in the query plan are both index seeks (one clustered and the other non-clustered). The third largest cost is an index scan on dbo.tblJob.


There's really only one way for the seeks to be taking longer than a scan... the seeks are being repeated. Look at the properties of the seeks and see that they are executed many times. A better index capable of doing an initial seek and a "range scan" following that would be a tremendous amount faster.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1359948
Posted Sunday, September 16, 2012 9:17 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 13, 2013 7:56 AM
Points: 939, Visits: 754
Silly question, how do I read the execution plan to know what columns to index?

I created two indexes (just eyeballed query and guessed). The first one seems to have improved the query, but the second one (though being used) still doesn't seem to improve anything.

-- Two indexes created --
CREATE NONCLUSTERED INDEX [IX_tblCustomer_CustomerId_EmailAddress] ON [dbo].[tblCustomer]
(
[CustomerID] ASC,
[EmailAddress] ASC
);


CREATE NONCLUSTERED INDEX [IX_tboWorkOrder_CustomerID_DatabaseInfoId] ON [dbo].[tblWorkOrder]
(
[CustomerID] ASC,
[DatabaseInfoId] ASC
);

Thanks,
Rob





  Post Attachments 
NewExecPlan_01.sqlplan (1 view, 252.04 KB)
Post #1359955
Posted Sunday, September 16, 2012 10:42 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 13, 2013 7:56 AM
Points: 939, Visits: 754
I see a couple of websites that explain some of this:

I also see that Grant has a book on execution plans:


Anyways, I've now got query down to 15-20 seconds; it looks like I've got some reading to do.

Rob





  Post Attachments 
NewExecPlan_02.sqlplan (2 views, 252.05 KB)
Post #1359966
Posted Monday, September 17, 2012 5:53 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:11 PM
Points: 15,517, Visits: 27,898
ISNULL and COALESCE on columns in the where clause are going to hurt performance. I still think your stats are off. I'm seeing estimated 4.5 million, actual 15,000. That's a pretty big disparity and is usually indicative of out of date or badly sampled statistics.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1360125
Posted Monday, September 17, 2012 6:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
Grant Fritchey (9/17/2012)
ISNULL and COALESCE on columns in the where clause are going to hurt performance....


This should be close to a SARGable version of the query:

SELECT DISTINCT COALESCE(f.FranchiseNumber, di.FranchiseNumber) AS FranchiseNumber,
a.CustomerID,
a.EmailAddress AS EmailBefore,
c.EmailAddress AS EmailAfter,
a.ChangeDate,
j.ScheduledEndDate AS MoveDate,
CASE
WHEN j.ScheduledEndDate < a.ChangeDate THEN 'After Move Change'
ELSE 'Before move change'
END AS Change
FROM dbo.tblEmailAudit a

INNER JOIN dbo.tblCustomer c
ON c.CustomerID = a.CustomerID
AND c.DatabaseInfoID = a.DatabaseInfoID
and (
c.EmailAddress <> a.EmailAddress
OR (c.EmailAddress IS NULL AND a.EmailAddress IS NOT NULL)
OR (c.EmailAddress IS NOT NULL AND a.EmailAddress IS NULL)
)
INNER JOIN dbo.tblWorkOrder wo
ON c.CustomerId = wo.CustomerId
AND wo.DatabaseInfoId = a.DatabaseInfoId

INNER JOIN dbo.tblJob j
ON j.WorkOrderID = wo.WorkOrderID
AND j.DatabaseInfoID = a.DatabaseInfoID

LEFT JOIN dbo.tblDatabaseInfo di
ON di.DatabaseInfoID = a.DatabaseInfoID
AND di.FranchiseNumber = 253

LEFT JOIN dbo.tblFranchise f
ON (f.FranUniqueID = a.FranUniqueID OR f.DatabaseInfoID = a.DatabaseInfoID)
--a.DatabaseInfoID = f.DatabaseInfoID
AND f.FranchiseNumber = 253
--AND ISNULL(f.FranUniqueID, f.DatabaseInfoID) = ISNULL(a.FranUniqueID, a.DatabaseInfoID)
WHERE 1 = 1
--(COALESCE(f.FranchiseNumber, di.FranchiseNumber) = 253)
AND j.ScheduledEndDate < a.ChangeDate
--AND ISNULL(a.EmailAddress,'') <> ISNULL(c.EmailAddress,'')
ORDER BY a.ChangeDate DESC;



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1360157
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse