更新统计所需的更新统计数据（？） [英] UPDATE STATISTICS necessary to improve performance (?)
问题描述
亲爱的Sql Server专家：
首先，我不是sql服务器专家:)
几个月前我放了一个数据库进入生产环境。
最近，我注意到在我们的开发环境中执行得非常快的特定查询在
中非常缓慢
生产。我分析了生产服务器上的计划（它看起来很好），然后尝试了一些我从
阅读新闻组中收集到的技巧。没有任何效果。然后我突发奇想，在一些被查询的表上执行了一个
UPDATE STATISTICS。
查询立即从61秒执行到1秒以下。
我检查确保统计信息自动更新。并且它们是
。
为什么我需要运行UPDATE STATISTICS？我需要再次使用吗？
多一点背景信息：
数据库开始空了，并且在最后增长很快
几个月。一个特定的表以每月约300,000
记录的速度增长。由于一些好的
索引，我得到了快速的查询时间。
一个简单的问题：
如果我添加一个索引，这个
新索引会立即自动更新统计数据吗？
提前感谢您的帮助，
Felix
Dear Sql Server experts:
First off, I am no sql server expert :)
A few months ago I put a database into a production environment.
Recently, It was brought to my attention that a particular query that
executed quite quickly in our dev environment was painfully slow in
production. I analyzed the the plan on the production server (it
looked good), and then tried quite a few tips that I''d gleaned from
reading newsgroups. Nothing worked. Then on a whim I performed an
UPDATE STATISTICS on a few of the tables that were being queried. The
query immediately went from executing in 61 seconds to under 1 second.
I checked to make sure that statistics were being "auto updated" and
they were.
Why did I need to run UPDATE STATISTICS? Will I need to again?
A little more background info:
The database started empty, and has grown quite rapidly in the last
few months. One particular table grows at a rate of about 300,000
records per month. I get fast query times due to a few well placed
indexes.
A quick question:
If I add an index, do statistics get automatically updated for this
new index immediately?
Thanks in advance for any help,
Felix
推荐答案
文章< 80 ********************** ****@posting.google.com>，
fe ************************* @ yahoo.com 说...
In article <80**************************@posting.google.com >,
fe*************************@yahoo.com says...
几个月前，我将一个数据库放入生产环境。
最近，我注意到在我们的开发环境中执行得非常快的特定查询在
生产。我分析了生产服务器上的计划（它看起来不错），然后尝试了一些我从阅读新闻组中收集到的技巧。没有任何效果。然后我突发奇想，在一些被查询的表上执行了
UPDATE STATISTICS。
查询立即从61秒执行到1秒以下。
我检查确保统计信息正在自动更新。他们是。
A few months ago I put a database into a production environment.
Recently, It was brought to my attention that a particular query that
executed quite quickly in our dev environment was painfully slow in
production. I analyzed the the plan on the production server (it
looked good), and then tried quite a few tips that I''d gleaned from
reading newsgroups. Nothing worked. Then on a whim I performed an
UPDATE STATISTICS on a few of the tables that were being queried. The
query immediately went from executing in 61 seconds to under 1 second.
I checked to make sure that statistics were being "auto updated" and
they were.
我已多次见过这种类型的东西，开发人员没有接触到
生产系统足够长，可以构建一个维护计划，并且没有足够的时间来监控数据库，以构建一个特定于数据库的维护计划。
在大多数情况下，我会创建一般维护计划，每晚自动选择
20％的表格并重新编制索引，与存储的标记相同/>
重新编译的程序。
如果你做这样的事情，或者你只是重新索引/重新编译它们所有周末你的b $ b应该能够保持你的表现。
只是你知道，这也是甲骨文的一个问题 - 有一个团队的b
开发者建立一个库存系统，工作了近一年，但
没有人真的注意到它每天都变慢，直到报告
开始失败。在给我打电话之前，他们试了三天来修理它。
我的第一个线索是没有进度表维护计划，第二个是它b / b
工作了近一年，然后那个每天在一张
单表中有大约20K的插页，没有删除....重新索引两张表格它/ b
返回到相同的性能水平开发。
另外，当你为重新编译标记一个sproc时，它第一次执行它
将是SLOW。当桌子重新索引时，它们也会很慢或者甚至可以锁定
，所以你想在下班时间做这件事。
-
-
sp*********@rrohio.com
（删除999回复我）
I''ve seen this type of thing many times, developers are not exposed to
the production system long enough to build a maintenance plan, and the
DBA doesn''t get enough time to monitor the DB to build a maintenance
plan specific to the database.
In most cases, I create generic maintenance plans that will auto select
20% of the tables per night and reindex them, same with marking stored
procedures for recompile.
If you do something like this, or if you just reindex/recompile them all
on a weekend you should be able to maintain your performance.
Just so you know, this is a problem in Oracle too - had a team of
developers build a inventory system, worked great for almost a year, but
no one really noticed it getting slower every day until the reports
started failing. They tried for three days to fix it before calling me.
My first clue was no schedule maintenance plan, second was that it
worked for almost a year, and then that there are about 20K inserts in a
single table per day with no deletes.... Reindexed two tables and it
returned to the same level of performance as when it was developed.
Also, when you mark a sproc for recompile, the first time it executes it
will be SLOW. While tables are reindexing they will also be slow or
could even be locked, so you want to do this in the off-hours.
--
--
sp*********@rrohio.com
(Remove 999 to reply to me)
Felix（fe ************ *************@yahoo.com）写道：
Felix (fe*************************@yahoo.com) writes:
几个月前我把一个数据库放到生产环境中。
最近，它带来了在我的注意中，在我们的开发环境中执行得非常快的特定查询在生产中非常缓慢。我分析了生产服务器上的计划（它看起来不错），然后尝试了一些我从阅读新闻组中收集到的技巧。没有任何效果。然后我突发奇想，在一些被查询的表上执行了
UPDATE STATISTICS。
查询立即从61秒执行到1秒以下。
我检查确保统计信息正在自动更新。他们是。
为什么我需要运行UPDATE STATISTICS？我需要再来一次吗？
如果你只执行UPDATE STATISTICS，那么看起来有点好笑，如果
有自动统计。如果你添加了WITH FULLSCAN，那么
就是一个可能的解释。
你是否需要再做一次，我不知道，但是作为Leythos
讨论，最关键的部分是当你开始空并且音量增加时b / b
。一旦超过某个级别，很多执行计划就会变得没有实际意义。
查询已参数化 - 包括自动参数化或在
a存储过程 - 还有另一种可能的解释，即
缓存计划是针对非典型参数的。回想一下，当SQL Server
为过程构建查询计划时，它使用当前值
构建计划的参数。如果使用的值恰好是一个非典型的价值，那么你可能会得到一个糟糕的常规价值计划。
如果是这种情况，查询中涉及的表上的sp_recompile
会这样做。
一个简单的问题：
如果我添加一个索引，那么统计数据会自动更新吗？ br />新索引立即？
A few months ago I put a database into a production environment.
Recently, It was brought to my attention that a particular query that
executed quite quickly in our dev environment was painfully slow in
production. I analyzed the the plan on the production server (it
looked good), and then tried quite a few tips that I''d gleaned from
reading newsgroups. Nothing worked. Then on a whim I performed an
UPDATE STATISTICS on a few of the tables that were being queried. The
query immediately went from executing in 61 seconds to under 1 second.
I checked to make sure that statistics were being "auto updated" and
they were.
Why did I need to run UPDATE STATISTICS? Will I need to again?
If you only performed UPDATE STATISTICS it seems a little funny, if
there is autostatistics on. If you added WITH FULLSCAN, then there
is a possible explanation.
Whether you will need to do it again, I cannot tell, but as Leythos
discusses, the most critical part is when you start empty and volume
grows. Once you are over a certain level a lot of execution plans
becomes moot.
It the query was parameterized - including auto-parameterized or in
a stored procedure - there is another possible explanation, that the
cached plan was for an atypical parameter. Recall that when SQL Server
builds a query plan for a procedure, it uses the current value of
the parameters to build the plan. If the value used happens to be an
atypical one, then you may get a bad plan for common values to linger.
If this is the case, an sp_recompile on a table involved in the query
will do.
A quick question:
If I add an index, do statistics get automatically updated for this
new index immediately?
是的，我记得，SQL Server在构建
索引时会创建统计信息。
-
Erland Sommarskog，SQL Server MVP， so **** @ algonet.se
SQL Server SP3联机丛书
http://www.microsoft.com/sql/techinf...2000/books.asp
查询未参数化，也未存储过程。
我没有使用FULLSCAN运行UPDATE STATISTICS。
什么d o你的意思是一旦你超过某个水平很多
执行计划变得毫无意义。
无论如何，我仍然感到困惑。
谢谢，
Felix
Erland Sommarskog< so **** @ algonet.se>在消息新闻中写道：< Xn ********************** @ 127.0.0.1> ...
The query was not parameterized, nor in a stored procedure.
And I did not run UPDATE STATISTICS WITH FULLSCAN.
What do you mean by "Once you are over a certain level a lot of
execution plans becomes moot."?
Anyway, I remain puzzled.
Thanks,
Felix
Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...
Felix（fe *************************@yahoo.com）写道：
Felix (fe*************************@yahoo.com) writes:
几个月前我把一个数据库放入生产环境。
最近，我注意到在我们的开发环境中执行得非常快的特定查询在生产过程中非常缓慢。我分析了生产服务器上的计划（它看起来不错），然后尝试了一些我从阅读新闻组中收集到的技巧。没有任何效果。然后我突发奇想，在一些被查询的表上执行了
UPDATE STATISTICS。
查询立即从61秒执行到1秒以下。
我检查确保统计信息正在自动更新。他们是。
为什么我需要运行UPDATE STATISTICS？我需要再次使用吗？
A few months ago I put a database into a production environment.
Recently, It was brought to my attention that a particular query that
executed quite quickly in our dev environment was painfully slow in
production. I analyzed the the plan on the production server (it
looked good), and then tried quite a few tips that I''d gleaned from
reading newsgroups. Nothing worked. Then on a whim I performed an
UPDATE STATISTICS on a few of the tables that were being queried. The
query immediately went from executing in 61 seconds to under 1 second.
I checked to make sure that statistics were being "auto updated" and
they were.
Why did I need to run UPDATE STATISTICS? Will I need to again?
如果您只执行了UPDATE STATISTICS，那么看起来有点好笑，如果
有自动统计。如果你添加了WITH FULLSCAN，那么
就是一个可能的解释。
无论你是否需要再做一次，我都说不出来，但正如Leythos所讨论的那样，最关键的部分是当你开始空虚和音量增长。一旦超过某个级别，很多执行计划就会变得毫无意义。
查询已参数化 - 包括自动参数化或存储过程 - 还有另一个可能的解释是，
缓存计划是针对非典型参数的。回想一下，当SQL Server为程序构建查询计划时，它使用参数的当前值来构建计划。如果使用的值恰好是一个非典型的值，那么你可能会得到一个糟糕的计划，让常见的值继续存在。
如果是这种情况，那么表中的sp_recompile就会涉及到查询
将会这样做。
If you only performed UPDATE STATISTICS it seems a little funny, if
there is autostatistics on. If you added WITH FULLSCAN, then there
is a possible explanation.
Whether you will need to do it again, I cannot tell, but as Leythos
discusses, the most critical part is when you start empty and volume
grows. Once you are over a certain level a lot of execution plans
becomes moot.
It the query was parameterized - including auto-parameterized or in
a stored procedure - there is another possible explanation, that the
cached plan was for an atypical parameter. Recall that when SQL Server
builds a query plan for a procedure, it uses the current value of
the parameters to build the plan. If the value used happens to be an
atypical one, then you may get a bad plan for common values to linger.
If this is the case, an sp_recompile on a table involved in the query
will do.
一个简单的问题：
如果我添加一个索引，是否会立即自动为这个新索引更新统计数据？
A quick question:
If I add an index, do statistics get automatically updated for this
new index immediately?
是的，我记得，SQL Server在构建
索引时会创建统计信息。
Yes, as I recall, SQL Server creates statistics when it builds an
index.
这篇关于更新统计所需的更新统计数据（？）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
