November 19, 2009 at 5:08 am
Hi Guys
I have noticed that some of my queries are taking almost 4 x as much time to execute after upgrading to sql 2008, is there any reason for this, indexes defraged and statistics updated. I dropped statistics as part of the upgrade. Auto create statistics ON as aprt of db settings.
Its fairly obvious that queries from stord procedures which might be taking less than a second on SQL 2000, could be taking 8 seconds on SQL 2008. I'm wondering what lines of enquiries to make here.
November 19, 2009 at 6:26 am
I am assuming you upgraded on the same server like you mentioned?
One question, maybe it doesnt mean anything, just a guess...
Did you leave your databases as level 80,90 compatibility? or did you upgrade to 100?
and
Does the new version pick up all your CPU's? Memory?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
November 19, 2009 at 10:03 am
Yes it did, SQL level = 100 and it can find the cpu and memory information.
March 18, 2010 at 9:44 pm
Hey, we're experiencing this exact same issue. It's wreaking havoc on our system!! Is there anyone out there with ideas on what we could look for in figuring out this horrible issue??? We ended up building a new server with 2008 and renaming it to our existing primary SQL server, thereby upgrading our primary SQL server to 2008. Now we're having issues with jobs taking much longer to execute than they used to, and some views are taking sometimes 7-8 minutes to run simple queries from!
This is crazy. Any help or idea would be appreciated!
Thanks in advance.
March 18, 2010 at 9:56 pm
Now that you are on 2k8, use the excellent** new Activity Monitor to see which processes are taking long AND why?
check your SQL logs for any errors or infor messages that might relate.
take one the 8-minute-to-run views, and investigate the correlating tables indexes?
there are quite a few things to investigate here.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 19, 2010 at 2:17 am
Can you post execution plans from both systems so that we can compare ?
March 19, 2010 at 2:33 am
If possible, add about 3 runs of IO stats as well...the more info you provide, the quicker and better assistance we can give.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 19, 2010 at 11:57 am
We had performance problems going from 2000 to 2005 ... maybe 2008 is similar ?
Some of them were resolved after closely looking at the joins and finding we needed to modify or add the CONVERT functions between dissimilar data types. Not sure why, but it seemed as though 2000 was more forgiving of data type differences in joins. Look at some executions plans for areas to tune.
See my and other responses in this similar thread:
http://www.sqlservercentral.com/Forums/Topic809627-149-1.aspx
September 15, 2010 at 12:22 am
Hi
I know this is an old post but did anyone find a solution to this. Redoing all views, SPs and other scripts to optimize is crazy. I work with a system that has over 1000 views so redoing them is out of the question. Any suggestions will be weolcomed.
Why is SQL 2008 execution plans so bad?? View execution time increased from 1sec to 9 min after upgrade.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
September 15, 2010 at 1:13 am
The major cause of performance problems after an upgrade is not updating statistics.
Try that , if thats no help then post the execution plan.
September 15, 2010 at 2:05 am
Hi
I did do a sp_updatestats and index rebuild. This did not change anything. I am running stats update with fullscan. I will post my findings after update and also add execution plan if it is still slow.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
September 15, 2010 at 4:26 am
The problem still persists. What I have done after upgrade.
Rebuild all indexes on tables fragmented more than 10%
Updated all stats on all tables with full scan
DBCC UPDATEUSAGE(HRTST)
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
I ran the script in the upgraded DB and it took 8 min. In the SQL 2000 DB it runs 1 sec.
This must be a SQL 2008 problem because it is using a very poor execution plan. Please advise.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
September 15, 2010 at 4:29 am
If you can send the graphical execution plan, I may be able to help you.
In my own experience, when I had same issues, it could be due to the following
Bad query plan.
Parallelism (depending on what your workload is)
If you still have the older version of SQL say SQL 2000 on a separate machine, have you compared the query plans ?
September 15, 2010 at 4:42 am
Please post the plans as per
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 15, 2010 at 4:45 am
Just as a stab in the dark , are you joining on the results of udfs ?
You might be seeing this....
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply