June 14, 2010 at 11:05 am
Hello all,
I'm hoping someone can shed some like on this issue from me.
We just migrated from MS SQL 2005 to 2008. For the most part, it went smoothly. However, it seems that the performance of queries that involve sub-queries to to statistical type analysis, is exponentially slower.
In my case, I'm dealing with golf scores, so when I try to calculate things like Greens in Regulation and Sand Saves for all players in an event, what used to take about 1 second in MS SQL 2005, took 38 seconds in MS SQL 2008, and on better hardware at that.
If I try to isolate a specific sub-query, looks like each sub-query on 2008 is taking several seconds, as opposed to fractions of a second on 2005.
I'm just a developer, the networking guys moved the actual databases, but we are all at a loss on this one. Any help/suggestions appreciated.
June 14, 2010 at 11:11 am
I'll start by gathering fresh performance statistics on SS2K8.
By any chance... do you have either exec plans from SS2K5 or the old SS2K5 still available?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 14, 2010 at 11:16 am
Hi, thanks for a quick reply. How do I gather fresh performance statistics on SS2K8.
Also, How do I get and share the exec plans from SS2K5 or the old SS2K5?
We do have both DBs still running, and I can run the queries against them for testing.
June 14, 2010 at 11:29 am
In regards to gathering stats, fresh stats can be collected using sp_createstats, check here... http://kb.dbatoolz.com/tp/3374.how_to_resize_and_multiplex_redo_group_members.html
Then I'll enable automatic statistics update.
In regards to SS2K5 vs SS2K8 exec plans, the idea is to compare exec plans of the same query running in each one of the environments.
If both databases have the same data and SS2K5 queries are faster chances are SS2K5 exec plan is much better. If gathering fresh stats on SS2K8 doesn't solve the issue I'll try adding "hints" to query forcing a exec plan like the one that shows on SS2K5.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 14, 2010 at 11:42 am
Ok, here are the execution plans. They are pretty big. And do look different.
June 14, 2010 at 5:19 pm
Did you rebuild your indexes after the migration ?
My first take on the query
I strongly suggest that you
- Break the query into smaller logical groupings to arrive at the desired result
- Derive columns and table to be avoided
- Don’t use “ * “ in the select list instead please include only the required fields
- Try and avoid complicated case statements in the select list
FYI - This is a very long query and irrespective 2005, 2008 you are going to face problems in time if the coding style is not changed. This query has the potential to lock , block all the tables in concern
June 15, 2010 at 11:15 am
Ok, looks like my initial assessment of the slow down was wrong. We went back to 2005, created a new event, ran a simulation and it's also slow.
I'm not too sure about the indexes, all the primary keys are there, as well as the foreign keys. But in SQL Management Studio, if I run a query for the old event it runs really fast, if I run it for the new event, it's slow, but suggests I add a couple of specific indexes. If I add those that query gets a lot faster, but another query starts being slow. Seems kinda insane, and I totally can't figure out what's causing it.
So I don't think it's a 2008 issue anymore, but it does seem to be related to indexes and new data.
June 16, 2010 at 9:36 am
If im not mistaken this is called the tripping point.
This is typical of how query optimizer handles such data. For some data the procedure works better and for others it works not so well, this is because the query optimizer reuses existing query plans.
Let say u flush the proc cache and execute the procedure with the parameter which takes a longer time first, you would notice the procedures performs better. And the other procedure will work at similar performance level.
You can use the following DBCC to free the proc cache ; “DBCC FREEPROCCACHE” ( please don’t execute this on you production server if you are not sure of the consequences )
If you want to help the procedure cache to some up with a better plan u need to stat with breaking the procedure.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply