Urgent problem peformance issue after database migration
-
Over the past weekend we migrated an application and backend SQL Server databases from servers in Miami to Atlanta. The databases migrated from four standalone servers with quad processors and 4 to 6GB of memory. To an active/active cluster with faster quad processors and 6GB of memory. They also migrated from SQL 2000 SP3a to SQL 2000 SP4, and from internal disks to SAN array. Since the migration the app has been having serious peformance issues. Locking and blocks can be seen from the database side. Since the migration we have done everything we can think of from the server side to try to improve the performance. To include adding a standalone server and moving databases to it and then separating the two heaviest used remaining atabases
to either side of the cluster. We also applied a hot fix to ensure AWE memory was being used correctly. Last night we attempted to reconfigure the layout of the logical disks on the SAN. Nothing has worked so far, and the only thing that I can think left is to look at the longest and highest read processes and start tuning the application. This is Where we probably should have started to look first, but the developers and app team swear that no changes were made to the schema or application during the move and that they weren't experiencing the locking/blocking problems before the migration. Does anyone have any ideas why the performance would have gotten so bad moving to what should be a much faster environment? Any ideas on the best way to
troubleshoot the problem. This is becoming very critical and I don't want to waste time spinning my wheels, so I'm hoping someone with more experience has already faced this and can shed some light.
Thanks,
Michelle
-
Michelle
I'm sure Rudy will chip in with his performance tuning shortlist (if he doesn't then search for it on this site) but in the meantime, how often do you update your statistics and reindex the databases? It may be that SQL Server is compiling inappropriate execution plans based on out-of-date statistics.
Try running a Profiler trace to capture locks and blocks and see whether that gives you any useful information. Check that there's a message in the SQL errorlog saying that it's using AWE memory. Is it possible that there's a slower network link to Atlanta and queries are being held up because they involve lots of round trips between client and server?
John
-
may I add ....
DBCC dbreindex all your tables and indexed views and indexes.
dbcc updateusage(yourdb) with count_rows
sp_updatestats all your db
and SP4 has changes regarding implicit datatypeconversion ! This may have a huuuuuuggggeeee impact
(indexes nolonger being used, ..) run profiler and examine (clustered) index scans and tablescans. You may need to adapt your queries !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
John,
Thanks for your reply. The databases are setup with auto update statistics. What system table can I check for the last date of the statistics. The optimization task runs every weekend. I ran the profiler trace yesterday. I saw a few deadlock issues and also identified a SP that was blocking other users. They have modified the SP and now we are waiting for assessement. After enabling AWE memory and applying the hotfix memory usage went from 3GB to 4.2GB for the SQL Server instance. I was thinking of using profiler trace to identify SPs taking the longest time and most number of reads. Not sure else what to do. I have Perfmon running and everything looks good so far, there are some CPU spikes, but it hasn't been sustained. Could the fact that the databases are now on SP4 when they were on SP3a have any negative effect on performance? Just trying to look at what's different. As for the location, Atlanta should actually be faster network wise.
-
btw "...The databases migrated from four standalone servers with quad processors and 4 to 6GB of memory...."
could it be that .... 4 * 4slow + 4*(4 to 6 Gb ram) < 4*4speed +6Gb ram ?? Oooh well maybe yes
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
alzdba,
Not sure I understand your comments. Are you saying it stands to reason the new environment would be slower? If so, right now we have to deal with the hardware we have. As far as server utilization, once we moved one of the heaviest utilized databases to the other side of the cluster the CPUs went down to a reasonable state, avg 20% and below with spikes into the 70s.
I checked and both the reindex and update stats ran yesterday morning at 1AM.
Thx,
Michelle
-
I suppose each node hosts 2 active instances of sqlserver.
On each node the active instanceses will compete for the ram (6Gb < 2*4Gb) and cpu (4speed cpu < 2*4slow ? ) . This is jus a wrinkle of an eyebrow that comes up.
I think the migration so SP4 may have a heavier impact to your issues than your hardwaremigration. You might have been better off doing it one at the time
and with some timegaps.nevertheless launch sqltraces to capture the table or index scans. Because of the implicit conversion changes of sp4 you will suffer them.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
couple of additional things,
if you have your original server available, comapre sp_configure statements and validate any differences that you find. Perhaps Max degree of parallelism was set to 1 on the old server (for example).
look at a couple of exec. plans on both servers, how different are they? This goes with the tracing people are suggesting above (a very good idea, as sp4 does play havoc).
Active active, make sure that each instance is on its own node, to prevent memory contention (at least for the duration of the testing).
-
CDA,
Thanks for the reply. Yes, there is one instance on each side of the cluster. I verified yesterday that the parallel processing was set to all processors in Miami. I thought that may have been an issue, because I've seen that before, but they said it was set the same way in Miami. I will verify with the Miami DBA on the settings they had before the migration again.
I see alot of system generated _WA indexes. Could that be an issue?
-
I would recommend turning off hyperthreading on the servers. We have had 2 recent incidents involving the migration of 2 clusters to new hardware, using Windows 2003 and SQL 2000, where performance was greatly degraded and turning off hyperthreading made a world of difference. SQL just does not appear to know what to do with all that CPU power! Honestly, I'd give it a try - I'm willing to bet money that this is the issue...
Anne
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply