SQLServer2005性能調(diào)優(yōu)

1、SQL Server 2005 性能調(diào)優(yōu)等待系統(tǒng)資源等待系統(tǒng)資源內(nèi)存，內(nèi)存，CPU，IO錯(cuò)誤的配置錯(cuò)誤的配置硬件硬件 & 軟件軟件不優(yōu)化的查詢不優(yōu)化的查詢 & 設(shè)計(jì)設(shè)計(jì)寫(xiě)法不好寫(xiě)法不好, 設(shè)計(jì)不周設(shè)計(jì)不周索引問(wèn)題索引問(wèn)題和工作量與資源配置沒(méi)有關(guān)系和工作量與資源配置沒(méi)有關(guān)系不好的執(zhí)行計(jì)劃不好的執(zhí)行計(jì)劃和客戶端網(wǎng)絡(luò)交互太多和客戶端網(wǎng)絡(luò)交互太多 SQL 2005對(duì)系統(tǒng)資源的使用和監(jiān)控方法 語(yǔ)句執(zhí)行計(jì)劃優(yōu)化 執(zhí)行計(jì)劃預(yù)測(cè)與選擇 執(zhí)行計(jì)劃重用與重編譯 有用的性能監(jiān)視指標(biāo) SQL Trace SQL Profiler對(duì)性能的影響可能比較大 Server Side Trace Performance Mon

2、itor DMV & DMF (動(dòng)態(tài)管理視圖和函數(shù)) 數(shù)據(jù)庫(kù)定義以及數(shù)據(jù)統(tǒng)計(jì)信息 在SQL 2000里面的PSSDiag 缺省在SQL 2005里面就有安裝 可以自動(dòng)搜集 SQL Trace Performance Log SQLDiag report Windows Event Logs 附帶兩個(gè)模板 SD_Detailed.XML SD_General.XML 使用SQLDiag監(jiān)視SQL 2005的運(yùn)行數(shù)據(jù)庫(kù)級(jí)數(shù)據(jù)庫(kù)級(jí) 共享的資源共享的資源數(shù)據(jù)庫(kù)性能受到日志文件最大寫(xiě)入能力的限制，數(shù)據(jù)庫(kù)性能受到日志文件最大寫(xiě)入能力的限制，日志的寫(xiě)入必須是串行的！日志的寫(xiě)入必須是串行的！可以通過(guò)以下改進(jìn)性

3、能可以通過(guò)以下改進(jìn)性能 增加更多的物理硬盤(pán)增加更多的物理硬盤(pán)增加數(shù)據(jù)庫(kù)的數(shù)目，以增加日志的數(shù)目增加數(shù)據(jù)庫(kù)的數(shù)目，以增加日志的數(shù)目服務(wù)器級(jí)服務(wù)器級(jí) 共享的資源共享的資源TEMPDBMemory (64-bit)Memory (32-bit)32-bit AWE擴(kuò)展的內(nèi)存只能擴(kuò)展的內(nèi)存只能cache數(shù)據(jù)頁(yè)面數(shù)據(jù)頁(yè)面Proc cache, locks, user connections, sorting 還是只能使用還是只能使用 2-3GB 的的地址空間地址空間可以通過(guò)在一臺(tái)機(jī)器上安裝多個(gè)可以通過(guò)在一臺(tái)機(jī)器上安裝多個(gè)instance解決解決機(jī)器級(jí)機(jī)器級(jí) 共享的資源共享的資源CPU 和網(wǎng)絡(luò)和網(wǎng)絡(luò)數(shù)據(jù)庫(kù)

4、的數(shù)據(jù)庫(kù)的scalability 受到日志文件最大寫(xiě)入能力受到日志文件最大寫(xiě)入能力的限制的限制Disk I/O實(shí)例（實(shí)例（Instance）的）的 scalability 受到進(jìn)程最大受到進(jìn)程最大資源數(shù)目的限制資源數(shù)目的限制Memory服務(wù)器的服務(wù)器的 scalability受到機(jī)器能力的限制受到機(jī)器能力的限制CPU 網(wǎng)絡(luò)帶寬網(wǎng)絡(luò)帶寬確定問(wèn)題的特征確定問(wèn)題的特征寫(xiě)瓶頸寫(xiě)瓶頸日志文件日志文件 (100% 串行寫(xiě)串行寫(xiě))Lazy Writer (隨機(jī)隨機(jī))讀瓶頸讀瓶頸隨機(jī)隨機(jī) vs. 串行串行測(cè)試某個(gè)硬件配置的測(cè)試某個(gè)硬件配置的IO能力（不使用能力（不使用SQLServer) :SQLIOSim

