收集的SQL Server性能相关资料
這是本人在工作中整理的關(guān)于SQL Server性能相關(guān)資料，便于隨時(shí)查閱
目錄
性能監(jiān)視器指標(biāo)
內(nèi)存問題診斷
偵測 CPU 壓力
磁盤相關(guān)
內(nèi)存
Memory Clerks
Buffer Pool
Plan Cache
CPU
線程
硬件信息
等待信息
SOS_SCHEDULER_YIELD等待
CXPACKET等待
資源消耗統(tǒng)計(jì)
I/O
讀寫信息
常見問題
索引
MDV
統(tǒng)計(jì)?
日志
WRITELOG等待
LOGBUFFER等待
小結(jié)
相關(guān)SQL
?
?
性能監(jiān)視器指標(biāo)
內(nèi)存問題診斷
| Memory Manager\Total Server Memory（KB） | Buffer Pool的大小 | ? |
| Memory Manager\Target Server Memory（KB） | 表示空閑的Buffer Pool大小。 Total和Target的值應(yīng)該盡可能相同， 如果Total明顯小于Target， 可能意味著出現(xiàn)了內(nèi)存壓力，需要更深入地研究。 | ? |
| Memory Manager\Granted Workspace Memory（ KB） | 當(dāng)前查詢正在使用的總內(nèi)存大小。 Workspace：指在查詢過程中，進(jìn)行hash和排序操作時(shí)，臨時(shí)存儲數(shù)據(jù)的結(jié)果集所用的內(nèi)存。如果在執(zhí)行計(jì)劃中看到出現(xiàn)了hash或者sort操作符，那么就表示會使用這部分的內(nèi)存完成處理。 Memory Grants：已經(jīng)分配給查詢的那部分內(nèi)存。 可以用過 sys. dm_exec_query_memory_grants查看。 另外， RESOURCE_ SEMAPHORE 等待狀態(tài)是針對 memory grants 的，所以如果在sys. dm_ os_ wait_ stats這個(gè)DMV中看到這個(gè)等待類型存在很久，并且處于前幾位，可能需要檢查內(nèi)存是否足夠快。 如果內(nèi)存授予（memory grants）太久，會導(dǎo)致查詢超時(shí)，可以使用SQL trace或者執(zhí)行計(jì)劃查看是否存在hash或者sort warning這些信息。 | ? |
| Memory Manager\Maximum Workspace Memory（ KB） | SQL Server 標(biāo)記預(yù)留給某個(gè)查詢的總內(nèi)存大小。 | ? |
| Memory Manager\Memory Grants Pending | 正在隊(duì)列中的內(nèi)存grants數(shù)量。 | >2 |
| Memory Manager\Memory Grants Outstanding | 正在使用的內(nèi)存grants數(shù)量。 | ? |
| Buffer Manager\Buffer Cache Hit Ratio | Percentage of pages that were found in the buffer pool without having to incur a read from disk. | >90% |
| Buffer Manager\Page Life Expectancy | 代表著一個(gè)數(shù)據(jù)存在于Buffer Pool的時(shí)間。這個(gè)值越長越好，最低時(shí)間應(yīng)該設(shè)置為300s。 | >1000，至少為300 |
| Buffer Manager\Page Lookups/sec | 這個(gè)計(jì)數(shù)器用于衡量實(shí)例中的請求在buffer pool里面查詢的單獨(dú)頁數(shù)量。當(dāng)這個(gè)數(shù)值很高時(shí)，可能意味著不高效的執(zhí)行計(jì)劃，通常需要研究該執(zhí)行計(jì)劃。一般數(shù)值很高是因?yàn)閳?zhí)行計(jì)劃中產(chǎn)生了大量的Page Lookups和Row Lookups。 | Page Lookups： Batch Request<100 |
| Plan Cache\Cache hit Ratio | Ratio between cache hits and lookups | ? |
| Memory：Available Mbytes | 以字節(jié)表示的物理內(nèi)存數(shù)量。此內(nèi)存能立刻分配給一個(gè)進(jìn)程或系統(tǒng)使用。它等于分配給待機(jī)(緩存的)、空閑和零分頁列表內(nèi)存的總和。要獲得內(nèi)存管理器的詳細(xì)解釋，請參閱 MSDN 和/或 Windows Server 2003 Resource Kit 里的系統(tǒng)性能和疑難解答指南章節(jié)。 | ? |
| Memory：Page Faults/sec | 每秒鐘出錯(cuò)頁面的平均數(shù)量。由于每個(gè)錯(cuò)誤操作中只有一個(gè)頁面出錯(cuò)，計(jì)算單位為每秒出錯(cuò)頁面數(shù)量，因此這也等于頁面錯(cuò)誤操作的數(shù)量。這個(gè)計(jì)數(shù)器包括硬錯(cuò)誤(那些需要磁盤訪問的)和軟錯(cuò)誤(在物理內(nèi)存的其他地方找到的錯(cuò)誤頁)。許多處理器可以在有大量軟錯(cuò)誤的情況下繼續(xù)操作。但是，硬錯(cuò)誤可以導(dǎo)致明顯的拖延。 | ? |
| Memory：Pages/sec | 指為解決硬頁錯(cuò)誤從磁盤讀取或?qū)懭氪疟P的速度。這個(gè)計(jì)數(shù)器是可以顯示導(dǎo)致系統(tǒng)范圍延緩類型錯(cuò)誤的主要指示器。它是 Memory\\Pages Input/sec 和 Memory\\Pages Output/sec 的總和。是用頁數(shù)計(jì)算的，以便在不用做轉(zhuǎn)換的情況下就可以同其他頁計(jì)數(shù)如: Memory\\Page Faults/sec 做比較，這個(gè)值包括為滿足錯(cuò)誤而在文件系統(tǒng)緩存(通常由應(yīng)用程序請求)的非緩存映射內(nèi)存文件中檢索的頁。 | ? |
偵測 CPU 壓力
| Access Methods\Forwarded Records/sec。 | 該計(jì)數(shù)器統(tǒng)計(jì)每秒通過正向記錄指針提取的記錄數(shù)，這個(gè)計(jì)數(shù)器用于衡量服務(wù)器中對Forwarded數(shù)據(jù)的訪問情況，通常來說，這個(gè)值不應(yīng)該超過Batch Requests/sec的10%。雖然10%不是絕對值，但它是一個(gè)警告值。 | ? |
| Access Methods\FreeSpace Scans/sec。 | 這是關(guān)于堆表的另外一個(gè)計(jì)數(shù)器。當(dāng)在堆表中插入數(shù)據(jù)時(shí)，它會標(biāo)識發(fā)生了什么操作。 | ? |
| Access Methods\Full Scans/sec。 | 通過這個(gè)計(jì)數(shù)器可查看Full Scans/sec的值，這個(gè)值包含聚集、非聚集索引及堆表。高值意味著查詢存在性能問題，這種情況可能會引起Page Life Expectancy（用于衡量內(nèi)存壓力的一個(gè)主要計(jì)數(shù)器）的變動(dòng)，這將加大數(shù)據(jù)在內(nèi)存中的存儲時(shí)間，并引起I/O問題。 | Batch Requests：Full Scans<1000 |
| Access Methods\Index Searches/sec | 大部分情況下，索引查找會比索引掃描有效，這個(gè)計(jì)數(shù)器顯示出SQL Server實(shí)例上發(fā)生索引查找的比率，這個(gè)值相對于Full Scans/sec來說越高越好。 | Index Searches： Full Scans>1000 |
| Access Methods\Page Splits/sec | 對應(yīng)于堆上的Forwarded Records，聚集索引上的就是Page Splits了。補(bǔ)充一下，雖然非聚集索引也有這個(gè)特性，但是由于沒有聚集索引的表就是堆表，所以堆表上的非聚集索引還是使用的Forwarded Records。Page Splits是一個(gè)較消耗資源的操作，而且在拆頁的時(shí)候會對原來的頁加上排他鎖，并且會產(chǎn)生碎片，所以應(yīng)盡可能少用。 | Batch Requests：Page Split>20 |
| Locks(*)\Lock Wait Time（ms） | 相對于前面的計(jì)數(shù)器，這類計(jì)數(shù)器更偏重于檢查索引的壓力情況。它可以衡量SQL Server花在索引、表、頁上鎖資源的時(shí)間。它沒有可參考值，但是可以作為一個(gè)歷史數(shù)據(jù)，然后用最近監(jiān)控的數(shù)據(jù)和這個(gè)歷史數(shù)據(jù)對比，比值應(yīng)該越低越好。 | ? |
| Locks(*)\Number of Deadlocks/sec。 | 極端情況下，不良的索引設(shè)計(jì)和過度鎖阻塞會引起死鎖（Deadlocks），這種情況是絕對不能容忍的。 | =0 |
| Processor/%Privileged Time | 花費(fèi)在執(zhí)行Windows內(nèi)核命令上的處理器時(shí)間的百分比。 | ? |
| Processor/%User Time | 花費(fèi)在處理應(yīng)用程序如SQL Server上的處理器時(shí)間百分比。 | ? |
| Process（sqlservr. exe）/%Processor Time | 每個(gè)處理器上所有進(jìn)程的總處理時(shí)間。 | ? |
| SQL Statistics\Auto-Param Attempts/sec | Number of auto-parameterization attempts. | ? |
| SQL Statistics\Failed Auto-params/sec | Number of failed auto-parameterizations. | ? |
| SQL Statistics\Batch Requests/sec | Number of failed auto-parameterizations. | ? |
| SQL Statistics\SQL Compilations/sec | Number of SQL compilations. | ? |
| SQL Statistics\SQL Re-Compilations/sec | Number of SQL re-compiles. | ? |
磁盤相關(guān)
| Physical Disk\Average Disk sec/Read | Avg. Disk sec/Read 指以秒計(jì)算的在此盤上讀取數(shù)據(jù)的所需平均時(shí)間。 | ? |
| Physical Disk\Average Disk sec/Write | Avg. Disk sec/Write 指以秒計(jì)算的在此盤上寫入數(shù)據(jù)的所需平均時(shí)間。 | ? |
| Physical Disk\Average Disk Read/Read Queue Length | Avg. Disk Read Queue Length 指讀取請求(為所選磁盤在實(shí)例間隔中列隊(duì)的)的平均數(shù)。 | ? |
| Physical Disk\Average Disk Read/Write Queue Length | Avg. Disk Write Queue Length 指寫入請求(為所選磁盤在實(shí)例間隔中列隊(duì)的)的平均數(shù)。 | ? |
| Physical Disk\Disk Reads/sec | Disk Reads/sec 指在此盤上讀取操作的速率。 | ＜10ms=沒有性能問題 ·10~20ms=存在問題 ·20~50ms=性能很低 ·＞50ms=性能問題嚴(yán)重 |
| Physical Disk\Disk Writes/sec | Disk Writes/sec 指在此盤上寫入操作的速率。 | ＜10ms=沒有性能問題 ·10~20ms=存在問題 ·20~50ms=性能很低 ·＞50ms=性能問題嚴(yán)重 |
?
內(nèi)存
Memory Clerks
-- 用于內(nèi)存緩存的一種機(jī)制，
SELECT [type],
?????? memory_node_id,
?????? virtual_memory_reserved_kb,
?????? virtual_memory_committed_kb,
?????? awe_allocated_kb
FROM?? sys.dm_os_memory_clerks
ORDER? BY virtual_memory_reserved_kb DESC
?
Buffer Pool
SQL Server 內(nèi)存中的最大消耗者。
--每個(gè)數(shù)據(jù)庫緩存大小
SELECT LEFT(CASE database_id
????????????? WHEN 32767 THEN 'ResourceDb'
????????????? ELSE Db_name(database_id)
??????????? END, 20)???????????????????????????????? AS Database_Name,
?????? Count(*)????????????????????????????????????? AS Buffered_Page_Count,
?????? Cast(Count(*) * 8 / 1024.0 AS NUMERIC(10, 2)) AS Buffer_Pool_MB
FROM?? sys.dm_os_buffer_descriptors
GROUP? BY Db_name(database_id),
????????? database_id
ORDER? BY Buffered_Page_Count DESC
?
--當(dāng)前臟頁數(shù)
SELECT Db_name(database_id) AS 'Database',
?????? Count(page_id)?????? AS 'Dirty Pages( KB)'
FROM?? sys. dm_os_buffer_descriptors
WHERE? is_modified = 1
GROUP? BY Db_name(database_id)
ORDER? BY Count(page_id) DESC
?
Plan Cache
--執(zhí)行計(jì)劃緩存
SELECT Count(*)???????????????????????????????????????? AS 'Number of Plans',
?????? Sum(Cast(size_in_bytes AS BIGINT)) / 1024 / 1024 AS 'Plan Cache Size (MB)'
FROM?? sys. dm_exec_cached_plans
?
--查看緩存對象的對應(yīng)內(nèi)存數(shù)。
SELECT objtype????????????????????????????????????? AS 'Cached Object Type',
?????? Count(*)???????????????????????????????????? AS 'Number of Plans',
?????? Sum(Cast(size_in_bytes AS BIGINT)) / 1048576 AS 'Plan Cache Size (MB)',
?????? Avg(usecounts)?????????????????????????????? AS 'Avg Use Count'
FROM?? sys. dm_exec_cached_plans
GROUP? BY objtype
?
CPU
線程
--當(dāng)前系統(tǒng)最大線程數(shù)
SELECT max_workers_count
FROM?? sys.dm_os_sys_info
?
--當(dāng)前工作線程
SELECT Count(*)
FROM?? sys.dm_os_workers
?
硬件信息
--NUMA配置
SELECT scheduler_id,
?????? cpu_id,
?????? parent_node_id,
?????? status
FROM?? sys.dm_os_schedulers
?
--得知超線程和核心方面的關(guān)系。
SELECT cpu_count???????????????????? AS [Logical(邏輯) CPU Count],
?????? hyperthread_ratio???????????? AS [Hyperthread(超線程) Ratio],
?????? cpu_count / hyperthread_ratio AS [Physical(物理) CPU Count],
?????? physical_memory_kb / 1024???? AS [Physical Memory (MB)]
FROM?? sys. dm_os_sys_info
?
等待信息
--等待類型中等待時(shí)間最長的10個(gè)類型。
SELECT TOP (10) wait_type,
??????????????? waiting_tasks_count,
??????????????? ( wait_time_ms - signal_wait_time_ms ) AS resource_wait_time,
??????????????? max_wait_time_ms,
??????????????? CASE waiting_tasks_count
????????????????? WHEN 0 THEN 0
????????????????? ELSE wait_time_ms / waiting_tasks_count
??????????????? END??????????????????????????????????? AS avg_wait_time
FROM?? sys.dm_os_wait_stats
WHERE? wait_type NOT LIKE '%SLEEP%'
?????? --去除不相關(guān)的等待類型
?????? AND wait_type NOT LIKE 'XE%'
?????? AND wait_type NOT IN
?????????? -- 去除系統(tǒng)類型
?????????? ( 'KSOURCE_WAKEUP', 'BROKER_TASK_STOP', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'SQLTRACE_BUFFER_FLUSH',
???????????? 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER', 'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER',
???????????? 'CHECKPOINT_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',
???????????? 'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE', 'BROKER_RECEIVE_WAITFOR',
???????????? 'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH' )
ORDER? BY wait_time_ms DESC
?
?
SELECT wait_type,
?????? signal_wait_time_ms,
?????? wait_time_ms
FROM?? sys.dm_os_wait_stats
WHERE? wait_time_ms > 0
?????? AND wait_type NOT IN ( 'CLR_SEMAPHORE', 'CLR_AUTO_EVENT', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
????????????????????????????? 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
????????????????????????????? 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
????????????????????????????? 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
????????????????????????????? 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP' )
ORDER? BY signal_wait_time_ms DESC
?
SOS_SCHEDULER_YIELD等待
發(fā)生在一個(gè)任務(wù)自愿放棄當(dāng)前的資源占用，讓給其他任務(wù)使用的時(shí)候，就像前面說的離開飲水機(jī)讓別人去接水。 現(xiàn)代關(guān)系型數(shù)據(jù)庫管理系統(tǒng)早已支持多個(gè)任務(wù)同時(shí)運(yùn)行，SQL Server的運(yùn)行機(jī)制之一就是減少runnable的線程等待，也就是說，SQL Server希望盡快運(yùn)行runnable的線程。它以協(xié)同模式運(yùn)行，在必要的時(shí)候，SQL Server會讓出某個(gè)資源來給其他線程，通常來說這種情況是臨時(shí)的。但是當(dāng)長期、大量出現(xiàn)這種等待類型的時(shí)候，有可能意味著CPU存在壓力，這時(shí)候可以檢查這個(gè)DMV中的數(shù)據(jù)：sys.dm_os_schedulers，看看當(dāng)前有多少runnable的任務(wù)在系統(tǒng)中運(yùn)行。
--當(dāng)前有多少runnable的任務(wù)在系統(tǒng)中運(yùn)行
SELECT scheduler_id,
?????? current_tasks_count,
?????? runnable_tasks_count,
?????? work_queue_count,
?????? pending_disk_io_count
FROM?? sys.dm_os_schedulers
WHERE? scheduler_id < 255
通常我們需要關(guān)注runnable_tasks_count這個(gè)列的數(shù)據(jù)，如果見到長時(shí)間存在兩位數(shù)的數(shù)值（記住是長時(shí)間存在），就意味著CPU可能存在壓力，無法應(yīng)付當(dāng)前負(fù)載。
?
CXPACKET等待
CXPACKET 這種類型有點(diǎn)像平時(shí)所說的木桶效應(yīng)，一個(gè)木桶的容量取決于組成木桶最短的那塊木條的長度。如果一個(gè)查詢由多個(gè)線程組成，那么只有在最慢的那個(gè)線程完成之后，整個(gè)查詢才會完成。在這種情況下，SQL Server就出現(xiàn)了CXPACKET等待。 CXPACKET是其中一個(gè)最常見的并行等待，在多CPU的環(huán)境下，這種等待經(jīng)常會出現(xiàn)。并行執(zhí)行最重要的目的就是使得運(yùn)算更快，一個(gè)單獨(dú)的查詢可以使用多個(gè)線程來共同完成，每個(gè)線程會單獨(dú)處理數(shù)據(jù)集的一部分。但應(yīng)該注意的是，某些等待并不總是表示系統(tǒng)性能存在問題，CXPACKET就是其中一種。比如有一家軟件公司，通常會招聘多個(gè)開發(fā)人員去完成開發(fā)工作。但是每天上下班時(shí)，總需要一個(gè)人一個(gè)人串行“打卡”，這時(shí)候CXPACKET就會出現(xiàn)在打卡的過程中，因?yàn)檫@個(gè)時(shí)候必須串行而不是并行。你可能覺得打卡過程中所用的時(shí)間是浪費(fèi)的，但是從整體來說，你雇用更多的人去完成功能開發(fā)，理想情況下是可以加快項(xiàng)目的開發(fā)進(jìn)度的，不應(yīng)該因?yàn)榇蚩ㄟ@幾秒鐘的浪費(fèi)而無視他們在一天中對進(jìn)度的貢獻(xiàn)。 在并行過程中，如果某個(gè)線程處于落后狀態(tài)，CXPACKET等待狀態(tài)就會產(chǎn)生。在上面的例子中，如果你為了移除這種等待狀態(tài)而減少雇員，比如只留下一個(gè)，那么你整體的項(xiàng)目進(jìn)度將會嚴(yán)重延遲。 但是畢竟出現(xiàn)等待狀態(tài)就是表示有一定的資源問題，所以需要針對這種情況進(jìn)行分析。前面提到過，對問題的分析要全面、整體，并且要區(qū)分應(yīng)用系統(tǒng)類型。
OLTP系統(tǒng)
它的特點(diǎn)是事務(wù)數(shù)量多，但是正常來說，事務(wù)的持續(xù)時(shí)間不會很久。
如果CXPACKET等待狀態(tài)頻繁出現(xiàn)，且持續(xù)時(shí)間很長，那就意味著性能可能有問題了。
理想情況下事務(wù)很短，這時(shí)候就沒有必要通過并行運(yùn)行來提高運(yùn)行速度了。所以對于這類系統(tǒng)，有一個(gè)極端方法（如非必要不要用），即把最大并行度（Max Degree of Parallelism）設(shè)為1，強(qiáng)制SQL Server不去使用并行操作，從而減少不必要的資源等待。
OLAP等系統(tǒng)
它的事務(wù)量可能不多，但是持續(xù)時(shí)間往往會很久。
由于事務(wù)普遍較長，所以并行操作往往能提高速度和資源利用率。這時(shí)候可以讓SQL Server自己控制并行，也就是把最大并行度設(shè)為0（即不限制）。
--計(jì)劃緩存中存在并行查詢的語句
SELECT TOP 10 p.*,
????????????? q.*,
????????????? qs.*,
????????????? cp.plan_handle
FROM?? sys.dm_exec_cached_plans cp
?????? CROSS apply sys.Dm_exec_query_plan(cp.plan_handle) p
?????? CROSS apply sys.Dm_exec_sql_text(cp.plan_handle) AS q
?????? JOIN sys.dm_exec_query_stats qs
???????? ON qs.plan_handle = cp.plan_handle
WHERE? cp.cacheobjtype = 'Compiled Plan'
?????? AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";?
??????? max(//p:RelOp/@Parallel)', 'float') > 0
ORDER? BY total_worker_time DESC
OPTION (MAXDOP 1)
?
資源消耗統(tǒng)計(jì)
--CPU最高消耗的10個(gè)語句
SELECT TOP 10 Substring(ST.text, ( QS.statement_start_offset / 2 ) + 1, ( ( CASE statement_end_offset
????????????????????????????????????????????????????????????????????????????? WHEN -1 THEN Datalength(st.text)
????????????????????????????????????????????????????????????????????????????? ELSE QS.statement_end_offset
??????????????????????????????????????????????????????????????????????????? END - QS.statement_start_offset ) / 2 ) + 1) AS statement_text,
????????????? execution_count,
????????????? total_worker_time / 1000?????????????????????????????????????????????????????????????????????????????????? AS total_worker_time_ms,
????????????? ( total_worker_time / 1000 ) / execution_count???????????????????????????????????????????????????????????? AS avg_worker_time_ms,
????????????? total_logical_reads,
????????????? total_logical_reads / execution_count????????????????????????????????????????????????????????????????????? AS avg_logical_reads,
????????????? last_logical_reads,
????????????? total_logical_writes,
????????????? total_logical_writes / execution_count???????????????????????????????????????????????????????????????????? AS avg_logical_writes,
????????????? last_logical_writes,
????????????? total_elapsed_time / 1000????????????????????????????????????????????????????????????????????????????????? AS total_elapsed_time_ms,
????????????? ( total_elapsed_time / 1000 ) / execution_count??????????????????????????????????????????????????????????? AS avg_elapsed_time_ms,
????????????? last_elapsed_time / 1000,
????????????? qp.query_plan
FROM?? sys.dm_exec_query_stats qs
?????? CROSS APPLY sys.Dm_exec_sql_text(qs.sql_handle) st
?????? CROSS APPLY sys.Dm_exec_query_plan(qs.plan_handle) qp
ORDER? BY total_worker_time DESC
?
--找出使用頻率最高的20%的查詢
SELECT TOP 20 PERCENT cp.usecounts???? AS ' 使用次數(shù)',
????????????????????? cp.cacheobjtype? AS ' 緩存類型',
????????????????????? cp.objtype?????? AS [ 對象類型],
????????????????????? st.text????????? AS 'TSQL',
????????????????????? --cp.plan_handle AS ' 執(zhí)行計(jì)劃',
????????????????????? qp.query_plan??? AS ' 執(zhí)行計(jì)劃',
????????????????????? cp.size_in_bytes AS ' 執(zhí)行計(jì)劃占用空間( Byte)'
FROM?? sys.dm_exec_cached_plans cp
?????? CROSS APPLY sys.Dm_exec_sql_text(plan_handle) st
?????? CROSS APPLY sys.Dm_exec_query_plan(plan_handle) qp
ORDER? BY usecounts DESC
?
I/O
讀寫信息
--查看當(dāng)前數(shù)據(jù)庫文件中的IO情況
SELECT Db_name(Db_id())?????????????????????????????????????????????????????????????????????????????????? AS [Database Name],
?????? [file_id],
?????? num_of_reads,
?????? num_of_writes,
?????? num_of_bytes_read,
?????? num_of_bytes_written,
?????? Cast(100. * num_of_reads / ( num_of_reads + num_of_writes ) AS DECIMAL(10, 1))???????????????????? AS [# Reads Pct],
?????? Cast(100. * num_of_writes / ( num_of_reads + num_of_writes ) AS DECIMAL(10, 1))??????????????????? AS [# Write Pct],
?????? Cast(100. * num_of_bytes_read / ( num_of_bytes_read + num_of_bytes_written ) AS DECIMAL(10, 1))??? AS [Read Bytes Pct],
?????? Cast(100. * num_of_bytes_written / ( num_of_bytes_read + num_of_bytes_written ) AS DECIMAL(10, 1)) AS [Written Bytes Pct]
FROM?? sys. Dm_io_virtual_file_stats(Db_id(), NULL);
?
--計(jì)劃緩存中存儲過程的邏輯寫排名，主要是寫操作的壓力
SELECT p. NAME????????????????? AS [SP Name],
?????? qs. total_logical_writes AS [TotalLogicalWrites],
?????? qs. execution_count,
?????? qs. cached_time
FROM?? sys. procedures AS p
?????? INNER JOIN sys. dm_exec_procedure_stats AS qs
?????????????? ON p.[object_id] = qs. [object_id]
WHERE? qs. database_id = Db_id()
?????? AND qs. total_logical_writes > 0
ORDER? BY qs. total_logical_writes DESC;
?
常見問題
解決PAGEIOLATCH等待
潛在問題
數(shù)據(jù)從磁盤加載到內(nèi)存buffer中的時(shí)間不會很長，一旦數(shù)據(jù)進(jìn)入buffer，提取數(shù)據(jù)會很快，并且查找過程不會產(chǎn)生任何等待。但是也有特殊情況，如果有10個(gè)任務(wù)都需要同時(shí)請求相同的數(shù)據(jù)，第一個(gè)任務(wù)會申請加載數(shù)據(jù)，其他9個(gè)任務(wù)都必須等待，直到第一個(gè)任務(wù)的數(shù)據(jù)加載完畢為止。如果這時(shí)磁盤已經(jīng)超負(fù)荷，那么第一個(gè)請求會花費(fèi)較長的時(shí)間。同樣，如果內(nèi)存不足，緩存在buffer中的數(shù)據(jù)會過早地被沖刷掉（從buffer中清空），從而引起9個(gè)任務(wù)再次等待下一輪的加載。
降低PAGEIOLATCH等待
當(dāng)buffer正在加載和卸載時(shí)，都會對其中的數(shù)據(jù)加上閂鎖。這意味著其他進(jìn)程必須等待，直到釋放閂鎖且數(shù)據(jù)依舊存在于buffer中為止?？焖俚丶虞d和長時(shí)間駐留能最大限度地降低等待，這類等待和其他等待類似，當(dāng)遇到相關(guān)問題時(shí)，可以評估下面的方案是否可行。
提升I/O子系統(tǒng)的速度。
當(dāng)存在內(nèi)存問題或者壓力出現(xiàn)時(shí)也會引起這種等待，檢查內(nèi)存相關(guān)的計(jì)數(shù)器，查看是否存在內(nèi)存問題，如果存在，嘗試優(yōu)化或者改善內(nèi)存。
將LDF/MDF/tempdb的文件分開存放，減少資源爭用。
檢查文件統(tǒng)計(jì)信息，看看高I/O讀寫操作出現(xiàn)在什么文件上。
檢查系統(tǒng)是否有合適的索引，丟失索引和不合理的索引都會造成大面積的掃描，通過添加有效的索引可減少I/O請求，緩解I/O壓力。使用覆蓋索引來替代聚集索引是一個(gè)不錯(cuò)的選擇，可以明顯降低CPU、內(nèi)存和I/O的壓力（因?yàn)橐话惴蔷奂饕牧卸急染奂饕?#xff0c;聚集索引實(shí)際上是全表索引）。
更新統(tǒng)計(jì)信息，使SQL Server查詢優(yōu)化器能選擇最優(yōu)執(zhí)行計(jì)劃。
檢查下面的性能計(jì)數(shù)器。
SQL Server：Memory Manager\Memory Grants Pending>2
SQL Server：Memory Manager\Memory Grants Outstanding
SQL Server：Buffer Manager\Buffer Hit Cache Ratio>90%
SQL Server：Buffer Manager\Page Life Expectancy>1000，至少為300
Memory：Available Mbytes Memory：Page Faults/sec ·檢查磁盤相關(guān)的計(jì)數(shù)器。
Average Disk sec/Read Average Disk sec/Write Average Disk Read/Write Queue Lenght
?
索引
MDV
| sys.index_columns | 提供了索引內(nèi)包含的列，也就是索引鍵。每個(gè)鍵一行，通過關(guān)聯(lián)其他DMV 就可以獲得索引定義的列情況。 |
| sys.xml_indexes | 和sys.indexes 類似，但是主要針對XML 索引。 |
| sys.spatial_indexes | 也和sys.indexes 類似，主要針對spatial 索引。 |
| sys.column_store_dictionaries sys.column_store_segments | 主要用于描述從SQL Server 2012 開始引入的列存儲索引信息。 |
| sys.dm_db_index_physical_stats | 索引碎片 |
| sys.dm_db_missing_index_details | 缺失索引相關(guān) |
| sys.dm_db_missing_index_columns | 缺失索引相關(guān) |
| sys.dm_db_missing_index_group_stats | 缺失索引相關(guān) |
| sys.dm_db_missing_index_groups | 缺失索引相關(guān) |
?
統(tǒng)計(jì)?
SELECT '[' + Db_name() + '].[' + su.[name] + '].[' + o.[name]
?????? + ']'????????????????? AS [statement],
?????? i.[name]?????????????? AS [index_name],
?????? ddius.[user_seeks] + ddius.[user_scans]
?????? + ddius.[user_lookups] AS [user_reads],
?????? ddius.[user_updates]?? AS [user_writes],
?????? Sum(SP.rows)?????????? AS [total_rows]
FROM?? sys.dm_db_index_usage_stats ddius
?????? INNER JOIN sys.indexes i
?????????????? ON ddius.[object_id] = i.[object_id]
????????????????? AND i.[index_id] = ddius.[index_id]
?????? INNER JOIN sys.partitions SP
?????????????? ON ddius.[object_id] = SP.[object_id]
????????????????? AND SP.[index_id] = ddius.[index_id]
?????? INNER JOIN sys.objects o
?????????????? ON ddius.[object_id] = o.[object_id]
?????? INNER JOIN sys.sysusers su
?????????????? ON o.[schema_id] = su.[UID]
WHERE? ddius.[database_id] = Db_id() -- current database only
?????? AND Objectproperty(ddius.[object_id], 'IsUserTable') = 1
?????? AND ddius.[index_id] > 0
GROUP? BY su.[name],
????????? o.[name],
????????? i.[name],
????????? ddius.[user_seeks] + ddius.[user_scans]
????????? + ddius.[user_lookups],
????????? ddius.[user_updates]
HAVING ddius.[user_seeks] + ddius.[user_scans]
?????? + ddius.[user_lookups] = 0
ORDER? BY ddius.[user_updates] DESC,
????????? su.[name],
????????? o.[name],
????????? i.[name]
?
--查找未被使用過的索引
SELECT Object_name(i.object_id)????????????????????????????????????? AS table_name,
?????? COALESCE(i.NAME, Space(0))??????????????????????????????????? AS index_name,
?????? ps.partition_number,
?????? ps.row_count,
?????? Cast(( ps.reserved_page_count * 8 ) / 1024.AS DECIMAL(12, 2)) AS size_in_mb,
?????? COALESCE(ius.user_seeks, 0)?????????????????????????????????? AS user_seeks,
?????? COALESCE(ius.user_scans, 0)?????????????????????????????????? AS user_scans,
?????? COALESCE(ius.user_lookups, 0)???????????????????????????????? AS user_lookups,
?????? i.type_desc
FROM?? sys.all_objects t
?????? INNER JOIN sys.indexes i
?????????????? ON t.object_id = i.object_id
?????? INNER JOIN sys.dm_db_partition_stats ps
?????????????? ON i.object_id = ps.object_id
????????????????? AND i.index_id = ps.index_id
?????? LEFT OUTER JOIN sys.dm_db_index_usage_stats ius
??????????????????? ON ius.database_id = Db_id()
?????????????????????? AND i.object_id = ius.object_id
?????????????????????? AND i.index_id = ius.index_id
WHERE? i.type_desc NOT IN ( 'HEAP', 'CLUSTERED' )
?????? AND i.is_unique = 0
?????? AND i.is_primary_key = 0
?????? AND i.is_unique_constraint = 0
?????? AND COALESCE(ius.user_seeks, 0) <= 0
?????? AND COALESCE(ius.user_scans, 0) <= 0
?????? AND COALESCE(ius.user_lookups, 0) <= 0
ORDER? BY Object_name(i.object_id),
????????? i.NAME
?
--寫操作還是遠(yuǎn)大于讀操作的索引
SELECT Object_name(ddius.[object_id])??????????????????????????? AS [Table Name],
?????? i.NAME??????????????????????????????????????????????????? AS [Index Name],
?????? i.index_id,
?????? user_updates????????????????????????????????????????????? AS [Total Writes],
?????? user_seeks + user_scans + user_lookups??????????????????? AS [Total Reads],
?????? user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference]
FROM?? sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK )
?????? INNER JOIN sys.indexes AS i WITH ( NOLOCK )
?????????????? ON ddius.[object_id] = i.[object_id]
????????????????? AND i.index_id = ddius.index_id
WHERE? Objectproperty(ddius.[object_id], 'IsUserTable') = 1
?????? AND ddius.database_id = Db_id()
?????? AND user_updates > ( user_seeks + user_scans + user_lookups )
?????? AND i.index_id > 1
ORDER? BY [Difference] DESC,
????????? [Total Writes] DESC,
????????? [Total Reads] ASC;
?
--索引上的碎片
SELECT '[' + Db_name() + '].['
?????? + Object_schema_name( ddips.[object_id], Db_id())
?????? + '].['
?????? + Object_name(ddips.[object_id], Db_id())
?????? + ']'????????????????????????????????????????????????? AS [statement],
?????? i.[name]?????????????????????????????????????????????? AS [index_name],
?????? ddips.[index_type_desc],
?????? ddips.[partition_number],
?????? ddips.[alloc_unit_type_desc],
?????? ddips.[index_depth],
?????? ddips.[index_level],
?????? Cast(ddips.[avg_fragmentation_in_percent] AS SMALLINT) AS [avg_frag_%],
?????? Cast(ddips.[avg_fragment_size_in_pages] AS SMALLINT)?? AS [avg_frag_size_in_pages],
?????? ddips.[fragment_count],
?????? ddips.[page_count]
FROM?? sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, 'limited') ddips
?????? INNER JOIN sys.[indexes] i
?????????????? ON ddips.[object_id] = i.[object_id]
????????????????? AND ddips.[index_id] = i.[index_id]
WHERE? ddips.[avg_fragmentation_in_percent] > 15
?????? AND ddips.[page_count] > 500
ORDER? BY ddips.[avg_fragmentation_in_percent],
????????? Object_name(ddips.[object_id], Db_id()),
????????? i.[name]
?
日志
WRITELOG等待
這是日志等待中最常見的等待類型。在事務(wù)量很高的系統(tǒng)中，這種等待比較常見。當(dāng)數(shù)據(jù)被修改時(shí)，在log cache和buffer cache中都會有記錄，如果在log cache中的數(shù)據(jù)在checkpoint時(shí)寫入磁盤，就會發(fā)生這種等待。但是有時(shí)候在客戶端，用戶會停止一個(gè)正在運(yùn)行并且運(yùn)行了很久的事務(wù)，這時(shí)會引起其回滾，這也就會導(dǎo)致這種等待的發(fā)生。
降低WRITELOG等待
把日志文件和數(shù)據(jù)文件及其他文件如TEMPDB存放到獨(dú)立的磁盤中。另外就是避免類似游標(biāo)等的低效操作，同時(shí)加快提交事務(wù)的頻率，最后檢查I/O相關(guān)的計(jì)數(shù)器。 除此之外，刪除沒用的非聚集索引、減少日志開銷、修改索引鍵或使用填充因子減少頁分裂（第6章介紹過）、修改程序架構(gòu)、把負(fù)載分?jǐn)偟蕉鄠€(gè)服務(wù)器或者數(shù)據(jù)庫中，這些手段都能減少出現(xiàn)這類等待的情況。
不要一見到這種等待就以為是I/O問題，也不要直接增加日志文件。上面已經(jīng)說過，增加日志文件解決不了這類問題。 應(yīng)該進(jìn)行如下更加深入的分析：
查看sys.dm_io_virtual_file_stats的數(shù)據(jù)。
查看LOGBUFFER等待（下面介紹），看是否存在對日志緩沖區(qū)（log buffer）的爭搶。
查看日志文件所在磁盤的磁盤等待隊(duì)列。
查看事務(wù)的平均大小。
查看是否有大量的頁分裂，因?yàn)檫@樣也會導(dǎo)致大量的日志。
?
LOGBUFFER等待
這種等待類型相對較少出現(xiàn)，當(dāng)一個(gè)任務(wù)正在等待存儲記錄到log buffer時(shí)，就會產(chǎn)生這種等待。這種等待類型的高值可能表示這日志所在的磁盤無法響應(yīng)請求。
降低LOGBUFFER等待
不同的原因解決方案不同，通常包括合理存放各類文件、避免類似游標(biāo)等編程技巧及加快事務(wù)提交的頻率等。
?
小結(jié)
可以查看sys.fn_vertualfilestats函數(shù)找到I/O相關(guān)的問題，并且可檢查I/O相關(guān)的計(jì)數(shù)器，比如使用SELECT * FROM fn_virtualfilestats(1,1)來查看數(shù)據(jù)庫ID為1、文件號為1的統(tǒng)計(jì)信息。
WRITELOG和LOGBUFFER等待的處理手段非常類似，它們的確有共同點(diǎn)，但是不要認(rèn)為它們是相同的，需要分開對待。
?
相關(guān)SQL
--檢查活動(dòng)事務(wù)的日志情況
SELECT DTST.[session_id],
?????? DES.[login_name]?????????????????????????????? AS [Login Name],
?????? Db_name(DTDT.database_id)????????????????????? AS [Database],
?????? DTDT.[database_transaction_begin_time]???????? AS [Begin Time],-- DATEDIFF(ms,DTDT.[database_transaction_begin_time], GETDATE()) AS [Durationms],
?????? CASE DTAT.transaction_type
???????? WHEN 1 THEN 'Read/write'
???????? WHEN 2 THEN 'Read-only'
???????? WHEN 3 THEN 'System'
???????? WHEN 4 THEN 'Distributed'
?????? END??????????????????????????????????????????? AS [Transaction Type],
?????? CASE DTAT.transaction_state
???????? WHEN 0 THEN 'Not fully initialized'
???????? WHEN 1 THEN 'Initialized, not started'
???????? WHEN 2 THEN 'Active'
???????? WHEN 3 THEN 'Ended'
???????? WHEN 4 THEN 'Commit initiated'
???????? WHEN 5 THEN 'Prepared, awaiting resolution'
???????? WHEN 6 THEN 'Committed'
???????? WHEN 7 THEN 'Rolling back'
???????? WHEN 8 THEN 'Rolled back'
?????? END??????????????????????????????????????????? AS [Transaction State],
?????? DTDT.[database_transaction_log_record_count]?? AS [Log Records],
?????? DTDT.[database_transaction_log_bytes_used]???? AS [Log Bytes Used],
?????? DTDT.[database_transaction_log_bytes_reserved] AS [Log Bytes RSVPd],
?????? DEST.[text]??????????????????????????????????? AS [Last Transaction Text],
?????? DEQP.[query_plan]????????????????????????????? AS [Last Query Plan]
FROM?? sys.dm_tran_database_transactions DTDT
?????? INNER JOIN sys.dm_tran_session_transactions DTST
?????????????? ON DTST.[transaction_id] = DTDT.[transaction_id]
?????? INNER JOIN sys.[dm_tran_active_transactions] DTAT
?????????????? ON DTST.[transaction_id] = DTAT.[transaction_id]
?????? INNER JOIN sys.[dm_exec_sessions] DES
?????????????? ON DES.[session_id] = DTST.[session_id]
?????? INNER JOIN sys.dm_exec_connections DEC
?????????????? ON DEC.[session_id] = DTST.[session_id]
?????? LEFT JOIN sys.dm_exec_requests DER
????????????? ON DER.[session_id] = DTST.[session_id]
?????? CROSS APPLY sys.Dm_exec_sql_text(DEC.[most_recent_sql_handle]) AS DEST
?????? OUTER APPLY sys.Dm_exec_query_plan(DER.[plan_handle]) AS DEQP
ORDER? BY DTDT.[database_transaction_log_bytes_used] DESC; -- ORDER BY [Duration ms] DESC;
?
?
?
總結(jié)
以上是生活随笔為你收集整理的收集的SQL Server性能相关资料的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Houdini湖边小屋-屋顶细分详细过程
- 下一篇： 开题报告、文献检索账号、文献综述、外文翻