CODEWITHSUNDEEP
sql-serversql-server-2008
JustAProgrammer
JustAProgrammer

Reputation: 609

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

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.

Upvotes: 3

Views: 7008

Answers (6)

AaronS
AaronS

Reputation: 435

In my case it turned out to be because the production database is off site (different city) and the development database is in the building. duh. The query was returning a lot of data and of course that amount of data takes longer over the external network. I simply didn't connect the dots since the production DB was so much faster than the old development box and most of our queries don't return enough data for the network to be the speed factor. Rather it is but the production box is so much faster that even with the slower network connection most queries were still returning faster than from the development box.

Upvotes: 1

JustAProgrammer
JustAProgrammer

Reputation: 609

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!

Upvotes: 1

gbn
gbn

Reputation: 432471

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

Upvotes: 0

SquareCog
SquareCog

Reputation: 19666

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?

Upvotes: 7

MadMurf
MadMurf

Reputation: 2323

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.

Upvotes: 1

Jordan Johnson
Jordan Johnson

Reputation: 253

Try running SQL profiler to see whats running on production.

Upvotes: 0

Related Questions