June 30, 2008 at 1:51 pm
I did update stats on all objects with fullscan.
The subquery in the criteria statement was the culprit. I removed it Friday evening b/c not only is that really inefficient but, under our business rules, it would always return true. (not sure what the original developers intent was)
Without that subquery the view returns all rows in less than a second on the new 2005 machine (compared to 30-40 mins, wow!)
I removed it from my production 2000 server as well, it runs in 2 seconds now compared to 7 seconds.
I'm hoping that this 'bad query' was the culprit in the performances issues I was having - it would have been running on the morning of my attempted server switch. I also still think the difference in how 2000 and 2005 dealt with this view is amazing.
June 30, 2008 at 2:08 pm
I'm hoping that this 'bad query' was the culprit in the performances issues I was having - it would have been running on the morning of my attempted server switch. I also still think the difference in how 2000 and 2005 dealt with this view is amazing.
I have little doubt that this query was at least partially responsible for extended periods of poor performance. 30 mins of that kind of stress is not insignificant!
You got hit by what I call a greater-good scenario. In total 2005 (especially with SP2+ which fixed some very significant procedure cache bloat and temp db issues) is a much better engine. But like I said, some queries definitely were worse off and if you get hit by them it can really suck!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 1, 2008 at 5:29 am
) You need to start using the correct datatypes. This is a VERY common error by database developers. For example, (CONVERT(varchar, GETDATE(), 101) <= dbo.ACADEMICCALENDAR.END_DATE) requires an implicit conversion, which causes numerous problems both CPU use and optimizer estimation issues.
What the correct code:)
July 1, 2008 at 6:58 am
In given case it isn't a datatype declaration, just not using the correct type due to the cast. I assume you were doing 101 convert to remove the time from getdate. Simply wrap that in an additional cast back to datetime datatype:
(CAST(CONVERT(varchar, GETDATE(), 101) as datetime) <= dbo.ACADEMICCALENDAR.END_DATE)
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply