SQL Server slow down after duplicating database

Question

I recently moved a bunch of tables from an existing database into a new database due to the database getting rather large. After doing so I noticed a dramatic decrease in performance of my queries when running against the new database.

The process I took to recreate the new database is this:

1. Generate Table CREATE scripts using sql servers automatic script
   generator.
2. Run the create table scripts
3. Insert all data into new database using INSERT INTO with a select from the existing database.
4. Run all the alter scripts to create the foreign keys and any indexes

Does anyone have any ideas of possible problems with my process, or some key step I'm missing that is causing this performance issue?

Thanks.

Answer 1

just had similar issues - were there any triggers or fulltext indexes on any of your tables?

Josh

Answer 2

Look on how you set up the initial size and growth options. If you didn't give it enough space to begin with or if you are growing by 1MB at a time that could be a cause of performance issues.

Answer 3

first I would an a mimimum make sure that auto create statistics is enabled you can also set auto update statistics to true

after that I would update the stats by running

sp_updatestats

or

UPDATE STATISTICS

Also realize that the first time you hit the queries it will be slower because nothing will be cached in RAM. On the second hit should be much faster

Answer 4

Did you script the indexes from the tables in the original database? Missing indexes could certainly account for poor performance.

Answer 5

Can you tell what about those queries got slower? New access plans? Same plans but they perform slower? Do they execute slower or are they suspended more? Did all queries got slower or just some? And last but not least, how do you know, ie. what exactly did you measure and how?

Some of the usual suspects could be:

• The new storage is much slower (.mdf on slow disk, or on a busy disk)
• You changed the data structure during move (ie. some indexes did not get ported)
• You changed the data size (ie. compression options) resulting on more pages for the same data
• Did anything else change at the same time, new app code or anything the like?
• By extending the data size (you do no mention deleting the old tables) you are now trashing the buffer pool (did the page lifetime expectancy decreased in performance counters?)

Answer 6

Have you tried looking at the execution plans on each server when running these queries - that should allow you to easily see if they're doing something different e.g. table scanning due to a missing index, poor statistics etc.

Are both DBs sat on the same box with their data files on the same drive arrays?