February 28, 2012 at 7:37 am
Hi, we have a sproc which runs on sql server 2000, (2gb memory, 2 proc server) in about 9 minutes. We recently upgraded to sql server 2008 R2 and this same sproc takes approx 5 hours to complete and pegs cpu at 100%. The new server is a VM server (2 proc, 16gm memory, 64bit, std 2008 R2). I've rebuilt the indexes, upgraded the database, and updated all statistics. Is there something with the way 2008 handles views that is different than 2000? Any ideas on what to check?
thanks
February 28, 2012 at 8:05 am
Not clear from your post where Views come into it? (Yet you mention them twice.)
Can you please explain more fully.
February 28, 2012 at 8:10 am
the sproc is doing a select against 4 views which are comprised of several different base tables. These views exist in both environments and are not indexed in either environment.
February 28, 2012 at 8:24 am
Compare the execution plans and everything will be much clearer.
-- Gianluca Sartori
February 28, 2012 at 8:34 am
If you have upgraded from SQL 2000, firstly check that you've specifically updated statistics WITH FULLSCAN.
Other than that, you may be hitting an edge case where SQL 2000 actually came up with a better plan - these aren't too uncommon, especially when dealing with complex queries. SQL 2005/8 introduced a lot of change in the optimiser based on common usage patterns, but it's by no means perfect.
It sounds like there'd be considerable scope for performance tuning if this is joining 4 base views together, after checking statistics, I'd concentrate on that as there may be no other magic bullet.
February 28, 2012 at 8:39 am
HowardW (2/28/2012)
Other than that, you may be hitting an edge case where SQL 2000 actually came up with a better plan - these aren't too uncommon, especially when dealing with complex queries. SQL 2005/8 introduced a lot of change in the optimiser based on common usage patterns, but it's by no means perfect.
Quite true. SQL 2000 used to have a wild preference for loop joins, which is not always as bad as one could think, after all. 😉
-- Gianluca Sartori
February 28, 2012 at 1:02 pm
ok...just an update on this. The sql 2000 server had 8 processors where the 2008 server had only 2. We bumped that up to 4 and had much better results as far as speed. We went from processing the example query from 5 hours to 5 minutes. When running the entire process I was hoping for great results but after 45 minutes of processing it blew up tempdb. On the old sql 2000 server we have allocated 50gb for tempdb and it doesn't use it all. On the new 2008 server I have 130gb available for tempdb and it chewed it up in 45 minutes? I agree with what you're saying regarding the query plan being different but would it be so different that it would use tempdb this way?
thanks for all the replies.
February 28, 2012 at 1:15 pm
Mark Thornton (2/28/2012)
ok...just an update on this. The sql 2000 server had 8 processors where the 2008 server had only 2. We bumped that up to 4 and had much better results as far as speed. We went from processing the example query from 5 hours to 5 minutes. When running the entire process I was hoping for great results but after 45 minutes of processing it blew up tempdb. On the old sql 2000 server we have allocated 50gb for tempdb and it doesn't use it all. On the new 2008 server I have 130gb available for tempdb and it chewed it up in 45 minutes? I agree with what you're saying regarding the query plan being different but would it be so different that it would use tempdb this way?thanks for all the replies.
1) You haven't confirmed whether you updated statistics WITH FULLSCAN (just running sp_updatestats does not do this)
2) Save the actual execution plans of both queries and upload them in a post for some more specific advice.
February 28, 2012 at 2:39 pm
ran update statistics with fullscan on the underlying base tables. Let me get the plans.
February 28, 2012 at 10:13 pm
Mark Thornton (2/28/2012)
When running the entire process I was hoping for great results but after 45 minutes of processing it blew up tempdb. On the old sql 2000 server we have allocated 50gb for tempdb and it doesn't use it all. On the new 2008 server I have 130gb available for tempdb and it chewed it up in 45 minutes? I agree with what you're saying regarding the query plan being different but would it be so different that it would use tempdb this way?thanks for all the replies.
Can't tell from here but nearly 50GB of usage on SQL Server 2000 tells me that you had a many-to-many join in the works that you've probably overcome either with a GROUP BY or a DISTINCT. I'm not sure why it's blowing the oil pan off of Temp DB in the new server but it also wasn't doing so well on the old server.
My recommendation is to look at the ACTUAL execution plan on the old server (because it does run there) and look for some really fat arrows that contain rowcounts much larger than the original tables. Those arrows will lead you to the problem with Temp DB.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 29, 2012 at 1:29 am
Tempdb probably blows because of excessive parallelism. I've seen this happening quite often.
This means you should review your indexes, because the optimizer most likely chooses to ignore some of those in favor of some huge parallel hash on the clustered index.
If this is enteprise edition, an indexed view could be a good choice.
However, without seeing the execution plans, all the above falls in the "wild guess" category.
Can you post the execution plans please?
-- Gianluca Sartori
February 29, 2012 at 1:46 am
The fact that adding additional processors increased the query execution speed suggested that the query needs to be re written . I don't think the execution of one single procedure determines the addition of more CPU and RAM.
Have you checked Max DOP and the SQL Server memory stetting AWE etc.
There may be a few functions in SQL Server 2008 that you could use to improve performance. But first compare the execution plans.
February 29, 2012 at 3:18 am
Given that it's a sproc based on several views, you could also try to compare the before and after performance of the individual views. e.g. if one view suddenly takes 10 times longer to finish than prior to the upgrade, while the others finish in more or less the same time, then this is a clue as to where to concentrate your efforts.
But the advice to compare execution plans is also sound.
Jeff suggested some sort of many to many join was at work. This also sounds like a very good place to look...what fields are used to join the views in the stored procedure. If the views individually appear to perform as before, then there's a good chance the problem lies in how they are subsequently joined. For each view, identify what column(s) form a unique key, then ensure that one of these is being used on one side of the join i.e. that you're always in a 1..many situation. As Jeff said, a group by or distinct is a common clue that an incorrect join is being swept under the carpet.
Good luck,
David McKinney.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply