troubleshooting Question

SQL Queries run at different performance

Avatar of Corey_819
Corey_819 asked on
Microsoft SQL Server 2005SQL
4 Comments1 Solution190 ViewsLast Modified:
Hello and sorry to bother everyone with probably an easy question, but I am stuck. What I have is an Select statement I run in one SQL database and it runs very quick less than a second. However, if I go to the same database schema just more data in the tables and run the exact same query I am running at 9 - to 10 seconds. I have ran the SHOW Plan XML and the one database takes the query down one plan/path and the other one take it down another plan/path. Any thoughts based on what you may see in the plan that I am not.

1st plan takes 9 - to 10 seconds
2nd plan take which is different and the one thing I am not sure why takes less than a second.

select distinct dbo . CV3BasicObservation . ItemName , dbo . CV3BasicObservation . ArrivalDtm , dbo . CV3BasicObservation . Value from dbo . CV3BasicObservation inner join dbo . CV3ResultCatalogItem on dbo . CV3BasicObservation . ResultItemGUID = dbo . CV3
   |--Stream Aggregate(GROUP BY:([UH1].[dbo].[CV3BasicObservation].[ArrivalDtm], [UH1].[dbo].[CV3BasicObservation].[ItemName], [UH1].[dbo].[CV3BasicObservation].[Value]))                                                                                       
        |--Parallelism(Gather Streams, ORDER BY:([UH1].[dbo].[CV3BasicObservation].[ArrivalDtm] ASC, [UH1].[dbo].[CV3BasicObservation].[ItemName] ASC, [UH1].[dbo].[CV3BasicObservation].[Value] ASC))                                                           
             |--Nested Loops(Inner Join, OUTER REFERENCES:([UH1].[dbo].[CV3CatalogClassTypeValue].[CatalogMasterGUID], [Expr1018]) WITH ORDERED PREFETCH)                                                                                                        
                  |--Sort(ORDER BY:([UH1].[dbo].[CV3BasicObservation].[ArrivalDtm] ASC, [UH1].[dbo].[CV3BasicObservation].[ItemName] ASC, [UH1].[dbo].[CV3BasicObservation].[Value] ASC))                                                                        
                  |    |--Hash Match(Inner Join, HASH:([UH1].[dbo].[CV3ResultCatalogItem].[OrderMasterItemGUID])=([UH1].[dbo].[CV3CatalogClassTypeValue].[CatalogMasterGUID]), RESIDUAL:([UH1].[dbo].[CV3ResultCatalogItem].[OrderMasterItemGUID]=[UH1].[dbo].[CV
                  |         |--Parallelism(Distribute Streams, Broadcast Partitioning)                                                                                                                                                                           
                  |         |    |--Merge Join(Inner Join, MERGE:([UH1].[dbo].[CV3ResultCatalogItem].[GUID])=([UH1].[dbo].[CV3BasicObservation].[ResultItemGUID]), RESIDUAL:([UH1].[dbo].[CV3BasicObservation].[ResultItemGUID]=[UH1].[dbo].[CV3ResultCatalogItem
                  |         |         |--Index Scan(OBJECT:([UH1].[dbo].[CV3ResultCatalogItem].[CV3ResultCatalogItemPK]), ORDERED FORWARD)                                                                                                                       
                  |         |         |--Sort(ORDER BY:([UH1].[dbo].[CV3BasicObservation].[ResultItemGUID] ASC))                                                                                                                                                 
                  |         |              |--Nested Loops(Inner Join)                                                                                                                                                                                           
                  |         |                   |--Index Seek(OBJECT:([UH1].[dbo].[CV3ClientVisit].[CV3ClientVisitPK]), SEEK:([UH1].[dbo].[CV3ClientVisit].[GUID]=[@0]) ORDERED FORWARD)                                                                         
                  |         |                   |--Parallelism(Gather Streams)                                                                                                                                                                                   
                  |         |                        |--Clustered Index Scan(OBJECT:([UH1].[dbo].[CV3BasicObservation].[BasicObservationClustIdx]), WHERE:([UH1].[dbo].[CV3BasicObservation].[ClientVisitGUID]=[@0] AND [UH1].[dbo].[CV3BasicObservation].[Arriva
                  |         |--Index Scan(OBJECT:([UH1].[dbo].[CV3CatalogClassTypeValue].[CV3CatalogClassTypeValuePK]))                                                                                                                                          
                  |--Clustered Index Seek(OBJECT:([UH1].[dbo].[CV3OrderCatalogMasterItem].[CV3OrderCatalogMasterItemPK] AS [CV3OrderCatalogMasterItem]), SEEK:([CV3OrderCatalogMasterItem].[GUID]=[UH1].[dbo].[CV3CatalogClassTypeValue].[CatalogMasterGUID]) ORD
 
 
 
 
select distinct dbo . CV3BasicObservation . ItemName , dbo . CV3BasicObservation . ArrivalDtm , dbo . CV3BasicObservation . Value from dbo . CV3BasicObservation inner join dbo . CV3ResultCatalogItem on dbo . CV3BasicObservation . ResultItemGUID = dbo . CV3
  |--Stream Aggregate(GROUP BY:([Replica_UH1].[dbo].[CV3BasicObservation].[ArrivalDtm], [Replica_UH1].[dbo].[CV3BasicObservation].[ItemName], [Replica_UH1].[dbo].[CV3BasicObservation].[Value]))                                                               
       |--Nested Loops(Inner Join)                                                                                                                                                                                                                              
            |--Index Seek(OBJECT:([Replica_UH1].[dbo].[CV3ClientVisit].[CV3ClientVisitPK]), SEEK:([Replica_UH1].[dbo].[CV3ClientVisit].[GUID]=[@0]) ORDERED FORWARD)                                                                                            
            |--Parallelism(Gather Streams, ORDER BY:([Replica_UH1].[dbo].[CV3BasicObservation].[ArrivalDtm] ASC, [Replica_UH1].[dbo].[CV3BasicObservation].[ItemName] ASC, [Replica_UH1].[dbo].[CV3BasicObservation].[Value] ASC))                              
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([CV3OrderCatalogMasterItem].[GUID]))                                                                                                                                                             
                      |--Sort(ORDER BY:([Replica_UH1].[dbo].[CV3BasicObservation].[ArrivalDtm] ASC, [Replica_UH1].[dbo].[CV3BasicObservation].[ItemName] ASC, [Replica_UH1].[dbo].[CV3BasicObservation].[Value] ASC))                                           
                      |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Replica_UH1].[dbo].[CV3ResultCatalogItem].[OrderMasterItemGUID]))                                                                                                                     
                      |         |--Nested Loops(Inner Join, OUTER REFERENCES:([Replica_UH1].[dbo].[CV3BasicObservation].[ResultItemGUID]))                                                                                                                      
                      |         |    |--Clustered Index Scan(OBJECT:([Replica_UH1].[dbo].[CV3BasicObservation].[BasicObservationClustIdx]), WHERE:([Replica_UH1].[dbo].[CV3BasicObservation].[ClientVisitGUID]=[@0] AND [Replica_UH1].[dbo].[CV3BasicObservation
                      |         |    |--Index Seek(OBJECT:([Replica_UH1].[dbo].[CV3ResultCatalogItem].[CV3ResultCatalogItemPK]), SEEK:([Replica_UH1].[dbo].[CV3ResultCatalogItem].[GUID]=[Replica_UH1].[dbo].[CV3BasicObservation].[ResultItemGUID]) ORDERED FOR
                      |         |--Clustered Index Seek(OBJECT:([Replica_UH1].[dbo].[CV3OrderCatalogMasterItem].[CV3OrderCatalogMasterItemPK] AS [CV3OrderCatalogMasterItem]), SEEK:([CV3OrderCatalogMasterItem].[GUID]=[Replica_UH1].[dbo].[CV3ResultCatalogIte
                      |--Clustered Index Seek(OBJECT:([Replica_UH1].[dbo].[CV3CatalogClassTypeValue].[MasterAndClassTypeClustIdx]), SEEK:([Replica_UH1].[dbo].[CV3CatalogClassTypeValue].[CatalogMasterGUID]=[Replica_UH1].[dbo].[CV3OrderCatalogMasterItem].[GU
ASKER CERTIFIED SOLUTION
Daniel Wilson

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros