SQLServer性能優(yōu)化

1、SQL Server性能優(yōu)化性能優(yōu)化 華測(cè)檢測(cè)華測(cè)檢測(cè) SQL Server SQL Server 基礎(chǔ)知基礎(chǔ)知識(shí)回顧識(shí)回顧 SQL Server SQL Server 性能優(yōu)化方法和工具性能優(yōu)化方法和工具01010202課時(shí)安排課時(shí)安排 華測(cè)檢測(cè)華測(cè)檢測(cè)你在工作中是否也常常遇到這樣的問題：你在工作中是否也常常遇到這樣的問題：客戶給你抱怨系統(tǒng)很卡很慢。當(dāng)你進(jìn)一步客戶給你抱怨系統(tǒng)很卡很慢。當(dāng)你進(jìn)一步詢問慢到什么程度，有什么表征時(shí)，得到詢問慢到什么程度，有什么表征時(shí)，得到的答案往往又很模糊的答案往往又很模糊.你會(huì)從哪些方面去排查問題？你會(huì)從哪些方面去排查問題？ 華測(cè)檢測(cè)華測(cè)檢測(cè)應(yīng)用程序中間層出現(xiàn)性

2、能瓶頸；處理事務(wù)、工作流的邏輯不合理；會(huì)話機(jī)制和緩存機(jī)制配置不合理邏輯算法不合理；存儲(chǔ)過程、函數(shù)效率低下維護(hù)性差；游標(biāo)、觸發(fā)器使用不合理應(yīng)用程序運(yùn)行在與工作負(fù)荷不匹配的硬件上；硬件資源分配利用不合理CPU相關(guān)配置沒有配置好；內(nèi)存相關(guān)配置沒有設(shè)置好 華測(cè)檢測(cè)華測(cè)檢測(cè)0 1SQL Server SQL Server 基礎(chǔ)知識(shí)回基礎(chǔ)知識(shí)回顧顧 華測(cè)檢測(cè)華測(cè)檢測(cè)前言前言 性能優(yōu)化順序性能優(yōu)化順序性能最優(yōu)性能最優(yōu)內(nèi)存、處理器親和度內(nèi)存、處理器親和度文件組、分區(qū)文件組、分區(qū)覆蓋查詢、鎖、事務(wù)覆蓋查詢、鎖、事務(wù)表、過程存儲(chǔ)、視圖表、過程存儲(chǔ)、視圖服務(wù)器優(yōu)化服務(wù)器優(yōu)化存儲(chǔ)優(yōu)化存儲(chǔ)優(yōu)化索引優(yōu)化、并發(fā)控制索引優(yōu)化

3、、并發(fā)控制架構(gòu)設(shè)計(jì)、查詢優(yōu)化架構(gòu)設(shè)計(jì)、查詢優(yōu)化 華測(cè)檢測(cè)華測(cè)檢測(cè)01 表與索引的物理存儲(chǔ)結(jié)構(gòu)表與索引的物理存儲(chǔ)結(jié)構(gòu) 了解表和索引的存儲(chǔ)結(jié)構(gòu)對(duì)于SQL優(yōu)化，是基礎(chǔ)，不可跨越的。不了解存儲(chǔ)結(jié)構(gòu)，知道再多的查詢優(yōu)化策略也無用，因?yàn)槭冀K會(huì)知其然不知其所以然！ 華測(cè)檢測(cè)華測(cè)檢測(cè)01 表的物理存儲(chǔ)結(jié)構(gòu)表的物理存儲(chǔ)結(jié)構(gòu) 在在SQL SERVERSQL SERVER中中，存儲(chǔ)數(shù)據(jù)的基本，存儲(chǔ)數(shù)據(jù)的基本單位是頁單位是頁(Page)(Page)每個(gè)頁上存放幾十條或幾百條記錄。多個(gè)頁組成一個(gè)表。每個(gè)頁大小固定是8K(SQL SERVER)，即8192個(gè)字節(jié).數(shù)據(jù)是一行行存在頁上，而不是直接存在表中的。頁是存儲(chǔ)數(shù)據(jù)的

