當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

收集的SQL Server性能相关资料

發(fā)布時(shí)間：2023/12/20 数据库 34 豆豆

生活随笔收集整理的這篇文章主要介紹了收集的SQL Server性能相关资料小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

這是本人在工作中整理的關(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)為它們是相同的，需要分開對待。

總結(jié)

以上是生活随笔為你收集整理的收集的SQL Server性能相关资料的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇： Houdini湖边小屋-屋顶细分详细过程
下一篇：开题报告、文献检索账号、文献综述、外文翻