當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

DBA基础系列SQL Server 2014：2. SQL Server用户数据库初始化配置

發布時間：2023/12/31 数据库 36 豆豆

生活随笔收集整理的這篇文章主要介紹了 DBA基础系列SQL Server 2014：2. SQL Server用户数据库初始化配置小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

前言

開始前先黑微軟一把：Microsoft秉承一貫的簡單易用作風（Next、Next、Next… ）這點是它吸引用戶的地方，但是這個優點如果用在數據庫上將是一場災難，如我們上一章講到的SQL Server安裝部署，很多人都只知道下一步下一步下一步，但是從來沒有考慮過安裝向導給出這么多參數配置意義何在，同樣的大家習慣了安裝完SQL Server后直接使用的方式基本上不去考慮用戶數據庫初始配置，隨著業務量不斷攀升，數據量幾何倍數的遞增，然后發現SQL Server越來越慢，但是又各種無解，最后得出一個結論SQL Server只能滿足中小企業需要，但是真實的情況是這樣嗎？答案當然是否定的【傳聞納斯達克核心數據庫原始數據量約2PB現已經轉到SQL 2014上了】視頻地址

下面我將給到大家一些基本配置和思路讓大家的用戶數據庫在未來數據量增長到TB、PB時依然可以有的放矢的去優化性能滿足業務需求

當然這個僅僅是根據我的經驗進行探討，實際情況怎么搭配大家仁者見仁智者見智

無論如何還是要重申一句：本文觀點僅代表個人經驗，如有不足請大家補充，本文內容如果在實際生產中遇到問題不負任何責任，使用前請先自行論證，最權威的資源請查閱微軟官方資料庫MSDN，TechNet，官方Blog以及官方技術白皮書等

關于數據庫文件

場景一：新建數據庫

這種情況進行優化最為輕松，優化成本最低

實施步驟：

1. 業務分析，回答自己幾個基本問題：

業務涉及的表：

哪些表是數據活躍表：

哪些是頻繁變更表，以哪一類變更為主（INSERT、DELETE 或 UPDATE）
哪些是主要查詢的表，數據增量速度

哪些表是數據較活躍表：

這些表數據變化頻率多高
這些表數據訪問頻率多高
涉及到這些表查詢的查詢速度是什么范圍
這些表數據需要保持多久

哪些表是數據惰性表：

這些表數據需要保持多久
這些表每周期增量多少

容量規劃：

首先用戶數據庫至少應該有4個獨立的文件組：

主文件組（默認且必須）：

建議主文件組數據留空，保持2GB固定大小即可（稍后其他章節再詳細討論保持2GB空的好處）

用戶數據文件組（現在存儲越來越快建議每文件4GB固定大小）：

常規用戶數據文件組（至少有一個，通常1-3年或更短）：用戶數據庫存儲
分區表文件組（每張分區表建議一個）：雖然分區也支持跨多個文件組，但是由于MSSQL與Oracle在分區維護上還是有所不同的（詳細的請自行實驗體會），所以一般情況每分區表對應一個文件組即可
歷史數據文件組（可選，通常3-5年）：用于存儲近期內需要匯總統計數據
歸檔數據文件組（可選，通常5-10年）：隨著存儲技術越來越成熟、存儲容量越來越大，很多早期需要Offline到磁帶備查的數據都可以直接存儲到在線數據庫中以方便不時只需

用戶索引文件組（建議每文件8GB固定大小）：

用于存儲常規用戶數據表對應的非聚集索引

日志文件組（默認且必須：建議單個文件，但是初始值建議要設置大些）：

日志文件是串行寫入，所以多個日志文件沒有任何意義，反而增加文件損壞的風險
日志文件每次擴展都很耗時，同時需要阻塞所有的會話，所以應該盡可能給予一個較大值

P.S. 雖然有資料建議文件數量應該為總CPU Core數的一半，但是這個并不是固定不變的（至少我在實際項目中從來沒按這個指導做過），DBA設計文件數量的時候需要考慮：

