數(shù)據(jù)庫文件有兩大類：數(shù)據(jù)文件跟日志文件，每一個數(shù)據(jù)庫至少各有一個數(shù)據(jù)文件或者日志文件，數(shù)據(jù)文件用來存儲數(shù)據(jù)，日志文件用來存儲數(shù)據(jù)庫的事務修改情況，可用于恢復數(shù)據(jù)庫使用。

    這里分為兩篇博文講解，本文來說說數(shù)據(jù)文件。

    如果轉載，請注明博文來源： www.cnblogs.com/xinysu/   ，版權歸 博客園 蘇家小蘿卜 所有。望各位支持！

    本系列上一篇博文鏈接：SQL SERVER大話存儲結構（5）_SQL SERVER 事務日志解析

回到頂部(go to top)

1 創(chuàng)建數(shù)據(jù)文件時，在考慮什么

1.1 數(shù)據(jù)文件與文件組

    數(shù)據(jù)文件有兩類，一類是主數(shù)據(jù)文件，一類是輔助數(shù)據(jù)文件。

    每一個數(shù)據(jù)庫都有一個主數(shù)據(jù)文件數(shù)據(jù)文件用來存儲數(shù)據(jù)，擴展名是 mdf。 

    一個數(shù)據(jù)庫可以有0到多個的輔助數(shù)據(jù)文件，擴展名是 ndf。

    文件組這個概念，可能大多數(shù)人只有在涉及表分區(qū)的時候有了解過。

    顧名思義，文件組，就是給數(shù)據(jù)文件分為多個組，方便分配磁盤IO資源以及運維管理。每個數(shù)據(jù)庫至少有一個文件組，含數(shù)據(jù)庫主數(shù)據(jù)文件的組稱之為 主文件組，一般不指定文件組名創(chuàng)建表格或者索引，則會默認把數(shù)據(jù)文件放在主文件組中，因為默認 主文件組就是 默認文件組，當然，也可以通過ALTER 語句來修改默認文件組為 其他文件組，這樣，創(chuàng)建數(shù)據(jù)文件但不指定文件組時，則存放到設置的默認文件組中。

    這里有個注意事項：數(shù)據(jù)庫中的大多數(shù)操作都是僅針對于文件組操作，比如創(chuàng)建索引或者創(chuàng)建表格。

    那么，什么情況下需要單文件，什么情況下有需要多個輔助數(shù)據(jù)文件呢？

• 建立表格及索引時，只能指定到某一個文件組，不能夠指定到這個文件組的某個文件

• 同一個文件組內(nèi)的數(shù)據(jù)文件，起到一個平攤分布數(shù)據(jù)的作用，如果是位于不同的驅動器，則有利于提高并發(fā)IO，如果是位于同一個驅動器，則有利于后期的運維管理；

• 當使用表分區(qū)的時候，每一個分區(qū)會使用到一個輔助數(shù)據(jù)文件（可以同一個驅動器，也可以不同）

• 大庫的靈活運維管理，其實呢，如果在同一個驅動器上建立多個數(shù)據(jù)文件，對IO性能并沒有任何改善，但是，卻為后期的管理提供了方便性，尤其是大庫管理，比如線上數(shù)據(jù)庫損壞，需要還原出來一個新的數(shù)據(jù)文件，或者是測試環(huán)境的搭建等等，很多時候會遇到剩余的磁盤空間并不足以來存放這個大庫，但是如果是多個數(shù)據(jù)文件，那么就可以分開指定驅動器存儲，減少磁盤大小的要求。

    那么，什么情況下，會使用到多個文件組呢？

• 使用表分區(qū)

• 當磁盤IO資源出現(xiàn)瓶頸的情況下，可以考慮遷移部分熱表到 其他文件組的文件上（不同驅動器），分散IO；

• 當磁盤空間不足但是想把文件中的 冷表（類似與記錄登錄日志）的表格，遷移到其他驅動器上，可以考慮使用文件組；

• 歷史數(shù)據(jù)和熱數(shù)據(jù)分開，歷史歸檔數(shù)據(jù)損壞，不影響熱數(shù)據(jù)；

• 大庫的靈活運維管理，可以使用文件組來備份數(shù)據(jù)庫的一部分，比如某些特定的表格放在 輔助數(shù)據(jù)文件上，出事故后，還原的時候，可以對數(shù)據(jù)庫進行部分還原，主文件組還原結束，即可提供服務，但在其他文件組上的對象暫時不能使用，等到其他文件組也還原結束，其存儲的數(shù)據(jù)才能提供服務。

    如何新增文件或者文件組呢？如何遷移表格數(shù)據(jù)到新的文件組呢？

 1 --案例 1 ：給數(shù)據(jù)庫 dbpage新增 文件組 testfg，并在這個文件組內(nèi)建立輔助數(shù)據(jù)文件 dbpage_3，dbpage_4 2 USE [master] 3 GO 4 ALTER DATABASE [dbpage] ADD FILEGROUP [testfg] 5 GO 6   7 ALTER DATABASE [dbpage] 8 ADD FILE ( 9            NAME = N'dbpage_3',10            FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\DATA\dbpage_3.ndf' ,11            SIZE = 51200KB ,12            FILEGROWTH = 10240KB13          ) TO FILEGROUP [testfg]14 GO15  16 ALTER DATABASE [dbpage]17 ADD FILE (18            NAME = N'dbpage_4',19            FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\DATA\dbpage_4.ndf' ,20            SIZE = 51200KB ,21            FILEGROWTH = 10240KB22          ) TO FILEGROUP [testfg]23 GO24  25 --案例 2 ：指定文件組創(chuàng)建表格26 CREATE TABLE tbtest(id int not null,name varchar(10) not null) on [testfg]27  28 --案例 3 ：遷移表到其他文件組29 --表無聚集索引，通過建立聚集索引，把整個表格遷移到 指定文件組30 alter table tbtest add constraint pk_tbtest primary key (id) on [testfg]31  32 --表有聚集索引33 方法一：重建聚集索引，先刪除聚集索引，然后再建立新的聚集索引指定到文件組，如上一個SQL34 方法二：利用表分區(qū)，先建立 中間表格，中間表添加分區(qū)方案，分區(qū)建立在 指定的文件組上，然后再 需要遷移到表格上執(zhí)行 swith partion，然后重命名表格，最后刪除舊表，中間表格的分區(qū)腳步這里不涉及35  36 ALTER TABLE tbtest SWITCH PARTITION 1 TO tbtest_new PARTITION 1 37 GO38  39 EXEC sp_rename 'tbtest','tbtest_old'40 EXEC sp_rename 'tbtest_new','tbtest'41 GO42  43 DROP TABLE tbtest_old44 GO

  檢查某個表格在文件組的分布

1 select2  3       fg.name fgname,o.name tbname ,index_id,rows,au.type_desc,au.container_id,au.total_pages,au.used_pages,au.data_pages4 from sys.partitions p5 join sys.allocation_units au on p.partition_id=au.container_id6 join sys.filegroups fg on fg.data_space_id=au.data_space_id7 join sys.objects o on p.object_id=o.object_id8 where o.type='u'  and p.object_id=object_id('orders')

    檢查每個文件組一共有多少個表格

 1 with data as( 2       select 3             fg.name fg_name, o.name tbname 4       from sys.partitions p 5          join sys.allocation_units au on p.partition_id=au.container_id 6          join sys.objects o on p.object_id=o.object_id 7          join sys.filegroups fg on fg.data_space_id=au.data_space_id 8          where o.type='u' 9       group by o.name,fg.name10 )11 select12       a.fg_name,13          count(*) tbcount,14          tbnames=stuff((select ','+b.tbname from data b where a.fg_name=b.fg_name order by tbname for xml path('')),1,1,'')15 from data a16 group by fg_name

1.2 增長選項

    設置數(shù)據(jù)庫文件的似乎，需要判斷是否啟用自動增長，如果啟用，是采用百分比增長還是按指定大小增長，是否設置文件最大大小。

    首先，在線上業(yè)務，建議是：不設置文件的最大大小，避免 某些業(yè)務數(shù)據(jù)異常增長 導致空間不足，當然，這個設置的前提是，做了磁盤剩余空間監(jiān)控及報警。

    既然不設置最大文件大小，那么就需要設置自動增長，但是，注意，建議在數(shù)據(jù)庫最開始的時候，就設置足夠大的空間，避免頻繁自動增長，每次自動增長都會在增長期間影響到數(shù)據(jù)庫的IO性能，從而影響數(shù)據(jù)庫的使用，所以建議在最開始的時候，設置足夠大的空間，如果后面發(fā)現(xiàn)文件自動增長比較頻繁，可以找一個業(yè)務低峰期，再擴大數(shù)據(jù)文件，設置文件增長大小，建議不要設置為 百分比，避免數(shù)據(jù)庫太大，按百分比，一次增長太大，導致增長影響時間加長，監(jiān)建議設置為 指定大小 ，可以在200M左右，實際可根據(jù)磁盤性能及增長情況來調(diào)整。

    增長的調(diào)整，可以通過指定選項FILEGROWTH ，設置百分比 FILEGROWTH = 10%或者 設置指定大小 FILEGROWTH = 204800KB，或者通過界面操作。