5、特殊考慮特殊考慮:日志文件日志文件一顆一顆CPU一個(gè)一個(gè)Tempdb 數(shù)據(jù)文件數(shù)據(jù)文件 I/O 瓶頸通常比較容易發(fā)現(xiàn)瓶頸通常比較容易發(fā)現(xiàn) 對(duì)日志文件一定要小心對(duì)日志文件一定要小心 使用獨(dú)立的設(shè)備使用獨(dú)立的設(shè)備 使用使用RAID 10 RAID5寫(xiě)性能問(wèn)題寫(xiě)性能問(wèn)題: Each RAID5 write = 2 READS + 2 WRITES ! 最近的測(cè)試結(jié)果是最近的測(cè)試結(jié)果是RAID5的寫(xiě)性能比的寫(xiě)性能比RAID 0+1要差要差50% 阻塞是由于并發(fā)的連接爭(zhēng)搶共同的資源，阻塞是由于并發(fā)的連接爭(zhēng)搶共同的資源，但是沒(méi)有形成死鎖但是沒(méi)有形成死鎖 檢測(cè)工具檢測(cè)工具 SQL 2005 Profiler

6、就可以檢測(cè)出就可以檢測(cè)出 DMVs Sp_who2 & sp_lock Snapshot Isolation - Row Versioning讀不阻塞寫(xiě)讀不阻塞寫(xiě) DMF sys.dm_db_index_operational_stats() 可以看出資源爭(zhēng)搶的對(duì)象 Row locks counts （行鎖申請(qǐng)數(shù)目） Row lock waits counts （行鎖等待次數(shù)） Total wait time for blocks （總共被阻塞的時(shí)間） 可以算出發(fā)生阻塞的百分比和平均等待時(shí)間row_lock_wait_count / row_lock_countrow_lock_wait_in

7、_ms / row_lock_wait_count Sp_lock & sp_who2 lists real time blocks Trace for historical analysis Capture long blocks using the Trace Event “Block Process Report” Sp_configure “blocked process threshold”,15 (seconds) 確認(rèn)SQL Server內(nèi)部的阻塞問(wèn)題Tempdb 在 SS2005里使用量更大Tempdb 的配置更加重要使用方式1DBCC CHECKDB - small chan

8、ge2Internal objects: work file (hash join, SORT_IN_TEMPDB) 3Internal objects: work table (cursor, spool) - small changes4Large object (LOB) variables 5Service Broker 6Temporary objects: global/local temp table, table variables 7Temporary objects: SPs and cursors - small changes8Version store: Genera

9、l 9Version store: MARS 10Version store: Online index 11Version store: Row version based isolation levels 12Version store: Triggers 13XMLOn line 索引重建: 2x-3x size of index Sort size, temp index and rollbackVersioning: Size of Version Store = 2 *Version store data generated per minute *Longest running

10、time (minutes) of your transaction * number of concurrent transactions/users可以通過(guò)Performance Monitor里面的counter進(jìn)行監(jiān)視事先就設(shè)置好一個(gè)合適的大小，tempdb自動(dòng)增長(zhǎng)會(huì)嚴(yán)重影響性能 能夠減少分配頁(yè)的爭(zhēng)搶 如果存儲(chǔ)過(guò)程大量地使用Create Table and Create Index，就要考慮使用.select sum(user_object_reserved_page_count)*8 as user_objects_kb,sum(internal_object_reserved_pa

11、ge_count)*8 as internal_objects_kb,sum(version_store_reserved_page_count)*8 as version_store_kb,sum(unallocated_extent_page_count)*8 as freespace_kbfrom sys.dm_db_file_space_usagewhere database_id = 2SELECT t1.session_id,(ernal_objects_alloc_page_count + task_alloc) as allocated,(ernal_o

12、bjects_dealloc_page_count + task_dealloc) as deallocated, t3.sql_handle, t3.statement_start_offset, t3.statement_end_offset, t3.plan_handlefrom sys.dm_db_session_space_usage as t1, sys.dm_exec_requests t3,(select session_id, sum(internal_objects_alloc_page_count) as task_alloc, sum (internal_objects

13、_dealloc_page_count) as task_dealloc from sys.dm_db_task_space_usage group by session_id) as t2where t1.session_id = t2.session_id and t1.session_id 50and t1.database_id = 2 - tempdb is database_id=2and t1.session_id = t3.session_idorder by allocated DESC 使用正確的，并且是盡量短的數(shù)據(jù)類型 程序中聲明的類型必須和數(shù)據(jù)庫(kù)中的一致 一次提交的命令

14、長(zhǎng)度要合適 盡量使用RPC Call，而不是執(zhí)行命令行 call dbo.qi (M01, M01.0407040000000002) exec dbo.qi v1=M01, v2=M01.0407040000000002 adds ADHOC query plans due to SQL string parsing 盡量避免使用數(shù)據(jù)庫(kù)端游標(biāo) 連接在Runnable狀態(tài)，完全是在等待CPU時(shí)間片執(zhí)行 CPU使用 執(zhí)行計(jì)劃的compile和recompile Sort， Join， Aggregation 通常情況下，SQL的CPU使用量不會(huì)太大。相對(duì)于CPU, memory和disk更容易成

15、為系統(tǒng)的瓶頸盡量避免從數(shù)據(jù)庫(kù)中取出大量的數(shù)據(jù)業(yè)務(wù)邏輯可以用stored procedure完成一次把所有的數(shù)據(jù)都從數(shù)據(jù)庫(kù)里取走沒(méi)有取走的數(shù)據(jù)在數(shù)據(jù)庫(kù)中會(huì)用active的游標(biāo)的方式處理，影響并發(fā)度如果客戶端放棄取走所有數(shù)據(jù)，服務(wù)器還要清理這些數(shù)據(jù)SET NOCOUNT ON避免INSERT, UPDATE and DELETE 語(yǔ)句導(dǎo)致的不必要的網(wǎng)絡(luò)傳輸Master.Sys.dm_exec_cached_plans包含存儲(chǔ)過(guò)程和語(yǔ)句引用該緩存對(duì)象的其他緩存對(duì)象數(shù) 自開(kāi)始以來(lái)使用該緩存對(duì)象的次數(shù)可以重用的執(zhí)行計(jì)劃Procs, Triggers, ViewsDefaults, Check const

16、raints, rulesadhoc SQL, sp_executesqlSQL Batch requests/sec和SQL Compilations/sec作對(duì)比SQL Compilations/sec包含初始的compiles AND re-compiles去掉re-compilations，能大致算出初始compiles的數(shù)目在Sys.dm_exec_cached_plans里面找出usecounts最低的SQL語(yǔ)句 SQL Re-compilations/sec語(yǔ)句一級(jí)的RecompilesCheck profiler for sp:recompile event to identi

17、fy SQL statement. http:/ Perfmon: SQLServer:SQL StatisticsBatch requests / sec 1000s/sec server is busySQL Compilations / sec 10s/sec could be problemSQL Recompilations / sec OLTP should avoid high recompsRatio of compiles / requests is importantCompiles recompiles = initial compiles Plan re-use = (

18、Batch requests initial compiles) / Batch requestsRecompile 的原因:表格的定義發(fā)生變化先前的并發(fā)計(jì)劃需要串行執(zhí)行統(tǒng)計(jì)值更新過(guò)了表格更新的行數(shù)超過(guò)了限度 sys.sysindexes.rowmodctr主要的event有:SP:CacheMiss (event ID 34 in Profiler)SP:CacheInsert (event ID 35 in Profiler)SP:CacheRemove (event ID 36 in Profiler)SP:Recompile (event ID 37 in Profiler)SP:Ca

19、cheHit (event ID 38 in Profiler)SP:Starting 標(biāo)志一個(gè)stored procedure開(kāi)始執(zhí)行SP:StmtStarting 標(biāo)志單個(gè)語(yǔ)句開(kāi)始執(zhí)行Example: sequence is SP:StmtStartingSP:CacheMiss (no plan found)SP:CacheInsert (plan created)注意：使用SQL Profiler可能會(huì)影響SQL性能！加入Eventsubclass 字段可以顯示recompile的原因EventSubClass Description 1Schema changed.2Statisti

20、cs changed.3Deferred compile.4SET option changed.5Temporary table changed.6Remote rowset changed.7FOR BROWSE permission changed.8Query notification environment changed.9Partitioned view changed.10Cursor options changed.11OPTION (RECOMPILE) requested. 觀察執(zhí)行計(jì)劃重用情況Memory: Page faults/secMemory: pages/se

21、cPhysical Disk: Avg. Disk Queue LengthPhysical Disk: Avg. Disk sec/TransferPhysical Disk: Avg. Disk sec/ReadPhysical Disk: Avg. Disk sec/WritePhysical Disk: Current Disk Queue LengthProcessor: %Processor TimeSS Access Methods: Forwarded Records/secSS Access Methods: Full Scans/secSS Access Methods:

22、Index Searches/secSS Access Methods: Page Splits/secSS Access Methods: Range Scans/secSS Access Methods: Table Lock escalations/secSS Buffer Manager: Checkpoint pages/secSS Buffer Manager: Lazy writes/secSS Buffer Manager: Page Life expectancySS Buffer Node:Foreign PagesSS Buffer Node:Page Life expe

23、ctancySS Buffer Node:Stolen PagesSS Databases: Log Flush Wait timeSS Databases: Log Flush Waits/secSS General Statistics: User ConnectionsSS Latches: Average Latch Wait Time(ms)SS Latches: Latch Waits/secSS Latches: Total Latch Wait Time (ms)SS Locks: Average Wait Time(ms)SS Locks: Lock requests/sec

24、SS Locks: Lock Wait Time (ms)SS Locks: Lock Waits/secSS Memory Manager: Memory grants pendingSS SQL Statistics: Auto-Params attempts/secSS SQL Statistics: Batch requests/secSS SQL Statistics: Safe Auto-Params/secSS SQL Statistics: SQL Compilations/secSS SQL Statistics: SQL Re-Compilations/secSystem:

25、 Processor Queue LengthResource IssueRuleDescriptionSourceProblem DescriptionDB Design1經(jīng)常有多個(gè)表格join的語(yǔ)句出現(xiàn)Sys.dm_exec_sql_textSys.dm_exec_cached_plans 表格join次數(shù)太多，會(huì)影響OLTP系統(tǒng)的性能2經(jīng)常更新的表格上有很多indexSys.indexessys.dm_db_operational_index_stats Index數(shù)目多，可以提高查詢速度，但是會(huì)降低修改速度，增加阻塞和死鎖發(fā)生的機(jī)率 3作了太多的硬盤(pán)讀寫(xiě)range scanstable

26、 scansPerfmon objectSQL Server Access MethodsSys.dm_exec_query_stats Missing index, flushes cache 4沒(méi)有用的index * Sys.dm_db_index_usage_stats Index maintenance for unused indexes Resource IssueRuleDescriptionValueSourceProblem DescriptionIO1Avg Disk seconds / read 10 ms Perfmon objectPhysical Disk Read

27、s should take 4-8ms with NO IO pressure2Avg Disk seconds / write 10 ms Perfmon objectPhysical Disk Writes (sequential) can be as fast as 1ms for transaction log.3Big IOsrange scanstable scans1 Perfmon objectSQL Server Access MethodsMissing index, flushes cache 4If Top 2 values for Wait stats includes:ASYNCH_IO_COMPLETIONIO_COMPLETIONLOGMGRWRITELOGPAGEIOLATCH_xTop 2 Sys.dm_os_wait_stats If top 2 wait_stats values include IO, there is an IO bottleneck Resource IssueRuleDescriptionValueSourceProblem DescriptionBlocking