Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Poor query performance post SQL 2008 upgrade Expand / Collapse
Author
Message
Posted Thursday, November 19, 2009 5:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 6:52 AM
Points: 282, Visits: 1,065
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.
Post #821507
Posted Thursday, November 19, 2009 6:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 12:58 AM
Points: 1,904, Visits: 2,771
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?



----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #821541
Posted Thursday, November 19, 2009 10:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 6:52 AM
Points: 282, Visits: 1,065
Yes it did, SQL level = 100 and it can find the cpu and memory information.
Post #821768
Posted Thursday, March 18, 2010 9:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 5:03 PM
Points: 137, Visits: 394
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.
Post #886045
Posted Thursday, March 18, 2010 9:56 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 12:58 AM
Points: 1,904, Visits: 2,771
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.


----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #886050
Posted Friday, March 19, 2010 2:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:26 AM
Points: 1,949, Visits: 8,291
Can you post execution plans from both systems so that we can compare ?




Clear Sky SQL
My Blog
Kent user group
Post #886141
Posted Friday, March 19, 2010 2:33 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 12:58 AM
Points: 1,904, Visits: 2,771
If possible, add about 3 runs of IO stats as well...the more info you provide, the quicker and better assistance we can give.

----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #886148
Posted Friday, March 19, 2010 11:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 12, 2014 11:40 AM
Points: 2,795, Visits: 8,297
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



Post #886622
Posted Wednesday, September 15, 2010 12:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 7:02 AM
Points: 214, Visits: 439
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.


>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
I see stupid people
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Post #986037
Posted Wednesday, September 15, 2010 1:13 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:26 AM
Points: 1,949, Visits: 8,291
The major cause of performance problems after an upgrade is not updating statistics.

Try that , if thats no help then post the execution plan.





Clear Sky SQL
My Blog
Kent user group
Post #986052
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse