SQL Server 2008 Query is slow in production but fast in development

Question

I have a query that runs in about 2-4 minutes on production but runs in a couple of seconds on development. Both of these databases are on the same exact server. (no lectures about dev and production, production is really still in development).

I mean, I can just open two query windows and get the two different results consistently. I have ran RedGate SQLCompare and there is no schema difference (indexes and so forth) difference. I have disabled the site that connects to the DB so there should be no connections other than my Management Studio session.

What could be causing this? I create the development database by copying the production one (in the Management Studio, right click database and click "Copy Database")

This is really strange. I don't want to make any index changes because the weird thing is that the copy is blazing fast but the production is very, very slow but should be essentially exact copies.

Answer 1

Try running SQL profiler to see whats running on production.

Answer 2

You don't provide any details of the DB structure or the SQL Query in question but if you are confident that the setup is the same for both environments then it may simply be the amound of data in your Production DB that is highlighting an in-efficient query.

Answer 3

I don't know SQLServer specifics, but usually this sort of thing is due to table statistics being different in the two databases. Look at the query plans to see if they are different. Run the SQLServer version of "analyze table" or "analyze schema" commands.

If these things don't help, check how the databases are set up. Is it possible that the data is identical, but server configurations are different, and, for example, there is a much lower threshold on available memory for the production version?

Something else to check -- and this is just me showing my ignorance -- but does "copy database" actually copy the data, or just the object definitions?

Answer 4

Red gate by default ignores statistics and things like fill factor.

Answer 5

Ok, thanks everyone. I think the problem was related to index fragmentation. I thought Copy Database basically just copied the files. I did a DBCC DBREINDEX on every table and it's working great now. Thanks everyone!