如何充分利用到多核并行計算能力
如何在一次連續的IO中讀取到更多的有效數據
在某文件組存儲數據量非常大，其下屬的文件也分得很大的時候其一個分解線程去尋找數據的時間或許會比較長（這個問題跟我們不建議數據庫采用單一大文件是一樣的），還不如多一些文件，雖然訪問較多文件會導致線程頻繁切換導致開銷加重，但是卻能緩解在海量數據中尋找有效數據的等待時間，這里又再次體現出了【平衡】兩個字的重要

Q/A

Q：為什么要將常規用戶數據、歷史數據、歸檔數據、分區數據、索引數據劃分文件組進行分別存儲？

A：在文件系統甚至于硬件存儲上，我們需要考慮到上述幾種數據在IO的尋道規律上的差異充分發揮硬件資源以及如何搭配不同IO性能和價格的存儲在滿足業務需求的同時將硬件成本降至最低這也是DBA需要考慮的問題

Q：表、文件組與數據文件是什么關系？

A：表存儲只在乎文件組是否還有空間，與數據文件沒有直接的關系；文件組的存儲空間大小=SUM（其下屬所有數據文件大小）；一個文件組可以包含若干數據文件，一張非分區表只能在一個文件組中（這里只是說的Heap和Clustered Index，而非聚集索引是可以在其它文件組的），一張分區表可以通過分區架構跨越多個文件組

Q：為什么要把數據文件做成固定大小的小文件？

A：為了充分利用多核并行計算能力；為了減少磁盤文件碎片；為了快速的維護存儲（Ex：當我們周期性對大表進行歸檔后會發現文件組一下子騰出很大一塊存儲空間，這時候如果我希望將這些空閑空間分配給其他文件組使用那么我們需要收縮數據庫文件，如果是一個大數據文件我們可能收縮幾天都沒法達到目標，但是如果通過一個一個地清空小數據文件，再刪除空數據文件，我們可以很快釋放出有效存儲供給更多需求使用）等

場景二：已在使用的數據庫

這種情況實際上是早期沒有合理規劃設計而遺留下來的后遺癥，但是我們卻需要去解決它【稍微會比較繁瑣些】

首先場景一所述我們先創建新的文件組并分配數據文件，然后針對小表進行遷移，針對大表進行分區再導入數據

P.S. 題外話：很多DBA只知道索引需要定期重建，卻不知道Heap也要定期重整，不過這給我們實現數據遷移帶來了一些好處（Ex：運行了幾年的數據庫Heap碎片非常的多，表面上可能有100GB，但是重整或遷移以后數據量會縮小一半甚至更多，同時性能會有很大程度的提升）

數據遷移工作：

場景一數據倉庫項目：

數據倉庫從架構設計到建模，唯一的目標：性能；這也使得我們對數據倉庫數據遷移變得方便，因為數據倉庫的表沒有那么多約束條件、主外鍵關聯等，而且通常事實表我們都需要做分區表，剩下大量的維度表數據量都不會太大；

那么步驟就簡單了（特殊情況特殊分析，實施前一定要先去采集和確認元數據中對遷移表的定義）使用以下腳本即可簡單完成：

核心思路：

對沒有聚集索引的表：創建唯一列，指定新的文件組，創建聚集索引，然后刪除聚集索引，最后刪除之前創建的唯一列；

已有聚集索引的表：先刪掉聚集索引，然后指定新的文件組，創建聚集索引，然后刪除聚集索引，最后刪除之前創建的唯一列；

以下腳本供參考，不同情況需要不同方式進行修改（寫得不好，但是能用，有時間再去優化，God請原諒我又再次情不自禁的使用了游標… … 阿門）：

? 1: ?DECLARE @tableName VARCHAR(128) ? 2: ?DECLARE cur CURSORFOR ? 3: ?SELECT T.NAME ? 4: ?FROM SYS.TABLES T ? 5: ?WHERE ? 6: ?T.OBJECT_ID NOTIN( ? 7: ?(SELECT OBJECT_ID ? 8: ?FROM SYS.INDEXES WHERE INDEX_ID=1 AND IS_PRIMARY_KEY = 1 )) ? 9: ? ?10: ?OPEN cur ?11: ? ?12: ?FETCHNEXTFROM cur ?13: ?INTO @tableName ?14: ? ?15: ?WHILE@@FETCH_STATUS =0 ?16: ?BEGIN ?17: ?PRINT' ?18: ? ALTER TABLE ' + @tableName + ' ?19: ? ADD MOVECOLUMN UNIQUEIDENTIFIER NOT NULL ?20: ? CONSTRAINT CONMOVE DEFAULT NEWID() ?21: ? ?22: ? ALTER TABLE ' + @tableName + ' ?23: ? ADD CONSTRAINT [PK_MOVE] ?24: ? PRIMARY KEY CLUSTERED ( MoveColumn) WITH (ONLINE = OFF) ON [FG_Test_001] ?25: ? ?26: ? ALTER TABLE ' + @tableName + ' DROP CONSTRAINT CONMOVE ?27: ? ?28: ? ALTER TABLE ' + @tableName + ' DROP CONSTRAINT [PK_MOVE] ?29: ? ?30: ? ALTER TABLE ' + @tableName + ' DROP COLUMN MoveColumn ?31: ? ?32: ? CHECKPOINT ?33: ? ?34: ? GO' ?35: ? ?36: ?FETCHNEXTFROM cur ?37: ?INTO @tableName ?38: ?END ?39: ? ?40: ?CLOSE cur ?41: ?DEALLOCATE cur

將上述T-SQL打印出來的語句Copy到新查詢器里面批量執行即可

然后再針對遺留在原始文件組中的那些非聚集索引導出其腳本，批量指定新文件組名字然后DROP，再CREATE

最后原始文件基本上都空了，這時候進行一次數據文件收縮，也就是幾秒鐘的事情了，至此底層數據整理完成

P.S.其實大家可以看出重整數據文件的過程并不是想象中那么復雜，理清思路產生一大堆腳本，然后扔到Job里面慢慢執行自己就可以提前下班吃飯了

【DBA的工作內容其實還是蠻愉快的：90%以上時間都是在等，等腳本執行，等問題發生，最關鍵的是等老板發工資；如果一家公司DBA每天都很忙要么是DBA能力不夠，要么就是公司或老板有問題】

場景二交易型項目：

交易型項目比數據倉庫要復雜一些，不過基本上也是使用上述腳本修修改改就能快速完成

需要注意的地方：主鍵、外鍵、約束（尤其是自增約束等）

如果有這些特殊情況就需要有針對性的逐步分析逐步遷移了，這時候就需要先理出一個優先級列表慢慢處理

關于數據庫參數

數據庫參數也是比較重要的一個環節，尤其是SQL 2000退役，SQL 2005停止主流支持、SQL 2008即將停止主流支持，大量的數據庫需要升級到更高版本

數據庫兼容級別

在幫助客戶進行性能問題分析的時候我留意了一下，很多升級上來的數據庫基本上數據庫兼容級別都沒有修改，這個是需要注意的

SQL Server一般情況默認向下兼容3個主流版本，是為了防止由于版本升級導致用戶使用的早期語法或特殊的系統視圖出現錯誤而給予的緩沖機會，并不是說大家在已經升級到高版本后還是繼續使用老版本的特性

CHECKSUM

這個參數一般是由于從SQL 2000升級上來時候沒有重新配置導致，

SQL 2000的時候還只能簡單的檢查一下數據頁頭和定義，無法檢查數據頁是否真的正常

建議升級上來的所有數據庫都去檢查一下這個并設置為CHECKSUM

自動維護

自動關閉數據庫：這個參數類似于Oracle關閉時僅發出shutdown命令，它會等待所有會話結束后關閉數據庫，一般情況我們都會用到immediate要求立即結束和關閉

自動創建統計信息：這個最好是開啟，SQL執行依賴的執行計劃，執行計劃評估執行成本就是根據統計信息來的，所以這個相當重要，不是特殊情況應該保持常開

自動收縮數據庫：個人從來就很詫異這個參數，如此耗費性能又沒有實際價值的一個參數有存在的意義嗎？如果不是特殊原因這個參數應該始終關閉

自動更新統計信息：這個參數同自動創建統計信息是一樣的，強烈建議常開，統計信息收集越準SQL執行選擇的執行計劃就會越優，性能也就越好

自動異步更新統計信息：這個參數建議設置為開啟，我們知道統計信息非常重要，但是如果每次數據變化都去收集一次統計信息這個性能也就不那么好了，這個又再次體現了【平衡】

生活随笔