为什么你不要收缩数据库文件(国外翻译)
<p>前言，这几天查看了很多关于sql server收缩数据文件方面的文章，准备写一篇关于收缩日志方面的文章，但是突然有种冲动将看过经典的文章翻译出来，下面这篇文章是翻译的是paul randal – “why you should not shrink your data files”。有些比较难以翻译、清晰的地方，我会贴上原文。好了，不啰嗦了，直接看下面的翻译吧。 </p>
<p>
我最大的一个热点问题是关于收缩数据文件，虽然在微软的时候，我自己写了相关收缩数据文件代码，我再也没有机会去重写它，让它操作起来更方便。我真的不喜欢收缩。</p>
<p>
现在，不要混淆了收缩事务日志文件和收缩数据文件，当事务日志文件的增长失控或为了移除过多的vlf碎片（和看到金佰利的优秀文章），然而，收缩事务日志数据文件不要频繁使用（罕见的操作）并且不应是你执行定期维护计划的一部分。</p>
<p>
收缩数据文件应该执行得甚至更少。这就是为什么——数据文件收缩导致产生了大量索引碎片，让我用一个简单并且你可以运行的脚步来演示。下面的脚本将会创建一个数据文件，创建一个10mb大小的“filler”表，一个10mb大小的“production”聚簇索引，然后分析新建的聚集索引的碎片情况。 </p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_469664">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
<div class="line number22 index21 alt1">
22</div>
<div class="line number23 index22 alt2">
23</div>
<div class="line number24 index23 alt1">
24</div>
<div class="line number25 index24 alt2">
25</div>
<div class="line number26 index25 alt1">
26</div>
<div class="line number27 index26 alt2">
27</div>
<div class="line number28 index27 alt1">
28</div>
<div class="line number29 index28 alt2">
29</div>
<div class="line number30 index29 alt1">
30</div>
<div class="line number31 index30 alt2">
31</div>
<div class="line number32 index31 alt1">
32</div>
<div class="line number33 index32 alt2">
33</div>
<div class="line number34 index33 alt1">
34</div>
<div class="line number35 index34 alt2">
35</div>
<div class="line number36 index35 alt1">
36</div>
<div class="line number37 index36 alt2">
37</div>
<div class="line number38 index37 alt1">
38</div>
<div class="line number39 index38 alt2">
39</div>
<div class="line number40 index39 alt1">
40</div>
<div class="line number41 index40 alt2">
41</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">use ;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">go</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number4 index3 alt1">
<code class="sql plain">if databasepropertyex(n</code><code class="sql string">'dbmaint2008'</code><code class="sql plain">, n</code><code class="sql string">'version'</code><code class="sql plain">) </code><code class="sql keyword">is</code> <code class="sql color1">not</code> <code class="sql color1">null</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces"> </code><code class="sql keyword">drop</code> <code class="sql keyword">database</code> <code class="sql plain">;</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql plain">go</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number8 index7 alt1">
<code class="sql keyword">create</code> <code class="sql keyword">database</code> <code class="sql plain">dbmaint2008;</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql plain">go</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql plain">use ;</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql plain">go</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql spaces"> </code>
</div>
<div class="line number13 index12 alt2">
<code class="sql keyword">set</code> <code class="sql plain">nocount </code><code class="sql keyword">on</code><code class="sql plain">;</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql plain">go</code>
</div>
<div class="line number15 index14 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number16 index15 alt1">
<code class="sql comments">-- create the 10mb filler table at the 'front' of the data file</code>
</div>
<div class="line number17 index16 alt2">
<code class="sql keyword">create</code> <code class="sql keyword">table</code> <code class="sql plain">(</code>
</div>
<div class="line number18 index17 alt1">
<code class="sql spaces"> </code><code class="sql plain"> </code><code class="sql keyword">int</code> <code class="sql plain">identity,</code>
</div>
<div class="line number19 index18 alt2">
<code class="sql spaces"> </code><code class="sql plain"> </code><code class="sql keyword">char</code> <code class="sql plain">(8000) </code><code class="sql keyword">default</code> <code class="sql string">'filler'</code><code class="sql plain">);</code>
</div>
<div class="line number20 index19 alt1">
<code class="sql plain">go</code>
</div>
<div class="line number21 index20 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number22 index21 alt1">
<code class="sql comments">-- fill up the filler table</code>
</div>
<div class="line number23 index22 alt2">
<code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain"> </code><code class="sql keyword">default</code> <code class="sql keyword">values</code><code class="sql plain">;</code>
</div>
<div class="line number24 index23 alt1">
<code class="sql plain">go 1280</code>
</div>
<div class="line number25 index24 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number26 index25 alt1">
<code class="sql comments">-- create the production table, which will be 'after' the filler table in the data file</code>
</div>
<div class="line number27 index26 alt2">
<code class="sql keyword">create</code> <code class="sql keyword">table</code> <code class="sql plain">(</code>
</div>
<div class="line number28 index27 alt1">
<code class="sql spaces"> </code><code class="sql plain"> </code><code class="sql keyword">int</code> <code class="sql plain">identity,</code>
</div>
<div class="line number29 index28 alt2">
<code class="sql spaces"> </code><code class="sql plain"> </code><code class="sql keyword">char</code> <code class="sql plain">(8000) </code><code class="sql keyword">default</code> <code class="sql string">'production'</code><code class="sql plain">);</code>
</div>
<div class="line number30 index29 alt1">
<code class="sql keyword">create</code> <code class="sql plain">clustered </code><code class="sql keyword">index</code> <code class="sql plain"> </code><code class="sql keyword">on</code> <code class="sql plain">();</code>
</div>
<div class="line number31 index30 alt2">
<code class="sql plain">go</code>
</div>
<div class="line number32 index31 alt1">
<code class="sql spaces"> </code>
</div>
<div class="line number33 index32 alt2">
<code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain"> </code><code class="sql keyword">default</code> <code class="sql keyword">values</code><code class="sql plain">;</code>
</div>
<div class="line number34 index33 alt1">
<code class="sql plain">go 1280</code>
</div>
<div class="line number35 index34 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number36 index35 alt1">
<code class="sql comments">-- check the fragmentation of the production table</code>
</div>
<div class="line number37 index36 alt2">
<code class="sql keyword">select</code>
</div>
<div class="line number38 index37 alt1">
<code class="sql spaces"> </code><code class="sql plain"></code>
</div>
<div class="line number39 index38 alt2">
<code class="sql keyword">from</code> <code class="sql plain">sys.dm_db_index_physical_stats(</code>
</div>
<div class="line number40 index39 alt1">
<code class="sql spaces"> </code><code class="sql plain">db_id(n</code><code class="sql string">'dbmaint2008'</code><code class="sql plain">), object_id(n</code><code class="sql string">'prodtable'</code><code class="sql plain">), 1, </code><code class="sql color1">null</code><code class="sql plain">, </code><code class="sql string">'limited'</code><code class="sql plain">);</code>
</div>
<div class="line number41 index40 alt2">
<code class="sql plain">go</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
执行结果如下</p>
<p>
<img title="为什么你不要收缩数据库文件(国外翻译)" alt="为什么你不要收缩数据库文件(国外翻译)" border="0" height="252" src="https://zhuji.jb51.net/uploads/img/202305/d664ce68eec5cee58a163f3084580a78.jpg" width="635"></p>
<p>
聚集索引的逻辑碎片在收缩数据文件前大约接近0.4%。[但是我测试结果是0.54%，如上图所示,不过也算是接近0.4%]</p>
<p>
现在我删除filter表，运行收缩数据文件命令后，重新分析聚集索引的碎片化。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_822358">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql comments">-- drop the filler table, creating 10mb of free space at the 'front' of the data file</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">drop</code> <code class="sql keyword">table</code> <code class="sql plain">;</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">go</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code>
</div>
<div class="line number5 index4 alt2">
<code class="sql comments">-- shrink the database</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql plain">dbcc shrinkdatabase();</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">go</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql spaces"> </code>
</div>
<div class="line number9 index8 alt2">
<code class="sql comments">-- check the index fragmentation again</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql keyword">select</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql spaces"> </code><code class="sql plain"></code>
</div>
<div class="line number12 index11 alt1">
<code class="sql keyword">from</code> <code class="sql plain">sys.dm_db_index_physical_stats(</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql spaces"> </code><code class="sql plain">db_id(n</code><code class="sql string">'dbmaint2008'</code><code class="sql plain">), object_id(n</code><code class="sql string">'prodtable'</code><code class="sql plain">), 1, </code><code class="sql color1">null</code><code class="sql plain">, </code><code class="sql string">'limited'</code><code class="sql plain">);</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql plain">go</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
下面是我的执行结果，作者执行结果，请看原文：</p>
<p>
<img title="为什么你不要收缩数据库文件(国外翻译)" alt="为什么你不要收缩数据库文件(国外翻译)" border="0" height="350" src="https://zhuji.jb51.net/uploads/img/202305/690be06e0f0fac0e5ff55dcc8945f15f.jpg" width="629"></p>
<p>
原文：</p>
<p>
wow! after the shrink, the logical fragmentation is almost 100%. the shrink operation *completely* fragmented the index, removing any chance of efficient range scans on it by ensuring the all range-scan readahead i/os will be single-page i/os.</p>
<p>
译文：</p>
<p>
哇，真是恐怖！数据文件收缩后，索引的逻辑碎片几乎接近100%，收缩数据文件导致了索引的完全碎片化。消除了任何关于它的有效范围扫描的机会，确保所有执行提前读范围扫描的 i/o 在单页的 i/o操作<br>
为什么会这样呢？ 当单个数据文件收缩操作一次后，它会用gam位图索引找出数据文件中分配最高的页，然后尽可能的向前移动到文件能够移动的地方，就这样子，在上面的例子中，它完全反转了聚集索引，让它从非碎片化到完全碎片化。<br>
同样的代码用于dbcc shrinkfile， dbcc shrinkdatabase，以及自动收缩，他们同样糟糕，就像索引的碎片化，数据文件的收缩同样产生了大量的i/o操作，耗费大量的cpu资源，并且生成了*load*事务日志，因为任何操作都会全部记录下来。<br>
数据文件收缩决不能作为定期维护的一部分，你决不能启用“自动收缩”属性，我尝试把它从sql 2005和sql 2008产品中移除，它还存在的唯一原因是为了更好的向前兼容，不要掉入这样的陷阱：创建一个维护计划，重新生成所有索引，然后尝试回收重建索引耗费的空间采取收缩数据文件 — — 这就是你做的生成了大量事务日志，但实质没有提高性能的零和游戏。<br>
所以，你为什么要运行一个收缩呢，？举例来说，如果你把一个相当大的数据库删除了相当大的比例，该数据库不太可能增长，或者你需要转移一个数据库文件前先清空数据文件？</p>
<p>
译文：</p>
<p>
我很想推荐的方法如下：</p>
<p>
创建一个新的文件组<br>
将所有受影响的表和索引移动到一个新的文件组用create index ... with (drop_existing=on)的脚本，在移动表的同时，删除表中的碎片。<br>
删掉那些你准备收缩的旧文件组，你反正要收缩（或缩小它的方式下来，如果它的主文件组）。<br>
基本上你需要提供一些更多的空间，才可以收缩的旧文件，但它是一个更清晰的设置。</p>
<p>
原文：</p>
<p>
the method i like to recommend is as follows:</p>
<p>
create a new filegroup<br>
move all affected tables and indexes into the new filegroup using the create index … with (drop_existing = on) on syntax, to move the tables and remove fragmentation from them at the same time<br>
drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)<br>
basically you need to provision some more space before you can shrink the old files, but it's a much cleaner mechanism.</p>
<p>
如果你完全没有选择需要收缩日志文件，请注意这个操作会导致索引的碎片化，你应该在收缩数据文件采取一些步骤消除它可能导致的性能问题，唯一的方式是用dbcc indexdefpage或 alter index ...reorganize消除索引的碎片不要引起数据文件的增长,这些命令要求扩展空间8kb的页代替重建一个新的索引在索引重建操作中。<br>
底线 — — 尽量避免不惜一切代价运行数据文件收缩</p>
<p>
所以，还在用作业定期收缩数据文件或数据库开启了“自动收缩”属性的朋友们，请及时纠正你们的错误认识吧！</p>
<p>
支持原著，也希望大家支持我辛苦的翻译劳动，请加上链接潇湘隐者博客。</p>
<p>
原文链接：http://www.cnblogs.com/kerrycode/archive/2013/06/04/3116339.html</p>
頁:
[1]