View Times out in SQL 2008

[JMac]

I just recently upgraded from SQL 2005 Standard to SQL 2008 Standard.
I have several views that were created in 2005 years ago and have
worked with no problems. When I run the views in SSMS 2008 I get a
time out error. When I run the views in the original VS 2005 report
they all work fine, but take several minutes to complete in the
upgraded VS 2008 report. Below is one of the views. Does anyone have
any ideas ?

Thanks

SELECT TOP (100) PERCENT CONVERT(CHAR(10),
dbo.ShiftSchedule.EndTime, 121) AS ShiftDate,
dbo.ShiftSchedule.ShiftName, COUNT(*) AS Count,
dbo.ShiftPeriodDetails.TargetProduction,
dbo.ShiftPeriodDetails.ShiftPeriod, dbo.ShiftPeriodDetails.SortOrder,
MIN(dbo.ProductionTracking.CSNID)
AS MinCSN, MAX(dbo.ProductionTracking.CSNID) AS
MaxCSN, dbo.ShiftPeriodDetails.HourofDay
FROM dbo.ShiftSchedule INNER JOIN
dbo.ProductionTracking ON
dbo.ShiftSchedule.StartTime <= dbo.ProductionTracking.a_Timestamp AND
dbo.ShiftSchedule.EndTime >
dbo.ProductionTracking.a_Timestamp RIGHT OUTER JOIN
dbo.ShiftPeriodDetails ON DATEPART(hour,
dbo.ProductionTracking.a_Timestamp) = dbo.ShiftPeriodDetails.HourofDay
GROUP BY CONVERT(CHAR(10), dbo.ShiftSchedule.EndTime, 121),
dbo.ShiftSchedule.ShiftName, dbo.ShiftPeriodDetails.TargetProduction,
dbo.ShiftPeriodDetails.ShiftPeriod,
dbo.ShiftPeriodDetails.SortOrder, dbo.ShiftPeriodDetails.HourofDay


ShiftSchedule Table


ID StartTime
EndTime ShiftName
5733 1/28/2012 3:00:00 PM 1/28/2012 11:00:00 PM Afternoons
5732 1/28/2012 7:00:00 AM 1/28/2012 3:00:00 PM Days
5731 1/27/2012 11:00:00 PM 1/28/2012 7:00:00 AM Nights
5730 1/27/2012 3:00:00 PM 1/27/2012 11:00:00 PM Afternoons
5729 1/27/2012 7:00:00 AM 1/27/2012 3:00:00 PM Days
5728 1/26/2012 11:00:00 PM 1/27/2012 7:00:00 AM Nights


ShiftPeriodDetails Table

ID ShiftPeriod TotalDur AvailDur TargetPro ShiftName
HourofDay SortOrder
1 7:00-8:00 60 56 66 Days 7 9
2 8:00-9:00 60 59 69 Days 8 10
3 9:00-10:00 60 50 59 Days 9 11
4 10:00-11:0060 59 69 Days 10 12
5 11:00-12:0060 40 47 Days 11 13
6 12:00-1:00 60 59 69 Days 12 14
7 1:00-2:00 60 50 59 Days 13 15
8 2:00-3:00 60 59 69 Days 14 16
9 3:00-4:00 60 56 66 Days 15 17
10 4:00-5:00 60 59 69 Days 16 18
11 5:00-6:00 60 50 59 Days 17 19
12 6:00-7:00 60 59 69 Days 18 20
13 7:00-8:00 60 40 47 Days 19 21
14 8:00-9:00 60 59 69 Days 20 22
15 9:00-10:00 60 50 59 Days 21 23
16 10:00-11:0060 59 69 Days 22 24
17 11:00-12:0060 56 66 Days 23 1
18 12:00-1:00 60 59 69 Days 0 2
19 1:00-2:00 60 50 59 Days 1 3
20 2:00-3:00 60 59 69 Days 2 4
21 3:00-4:00 60 40 47 Days 3 5
22 4:00-5:00 60 59 69 Days 4 6
23 5:00-6:00 60 50 59 Days 5 7
24 6:00-7:00 60 59 69 Days 6 8


ProductionTracking Table

