ANSWER:

1. Whenever I upgrade databases, and I’ve been doing it for at least twelve years, among the first things that I do after bring the database back up is to reindex all of the tables. I do this before I let anyone use the database. Reindexing is simple and it should only take a couple of seconds to reindex 28 MB of data on anything faster than a laptop from 2005.

2. Make sure that the database is set to AUTOCLOSE = FALSE. Some environments default this to TRUE, which is wrong for any production server.

3. As cmenke says, unless you are using a point-in-time recovery strategy, you should set your database to SIMPLE recovery mode. Was the database set to SIMPLE recovery mode on the SBS server?

4. Does the database on EC2 have the same indexing in place as the old database? You didn’t mention how you moved your data, so we don’t know if you did a backup and restore, used the Copy Database Wizard or wrote your own SSIS packages. Some methods of moving databases won’t preserve your indexes. For any future readers of this answer who are trying to move databases between servers, you should always try to use BACKUP/RESTORE or copy the MDF and LDF files. Using SSIS/DTS for all but the most trivial databases will drive you mad.

5. If the database is still slow, fire up SQL Profiler (on the server, not on your desktop) and capture a little bit of traffic. Is the problem one big, slow SQL statement or lots and lots of little SQL statements? How many SQL statements are issued by that first page?

Keep in mind that you’ve already made a bunch of changes to your SQL Server and didn’t see any behavior change. Maybe the problem is somewhere else. Is your web page interacting with your AD setup in an unexpected way? Is something failing on the web page without showing you any error? Is the web page looking for something on the old SBS setup that didn’t get migrated to EC2? If you have the code and some development chops, I’d have a look through that code.

by darin strait from http://serverfault.com/questions/404746