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è)你在工作中是否也常常遇到這樣的問(wèn)題：你在工作中是否也常常遇到這樣的問(wèn)題：客戶給你抱怨系統(tǒng)很卡很慢。當(dāng)你進(jìn)一步客戶給你抱怨系統(tǒng)很卡很慢。當(dāng)你進(jìn)一步詢(xún)問(wèn)慢到什么程度，有什么表征時(shí)，得到詢(xún)問(wèn)慢到什么程度，有什么表征時(shí)，得到的答案往往又很模糊的答案往往又很模糊.你會(huì)從哪些方面去排查問(wèn)題？你會(huì)從哪些方面去排查問(wèn)題？ 華測(cè)檢測(cè)華測(cè)檢測(cè)應(yīng)用程序中間層出現(xiàn)性

2、能瓶頸；處理事務(wù)、工作流的邏輯不合理；會(huì)話機(jī)制和緩存機(jī)制配置不合理邏輯算法不合理；存儲(chǔ)過(guò)程、函數(shù)效率低下維護(hù)性差；游標(biāo)、觸發(fā)器使用不合理應(yīng)用程序運(yùn)行在與工作負(fù)荷不匹配的硬件上；硬件資源分配利用不合理CPU相關(guān)配置沒(méi)有配置好；內(nèi)存相關(guān)配置沒(méi)有設(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ū)覆蓋查詢(xún)、鎖、事務(wù)覆蓋查詢(xún)、鎖、事務(wù)表、過(guò)程存儲(chǔ)、視圖表、過(guò)程存儲(chǔ)、視圖服務(wù)器優(yōu)化服務(wù)器優(yōu)化存儲(chǔ)優(yōu)化存儲(chǔ)優(yōu)化索引優(yōu)化、并發(fā)控制索引優(yōu)化

3、、并發(fā)控制架構(gòu)設(shè)計(jì)、查詢(xún)優(yōu)化架構(gòu)設(shè)計(jì)、查詢(xún)優(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)，知道再多的查詢(xún)優(yōu)化策略也無(wú)用，因?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ù)的基本單位是頁(yè)單位是頁(yè)(Page)(Page)每個(gè)頁(yè)上存放幾十條或幾百條記錄。多個(gè)頁(yè)組成一個(gè)表。每個(gè)頁(yè)大小固定是8K(SQL SERVER)，即8192個(gè)字節(jié).數(shù)據(jù)是一行行存在頁(yè)上，而不是直接存在表中的。頁(yè)是存儲(chǔ)數(shù)據(jù)的

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

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

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

7、頁(yè)放100條索引，一個(gè)1000萬(wàn)筆記錄的大表，那么葉級(jí)索引約有10萬(wàn)頁(yè)，二級(jí)索引有1000頁(yè)，三級(jí)索引10頁(yè)，根索引1頁(yè)，每一級(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頁(yè)601 第6頁(yè)701 第7頁(yè).根索引1. 1. 掃描根頁(yè)，工號(hào)掃描根頁(yè)，工號(hào)655655介于第介于第6 6頁(yè)頁(yè)和第和第7 7頁(yè)之間，第頁(yè)之間，第7 7頁(yè)的第頁(yè)的第1 1行大于行大于655655，那么去在中間級(jí)搜索第，那么去在中間級(jí)搜索第6 6頁(yè)頁(yè)。工號(hào) 指針641 第41頁(yè)651

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

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

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)建聚集索引若未指定索引類(lèi)型，則默認(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ù)查詢(xún)將女，大多數(shù)查詢(xún)將不使用索引，因?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è)范圍的查詢(xún)：經(jīng)常返回一個(gè)范圍的查詢(xún)：BETWEENBETWEEN、 、 。經(jīng)常被連續(xù)訪問(wèn)的列、返回大型結(jié)果集的查詢(xún)。經(jīng)常被連續(xù)訪問(wèn)的列、返回大型結(jié)果集的查詢(xún)。經(jīng)常被使用聯(lián)接或經(jīng)常被使用聯(lián)接或 GROUP B