1.3 即時初始化

  說起文件增長，這里要提一個至關重要的 windows 系統(tǒng)參數(shù)配置：即時初始化（Instant File Initialization）。

    什么是初始化呢？

    當服務需要申請存儲空間來使用 時，操作系統(tǒng)需要用零來填充空間，填充結束則完成初始化操作，但是，如果申請的空間比較大時，會耗費非常久的時間。

    什么是即時初始化呢？

    即時文件初始化功能將回收使用的磁盤空間，而無需使用零填充空間，直接跳過了零填充的過程，新數(shù)據(jù)寫入文件時會覆蓋磁盤內(nèi)容。如果SQL SERVER服務登錄用戶開啟了即時初始化，那么就可以瞬間完成對數(shù)據(jù)文件的初始化，注意，日志文件不能立即初始化。

    SQL SERVER 中哪些操作可以即時初始化？

1. 創(chuàng)建數(shù)據(jù)庫

2. 向現(xiàn)有數(shù)據(jù)庫中添加文件

3. 增大現(xiàn)有文件的大小、包括自動增長操作（不含日志文件的自動增長）

4. 還原數(shù)據(jù)庫或文件組

    就拿建立數(shù)據(jù)庫來說，不設置即時初始化文時，創(chuàng)建一個100G的數(shù)據(jù)庫需要接近6min，但是開啟了即時初始化后，僅需要3秒。更好的應用是在自動增長這塊，能有效減少自動增長的時間，從而大大減少自動增長的影響時間。

    如何開啟即時初始化？

    查看SQLSERVER引擎的登錄用戶->給該用戶添加 ' 執(zhí)行卷維護服務 ' -> 重啟SQLSERVER服務。

    查看SQL SERVER引擎的登錄用戶，如下：

    打開 `管理工具`，點擊 `本地安全策略` ，按下圖找到 `執(zhí)行卷維護任務` ，雙擊后選擇添加 SQL server 服務的登錄用戶，然后點擊 `應用` 即可。

    添加后，需要重啟SQL SERVER服務，使其加載該權限。所以，建議在數(shù)據(jù)庫一開始安裝的時候，就配置好該權限，或者在數(shù)據(jù)庫宕機或者維護期間，做該操作。

    如何檢查是否開啟即時初始化？

    檢查創(chuàng)建DB的時候，是否直接跳過零填充的過程。

 1 /* 2 以全局方式打開跟蹤標記 3004 和 3605。 3 3004：查看SQL Server對日志文件進行填零初始化的過程 4 3605：要求DBCC的輸出放到SQL server ERROR LOG 5   -1：以全局方式打開指定的跟蹤標記。 6 */ 7   8 DBCC TRACEON(3004,3605,-1) 9 GO10  11 --創(chuàng)建測試庫12 CREATE DATABASE [xinysu]13  CONTAINMENT = NONE14  ON  PRIMARY15 ( NAME = N'xinysu',16   FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\DATA\xinysu.mdf' ,17   SIZE = 104857600KB , FILEGROWTH = 204800KB18 )19  LOG ON20 ( NAME = N'xinysu_log',21   FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\DATA\xinysu_log.ldf' ,22   SIZE = 524288KB , FILEGROWTH = 102400KB23 )24 GO25  26 --查看錯誤日志27 Exec xp_readerrorlog 0,1,Null,Null,'2017-05-29 10:28:00','2017-05-29 10:30:00'28  29 --刪除測試庫30 DROP DATABASE xinysu31 GO32  33 DBCC TRACEOFF(3004,3605,-1)34 GO

    可以看到，創(chuàng)建數(shù)據(jù)庫xinysu，數(shù)據(jù)文件100G，日志文件512Mb，都是直接跳過零填充的過程，速度非常快。

回到頂部(go to top)

2 DB收縮

2.1 指令及設置

    執(zhí)行收縮的指令有兩種：shrinkfile跟shrinkdatabase。一個是指定某個文件進行壓縮，一個是指定某個數(shù)據(jù)庫，對數(shù)據(jù)庫下的所有文件進行壓縮。  

    Shrinkfile的指令如下：

 1 DBCC SHRINKFILE  ( { file_name | file_id } { [ , EMPTYFILE ] | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]   }  )  [ WITH NO_INFOMSGS ] 2   3 /* 4 target_size 5 用兆字節(jié)表示的文件大?。ㄓ谜麛?shù)表示）。 如果未指定，則 DBCC SHRINKFILE 將文件大小減少到默認文件大小。 默認大小為創(chuàng)建文件時指定的大小。如果target_size指定，DBCC SHRINKFILE 嘗試將文件收縮到指定的大小。 將要釋放的文件部分中的已使用頁重新定位到保留的文件部分中的可用空間。 
 6   7 EMPTYFILE 8 將所有數(shù)據(jù)從指定的文件都遷移到其他文件相同的文件組。 換而言之，清空文件將遷移數(shù)據(jù)，從指定的文件到同一個文件組中的其他文件。 清空文件可確保你沒有新數(shù)據(jù)將添加到文件。可以通過刪除該文件ALTER DATABASE語句。 9  10 NOTRUNCATE11 文件末尾的可用空間不會返回給操作系統(tǒng)，文件的物理大小也不會更改。 因此，指定 NOTRUNCATE 時，文件看起來未收縮。12 NOTRUNCATE 只適用于數(shù)據(jù)文件。 日志文件不受影響。13  14 TRUNCATEONLY15 將文件末尾的所有可用空間釋放給操作系統(tǒng)，但不在文件內(nèi)部執(zhí)行任何頁移動。 數(shù)據(jù)文件只收縮到最后分配的區(qū)。16 target_size如果使用 TRUNCATEONLY 指定將被忽略。17 TRUNCATEONLY 選項不會移動日志中的信息，但會刪除日志文件末尾的失效 VLF。18  19 WITH NO_INFOMSGS20 取消顯示所有信息性消息。21 */22  23 --舉例說明24 DBCC SHRINKFILE ( dbpage_data, 100 )25  26 DBCC SHRINKFILE ( dbpage_data, EMPTYFILE)27 --清空 dbpage_data 數(shù)據(jù)文件上面的所有內(nèi)容28  29 DBCC SHRINKFILE ( dbpage_data, 100 ,NOTRUNCATE)30 --收縮數(shù)據(jù)庫 datapage的數(shù)據(jù)文件，文件名師 dbpage_data，收縮到100Mb31 --重新分配超過100Mb的數(shù)據(jù)行到前面100Mb未分配的區(qū)，保留空閑空間32  33 DBCC SHRINKFILE ( dbpage_data, TRUNCATEONLY)34 --收縮數(shù)據(jù)庫 datapage的數(shù)據(jù)文件，文件名是 dbpage_data，文件末尾未使用的空間釋放給操作系統(tǒng)，不會重新分配數(shù)據(jù)行到未分配的區(qū)35

  Shrinkdatabase指令使用如下：

 1 DBCC SHRINKDATABASE ( database_name | database_id | 0  [ , target_percent ]  [ , { NOTRUNCATE | TRUNCATEONLY  } ] ) [ WITH NO_INFOMSGS ]   2   3 /* 4 database_name | database_id | 0  
 5 要收縮的數(shù)據(jù)庫的名稱或 ID。 如果指定 0，則使用當前數(shù)據(jù)庫。 6   7 target_percent 8 數(shù)據(jù)庫收縮后的數(shù)據(jù)庫文件中所需的剩余可用空間百分比。 9  10 NOTRUNCATE11 通過將已分配的區(qū)從文件末尾移動到文件前面的未分配區(qū)來壓縮數(shù)據(jù)文件中的數(shù)據(jù)。 target_percent是可選的。12 文件末尾的可用空間不會返回給操作系統(tǒng)，文件的物理大小也不會更改。 因此，指定 NOTRUNCATE 時，數(shù)據(jù)庫看起來未收縮。13 NOTRUNCATE 只適用于數(shù)據(jù)文件。 日志文件不受影響。14  15 TRUNCATEONLY16 將文件末尾的所有可用空間釋放給操作系統(tǒng)，但不在文件內(nèi)部執(zhí)行任何頁移動。 數(shù)據(jù)文件只收縮到最后分配的區(qū)。 target_percent如果使用 TRUNCATEONLY 指定將被忽略。17 TRUNCATEONLY 將影響日志文件。 若要僅截斷數(shù)據(jù)文件，請使用 DBCC SHRINKFILE。18  19 WITH NO_INFOMSGS20 取消嚴重級別從 0 到 10 的所有信息性消息。21 */22  23 --舉例說明24 DBCC SHRINKDATABASE (dbpage, 20)25 --對數(shù)據(jù)庫dbpage執(zhí)行收縮處理，其中收縮后空閑空間占整個數(shù)據(jù)庫大小的 20%26 --等同于先執(zhí)行 DBCC SHRINKDATABASE (dbpage, 20, NOTRUNCATE) ，再執(zhí)行DBCC SHRINKDATABASE (dbpage, 20, TRUNCATEONLY)27  28 DBCC SHRINKDATABASE (dbpage, 20, NOTRUNCATE)29 --對數(shù)據(jù)庫dbpage執(zhí)行收縮處理，其中收縮后空閑空間占整個數(shù)據(jù)庫大小的 20%30 --數(shù)據(jù)文件，分配文件末尾的區(qū)到文件前面未分配的區(qū)，壓縮空間不會返回給操作系統(tǒng)，文件大小不變31  32 DBCC SHRINKDATABASE (dbpage, 20, TRUNCATEONLY)33 --對數(shù)據(jù)庫dbpage執(zhí)行收縮處理，但是收縮的空間不一定是 20%34 --收縮的空間是文件末尾的可用空間，也就是 target_percent 在這里指定了也沒有用35 --日志文件跟數(shù)據(jù)文件，釋放文件末尾的可用空間給系統(tǒng)文件，但是文件內(nèi)不執(zhí)行任何數(shù)據(jù)頁移動

    測試數(shù)據(jù)庫 dbpage，先查看數(shù)據(jù)庫的空間分布情況，再收縮數(shù)據(jù)庫，使得收縮后的數(shù)據(jù)，剩余的空余空間占整個數(shù)據(jù)庫的50%。

use dbpage

GO

sp_spaceused

--數(shù)據(jù)庫總大小 58M，其中未使用的空間有 40Mb

dbcc shrinkdatabase ('dbpage',50)

--執(zhí)行收縮后，返回數(shù)據(jù)文件占用2512個數(shù)據(jù)頁，實際使用1248個數(shù)據(jù)頁，預估可以再收縮1248個數(shù)據(jù)頁；日志文件占用288個數(shù)據(jù)頁。

sp_spaceused

     收縮是指，回收數(shù)據(jù)庫未使用的空間，如果數(shù)據(jù)文件20M，但是實際大小只有10M，那么DBCC SHRINKFILE 指定target_size=15M，則是把最后5Mb上面實際存儲的數(shù)據(jù)內(nèi)容重新分配到前面15Mb中未分配的區(qū)中，注意，DBCC SHRINKFILE 不會將文件收縮到小于存儲文件中的數(shù)據(jù)所需要的大小。 例如，數(shù)據(jù)文件 實際使用 7 MB ，但占用 10 MB ，執(zhí)行 DBCC SHRINKFILE 語句target_size的 6 時，將文件收縮到僅 7 MB，不是 6 MB。對于dbcc shrinkdatabase，也是跟以上的注意事項一樣。

    設置有自動收縮跟手動收縮兩種。

    自動收縮，可在 數(shù)據(jù)庫 的`屬性` 設置，把自動收縮設置為true，也可以執(zhí)行命令如下：

USE [master]

GO

ALTER DATABASE [databasename] SET AUTO_SHRINK ON WITH NO_WAIT

GO

    自動收縮，其運行結果等同于 執(zhí)行 dbcc shrinkdatabase(dbname,25)，及在數(shù)據(jù)庫中保留25%的自由空間，其他剩余空間回收，每30分鐘檢查一次來收縮數(shù)據(jù)。

2.2 原理

    執(zhí)行的時候，對數(shù)據(jù)庫的每一個文件逐個進行壓縮，從文件的末尾開始壓縮。數(shù)據(jù)庫引擎將按

http://www.cnblogs.com/xinysu/p/6949867.html

延伸閱讀

• LINQ TO SQL和Entity Framework 的關系 你了解多少？ 2017-07-26
• SQL Server事務的隔離級別和鎖 2017-07-21
• MyBatis-sql映射文件 2017-07-21
• SQL數(shù)據(jù)庫操作(CURD) 2017-07-13
• SQL Server AlwaysOn配置兩個節(jié)點加共享文件夾仲裁見證 2017-07-13
• SQL-結構化查詢語言(1) 2017-07-12
• SQL執(zhí)行過程中的性能負載點 2017-07-10
• SQL Server特殊用法筆記 2017-07-07
• hive集成sentry的sql使用語法 2017-07-04
• SQL Server--獲取磁盤空間使用情況 2017-06-27

SQL Server事務的隔離級別和鎖