4、基本單位。. 華測(cè)檢測(cè)華測(cè)檢測(cè)01 表的物理存儲(chǔ)結(jié)構(gòu)表的物理存儲(chǔ)結(jié)構(gòu)-頁構(gòu)造頁構(gòu)造 頁首頁首 數(shù)據(jù)區(qū)數(shù)據(jù)區(qū)每頁的開始部分是 96 字節(jié)的頁首，用作SQL中的管理字節(jié)，管理字節(jié)中記錄著本頁的編號(hào)、上一頁和下一頁的編號(hào)等（還有其他信息）。頁首下面緊跟著放記錄，每條數(shù)據(jù)一行，數(shù)據(jù)區(qū)存放多行數(shù)據(jù)。 華測(cè)檢測(cè)華測(cè)檢測(cè)01 表的物理存儲(chǔ)結(jié)構(gòu)表的物理存儲(chǔ)結(jié)構(gòu)-頁構(gòu)造示圖頁構(gòu)造示圖 96 96字節(jié)頁字節(jié)頁首首 數(shù)據(jù)數(shù)據(jù)區(qū)區(qū) 華測(cè)檢測(cè)華測(cè)檢測(cè)01 表的物理存儲(chǔ)結(jié)構(gòu)表的物理存儲(chǔ)結(jié)構(gòu)-頁鏈頁鏈 SQL 通過頁上的編號(hào)把所有的頁串成一個(gè)鏈。 這個(gè)鏈組成了一張表。 SQL通過頁鏈依次找到表的所有頁和所有記錄。 華測(cè)檢測(cè)

5、華測(cè)檢測(cè)01 表的物理存儲(chǔ)結(jié)構(gòu)表的物理存儲(chǔ)結(jié)構(gòu)-頁鏈?zhǔn)疽鈭D頁鏈?zhǔn)疽鈭D上一頁上一頁下一頁下一頁當(dāng)前頁當(dāng)前頁上一頁號(hào)下一頁號(hào)當(dāng)前頁頁號(hào)數(shù)據(jù)區(qū)數(shù)據(jù)區(qū)通過通過”上一頁上一頁”和和”下一頁下一頁”串起來串起來 華測(cè)檢測(cè)華測(cè)檢測(cè)01 表的物理存儲(chǔ)結(jié)構(gòu)表的物理存儲(chǔ)結(jié)構(gòu)-表示意圖表示意圖頁鏈頁鏈頁頁頁頁頁頁頁頁記錄記錄組成了一張表組成了一張表 華測(cè)檢測(cè)華測(cè)檢測(cè)01 表的物理存儲(chǔ)結(jié)構(gòu)表的物理存儲(chǔ)結(jié)構(gòu)-表存儲(chǔ)結(jié)構(gòu)表存儲(chǔ)結(jié)構(gòu)每個(gè)頁上最大存儲(chǔ)819296=8096個(gè)字節(jié)的數(shù)據(jù)。如：表的每條記錄的長(zhǎng)度是1000字節(jié)，則每個(gè)頁可存放8條記錄。每條記錄的長(zhǎng)度不可大于8096個(gè)字節(jié)，超過存儲(chǔ)限制，則無法存儲(chǔ)數(shù)據(jù)。記錄不可跨頁

6、。就是說，假設(shè)一條記錄長(zhǎng)度為5000個(gè)字節(jié)的話，那么一頁上只能存放一條記錄，而頁上的剩余的3096個(gè)字節(jié)就浪費(fèi)了，且沒辦法回收利用。 華測(cè)檢測(cè)華測(cè)檢測(cè)01索引的物理存儲(chǔ)結(jié)構(gòu)索引的物理存儲(chǔ)結(jié)構(gòu)索引的存儲(chǔ)結(jié)構(gòu)和表差不多。也是存儲(chǔ)在頁上的。只是表中存儲(chǔ)的字段多，而索引中存的字段非常少而已。所以索引一般比表小很多。索引是一種結(jié)構(gòu)，索引和表（這里指的是加了聚集索引的表）的存儲(chǔ)結(jié)構(gòu)是一樣的，都是B樹，B樹是一種用于查找平衡多叉樹。 華測(cè)檢測(cè)華測(cè)檢測(cè)01索引的物理存儲(chǔ)結(jié)構(gòu)索引的物理存儲(chǔ)結(jié)構(gòu)-B樹結(jié)構(gòu)圖樹結(jié)構(gòu)圖 華測(cè)檢測(cè)華測(cè)檢測(cè)01索引的物理存儲(chǔ)結(jié)構(gòu)索引的物理存儲(chǔ)結(jié)構(gòu)-索引層的計(jì)算方法索引層的計(jì)算方法 假設(shè)每

7、頁放100條索引，一個(gè)1000萬筆記錄的大表，那么葉級(jí)索引約有10萬頁，二級(jí)索引有1000頁，三級(jí)索引10頁，根索引1頁，每一級(jí)都是上一級(jí)的1/100。這個(gè)表的索引共有4層。 華測(cè)檢測(cè)華測(cè)檢測(cè)01索引的物理存儲(chǔ)結(jié)構(gòu)索引的物理存儲(chǔ)結(jié)構(gòu)-根據(jù)索引找記錄根據(jù)索引找記錄用戶要查工號(hào)是655的員工的信息工號(hào) 指針501 第5頁601 第6頁701 第7頁.根索引1. 1. 掃描根頁，工號(hào)掃描根頁，工號(hào)655655介于第介于第6 6頁頁和第和第7 7頁之間，第頁之間，第7 7頁的第頁的第1 1行大于行大于655655，那么去在中間級(jí)搜索第，那么去在中間級(jí)搜索第6 6頁頁。工號(hào) 指針641 第41頁651

8、第42頁661 第43頁中間索引2. 2. 掃描中間級(jí)頁，工號(hào)掃描中間級(jí)頁，工號(hào)655655介于介于第第4242頁和第頁和第4343頁之間，接著去下級(jí)頁之間，接著去下級(jí)搜索第搜索第4242頁。頁。工號(hào) 指針654 655 656 葉級(jí)索引3. 掃描第42頁，查找到工號(hào)655，根據(jù)指針定位到所在的數(shù)據(jù)頁，取得數(shù)據(jù)。 華測(cè)檢測(cè)華測(cè)檢測(cè)01索引的物理存儲(chǔ)結(jié)構(gòu)索引的物理存儲(chǔ)結(jié)構(gòu)-根據(jù)索引找記錄根據(jù)索引找記錄由上我們知道：SQL根據(jù)索引查找記錄時(shí)，只需要對(duì)索引中的每一級(jí)掃描一頁，最后再掃描一個(gè)數(shù)據(jù)頁就可以了。索引字段一般都很短，每個(gè)頁上可能會(huì)存放幾百條索引記錄。假設(shè)1000萬筆記錄、一個(gè)2G的大表，每頁

9、放100條索引，根據(jù)索引查找記錄，只需掃描5個(gè)頁，4個(gè)索引頁+1個(gè)數(shù)據(jù)頁，5個(gè)頁是多少？硬盤要做多少動(dòng)作？5*8K=40K而已，不論記錄是在表的任何一個(gè)地方：前面、后面或中間。如果對(duì)一個(gè)只有200條記錄的表建索引，是沒有什么效率的，因?yàn)楸淼臄?shù)據(jù)一共可能只有一兩頁。使用了索引不會(huì)有任何速度提升。 華測(cè)檢測(cè)華測(cè)檢測(cè)01索引的物理存儲(chǔ)結(jié)構(gòu)索引的物理存儲(chǔ)結(jié)構(gòu)-索引分類索引分類 數(shù)據(jù)表本身就是索引的一部分，數(shù)據(jù)表本身就是聚集索引的子葉層，整個(gè)數(shù)據(jù)表的擺放順序是按照你選定的鍵值由小到大排序聚集索引聚集索引非聚集索引非聚集索引 完全獨(dú)立于數(shù)據(jù)表之外的結(jié)構(gòu),葉級(jí)頁指向表中的記錄,記錄的物理順序與邏輯順序沒有必

10、然的聯(lián)系。 華測(cè)檢測(cè)華測(cè)檢測(cè)01索引的物理存儲(chǔ)結(jié)構(gòu)索引的物理存儲(chǔ)結(jié)構(gòu)-索引使用規(guī)則索引使用規(guī)則每個(gè)表只能有一個(gè)聚集索引每個(gè)表最多可以有 249個(gè)非聚集索引在非聚集索引創(chuàng)建之前創(chuàng)建聚集索引若未指定索引類型，則默認(rèn)為非聚集索引 華測(cè)檢測(cè)華測(cè)檢測(cè)01索引的物理存儲(chǔ)結(jié)構(gòu)索引的物理存儲(chǔ)結(jié)構(gòu)-非聚集索引使用場(chǎng)合非聚集索引使用場(chǎng)合頻繁的從一個(gè)大表中選擇很少量的數(shù)據(jù)的情況。頻繁的從一個(gè)大表中選擇很少量的數(shù)據(jù)的情況。重復(fù)值非常少的時(shí)候，如物料編號(hào)、姓名。如果有重復(fù)值非常少的時(shí)候，如物料編號(hào)、姓名。如果有很多的重復(fù)值，如只有很多的重復(fù)值，如只有 男男 和和 女，大多數(shù)查詢將女，大多數(shù)查詢將不使用索引，因?yàn)榇藭r(shí)表掃描通常更有效！不使用索引，因?yàn)榇藭r(shí)表掃描通常更有效！經(jīng)常用于和其他表做關(guān)聯(lián)的字段。經(jīng)常用于和其他表做關(guān)聯(lián)的字段。 華測(cè)檢測(cè)華測(cè)檢測(cè)經(jīng)常返回一個(gè)范圍的查詢：經(jīng)常返回一個(gè)范圍的查詢：BETWEENBETWEEN、 、 。經(jīng)常被連續(xù)訪問的列、返回大型結(jié)果集的查詢。經(jīng)常被連續(xù)訪問的列、返回大型結(jié)果集的查詢。經(jīng)常被使用聯(lián)接或經(jīng)常被使用聯(lián)接或 GROUP B