ID CSNID a_TimeStamp
BuildCode
1082746 1474963 1/28/2012 12:35:32 PM 12
1082745 1474962 1/28/2012 12:34:40 PM 281
1082744 1474961 1/28/2012 12:33:53 PM 282
1082743 1474960 1/28/2012 12:33:06 PM 281
1082742 1474959 1/28/2012 12:31:47 PM 11
1082741 1474958 1/28/2012 12:30:48 PM 12
1082740 1474957 1/28/2012 12:30:18 PM 12
1082739 1474956 1/28/2012 12:29:22 PM 43

[Erland Sommarskog]

Post by JMac
I just recently upgraded from SQL 2005 Standard to SQL 2008 Standard.
I have several views that were created in 2005 years ago and have
worked with no problems. When I run the views in SSMS 2008 I get a
time out error. When I run the views in the original VS 2005 report
they all work fine, but take several minutes to complete in the
upgraded VS 2008 report. Below is one of the views. Does anyone have
any ideas ?

First of all, a view itself is only a macro which is expanded into a
query. Do these reports only run "SELECT * FROM thisview" or are there
WHERE conditions?

Looking at a cleaned-up version of the view:

SELECT CONVERT(CHAR(10), SS.EndTime, 121) AS ShiftDate,
SS.ShiftName, COUNT(*) AS Count, SPH.TargetProduction,
SPH.ShiftPeriod, SPH.SortOrder,
MIN(PT.CSNID) AS MinCSN, MAX(PT.CSNID) AS MaxCSN,
SPH.HourofDay
FROM dbo.ShiftSchedule SS
JOIN dbo.ProductionTracking PT ON SS.StartTime <= PT.a_Timestamp
AND SS.EndTime > PT.a_Timestamp
RIGHT JOIN dbo.ShiftPeriodDetails SPH ON
DATEPART(hour, PT.a_Timestamp) = SPH.HourofDay
GROUP BY CONVERT(CHAR(10), SS.EndTime, 121), SS.ShiftName,
SPH.TargetProduction, SPH.ShiftPeriod, SPH.SortOrder, SPH.HourofDay

I am not so surprised that things are slow. I am more surprised that things
are fast in SQL 2005. Then again, I don't know the data volumes or the
indexes. I can guess that ShiftPeriodDetails is a samll table with exactly
those 24 rows you posted, while the other are bigger. (I should also
add that queries with RIGHT JOIN gives me a headache.)

I assume that you have a number of queries where you join ShiftSchedule
ProductionTracking in the way you do above. There could be an idea to
add ShiftScheduleID to the ProductionTracking table, so that you can
make a simpler join. This will help the optimizer quite a bit.

I could also suggest other changes, but without knowledge of the full
picture, these ideas can be way off.

Keep in mind that SQL Server uses a cost-based optimizer that from
statistics *sampled* about the data makes *estimates* of what is the
best query plan. Statistics may be skewed, estimates may be wrong.
That the performance of a query degrades drastically is not unexpectd
in that light, partiularly if the query is "difficult".

Comparing the execution plans in SQL 2005 and SQL 2008 is a good start.

[JMac]

Post by Erland Sommarskog

Post by JMac
I just recently upgraded from SQL 2005 Standard to SQL 2008 Standard.
I have several views that were created in 2005 years ago and have
worked with no problems. When I run the views in SSMS 2008 I get a
time out error. When I run the views in the original VS 2005 report
they all work fine, but take several minutes to complete in the
upgraded VS 2008 report. Below is one of the views. Does anyone have
any ideas ?

First of all, a view itself is only a macro which is expanded into a
query. Do these reports only run "SELECT * FROM thisview" or are there
WHERE conditions?
SELECT  CONVERT(CHAR(10), SS.EndTime, 121) AS ShiftDate,
        SS.ShiftName, COUNT(*) AS Count, SPH.TargetProduction,
        SPH.ShiftPeriod, SPH.SortOrder,
        MIN(PT.CSNID) AS MinCSN, MAX(PT.CSNID) AS MaxCSN,
        SPH.HourofDay
FROM    dbo.ShiftSchedule SS
JOIN    dbo.ProductionTracking PT ON SS.StartTime <= PT.a_Timestamp
                                 AND SS.EndTime > PT.a_Timestamp
RIGHT   JOIN dbo.ShiftPeriodDetails SPH ON
                    DATEPART(hour, PT.a_Timestamp) = SPH.HourofDay
GROUP BY CONVERT(CHAR(10), SS.EndTime, 121), SS.ShiftName,
         SPH.TargetProduction, SPH.ShiftPeriod, SPH.SortOrder, SPH.HourofDay
I am not so surprised that things are slow. I am more surprised that things
are fast in SQL 2005. Then again, I don't know the data volumes or the
indexes. I can guess that ShiftPeriodDetails is a samll table with exactly
those 24 rows you posted, while the other are bigger. (I should also
add that queries with RIGHT JOIN gives me a headache.)
I assume that you have a number of queries where you join ShiftSchedule
ProductionTracking in the way you do above. There could be an idea to
add ShiftScheduleID to the ProductionTracking table, so that you can
make a simpler join. This will help the optimizer quite a bit.
I could also suggest other changes, but without knowledge of the full
picture, these ideas can be way off.
Keep in mind that SQL Server uses a cost-based optimizer that from
statistics *sampled* about the data makes *estimates* of what is the
best query plan. Statistics may be skewed, estimates may be wrong.
That the performance of a query degrades drastically is not unexpectd
in that light, partiularly if the query is "difficult".
Comparing the execution plans in SQL 2005 and SQL 2008 is a good start.
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

These views are used in a SSRS report with a WHERE condition on the
timestamp.
You are correct - the ShiftPeriodDetails table does not grow and the
others do. The ShiftSchedule Table grows by 3 rows a day, the other
table by about 1500 rows per day.
With my being a SQL novice, comparing the execution plans is somewhat
over my head. I did look at both and see that they appear quite
different. I still working on trying to understand the reasons.

Thanks

[Erland Sommarskog]

Post by JMac
These views are used in a SSRS report with a WHERE condition on the
timestamp.
You are correct - the ShiftPeriodDetails table does not grow and the
others do. The ShiftSchedule Table grows by 3 rows a day, the other
table by about 1500 rows per day.
With my being a SQL novice, comparing the execution plans is somewhat
over my head. I did look at both and see that they appear quite
different. I still working on trying to understand the reasons.

Maybe then you can run the queries in SSMS with Actual Execution Plan
enabled, and then save the execution plans and then upload the files
somewhere? (Please don't include them in a post, since the XML will be
quite mashed. And you if you include them as attachments, I will not
see them, since my news host is strictly non-binary.)

[JMac]

Post by Erland Sommarskog

Post by JMac
These views are used in a SSRS report with a WHERE condition on the
timestamp.
You are correct - the ShiftPeriodDetails table does not grow and the
others do. The ShiftSchedule Table grows by 3 rows a day, the other
table by about 1500 rows per day.
With my being a SQL novice, comparing the execution plans is somewhat
over my head. I did look at both and see that they appear quite
different. I still working on trying to understand the reasons.

Maybe then you can run the queries in SSMS with Actual Execution Plan
enabled, and then save the execution plans and then upload the files
somewhere? (Please don't include them in a post, since the XML will be
quite mashed. And you if you include them as attachments, I will not
see them, since my news host is strictly non-binary.)
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

If you have an oppourtunity, I've uploaded the plans to the following
links.

http://www.4shared.com/document/Uj6DO0L5/2008_Execution_plan.html

http://www.4shared.com/file/bbugo_pJ/SQL_2005_EP.html

Thanks

[Erland Sommarskog]

Post by JMac
If you have an oppourtunity, I've uploaded the plans to the following
links.

I would have preferred if you had posted the plan for the actual queries
than for the entire view. (And I would have preferred if you had used a
different site. I must have taken me half an hour to download the files.
First Firefox just keep spinning waiting for www.goggle-analtics.com, not
the first time that happens. This time I killed with AdBlock. But alas,
the download did not work. I was eventually able to download the files
with Opera.)

But I did notice one thing: the statistics are grossly inaccurate on
SQL 2008, while they are spot on target on SQL 2005. Statistics are often
lost when you upgrade.

Run UPDATE STATISTICS tbl WITH FULLSCAN on both the bigger tables. (Replace
"tbl" with the actual table name.)

I would also ask you do consider to materialise the ShiftScheduleId in
Production Tracking. This join:

JOIN    dbo.ProductionTracking PT ON SS.StartTime <= PT.a_Timestamp
                                 AND SS.EndTime > PT.a_Timestamp

Is very difficult for the optimizer to get right.