June 22, 2010 at 9:47 am
We upgraded our database to 2008 from 2000 on Sunday. The performance has become very bad. To the point that people can't even enter timesheets. I've read here that older ANSI style joins are not handled well in 2008. Most everything I have read is that 2008 is so great and very little on problems it may cause. Our queries use a lot of joins but they aren't the older style (they all have Inner Join, Right Outer, etc). We also use alot of temp tables and sometimes run joins with those. Other than that I'm not sure there's anything out of the ordinary.
I would like to know if there are other things in 2008 that we could look at as being the possible cause of this. Has anyone else had similar issues with their apps after upgrading and if so how did you resolve them?
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 22, 2010 at 11:03 am
Hi,
How did you upgrade the databases ? Was it an in-place upgrade, or did you restore backups / attached db files ? Did you use SQL Upgrade Advisor ?
You may consider the following post upgrade tasklist:
- Run DBCC CHECKDB with DATA_PURITY to check datatype ranges.
- Run DBCC UPDATEUSAGE on all user tables.
- Rebuild all the clustered indexes or repopulate the statistics.
- Turn PAGE_VERIFY to ON in each user database.
Usually you may want to use SQL Upgrade Advisor to review the code and detect possible breaking changes, like outer joins syntax, etc... Then make all the approriate changes in your code so you can finally turn the compatibility level to 100.
David B.
David B.
June 22, 2010 at 12:09 pm
It was an in place upgrade and we used the upgrade advisor. It didn't complain about much. We had 'DateInterval.Day' in some places that simply broke and we fixed those but other than that not much. We also reindexed and updated the stats on our databases to no avail. ...we're thinking we'll set compatibility mode to 2000 in the morning if we can't find anything today and trouble shoot our queries. We're also running on RAID 5 with an incorrectly set offset but we had that with SQL 2000 and things weren't this bad.
PAGE_VERIFY has been on and set to the older torn page detection. I read here that checksum is better but it is more expensive and seeing that we are pegged at 00 cpu most of the day i can't do that now.
I had an aha on the way home and had our it folks put the new directories into our McAffee exclusion list. Now they aren't being scanned and it appears to have helped although the heavy load test will come in the morning as this was changed near the end of the work day.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 23, 2010 at 12:19 am
Check the execution plan for both 2000 & 2008, there might be missing indexes,
Did you changed the compatability level to 100 after upgradation? Please check and if possible run the profiler to capture more details and share it with us.
"More Green More Oxygen !! Plant a tree today"
June 23, 2010 at 12:34 am
Did you installed Sql 2008 SP1, and the latest CU ?
June 23, 2010 at 4:49 am
Without more information, a couple of things I can think of to check:
make sure you have multiple tempdb data files (one for each processor) on a fast disk. The IO for tempdb seems to have increased from SQL 2000 to 2008 (from what I have seen).
Also, look at the Max degree of parallelizm setting- this behaves differently in SQL 2008 (compared to SQL 2000)
June 23, 2010 at 7:24 am
Hi folks. Thanks for chiming in on this. I looked at the Event Viewer yesterday and there were a lot of McAffee errors. There have been no errors since excluding the sql server directory fro McAffee scans. I'm hopeful that this will have enough of an effect today so that we won't have to dumb down the compatibility levels. At least if it buys us enough cpus to run some basic profiler traces on the stored procedure execution. I've been trying to get folks to optimize those for months so perhaps this is all a good thing.
Minaz Amin - The CL was set to 100 right after we upgraded. Not sure how to check the execution plan.. do you know of a good resource that explains that or I can just search for it?
MSzI What is 'CU' We did apply SP1 on Monday.
David, I have only one tempdb datafile and everything is on RAID5. Is adding another tempdb file fairly simple or does it reuire advanced knowedge of ss2k internals? Is it something that Enterprise Manager can do? We have a 4 cpu box with 8Gb of ram and its in awe mode.
I've seen others talking about Max Degree of Parallelism. I'll look into that as well. I'm hoping that is something a relative newbie (I'm using 'relative' to calm my nerves and bolster some confidence ... lol)
Thanks again for your input on this. I'm going to start in on it now.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 23, 2010 at 7:43 am
CU = Cumulative Update package.
A new term introduced by the sql server servicing model similar to hotfixes, for details see:
http://support.microsoft.com/kb/935897
An up to date list of the latest CU-s can find at:
http://www.sqlservercentral.com/articles/SQL+Server+2008/63491/
June 23, 2010 at 8:02 am
Thanks for that list MSzI! Our current build is 01.0.2531 so it appears then that there are several hotfixes that have come out since then. We have always waited until there was a SP to apply the hotfixes all at once. Should these be applied in the order they came out and should every one of them be applied? I'm not sure we have the expertise to make those kinds of determinations so discriminating between which ones to apply and which ones to leave off might take awhile. I'm just not that familiar with applying these to a production db.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 23, 2010 at 10:18 am
Well I can report to the users here that the high cpu utilization was entirely due to not putting the new sql server directories into the McAfee Exclusion list. There are zero issues now with our installation. I'm still going to move forward with the advice given here but I can now do that without a sense of urgency. I hope somebody else can profit from this before going live 🙂
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 23, 2010 at 7:40 pm
If I were you, I would move the tempdb off RAID5 as a priority. Even if it's not the cause of your current performance issues- it will be in the future if left on RAID5 array (as the disks can't write fast enough for the heavy write demands of tempdb). This is especially true if you are using many temp tables and joins.
You should have one data file for each of your 4 CPUs and they are easy to add through Management Studio- but they need to be on fast disk. Have a look at:
http://msdn.microsoft.com/en-us/library/ms175527.aspx
This is a quick way to see what database/file is generating the most reads/writes:
SELECT * FROM fn_virtualfilestats(NULL,NULL);
Good luck!
David.
June 23, 2010 at 7:50 pm
Same goes for your transaction logs- you need them on a faster disk also.
I would run the best practices analyzer against the server- it will give you a wealth of information as to how well (or poorly) your server is configured:
July 8, 2010 at 2:13 am
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply