January 29, 2010 at 1:16 pm
Hello,
We have recently moved a database to Amazon EC2 instance. We chose the server with identical configurations. The performance on the new server is awful. After initial analysis I realized that the old one used much more memory through AWE enable option, and so I reset memory on the new server the same way. However, it does not seem to have helped the problem.
The only idea I have is to rebuild indexes. The only problem is that db size is pretty big (~ 80 GB) so it might take a while. So before doing that I wanted to get some opinions as to whether I am on the right track or there is something else to check.
Thank you!
January 29, 2010 at 3:28 pm
How did you move the database? Are you getting similar resource counters on the new Amazon db? Are you sure there isn't network traffic delaying things?
I might update statistics as a wild guess for now.
January 29, 2010 at 3:47 pm
was this a move to a SQL 2005 instance from a 2000 instance?
---------------------------------------------------------------------
January 29, 2010 at 7:17 pm
Heh... Rumor has it that you probably need to rebuild stats because they don't "move" with the data.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 1, 2010 at 8:39 am
Steve: The move was via backup and restore. I did not get a chance to look at the resource counters. However, comparing execution plans, they are identical. As to the network traffic, I connect directly to the machine (RDP), open query analyzer there, and run the query there. Could it still be network issue? If so, how can I find it out?
George: The move was from SQL 2000 to SQL 2000
Jeff: Are you referring to rebuilding indexes and/or updating statistics?
I found out that the machines are not actually identical. The old one had 8 CPUs and the new on has only 4. Could this be causing such a slow down?
In the meantime, I am trying to rebuild indexes and update statistics.
Thank you!
February 1, 2010 at 8:58 am
Having lower # of CPU's would be one of the reasons, and as Jeff suggested have the Stats updated..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 1, 2010 at 9:00 am
check the processor busy %in perfmon (or task manager) see if they are maxed out
---------------------------------------------------------------------
February 1, 2010 at 9:34 am
If you're using RDP, network isn't an issue.
Stats should move, but as Jeff mentioned, they often don't seem to in a backup/restore.
February 1, 2010 at 9:45 am
Steve Jones - Editor (2/1/2010)
If you're using RDP, network isn't an issue.Stats should move, but as Jeff mentioned, they often don't seem to in a backup/restore.
I thought Jeff was joking when he posted that. How would that happen?
---------------------------------------------------------------------
February 1, 2010 at 9:51 am
if you open up perfmon and start a counter,
add the drives with the DB data and log file Physical Disk counter Avg. Disk Queue Length
to See if the I/O is a problem. you can also put in disk read or write depending on what your server is heavy in.
add the processors individually (not the total counter at the top) %Processor Time & Processor Queue Length
This would be the best way to see if the move from 8 to 4 has effected the app
add memory counters Available MB, Pages /sec, SQL Server BufferManager\Buffer cache hit ratio
if Buffer cache hit ratio is low it will be a good indication that you need more memory
This will be a good way to see if your hardware resources are what is causing the problem.
is this server dedicated to just your app or is it sharing resources with other DB instances?
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
February 1, 2010 at 9:55 am
http://support.microsoft.com/kb/925419/%5B/url%5D
also not sure what your build number is, but I found this the other day, not quite the same, but it was one of the SQL 2000 hotfixes after sp4
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
February 1, 2010 at 9:58 am
Don't know, but I've seen so many cases of backup / restore being slower and then fixed when stats are updated.
February 1, 2010 at 10:02 am
Agreed, that is a very good point. I figure the hardware counters would be a good way to say problem here or not here.
and certianly before attempting any hotfix, update the statistics.
Steve is that a rule out of thumb for you? Restore a DB then you should always update the statistics?
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
February 1, 2010 at 10:19 am
I think that it is. I can't imagine why it should help, the statistics are stored in tables, but I've seen it often noted by people as a fix. It isn't an overbearing requirement, so just updating them makes sense. In fact....
February 1, 2010 at 3:03 pm
If you went from a Raid 10 stripe-set to Raid 5, your perf will suffer. The more write intensive in fact the worse it will get.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply