November 7, 2011 at 4:46 pm
HI All,
I have a most vexing problem that I haven't been able to get to the bottom of yet.
I have a couple of users who have run into query response issues on our data warehouse production database HCP. It looks to have coincided with the promotion of database changes from the UAT database (db) to production.
One of the users has provided me with a query that is taking two hours to complete in production which I have run against the UAT db (in management studio)and the result set (9500 rows) returns in approx. 1 minute. FYI the query uses two views joined together (users lack technical knowhow to develop their own queries).
If I run the query against the production DB, the first 100 odd rows return in around 1 minute but then it takes > 1 hour for the remaining 9 odd thousand to display in the result set.
The UAT and prod db are on the same server.
I have compared indexes between UAT and production on all tables used in this query and they are identical.
Statistics are up to date.
The query has a group by clause (which is not actually required as the user does not use aggregates in the SELECT list)
I ran an explain plan for both queries and the production explain does a STREAM AGGREGRATE for the GROUP BY clause, whereas the UAT explain shows a DISTINCT SORT. I would have expected the explain plans to be the same? UAT and production databases contain virtually identical data (e.g. UAT 118490 rows for 1 table versus 118516 rows in prod).
WITH (NOLOCK) is used on all tables in the query
The other user who is experiencing performance issues is accessing the production DB from another DB on the same server. I am waiting for further detail from him.
How can i ascertain why the same query is performing so badly in the production DB verses the UAT DB?
Any advice would be greatly appreciated. Please let me know if you'd like the explain plan output.
FYI, if I remove the GROUP BY clause from the query, the production Db performs comparably to UAT.
Help! :unsure:
Here is the user view query for reference:
SELECT
TheatreCaseStartDateTime
,SurgeryStartDateTime
,SurgeryEndDateTime
,datediff(mi,SurgeryStartDateTime,SurgeryEndDateTime) as 'surgery Time'
FROM
TheatreCaseView t
INNER JOIN InpatientEpisodeView i ON t.ProviderSpell = i.ProviderSpell
WHERE
TheatreCaseStartDateTime >= '2010/07/05'
AND TheatreCaseStartDateTime < '2011/09/27'
AND BookingCancellationDateTime IS NULL
AND TheatreLocationCode LIKE 'Nth%'
AND AnaestheticTypeDesc LIKE('%GEN%') --- and AdmissionAge <= '6'
GROUP BY TheatreCaseStartDateTime
,SurgeryStartDateTime
,SurgeryEndDateTime
November 8, 2011 at 5:32 pm
MY 2nd user experiencing response problems came back to me and it looks like it's the one table causing problems. My 2nd user also has a 'group by' on this one table and when I run an explain plan, again, the main difference appears to be that in the production db a STREAM AGGREGATE is being performed in and in the UAT database there isn't.
I also restored a back-up of the production db from just before the db upgrade and ran the query against that, and no STREAM AGGREGRATE appears in the explain plan.
What is causing the STREAM AGGREGRATE operation to be chosen to be performed?
November 9, 2011 at 7:19 am
Claudie36 (11/8/2011)
MY 2nd user experiencing response problems came back to me and it looks like it's the one table causing problems. My 2nd user also has a 'group by' on this one table and when I run an explain plan, again, the main difference appears to be that in the production db a STREAM AGGREGATE is being performed in and in the UAT database there isn't.I also restored a back-up of the production db from just before the db upgrade and ran the query against that, and no STREAM AGGREGRATE appears in the explain plan.
What is causing the STREAM AGGREGRATE operation to be chosen to be performed?
The Stream Aggregate operator groups rows by one or more columns and then calculates one or more aggregate expressions returned by the query.
http://technet.microsoft.com/en-us/library/ms189907.aspx
The Distinct Sort logical operator scans the input, removing duplicates and sorting by the columns specified in the DISTINCT ORDER BY:() predicate of the Argument column.
http://msdn.microsoft.com/en-us/library/ms177486.aspx
A Stream Aggregate is a normal operation to be expected with a GROUP BY query, but I'm not sure why an identical SQL statement on an identical data model with similar data and hardware would choose DISTINCT ORDER BY instead of.
However, looking at the SQL it's unclear why you need to perform a GROUP BY at all. I assume the goal is to remove from the result what would otherwise be unneeded rows containing duplicated columns. This is an expensive task which should be avoided if possible. So consider modifying your table joins, joining on additional columns or adding conditions that would prevent duplicates without the need to perform a GROUP BY or DISTINCT. This would need to be considered in both the user's SQL query and also the underlying SQL within the views.
Also, even though both UAT and Production have a similar volume of data, the data distribution may signigicantly differ in some way that it results in a different execution plan. For this specific table, compare the definition of STATISTICS between the two servers, and that may provide clues. Perhaps one environment conatins "duplicate" key columns and the other doesn't.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply