starter_pdfmerge

Transcription

1 許 致 學

2 本 場 次 內 容 效 能 調 校 與 規 劃 SQL Server 2008 提 升 效 能 的 新 功 能 SQL Server 2008 效 能 調 校 工 具

3 效 能 不 佳 之 影 響 法 滿 足 使 用 者 需 求 無 業 生 產 力 客 戶 忠 誠 度 企 資 訊 人 員 績 效 不 彰 痛 醫 頭 腳 痛 醫 腳 頭 急 亂 投 醫 硬 體 升 級 不 一 定 可 以 獲 得 改 善 病 能 不 佳 的 瓶 頸 效 應 會 遞 延 累 加 更 形 嚴 重 效 體 資 源 不 足 過 於 忙 碌 只 能 將 待 處 理 的 要 求 硬 分 配 更 少 的 資 源 進 行 處 理 服 器 不 穩 定 伺 體 資 源 不 足 過 於 忙 碌 甚 至 影 響 硬 體 壽 命 硬

4 效 能 調 校 的 範 圍 系 統 出 效 能 不 佳 的 原 因 找 統 硬 體 資 源 系 SQL Server 與 效 能 相 關 的 設 定 應 用 程 式 應 用 程 式 作 效 能 改 善 方 案 實 出 效 能 最 大 瓶 頸 研 究 是 否 有 方 法 找 以 改 善 可 服 器 硬 體 架 構 資 料 庫 應 用 程 式 伺 避 免 效 能 不 佳 導 致 的 系 統 不 穩 定 適 當 的 規 劃 達 成 更 佳 的 效 能 表 現

5 效 能 調 校 的 挑 戰 統 硬 體 資 源 系 理 器 記 憶 體 磁 碟 網 路 處 效 能 相 關 的 設 定 與 料 庫 大 小 資 料 表 大 小 使 用 者 人 數 資 料 庫 設 計 檔 案 規 劃 索 引 資 料 分 割 資 料 壓 縮 資 tempdb 用 程 式 應 用 程 式 架 構 語 法 交 易 與 鎖 定 應 中 式 處 理 vs. 分 散 式 處 理 集 合 處 理 vs. 逐 筆 處 理 集 單 一 主 機 硬 體 資 源 升 級 vs. 多 部 主 機 分 擔 處 理 負 荷 如 何 達 到 影 響 程 度 最 小 效 能 有 效 提 升

6 效 能 調 校 與 規 劃 速 資 料 庫 處 理 效 能 加 加 伺 服 器 處 理 效 能 增 多 部 伺 服 器 架 構

7 加 速 資 料 庫 處 理 效 能 料 表 設 計 資 料 型 態 欄 位 數 量 資 引 設 計 索 集 索 引 vs. 非 叢 集 索 引 叢 料 庫 物 件 資 存 程 序 使 用 者 自 訂 預 式 設 計 程 料 指 標 vs. 資 料 集 資 正 規 化 程 度 函 數 CLR 組 件 TRUNCATE TABLE 計 算 型 欄 位 + 索 引 索 引 檢 視 表 BULK INSERT 詢 語 法 交 易 處 理 查 非 同 步 處 理 MARS XML 索 引 全 文 檢 索

8 資 料 庫 設 計 之 建 議 料 表 設 計 資 料 欄 位 型 態 與 長 度 要 適 切 資 主 鍵 叢 集 索 引 與 外 鍵 正 規 化 與 資 料 表 的 切 割 和 合 併 免 前 端 程 式 直 接 存 取 基 礎 資 料 表 避 量 透 過 預 存 程 序 檢 視 表 以 及 使 用 者 自 訂 函 數 來 存 取 儘 料 不 要 以 程 式 直 接 存 取 資 料 表 資 率 較 佳 更 有 彈 性 的 安 全 設 定 提 供 修 改 資 料 表 架 構 的 空 間 效 分 開 線 上 交 易 與 線 上 分 析 的 存 取

9 際 案 例 實 當 的 資 料 類 型 與 長 度 適 資 料 類 型 資 料 類 型 ( 調 整 後 ) 差 異 欄 位 數 節 省 INT TINYINT 3 Bytes 2 6 Bytes INT SMALLINT 2 Bytes 4 8 Bytes DATETIME SMALLDATETIME 4 Bytes 3 12 Bytes NCHAR(10) CHAR(10) 10 Bytes 1 10 Bytes VARCHAR(x) CHAR(x) 2 Bytes 7 14 Bytes 每 筆 記 錄 共 節 省 50 Bytes

10 Server 的 索 引 架 構 SQL 籍 SQL Server 資 料 庫 儲 存 方 式 書 料 表 資 (Table) 集 索 引 叢 Index) (Clustered 叢 集 索 引 非 Index) (Nonclustered 料 頁 資 Pages) (Data 每 個 索 引 最 多 可 以 包 括 16 個 欄 位 不 得 超 過 900 Bytes 4. 搭 配 內 含 資 料 行 則 不 在 此 限 內 容 索 引 頁 + 資 料 頁 目 錄 引 頁 索 Pages) (Index 名 詞 解 釋 1. 每 個 資 料 表 只 能 有 1 個 叢 集 索 引 2. 每 個 資 料 表 最 多 可 以 有 249 個 非 叢 集 索 引 3. 非 叢 集 索 引 對 應 至 Row ID 或 Clustering Key

11 叢 集 索 引 與 非 叢 集 索 引 Non-Leaf Leaf B-Tree 集 索 引 索 引 頁 資 料 頁 叢 8K Bytes 非 叢 集 索 引 索 引 頁 索 引 頁 8K Bytes Root Balanced Tree Non-Leaf Leaf 叢 集 索 引 目 錄 會 影 響 資 料 表 實 際 儲 存 資 料 時 的 排 序 1. 則 適 用 於 經 常 ORDER BY GROUP BY 等 查 詢 規 非 叢 集 索 引 名 詞 解 釋 不 會 影 響 資 料 表 實 際 儲 存 資 料 2. 的 排 序 規 則 適 用 於 查 詢 資 料 表 部 分 欄 位 時 時

12 索 引 建 立 之 注 意 事 項 引 建 立 前 索 資 料 庫 之 復 原 模 式 選 項 變 更 為 BULK_LOGGED 將 建 立 叢 集 索 引 再 建 立 非 叢 集 索 引 先 則 非 叢 集 索 引 還 會 再 由 Row ID 對 應 至 Clustering Key 否 引 建 立 時 機 索 系 統 已 很 忙 碌 且 要 建 立 索 引 的 資 料 表 資 料 量 龐 大 若 引 建 立 後 索 資 料 庫 之 復 原 模 式 選 項 變 更 為 FULL 將 認 建 置 的 索 引 發 揮 足 夠 的 效 能 改 善 確 使 用 到 的 使 用 率 低 的 索 引 考 慮 予 以 調 整 或 刪 除 未 引 建 立 的 過 程 不 會 逐 一 寫 入 交 易 記 錄 檔 索 大 幅 減 少 磁 碟 I/O 建 置 時 間 約 可 省 40% 可 要 馬 上 建 立 索 引 避 免 增 加 系 統 的 負 荷 不 程 於 離 峰 時 段 建 立 索 引 的 作 業 排

13 索 引 建 立 之 準 則 慮 建 立 索 引 之 欄 位 考 要 鍵 與 外 部 鍵 主 位 經 常 需 要 搜 尋 某 個 範 圍 的 值 欄 位 經 常 需 要 排 序 欄 欄 位 經 常 需 要 群 組 以 進 行 匯 總 慮 不 要 建 立 索 引 之 欄 位 考 查 詢 中 很 少 用 到 的 欄 位 在 位 的 值 重 複 性 很 高 欄 ntext, 或 image 等 資 料 類 型 text,

14 際 案 例 實 需 保 留 三 個 月 的 資 料 只 TRUNCATE TABLE 取 代 以 DELETE WHERE

15 去 除 WHERE 子 句 中 不 必 要 的 判 斷 式 調 整 前 95.74% 能 相 差 效 倍 22.5 調 整 前 4.26%

16 WHERE 子 句 中 盡 量 不 要 用 否 定 條 件 調 整 前 97.36% 能 相 差 效 倍 36.9 調 整 前 2.64%

17 增 加 伺 服 器 處 理 效 能 位 元 系 統 64 足 的 記 憶 體 充 碟 規 劃 磁 碟 陣 列 最 佳 化 磁 之 規 劃 Tempdb 易 記 錄 檔 與 資 料 檔 儲 存 於 不 同 磁 碟 組 交 個 檔 案 群 組 搭 配 多 個 資 料 檔 案 多 割 資 料 表 分 割 索 引 資 料 表 壓 縮 分

18 際 案 例 實 體 升 級 + 程 式 修 改 硬 過 70 個 資 料 庫 在 同 一 部 主 機 上 超 個 查 詢 導 致 處 理 器 滿 載 10 分 鐘 以 上 嚴 重 影 響 所 有 系 統 效 能 某 體 升 級 前 硬 理 器 Itanium * 8 + 記 憶 體 128 GB 處 體 升 級 後 硬 理 器 Itanium * 16 + 記 憶 體 256 GB 處 體 升 級 前 硬 體 升 級 後 程 式 修 改 前 程 式 修 改 後 硬 詢 ID 數 量 100 查 詢 時 間 275 秒 查 詢 ID 數 量 100 查 詢 時 間 49 秒 查 詢 ID 數 量 275 查 詢 時 間 145 秒 查 詢 ID 數 量 275 查 詢 時 間 6 秒 查

19 資 料 分 割 單 一 資 料 表 索 引 區 分 將 不 同 儲 存 體 成 用 Range 分 割 技 術 進 行 資 料 分 組 使 史 資 料 與 線 上 資 料 管 理 更 容 易 歷 升 資 料 庫 備 份 與 還 原 的 效 率 提 升 資 料 刪 除 與 大 量 資 料 載 入 的 效 率 提 少 歷 史 資 料 索 引 離 散 與 重 整 的 狀 況 減 化 應 用 程 式 開 發 與 資 料 庫 管 理 簡 企 業 版 援 資 料 表 與 索 引 的 分 割 支 存 至 不 同 的 檔 案 群 組 儲

20 Tempdb 之 規 劃 之 用 途 Tempdb 存 資 料 表 子 查 詢 HASH JOIN ORDER BY 暫 BY SELECT DISTINCT 快 照 式 交 易 隔 離 等 級 GROUP 上 索 引 維 護 作 業 線 量 使 用 Tempdb 時 之 設 定 大 保 Tempdb 有 足 夠 的 資 料 檔 大 小 確 將 Tempdb 的 資 料 檔 指 定 至 不 同 的 磁 碟 組 果 是 SQL Server 有 多 個 CPU 時 建 議 Tempdb 的 資 如 檔 個 數 與 CPU 核 心 數 相 同 料 MS KB328551

21 資 料 庫 異 動 與 交 易 記 錄 檔 1 藉 由 應 用 程 式 異 動 資 料 3 異 動 資 料 寫 入 將 易 記 錄 檔 磁 碟 交 交 易 記 錄 檔 磁 碟 用 戶 端 緩 衝 區 快 取 SQL Server 循 序 存 取 資 料 檔 磁 碟 2 資 料 讀 出 並 寫 入 將 衝 區 快 取 然 後 修 改 緩 4 Checkpoint 當 生 將 已 完 成 的 發 隨 機 存 取 交 易 寫 入 資 料 庫

22 規 劃 檔 案 群 組 提 升 效 能 經 常 要 查 詢 或 更 新 的 資 料 表 指 定 存 放 於 將 同 磁 碟 組 的 檔 案 群 組 不 非 叢 集 索 引 指 定 存 放 於 不 同 磁 碟 組 的 檔 將 群 組 案 常 用 的 現 有 資 料 與 歷 史 資 料 分 割 儲 存 至 不 將 的 資 料 表 並 指 定 存 放 於 不 同 磁 碟 組 的 檔 同 群 組 案 考 慮 採 用 分 割 資 料 表 與 資 料 壓 縮 或

23 將 資 料 庫 分 為 多 個 檔 案 一 資 料 檔 案 只 能 以 單 一 執 行 單 執 行 檔 案 I/O 無 法 發 揮 硬 緒.mdf 碟 系 統 的 效 能 CPU DISK 資 料 庫 每 個 檔 案 群 組 分 為 多 將 資 料 檔 案 可 充 分 發 揮 多 個.ndf.mdf.ndf.ndf.ndf 多 核 心 同 時 執 行 檔 案 I/O CPU 多 執 行 緒 特 性 使 硬 碟 系 統 的.ndf.ndf.ndf 發 揮 更 好 的 效 能 ( 類 似 RAID 0) 意 事 項 注 個 資 料 檔 案 必 須 建 立 為 同 樣 的 初 始 大 小 同 樣 的 成 長 大 小 多 否 則 資 料 檔 案 會 以 檔 案 大 小 的 比 例 寫 入 造 成 磁 碟 I/O 不 平 均

24 磁 碟 配 置 建 議 ( 一 ) RAID 1 RAID 5 作 業 系 統 檔 案 群 組 (Primary) 檔 案 1 SQL Server 檔 案 N Tempdb 檔 案 群 組 (Index) 檔 案 1 檔 案 N 交 易 記 錄 檔

25 磁 碟 配 置 建 議 ( 二 ) 案 群 組 (Data2) 檔 案 1 檔 案 N 檔 案 群 組 (Data3) 檔 案 1 檔 案 N 檔 RAID 1 RAID 10 RAID 10 作 業 系 統 檔 案 群 組 (Primary) 檔 案 1 檔 案 N SQL Server 檔 案 群 組 (Data1) 檔 案 N Tempdb 檔 案 1 交 易 記 錄 檔 檔 案 群 組 (Index) 檔 案 1 檔 案 N

26 際 案 例 實 個 檔 案 群 組 搭 配 多 個 資 料 檔 案 一

27 多 部 伺 服 器 架 構 散 式 查 詢 分 割 檢 視 表 分 寫 複 擴 充 共 用 資 料 庫 可

28 分 散 式 查 詢 立 連 結 伺 服 器 建 料 庫 物 件 完 整 名 稱 規 則 資 Server SQL LinkedServer.DB.Schema.Object SQL Server ORACLE ORA..Schema.Object Access Microsoft MsAccess...Object 連 結 伺 服 器

29 分 割 檢 視 表 企 業 版 ServerHQ Server1 Server2 Server3 Member Table Member Table Member Table

30 分 割 檢 視 表 企 業 版 員 資 料 表 成 條 件 索 引 定 序 CHECK 算 型 欄 位 不 可 有 索 引 相 同 的 主 鍵 定 義 與 ANSI 計 選 項 設 定 PADDING 每 個 執 行 個 體 建 立 連 結 伺 服 器 為 sp_addlinkedserver 立 分 割 檢 視 表 建 搭 配 INSTEAD OF 觸 發 程 序 可

31 易 式 複 寫 交 上 交 易 + 報 表 線 資 料 異 動 資 料 查 詢 資 料 複 寫 用 程 式 應 服 器 伺 主 要 伺 服 器 ( 發 行 者 ) 只 複 寫 需 要 的 資 料 可 採 用 不 同 的 索 引 報 表 伺 服 器 ( 訂 閱 者 )

32 對 點 交 易 式 複 寫 點 散 查 詢 + 容 錯 分 Reader Log Agent Distribution Agent Reader Log Agent Distribution Agent Reader Log Agent Distribution Agent 企 業 版 台 北 紐 約 上 海

33 對 點 交 易 式 複 寫 點 Server 2008 增 強 功 能 SQL 的 複 寫 拓 樸 檢 視 器 新 入 新 的 結 點 無 須 先 將 既 有 的 複 寫 離 線 加 資 料 衝 突 偵 測 機 制

34 際 案 例 實 路 服 務 公 司 網 員 人 數 急 速 成 長 約 每 四 個 月 就 會 成 長 200% 會 萬 130 萬 240 萬 會 員 人 數 70 多 部 Web 伺 服 器 主 機 以 網 路 負 載 平 衡 設 備 分 散 負 荷 部 資 料 庫 伺 服 器 主 機 須 應 付 大 量 的 及 時 性 統 計 資 訊 1 得 不 斷 更 換 處 理 器 更 多 更 強 的 主 機 來 支 撐 (CPU > 90%) 只 Web 服 器 伺 料 庫 資 服 器 伺

35 際 案 例 實 易 式 複 寫 ( 向 外 擴 充 ) 交 資 料 異 動 資 料 查 詢 資 料 複 寫 增 加 3 部 x86 主 機 訂 閱 IA64 主 機 之 複 寫 發 行 集 Web 服 器 伺 IA64 主 機 從 > 90% CPU 至 < 40% 降 ( 發 行 者 ) x86 主 機 付 大 量 的 應 時 性 統 計 資 訊 及 ( 訂 閱 者 )

36 可 擴 充 共 用 資 料 庫 企 業 版 表 資 料 量 龐 大 報 料 倉 儲 系 統 資 部 主 機 有 各 自 獨 立 的 處 每 器 記 憶 體 和 Tempdb 理 用 的 唯 讀 檔 案 必 須 存 共 在 SAN 儲 存 設 備 且 放 下 儲 存 設 備 費 用 省 護 費 用 與 電 費 維 共 用 磁 碟 區 必 須 是 唯 讀

37 提 升 效 能 的 新 功 能 資 料 表 壓 縮 1. 備 份 壓 縮 2. 資 源 管 理 員 3. 一 次 可 新 增 多 筆 資 料 4. 常 數 資 料 表 5. 更 優 越 的 儲 存 體 結 構 可 擴 充 共 用 資 料 庫

38 資 料 表 壓 縮 企 業 版 常 較 適 於 線 上 交 易 通 - 5% CPU 2% 20% 壓 縮 比 Row Page 常 較 適 於 資 料 倉 儲 通 - 15% CPU 10% 60% - 70% 壓 縮 比 Compression white paper Data Warehousing white paper

39 備 份 壓 縮 企 業 版 未 壓 縮 3 備 份 壓 縮 備 份 大 小 備 份 時 間 還 原 時 間 Backup Compression

40 資 源 管 理 員 入 帳 號 登 料 庫 資 時 間 CPU 與 逾 時 授 企 業 版 SQL Server 依 條 件 區 分 工 作 負 荷 可 用 程 式 應 OLTP Activity Executive Reports Backup Admin Tasks Ad-hoc Reports 作 負 荷 限 制 條 件 工 憶 體 授 與 % 記 High Admin Workload OLTP Workload Report Workload 最 大 要 求 數 Memory 10% Min Memory 20% Max Max CPU 90% Max CPU 20% Admin Pool Application Pool

41 一 次 可 新 增 多 筆 資 料 快 更 容 易 更 前 以 INTO customers (custid,name) INSERT (1, 'cust 1'); VALUES INTO customers (custid,name) INSERT (2, 'cust 2'); VALUES INTO customers (custid,name) INSERT (3, 'cust 3'); VALUES INTO customers (custid,name) INSERT (4, 'cust 4'); VALUES INTO customers (custid,name) INSERT 2008 SQL INTO customers (custid, name) INSERT VALUES 'cust 1'), (1, 'cust 2'), (2, 'cust 3'), (3, 'cust 4'), (4, 'cust 5'); (5, VALUES (5, 'cust 5');

42 常 數 資 料 表 用 更 簡 單 TABLE DECLARE INT, name VARCHAR(20)) (custid (custid,name) INSERT (1, 'cust 1'); VALUES (custid,name) INSERT (2, 'cust 2'); VALUES (custid,name) INSERT (3, 'cust 3'); VALUES (custid,name) INSERT (4, 'cust 4'); VALUES (custid,name) INSERT (5, 'cust 5'); VALUES * FROM * SELECT ( FROM VALUES 'cust 1'), (1, 'cust 2'), (2, 'cust 3'), (3, 'cust 4'), (4, 'cust 5') (5, 以 前 SQL 2008 ) MyCustomers(custid, name);

43 更 優 越 的 儲 存 體 結 構 Sparse column // Table Products(Id int, Type nvarchar(16), Create Filtered Indices // Index ZoomIdx on Products(ZoomLength) where Create HierarchyID // TABLE [dbo].[folder] CREATE HIERARCHYID NOT NULL UNIQUE, [FolderNode] AS [FolderNode].GetLevel() PERSISTED, [Level] Resolution int SPARSE, ZoomLength int SPARSE); Type = Camera ; ( [Description] NVARCHAR(50) NOT NULL 5 ); 1 HierarchyID 儲 存 階 層 式 結 構 的 資 料 並 有 效 地 查 詢 它 們 2 使 用 者 自 訂 類 型 使 用 者 自 訂 類 型 (UDTs) 沒 有 8K 的 限 制 3 疏 鬆 資 料 行 提 供 對 疏 鬆 資 料 行 (Sparse Columns)) 的 最 佳 化 的 儲 存 體 4 寬 型 資 料 表 寬 型 資 料 表 (Wide Tables) 支 援 疏 鬆 資 料 行 資 料 行 總 數 可 達 30,000 個 5 篩 選 索 引 篩 選 索 引 (Filtered indexes) 可 為 資 料 表 內 的 資 料 子 集 定 義 索 引

44 HierarchyID 資 料 類 型 現 在 階 層 內 的 位 置 直 覺 地 展 現 呈 織 圖 組 Bill of Materials GL - accounts/subaccounts Content management Forums/mailing lists 壓 縮 / 有 效 率 地 儲 存 支 援 任 意 的 新 增 和 刪 除 大 的 查 詢 方 法 強 GetRoot GetLevel IsDescendantOf GetReparentedValue 等 等

45 鬆 資 料 行 (Sparse Columns) 疏 料 儲 存 空 間 更 少 資 值 Null 存 最 佳 化 儲 White paper

46 疏 鬆 資 料 行 (Sparse Columns) 點 優 Null 值 不 佔 用 儲 存 空 間 + 點 缺 儲 存 非 Null 值 需 要 更 多 的 儲 存 空 間 - 用 情 境 適 空 間 至 少 節 省 了 20% ~ 40% 當 型 資 料 表 (Wide Tables) 大 部 分 皆 為 Null 值 寬 援 30,000 個 資 料 行 支 節 省 儲 存 空 間 提 升 效 能 + 查 詢 /DML 無 需 修 改 + 搭 配 資 料 行 集 (Column Sets) 和 篩 選 索 引 一 起 可 用 使

47 鬆 資 料 行 疏 節 省 40% 空 間 為 例 ( 資 料 類 型 vs. Null 百 分 比 ) 以 bit tinyint smallint int bigint real float smallmoney money smalldatetime datetime uniqueidentifier date datetime2(0) datetime2(7) time(0) time(7) datetimetoffset(0) datetimetoffset (7) decimal/numeric(1,s) decimal/numeric(38,s) Null 百 分 比 100% 98% 86% 80% 76% 64% 69% 69% 64% 64% 64% 60%52%49% 57% 60% 52% 52% 52% 52% 52% 43% 60% 42% 40% 20% 0%

48 SQL Server 2008 索 引 新 功 能 壓 縮 索 引 篩 選 索 引

49 選 索 引 (Filtered indexes) 篩 索 引 效 率 再 提 升 使

50 選 索 引 ID Type Brand Zoom Resolution WaistSize Inseam Price.. 篩 1 Camera Canon 3x 5 M Pant Dockers Camera Nikon 5x 10M Camera Pentax 3x 3M Pant Polo 當 WHERE 條 件 符 合 時 Query Optimizer 會 6 Pant Dockers 建 立 篩 選 索 引 挑 選 合 適 的 篩 選 索 引 7.. Index ZoomIdx on Products(Zoom) where Type = 'Camera'; Create Index PlusSizeIdx on Products(WaistSize) where Type = 'Pant' Create WaistSize > 36 and ProductId, Type, Resolution, ZoomLength where Type = 'Camera' Select ProductId, Type, WaistLength, Inseam where Type = 'Pant' and Select > 38 WaistSize 有 在 需 要 時 DML 運 只 會 導 致 維 護 索 引 資 料 作 into Products(ProductId, Type, Brand, WaistSize, inseam) Insert (201, 'Pant', 'Polo', 30, 32); values into Products(ProductId, Type, Brand, WaistSize, inseam) Insert (202, 'Pant', 'Polo', 38, 36); values

51 資 料 表 值 參 數 將 多 個 資 料 列 傳 送 到 T-SQL 陳 述 式 或 常 式 ( 預 存 可 序 或 函 數 ) 而 不 需 建 立 暫 存 資 料 表 或 許 多 參 數 程 傳 址 方 式 將 資 料 表 值 參 數 傳 遞 給 常 式 以 需 額 外 再 產 生 資 料 副 本 無 Batching Table-Valued Parameters 類 似 於 OLE DB 和 ODBC 中 的 參 數 陣 列 但 是 更 加 彈 性

52 效 能 調 校 工 具 可 靠 性 與 效 能 監 視 器 1. SQL Trace 與 SQL Server Profiler 2. Database Engine Tuning Advisor 3. Management Studio 標 準 報 表 4. Performance Dashboard Reports 5. 資 料 收 集 器 查 詢 執 行 計 畫

53 可 靠 性 與 效 能 監 視 器 效 能 物 件 即 時 監 看 效 能 計 數 器 期 觀 察 長 料 收 集 器 集 合 工 具 資 例 項 排 程 表 圖 路 線 停 止 條 件 工 作 長 條 圖 列 資 料 收 集 器 報 告 效 能 計 數 器 資 料 收 集 器 區 域 事 件 追 蹤 資 料 收 集 器 堆 疊 區 域 圖 組 態 資 料 收 集 器 效 能 計 數 器 警 訊

54 重 要 的 效 能 物 件 與 計 數 器 (OS) 資 源 效 能 物 件 效 能 計 數 器 瓶 頸 條 件 建 議 的 效 能 調 整 方 法 記 憶 體 Memory Pages/Sec < 20 增 加 記 憶 體 大 小 記 憶 體 Memory Available MBytes < 200 MB 同 上 理 器 Processor % Processor Time < 75% 1. 升 級 處 理 器 速 度 或 處 加 處 理 器 個 數 增 改 善 耗 處 理 器 的 瓶 2. 頸 理 器 System Processor Queue 處 Length < 2 同 上 碟 PhysicalDisk Avg. Disk Read 硬 Length Queue 2 1. 換 更 快 速 的 磁 碟 機 < 資 料 庫 檔 案 的 檔 案 2. 組 重 新 規 劃 分 散 群 不 同 的 磁 碟 陣 列 於 碟 PhysicalDisk Avg. Disk Write 硬 Length Queue < 2 同 上

55 重 要 的 效 能 物 件 與 計 數 器 (SQL) 資 源 效 能 物 件 效 能 計 數 器 瓶 頸 條 件 建 議 的 效 能 調 整 方 法 記 憶 體 < 90 增 加 記 憶 體 大 小 Buffer Manager Cache Hit Buffer Ratio 增 加 記 憶 體 大 小 記 憶 體 Memory Manager Server Target Memory 實 體 記 憶 體 > 90% 增 加 記 憶 體 大 小 記 憶 體 Memory Manager Server Total Memory 實 體 記 憶 體 > 90% tempdb Databases Data File Size 是 否 持 續 增 加 詳 見 後 面 說 明 Access Manager Worktables Created/Sec tempdb 是 否 持 續 增 加 詳 見 後 面 說 明 易 記 錄 交 檔 Log File Size 10~25% Databases File Date 份 或 清 除 交 易 記 錄 備 然 後 壓 縮 檔 案 檔 Size

56 SQL Trace 與 SQL Profiler Profiler 是 圖 形 化 工 具 以 監 控 SQL Server 的 SQL 種 事 件 各 控 SQL Server 的 各 種 活 動 產 生 各 種 紀 錄 檔 供 事 後 監 析 分 Database Engine Tuning Advisor 搭 配 使 用 與 Performance Monitor 整 合 與 斷 問 題 診 應 用 程 式 除 錯 為 Trace Server-side SQL Profiler Client-side SQL 重 演 一 次 以 模 擬 或 重 新 產 生 問 題

57 Profiler 整 合 效 能 計 數 器 記 錄 立 Profiler 追 蹤 與 效 建 監 視 記 錄 能 啟 追 蹤 ( 完 成 載 入 ) 檔 開 匯 入 效 能 資 料 選 案 擇 物 件 / 計 數 器 於 時 間 相 互 關 聯 基 與 EndTime 欄 位 StartTime 標 有 相 互 關 聯 性 可 指 兩 個 數 據 集 合 中 任 意 在 挑 選

58 用 Profiler 找 出 最 常 執 行 的 查 詢

59 實 際 案 例 經 驗 分 享 以 Profiler 擷 取 應 用 程 式 指 令 找 出 Top20 查 詢 1. 時 以 系 統 管 理 工 具 效 能 記 錄 效 能 計 數 器 同

60 實 際 案 例 經 驗 分 享 分 析 Top20 查 詢 的 執 行 計 畫 依 需 求 調 整 索 引 2. 再 次 以 Profiler 擷 取 應 用 程 式 指 令 同 時 以 系 統 3. 管 理 工 具 效 能 記 錄 效 能 計 數 比 較 效 能 改 善 程 度

61 實 際 案 例 經 驗 分 享

62 Database Engine Tuning Advisor 分 析 的 來 源 分 析 的 結 果.trc file Table.sql script DTA Databases

63 何 使 用 Database Engine 如 Advisor Tuning.trc file.sql script Table 建 立 一 個 新 的 連 線 指 定 需 要 分 析 的 來 源 設 定 效 能 調 整 選 項 執 行 分 析 DTA 檢 視 分 析 結 果 實 行 分 析 結 果 之 建 議 Databases

64 Management Studio 報 表 伺 服 器

65 Management Studio 報 表 資 料 庫

66 Management Studio 活 動 監 視 器

67 Performance Dashboard Reports 組 遵 循 Reporting Services 定 義 的 客 製 化 一 表 報 合 SQL Server Management Studio 整 Viewer 控 制 項 Report 針 對 SQL Server 2005 SP2 之 後 的 效 能 監 控 由 存 取 SQL Server 2005 所 提 供 的 系 統 物 藉 呈 現 SQL Server 2005 執 行 個 體 當 下 運 件 作 的 情 形 藉 以 分 析 效 能 問 題

68 Performance Dashboard Reports 改 預 設 安 裝 路 徑 更 Files\Microsoft SQL Server\100\Tools\PerformanceDashboard C:\Program 7e0c e419218c1efc&displaylang=en Server 2008 需 稍 加 修 改 才 可 使 用 SQL 載 PerfDashboardReports.zip 下 Server 2005 免 費 下 載 SQL Files (x86)\microsoft SQL Server\100\Tools\ C:\Program PerformanceDashboard 下 列 二 個 檔 案 取 代 將 和 Setup.sql Performance_dashboard_main.rdl 執 行 Setup.sql 前 建 議 修 改 datediff(ms, ) 變 更 為 datediff(s, )

69 Dashboard Performance 之 應 用 Reports 體 資 源 的 情 況 硬 視 指 令 所 使 用 CPU 和 磁 碟 I/O 檢 鎖 封 下 被 迫 等 待 的 指 令 當 引 索 議 須 新 增 的 建 引 以 提 升 索 能 效

70 際 案 例 經 驗 分 享 實 以 Visual Studio 2008 壓 力 測 試 模 擬 1,400 個 使 用 者 同 時 上 線 存 取 1. 用 Performance Dashboard Report 找 出 Missing Index 2. 其 中 3 組 Missing Index 的 Impact > 90 依 建 議 建 立 相 關 索 引 3. 調 校 前 50%~80% 調 校 後 5%~10%

71 資 料 收 集 器 資 料 來 源 SQL Trace 效 能 計 數 器 Transact-SQL 集 中 的 資 料 儲 存 效 能 資 料 倉 儲 效 能 報 表 低 負 荷 資 料 收 集

72 設 定 管 理 資 料 倉 儲 精 靈

73 設 定 管 理 資 料 倉 儲 精 靈

74 檢 視 管 理 資 料 倉 儲 報 表

75 資 料 收 集 器 自 動 化 作 業 統 資 料 收 集 組 系 伺 服 器 活 動 1. 查 詢 統 計 資 料 2. 磁 碟 使 用 量 3. 料 收 集 資 入 清 除 載

76 查 詢 執 行 計 畫 與 Query Optimizer Server 資 料 引 擎 內 建 強 大 之 查 詢 分 析 功 能 SQL Optimizer 產 生 查 詢 執 行 計 畫 的 三 階 段 Query 詢 分 析 階 段 查 條 件 與 JOIN 條 件 WHERE 引 選 擇 階 段 索 估 是 否 有 索 引 存 在 評 若 有 索 引 評 估 是 否 可 以 提 升 效 能 選 擇 階 段 JOIN 估 採 用 下 列 何 種 運 算 方 式 執 行 JOIN 評 LOOP HASH MERGE REMOTE

77 圖 形 化 查 詢 執 行 計 畫 昇 效 能 提 少 Table Scan 減 少 Clustered Index Scan 減 少 Sort 減 碟 I/O 效 能 磁 Nonclustered Index Seek 1. Clustered Index Seek 2. Nonclustered Index Scan 3.

78 查 詢 與 索 引 之 範 例 設 條 件 假 tablea 有 20 個 資 料 欄 每 個 資 料 欄 10 Bytes 1. 下 列 查 詢 最 佳 之 索 引 是 column1, column3 SELECT tablea FROM WHERE column3 =? tablea 共 有 1,000,000 筆 資 料 2. 符 合 column3 =? 共 有 1,000 筆 資 料 3.

79 觀 察 磁 碟 I/O 之 差 異 Scan 的 磁 碟 I/O Table Index Scan 的 磁 碟 I/O Nonclustered Seek 的 磁 碟 I/O Clustered Index Seek 的 磁 碟 I/O Nonclustered PS. 下 說 明 的 計 算 範 例 皆 為 概 算 值 與 實 際 上 SQL Server 以 所 花 費 之 磁 碟 I/O 會 有 些 許 的 差 異

80 Table Scan 的 磁 碟 I/O 200 MB (8K 25,000) Table Scan = 沒 有 任 何 的 索 引 tablea Data Row = 200 Bytes (10 Bytes 20 Columns) 1 Data Page = 40 Data Rows (8K 200 Bytes) 1 Space for Data Pages Disk 25,000 Data Pages (1,000,000 rows 40 rows/pages ) = Index Scan Clustered 集 索 引 是 column3 叢 碟 I/O 太 大 極 磁 造 成 查 詢 逾 時 易 以 外 的 欄 位 Root Non-Leaf Leaf

81 Nonclustered Index Scan 的 磁 碟 I/O Index Row = 20 Bytes (10 Bytes 2 Columns) 1 Index Page = 400 Index Rows (8K 20 Bytes) 1 Space for Index Pages Disk 2,500 Index Pages (1,000,000 rows 400 rows/pages ) = = 20 MB (8K 2,500) Scan Index 叢 集 索 引 是 非 Root column1, column3 Non-Leaf Leaf 然 磁 碟 I/O 只 有 table scan 雖 10 % 但 仍 有 可 能 逾 時 的

82 Clustered Index Seek 的 磁 碟 I/O Data Row = 200 Bytes (10 Bytes 20 Columns) 1 Data Page = 40 Data Rows (8K 200 Bytes) 1 Space for Data Pages Disk 25 Data Pages (1,000 rows 40 rows/pages ) = = 200 KB (8K 25) 碟 I/O 只 有 磁 scan 的 0.1 % table Index Seek Clustered 集 索 引 是 叢 Index scan 的 1 % column3 的 欄 位 Root Non-Leaf Leaf

83 Nonclustered Index Seek 的 磁 碟 I/O Index Row = 20 Bytes (10 Bytes 2 Columns) 1 Index Page = 400 Index Rows (8K 20 Bytes) 1 Seek Index 叢 集 索 引 是 非 Disk Space for Index Pages 3 Index Pages (1,000 rows 400 rows/pages ) column3, column1= = 24 KB (8K 3) Seek Index 叢 集 索 引 是 column3 非 碟 I/O 只 有 磁 Index Clustered + 叢 集 索 引 是 column1 seek 的 12 % 覆 蓋 索 引 類 似 更 多 資 訊 的 名 詞 解 釋 架 構 除 了 有 名 詞 的 頁 次 還 包 括 章 節 類 型 等 資 訊 Root Non-Leaf Leaf

84 際 案 例 實 境 說 明 情 日 同 時 上 線 使 用 者 平 1,000 人 < 處 理 器 雙 核 x 2 記 憶 體 8GB MS SQL IIS Web Server 資 料 庫 450MB 日 爆 量 同 時 上 線 使 用 者 某 20,000 人 >

85 際 案 例 實 校 前 之 效 能 瓶 頸 調 主 機 與 SQL 主 機 安 裝 於 同 一 部 主 機 IIS Server 過 於 忙 碌 時 處 理 器 資 源 不 足 使 用 率 超 過 SQL 95% 導 致 網 站 回 應 逾 時 要 效 能 瓶 頸 是 處 理 器 主 料 庫 大 小 約 450MB 處 理 器 執 行 查 詢 時 可 利 用 伺 服 資 充 足 的 記 憶 體 資 源 處 理 因 此 硬 碟 存 取 量 較 小 磁 碟 器 有 瓶 頸 發 生 沒 因 為 沒 有 適 當 的 索 引 即 使 資 料 都 在 記 憶 體 仍 需 要 但 量 耗 費 處 理 器 資 源 導 致 處 理 器 發 生 效 能 瓶 頸 大

86 際 案 例 實 驗 分 享 經 利 用 SQL Server Profiler 找 出 執 行 時 間 過 長 的 指 令 1. 分 析 指 令 的 執 行 計 畫 建 立 適 當 的 索 引 效 能 提 升 約 3332 倍 (99.97 / 0.03) 調 校 前 99.97% 調 校 後 0.03% 資 料 表 BOOKLET 於 增 一 個 非 叢 集 索 引 新

87 際 案 例 實 能 調 校 前 後 比 較 效

88 結 論 效 能 調 校 與 規 劃 SQL Server 2008 提 升 效 能 的 新 功 能 SQL Server 2008 效 能 調 校 工 具

89

90 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should The be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, not OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION. IMPLIED