Crazy Query Plan''s killing performance!
-
G'day all, my first post on here, so be gentle
We've just finished writing a fairly large in-house application, the usual CRM sort of stuff, based on SQL Server 2000 sp3.
Unfortunately an export process struggles to push 120 custs/min through the live server (Dual 3.2GHz Xeons, 4Gb RAM, SAN) based on 15k rows of data.
Our development database (effectively a single 2.8 Xeon, it's on a VMWare ESX server, 1Gb RAM, abysmal HDD throughput) consistently manages 500-1000 custs/min over 900k rows.
The only real difference I can find is one part of the main CustomerSelect stored procedure... The Dev box uses a query plan that eliminates most of the worthless rows right at the start, performing Nested Loop joins on 0-4 rows until we get a final result. The Live server does Hash Joins on the full 15k rows until the very last step, when it reduces the row count to the final 1 or 2.
I know I can use Join hints to alter the type of join used, but I don't know how to specify the order it performs those joins in.
This has been consistently reproducible using the exact same SQL run on both machines using Query Analyser.
Any thoughts guys?? I'm at my wits end!
Cheers, and I hope you all had a great Chrismas
Tim
-
Tim,
Assuming you checked that indexes are the same on both. Be sure that someone hasn't dropped one.
I have seen that sometimes you get weird query plans, especially if data if different. I might try a hint for a particular index and see if that helps.
-
also check to ensure that the SET options are the same for both connections. Are you using anything fancy like indexed views - they only work (without extra hints to force their use) on enterprise/developer...
Could you post the query, DDL and the textual query plan?
-
A few months back, we had a somewhat similar problem where our production box's performance paled in comparison to our development box for a particular stored procedure. We manually ran an UPDATE STATISTICS with FULLSCAN on the tables involved and that fixed the problem.
I'm not sure it will help your situation, but just thought I'd mention it.
Good luck,
Rob
-
Hello Tim,
in addition to the above, you could also check fragmentation of indexes and run DBCC INDEXDEFRAG, or maybe even better, a full optimization maintenance job on the production DB. Alternatively, try to drop and recreate all indexes on tables used in this particular query. I've seen similar behaviour when indexes are in "bad shape".
HTH, Vladan
-
Here's my 'short list' for tuning:
Round I
DBCC UPDATEUSAGE
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
Round II
DBCC DBREINDEX
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
Round III
Profiler
Query Hints
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
That's an unreal response guys, I should have asked about 1/2 a head of hair ago!!
I can post SQL, query plans etc, but I'm on hols for a few weeks. I'm likely to drop back in to work for an hr or two later in the week to give this stuff a try though.
No indexed views, or even views, LEFT and OUTER joins on about 8 different tables, then unioned with effectively the same query but on a different ID. The UNION tested significantly faster than an OR condition in the Joins.
I don't believe the SET's would be any different, because I can reproduce it in 2 QA windows.
Seeing as everyone has suggested effectively the same things (and none of them have been "rewrite the query" which I was concerned about), I think I'm onto a winner!
I'll let you know how it goes later in the week.
Cheers
Tim
-
So how did it go?
-
I haven't had a chance to drop in to work to give it a whirl, I'm enjoying my holidays too much
Rest assured, I haven't forgotten either the problem, or the need to post feedback up here.
Cheers,
Tim
-
G'day guys, back at work now...
I've run the Round 1 changes above on a database which is showing these symptoms. DBCC UPDATEUSAGE showed a lot of updates, but it doesn't seem to have changed the execution plan.
DBCC INDEXDEFRAG on every index in the dbase (using the script at http://weblogs.sqlteam.com/tarad/archive/2004/06/22/1657.aspx) also had no effect.
These databases have a full maintenance plan (Optimisations, Integrity Checks), run over them every week, There are no recent errors in the history.
I've got a nasty feeling the forums are going to break the formatting of the query plans, if there's a better way to do this please just let me know.
This is the actual SQL for the select. There's a bit of setup code to create the temp tables which I've excluded for brevity (it's still pretty big). The actual perf of these setup things doesn't seem to be an issue, as it's this query which takes 98% on the poor query plan, and 45% on the fast plan.:
SELECT CA.CustAgentId,
CA.CustSupplyProductId,
CA.AgentId,
CA.StartDate,
CA.StopDate,
CA.DeliveryOption,
CA.Mon,
CA.Tue,
CA.Wed,
CA.Thu,
CA.Fri,
CA.Sat,
CA.Sun,
CA.CustSupplyAddressId,
CA.TimeStamp,
CA.CreatedDate,
CA.CreatedBy,
CA.UpdatedDate,
CA.UpdatedBy,
P.Code,
A.AgentName,
A.AgentGroupId,
ISNULL(APT.ProductType, 0)
FROM CustAgents AS CA
INNER JOIN CustSupplyAddresses AS CSA
ON CA.CustSupplyAddressId = CSA.CustSupplyAddressId
INNER JOIN CustSupplies AS CS
ON CSA.SupplyId = CS.SupplyId
INNER JOIN Agents A
ON A.AgentId = CA.AgentId
-- look for newspaper product type
LEFT JOIN AgentProductTypes APT
ON A.AgentId = APT.AgentId
AND APT.ProductType = 1 -- newspaper product type
LEFT JOIN CustSupplyProducts AS CSP
ON CA.CustSupplyProductId = CSP.CustSupplyProductId
LEFT JOIN Products AS P
ON CSP.ProductId = P.ProductId
inner join #t on #t.LookCustId = CS.CustomerId
where CSP.ProductId in (Select ProductId from #Products)
UNION --linked to supplies we recieve, but not pay for
SELECT CA.CustAgentId,
CA.CustSupplyProductId,
CA.AgentId,
CA.StartDate,
CA.StopDate,
CA.DeliveryOption,
CA.Mon,
CA.Tue,
CA.Wed,
CA.Thu,
CA.Fri,
CA.Sat,
CA.Sun,
CA.CustSupplyAddressId,
CA.TimeStamp,
CA.CreatedDate,
CA.CreatedBy,
CA.UpdatedDate,
CA.UpdatedBy,
P.Code,
A.AgentName,
A.AgentGroupId,
ISNULL(APT.ProductType, 0)
FROM CustAgents AS CA
INNER JOIN CustSupplyAddresses AS CSA
ON CA.CustSupplyAddressId = CSA.CustSupplyAddressId
INNER JOIN CustSupplies AS CS
ON CSA.SupplyId = CS.SupplyId
INNER JOIN Agents A
ON A.AgentId = CA.AgentId
-- look for newspaper product type
LEFT JOIN AgentProductTypes APT
ON A.AgentId = APT.AgentId
AND APT.ProductType = 1 -- newspaper product type
LEFT JOIN CustSupplyProducts AS CSP
ON CA.CustSupplyProductId = CSP.CustSupplyProductId
LEFT JOIN Products AS P
ON CSP.ProductId = P.ProductId
inner join #t on #t.LookCustId = CS.RecipientCustomerId
where CSP.ProductId in (Select ProductId from #Products)
This is the bad Query plan (from a very fast server, took 2 secs to return):
|--Sort(DISTINCT ORDER BY
[Union1024] ASC, [Union1025] ASC, [Union1026] ASC, [Union1027] ASC, [Union1028] ASC, [Union1029] ASC, [Union1030] ASC, [Union1031] ASC, [Union1032] ASC, [Union1033] ASC, [Union1034] ASC, [Union1035] ASC, [Union1036] ASC, [Union|--Concatenation
|--Compute Scalar(DEFINE
[Expr1011]=isnull([APT].[ProductType], 0)))| |--Nested Loops(Left Outer Join, OUTER REFERENCES
[CSP].[ProductId]))| |--Hash Match(Inner Join, HASH
[#t].[LookCustId])=([CS].[CustomerId]), RESIDUAL[#t].[LookCustId]=[CS].[CustomerId]))| | |--Table Scan(OBJECT
[tempdb].[dbo].[#t__________________________________________________________________________________________________________________00000000265C]))| | |--Nested Loops(Left Semi Join, WHERE
[CSP].[ProductId]=[#Products].[ProductId]))| | |--Nested Loops(Left Outer Join, WHERE
[APT].[AgentId]=[A].[AgentId]))| | | |--Hash Match(Inner Join, HASH
[A].[AgentId])=([CA].[AgentId]))| | | | |--Clustered Index Scan(OBJECT
[SubscriptionsNational].[dbo].[Agents].[PK_Agencies] AS [A]))| | | | |--Hash Match(Inner Join, HASH
[CA].[CustSupplyProductId])=([CSP].[CustSupplyProductId]), RESIDUAL[CSP].[CustSupplyProductId]=[CA].[CustSupplyProductId]))| | | | |--Hash Match(Inner Join, HASH
[CSA].[SupplyId])=([CS].[SupplyId]))| | | | | |--Merge Join(Inner Join, MERGE
[CSA].[CustSupplyAddressId])=([CA].[CustSupplyAddressId]), RESIDUAL[CA].[CustSupplyAddressId]=[CSA].[CustSupplyAddressId]))| | | | | | |--Index Scan(OBJECT
[SubscriptionsNational].[dbo].[CustSupplyAddresses].[PK_SupplyAddresses] AS [CSA]), ORDERED FORWARD)| | | | | | |--Clustered Index Scan(OBJECT
[SubscriptionsNational].[dbo].[CustAgents].[IX_CustAgents_1] AS [CA]), ORDERED FORWARD)| | | | | |--Index Scan(OBJECT
[SubscriptionsNational].[dbo].[CustSupplies].[IX_CustSupplies_1] AS [CS]))| | | | |--Clustered Index Scan(OBJECT
[SubscriptionsNational].[dbo].[CustSupplyProducts].[IX_CustSupplyProducts] AS [CSP]))| | | |--Table Spool
| | | |--Clustered Index Scan(OBJECT
[SubscriptionsNational].[dbo].[AgentProductTypes].[IX_AgencyProductTypes] AS [APT]), WHERE[APT].[ProductType]=1))| | |--Table Spool
| | |--Table Scan(OBJECT
[tempdb].[dbo].[#Products___________________________________________________________________________________________________________00000000265C]))| |--Clustered Index Seek(OBJECT
[SubscriptionsNational].[dbo].[Products].[PK_Products] AS [P]), SEEK[P].[ProductId]=[CSP].[ProductId]) ORDERED FORWARD)|--Compute Scalar(DEFINE
[Expr1023]=isnull([APT].[ProductType], 0)))|--Nested Loops(Left Outer Join, OUTER REFERENCES
[CSP].[ProductId]))|--Hash Match(Inner Join, HASH
[#t].[LookCustId])=([CS].[RecipientCustomerId]), RESIDUAL[#t].[LookCustId]=[CS].[RecipientCustomerId]))| |--Table Scan(OBJECT
[tempdb].[dbo].[#t__________________________________________________________________________________________________________________00000000265C]))| |--Nested Loops(Left Semi Join, WHERE
[CSP].[ProductId]=[#Products].[ProductId]))| |--Nested Loops(Left Outer Join, WHERE
[APT].[AgentId]=[A].[AgentId]))| | |--Hash Match(Inner Join, HASH
[A].[AgentId])=([CA].[AgentId]))| | | |--Clustered Index Scan(OBJECT
[SubscriptionsNational].[dbo].[Agents].[PK_Agencies] AS [A]))| | | |--Hash Match(Inner Join, HASH
[CA].[CustSupplyProductId])=([CSP].[CustSupplyProductId]), RESIDUAL[CSP].[CustSupplyProductId]=[CA].[CustSupplyProductId]))| | | |--Hash Match(Inner Join, HASH
[CSA].[SupplyId])=([CS].[SupplyId]))| | | | |--Merge Join(Inner Join, MERGE
[CSA].[CustSupplyAddressId])=([CA].[CustSupplyAddressId]), RESIDUAL[CA].[CustSupplyAddressId]=[CSA].[CustSupplyAddressId]))| | | | | |--Index Scan(OBJECT
[SubscriptionsNational].[dbo].[CustSupplyAddresses].[PK_SupplyAddresses] AS [CSA]), ORDERED FORWARD)| | | | | |--Clustered Index Scan(OBJECT
[SubscriptionsNational].[dbo].[CustAgents].[IX_CustAgents_1] AS [CA]), ORDERED FORWARD)| | | | |--Clustered Index Scan(OBJECT
[SubscriptionsNational].[dbo].[CustSupplies].[IX_CustomerSupplies] AS [CS]))| | | |--Clustered Index Scan(OBJECT
[SubscriptionsNational].[dbo].[CustSupplyProducts].[IX_CustSupplyProducts] AS [CSP]))| | |--Table Spool
| | |--Clustered Index Scan(OBJECT
[SubscriptionsNational].[dbo].[AgentProductTypes].[IX_AgencyProductTypes] AS [APT]), WHERE[APT].[ProductType]=1))| |--Table Spool
| |--Table Scan(OBJECT
[tempdb].[dbo].[#Products___________________________________________________________________________________________________________00000000265C]))|--Clustered Index Seek(OBJECT
[SubscriptionsNational].[dbo].[Products].[PK_Products] AS [P]), SEEK[P].[ProductId]=[CSP].[ProductId]) ORDERED FORWARD)And this is the good (fast) plan, running on the dev server:
|--Sort(DISTINCT ORDER BY
[Union1024] ASC, [Union1025] ASC, [Union1026] ASC, [Union1027] ASC, [Union1028] ASC, [Union1029] ASC, [Union1030] ASC, [Union1031] ASC, [Union1032] ASC, [Union1033] ASC, [Union1034] ASC, [Union1035] ASC, [Union1036] ASC, [Union|--Concatenation
|--Compute Scalar(DEFINE
[Expr1011]=isnull([APT].[ProductType], 0)))| |--Nested Loops(Left Outer Join, OUTER REFERENCES
[CSP].[ProductId]))| |--Nested Loops(Left Semi Join, WHERE
[CSP].[ProductId]=[#Products].[ProductId]))| | |--Nested Loops(Left Outer Join, OUTER REFERENCES
[A].[AgentId]))| | | |--Nested Loops(Inner Join, OUTER REFERENCES
[CA].[AgentId]))| | | | |--Bookmark Lookup(BOOKMARK
[Bmk1005]), OBJECT[SubscriptionsNational].[dbo].[CustSupplyProducts] AS [CSP]))| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES
[CA].[CustSupplyProductId]))| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES
[CSA].[CustSupplyAddressId]))| | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES
[CS].[SupplyId]))| | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES
[#t].[LookCustId]))| | | | | | | | |--Table Scan(OBJECT
[tempdb].[dbo].[#t__________________________________________________________________________________________________________________000000000027]))| | | | | | | | |--Clustered Index Seek(OBJECT
[SubscriptionsNational].[dbo].[CustSupplies].[IX_CustomerSupplies] AS [CS]), SEEK[CS].[CustomerId]=[#t].[LookCustId]) ORDERED FORWARD)| | | | | | | |--Clustered Index Seek(OBJECT
[SubscriptionsNational].[dbo].[CustSupplyAddresses].[IX_SupplyAddresses] AS [CSA]), SEEK[CSA].[SupplyId]=[CS].[SupplyId]) ORDERED FORWARD)| | | | | | |--Clustered Index Seek(OBJECT
[SubscriptionsNational].[dbo].[CustAgents].[IX_CustAgents_1] AS [CA]), SEEK[CA].[CustSupplyAddressId]=[CSA].[CustSupplyAddressId]) ORDERED FORWARD)| | | | | |--Index Seek(OBJECT
[SubscriptionsNational].[dbo].[CustSupplyProducts].[PK_CustSupplyProducts] AS [CSP]), SEEK[CSP].[CustSupplyProductId]=[CA].[CustSupplyProductId]) ORDERED FORWARD)| | | | |--Clustered Index Seek(OBJECT
[SubscriptionsNational].[dbo].[Agents].[PK_Agencies] AS [A]), SEEK[A].[AgentId]=[CA].[AgentId]) ORDERED FORWARD)| | | |--Clustered Index Seek(OBJECT
[SubscriptionsNational].[dbo].[AgentProductTypes].[IX_AgencyProductTypes] AS [APT]), SEEK[APT].[AgentId]=[A].[AgentId]), WHERE[APT].[ProductType]=1) ORDERED FORWARD)| | |--Table Scan(OBJECT
[tempdb].[dbo].[#Products___________________________________________________________________________________________________________000000000027]))| |--Clustered Index Seek(OBJECT
[SubscriptionsNational].[dbo].[Products].[PK_Products] AS [P]), SEEK[P].[ProductId]=[CSP].[ProductId]) ORDERED FORWARD)|--Compute Scalar(DEFINE
[Expr1023]=isnull([APT].[ProductType], 0)))|--Nested Loops(Left Outer Join, OUTER REFERENCES
[CSP].[ProductId]))|--Nested Loops(Left Semi Join, WHERE
[CSP].[ProductId]=[#Products].[ProductId]))| |--Nested Loops(Left Outer Join, OUTER REFERENCES
[A].[AgentId]))| | |--Nested Loops(Inner Join, OUTER REFERENCES
[CA].[AgentId]))| | | |--Bookmark Lookup(BOOKMARK
[Bmk1017]), OBJECT[SubscriptionsNational].[dbo].[CustSupplyProducts] AS [CSP]))| | | | |--Nested Loops(Inner Join, OUTER REFERENCES
[CA].[CustSupplyProductId]))| | | | |--Nested Loops(Inner Join, OUTER REFERENCES
[CSA].[CustSupplyAddressId]))| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES
[CS].[SupplyId]))| | | | | | |--Bookmark Lookup(BOOKMARK
[Bmk1014]), OBJECT[SubscriptionsNational].[dbo].[CustSupplies] AS [CS]))| | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES
[#t].[LookCustId]))| | | | | | | |--Table Scan(OBJECT
[tempdb].[dbo].[#t__________________________________________________________________________________________________________________000000000027]))| | | | | | | |--Index Seek(OBJECT
[SubscriptionsNational].[dbo].[CustSupplies].[IX_CustSupplies] AS [CS]), SEEK[CS].[RecipientCustomerId]=[#t].[LookCustId]) ORDERED FORWARD)| | | | | | |--Clustered Index Seek(OBJECT
[SubscriptionsNational].[dbo].[CustSupplyAddresses].[IX_SupplyAddresses] AS [CSA]), SEEK[CSA].[SupplyId]=[CS].[SupplyId]) ORDERED FORWARD)| | | | | |--Clustered Index Seek(OBJECT
[SubscriptionsNational].[dbo].[CustAgents].[IX_CustAgents_1] AS [CA]), SEEK[CA].[CustSupplyAddressId]=[CSA].[CustSupplyAddressId]) ORDERED FORWARD)| | | | |--Index Seek(OBJECT
[SubscriptionsNational].[dbo].[CustSupplyProducts].[PK_CustSupplyProducts] AS [CSP]), SEEK[CSP].[CustSupplyProductId]=[CA].[CustSupplyProductId]) ORDERED FORWARD)| | | |--Clustered Index Seek(OBJECT
[SubscriptionsNational].[dbo].[Agents].[PK_Agencies] AS [A]), SEEK[A].[AgentId]=[CA].[AgentId]) ORDERED FORWARD)| | |--Clustered Index Seek(OBJECT
[SubscriptionsNational].[dbo].[AgentProductTypes].[IX_AgencyProductTypes] AS [APT]), SEEK[APT].[AgentId]=[A].[AgentId]), WHERE[APT].[ProductType]=1) ORDERED FORWARD)| |--Table Scan(OBJECT
[tempdb].[dbo].[#Products___________________________________________________________________________________________________________000000000027]))|--Clustered Index Seek(OBJECT
[SubscriptionsNational].[dbo].[Products].[PK_Products] AS [P]), SEEK[P].[ProductId]=[CSP].[ProductId]) ORDERED FORWARD) -
Some questions & observations ...
- How many rows are in temp table #t ? it is being table-scanned.
>>where CSP.ProductId in (Select ProductId from #Products)
How many rows does this sub-select return ? Using IN () with a sub-select can cause performance issues, and you`ll usually get better performance using a correlated WHERE EXISTS sub-query instead.
>>UNION --linked to supplies we recieve, but not pay for
Are the resultsets of the 2 queries mutually exclusive ? If so, you should use UNION ALL, to prevent the server having to sort & get the distinct set.
-
>How many rows are in temp table #t
In the vast majority of cases (and certainly the one I'm concerend with here), only a single row.
>How many rows does this sub-select return ?
Normally 1, as high as 3. I optimised this out yesterday, and it made no discernable difference to performance, or the query plan chosen, so I reerted to teh preiously tested configuration.
>Are the resultsets of the 2 queries mutually exclusive
Unfortunately not. The number of cases of their being more than a dozen rows returned are extremely rare howeer, and the performance issues appear to be in getting the data to that point, rather than cleaning it up prior to return.
I think I've (partially) resolved the issue by re-writing the joins in a different order. Average SQL CPU time to execute is down to 75 from 2000, and export performance is up a lot. I haven't had a chance to do any serious performance testing, but it looks like I'm onto a winner.
Cheers for the help guys!!
Tim
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply