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).
我有一个查询,在生产中运行大约2-4分钟,但在开发时运行几秒钟。这两个数据库都在同一个服务器上。 (没有关于开发和生产的讲座,生产真的还在开发中)。
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.
我的意思是,我可以打开两个查询窗口并始终获得两个不同的结果。我运行了RedGate SQLCompare并且没有架构差异(索引等)的区别。我已禁用连接到数据库的站点,因此除了我的Management Studio会话之外不应该有任何连接。
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")
可能是什么导致了这个?我通过复制生产数据库来创建开发数据库(在Management Studio中,右键单击数据库并单击“复制数据库”)
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.
这真的很奇怪。我不想进行任何索引更改,因为奇怪的是副本速度非常快,但生产非常非常慢,但应该是基本上完全复制的。
6 个解决方案
#1
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.
我不知道SQLServer的细节,但通常这种情况是由于两个数据库中的表统计信息不同。查看查询计划以查看它们是否不同。运行SQLServer版本的“analyze table”或“analyze schema”命令。
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?
要检查的其他东西 - 这只是我表现出的无知 - 但“复制数据库”实际上是复制数据,还是只是对象定义?
#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.
您没有提供有关数据库结构或SQL查询的任何详细信息,但如果您确信两种环境的设置相同,那么生产数据库中的数据可能只是突出显示有效的查询。
#3
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!
好的,谢谢大家。我认为这个问题与索引碎片有关。我以为Copy Database基本上只是复制了文件。我在每张桌子上都做了一个DBCC DBREINDEX,现在它运行得很好。感谢大家!
#4
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.
在我的情况下,原来是因为生产数据库不在现场(不同的城市),开发数据库在建筑物中。咄。该查询返回了大量数据,当然这些数据量在外部网络上需要更长的时间。我只是没有连接点,因为生产数据库比旧开发盒快得多,并且我们的大多数查询都没有返回足够的数据,网络成为速度因素。相反它是生产箱,但生产箱速度要快得多,即使网络连接速度较慢,大多数查询仍然比开发箱更快地返回。
#5
Try running SQL profiler to see whats running on production.
尝试运行SQL事件探查器以查看在生产中运行的内容。
#6
Red gate by default ignores statistics and things like fill factor.
默认情况下,红门会忽略统计数据和填充因子等内容。
#1
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.
我不知道SQLServer的细节,但通常这种情况是由于两个数据库中的表统计信息不同。查看查询计划以查看它们是否不同。运行SQLServer版本的“analyze table”或“analyze schema”命令。
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?
要检查的其他东西 - 这只是我表现出的无知 - 但“复制数据库”实际上是复制数据,还是只是对象定义?
#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.
您没有提供有关数据库结构或SQL查询的任何详细信息,但如果您确信两种环境的设置相同,那么生产数据库中的数据可能只是突出显示有效的查询。
#3
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!
好的,谢谢大家。我认为这个问题与索引碎片有关。我以为Copy Database基本上只是复制了文件。我在每张桌子上都做了一个DBCC DBREINDEX,现在它运行得很好。感谢大家!
#4
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.
在我的情况下,原来是因为生产数据库不在现场(不同的城市),开发数据库在建筑物中。咄。该查询返回了大量数据,当然这些数据量在外部网络上需要更长的时间。我只是没有连接点,因为生产数据库比旧开发盒快得多,并且我们的大多数查询都没有返回足够的数据,网络成为速度因素。相反它是生产箱,但生产箱速度要快得多,即使网络连接速度较慢,大多数查询仍然比开发箱更快地返回。
#5
Try running SQL profiler to see whats running on production.
尝试运行SQL事件探查器以查看在生产中运行的内容。
#6
Red gate by default ignores statistics and things like fill factor.
默认情况下,红门会忽略统计数据和填充因子等内容。