監(jiān)控 SQL Server 的運行狀況

1、監(jiān)控 SQL Server 的運行狀況Appendix A: monitor the operation of SQL ServerTurn on low bandwidth viewLanguage filter: all Visual BasicC#C+J#JScriptXAMLF#Appendix A: monitor the operation of SQL ServerMicrosoft SQL Server 2005 provides a number of tools to monitor the database. One way is to dynamically manag

2、e views. The server status information returned by the dynamic management view (DMV) and the dynamic management function (DMF) can be used to monitor the running of the server instance, diagnose problems, and optimize performance.Conventional server dynamic management objects include:Dm_db_*: databa

3、se and database objectsDm_exec_*: execute user code and associated connectionsDm_os_*: memory, locks, and timingDm_tran_*: transaction and isolationDm_io_*: network / disk input / outputThis section describes some of the common queries that run for these dynamic management views and functions to mon

4、itor SQL Server operations.Sample queryYou can run the following query to get all the DMV and DMF names:Copy codeSELECT * FROM sys.system_objectsWHERE name LIKEdm_%ORDER BY nameMonitor CPU bottleneckCPU bottlenecks are usually caused by the following: the query plan is not optimal, poorly configured

5、, poorly designed, or lack of hardware resources. The following frequently used queries help you determine the cause of the CPU bottleneck.The following query allows you to gain insight into what batch or process the current cache takes up most of the CPU resources.Copy codeSELECT TOP 50SUM (qs.tota

6、l_worker_time) AS total_cpu_time,SUM (qs.execution_count) AS total_execution_count,COUNT (*) AS number_of_statements,Qs.sql_handleFROM sys.dm_exec_query_stats AS QSGROUP BY qs.sql_handleORDER BY SUM (qs.total_worker_time) DESCThe following query shows the total CPU utilization of the cache plan (wit

7、h SQL text).Copy codeSELECTTotal_cpu_time,Total_execution_count,Number_of_statements,S2.text(SELECT - SUBSTRING (s2.text, statement_start_offset / 2, (CASE WHEN -1 THEN (statement_end_offset = LEN (CONVERT (NVARCHAR (MAX), s2.text) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) A

8、S query_textFROM(SELECT TOP 50SUM (qs.total_worker_time) AS total_cpu_time,SUM (qs.execution_count) AS total_execution_count,COUNT (*) AS number_of_statements,Qs.sql_handle -,民（statement_start_offset）作為statement_start_offset，馬克斯（statement_end_offset）作為statement_end_offset從sys.dm_exec_query_stats QS集

9、團通過qs.sql_handle為了總結(jié)（QS。total_worker_time）降序）統(tǒng)計跨應(yīng)用系統(tǒng)。dm_exec_sql_text（統(tǒng)計。sql_handle）S2下面的查詢顯示CPU平均占用率最高的前50個SQL語句。復(fù)制代碼選擇前50total_worker_time / execution_count為平均CPU時間，（選擇的子字符串（文本，statement_start_offset / 2，（在statement_end_offset = 1然后len（轉(zhuǎn)換（nvarchar（max）、文本）2其他statement_end_offset端statement_start_of

10、fset）/ 2）從系統(tǒng)dm_exec_sql_text（sql_handle）為query_text，*從sys.dm_exec_query_stats通過 平均CPU時間倒序下面顯示用于找出過多編譯/重新編譯的DMV查詢。復(fù)制代碼SELECT * FROM sys.dm_exec_query_optimizer_info哪里計數(shù)器=優(yōu)化或計數(shù)器=“經(jīng)過時間”下面的示例查詢顯示已重新編譯的前25個存儲過程plan_generation_num指示該查詢已重新編譯的次數(shù)。復(fù)制代碼選擇前25sql_text.text，sql_handle，plan_generation_num，executio

11、n_count，DBID，objectID從sys.dm_exec_query_stats一跨應(yīng)用系統(tǒng)。dm_exec_sql_text（sql_handle）作為sql_text在plan_generation_num 1通過plan_generation_num倒序效率較低的查詢計劃可能增大CPU占用率。下面的查詢顯示哪個查詢占用了最多的CPU累計使用率。復(fù)制代碼選擇highest_cpu_queries.plan_handle，highest_cpu_queries.total_worker_time，q.dbid，q.objectid，q.number，q.encrypted，文本從（

12、選擇前50名）qs.plan_handle，qs.total_worker_time從sys.dm_exec_query_stats QS通過qs.total_worker_time倒序）作為highest_cpu_queries跨應(yīng)用系統(tǒng)。dm_exec_sql_text（plan_handle）Q通過highest_cpu_queries.total_worker_time倒序The following query shows some operators that might occupy a large number of CPU usage (such as%Hash, Match%

13、 ,%Sort% ) to find suspicious objects.Copy codeSelect *FromSys.dm_exec_cached_plansCross apply sys.dm_exec_query_plan (plan_handle)WhereCast (query_plan, as, nvarchar (max) like%Sort%Or cast (query_plan, as, nvarchar (max) like,%Hash, Match%If you have detected inefficiencies and lead to a higher CP

14、U occupancy query plan, run the UPDATE STATISTICS to the table involved in the query to see if the problem still exists. Then, collect the relevant data and report the problem to the PerformancePoint Planning support staff.If your system has too much compilation and recompile, you may cause performa

15、nce problems associated with CPU in your system.You can run the following DMV query to find too many compiled / recompiled.Copy codeSelect * from sys.dm_exec_query_optimizer_infoWhereCounter =optimizationsOr counter =elapsed timeThe following example query shows the first 25 stored procedures that h

16、ave been recompiled. Plan_generation_num indicates the number of times the query has been recompiled.Copy codeSelect top 25Sql_text.text,Sql_handle,Plan_generation_num,Execution_count,Dbid,ObjectidFrom sys.dm_exec_query_stats aCross, apply, sys.dm_exec_sql_text (sql_handle), as, sql_textWhere plan_g

17、eneration_num 1Order by plan_generation_num descIf too many compile or recompile has been detected, please collect the relevant data as much as possible and report it to the Planning support staff.Memory bottleneckBefore you start the memory pressure check and survey, make sure that the advanced opt

18、ions in SQL Server are enabled. First run the following query on the master database to enable this option.Copy codeSp_configureshow advanced optionsGoSp_configure,show, advanced, options, 1GoReconfigureGoFirst, run the following query to check memory related configuration options.Copy codeSp_config

19、ureawe_enabledGoSp_configuremin server memoryGoSp_configureMAX server memoryGoSp_configure,min, memory, per, queryGoSp_configurequery waitGoRun the following DMV query to view the CPU, scheduler, memory, and buffer pool information.Copy codeSelectCpu_count,Hyperthread_ratio,Scheduler_count,Physical_

20、memory_in_bytes / 1024 / 1024 as physical_memory_mb,Virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,bpool_committed * 8 / 1024 bpool_committed_mb，bpool_commit_target * 8 / 1024 bpool_target_mb，bpool_visible * 8 / 1024 bpool_visible_mb從sys.dm_os_sys_infoI/O瓶頸檢查閂鎖等待統(tǒng)計信息以確定I/O瓶頸。運行下面的DMV查詢以查

21、找I/O閂鎖等待統(tǒng)計信息。復(fù)制代碼選擇wait_type，waiting_tasks_count，wait_time_ms，signal_wait_time_ms，wait_time_ms / waiting_tasks_count從sys.dm_os_wait_stats在wait_type喜歡pageiolatch %和waiting_tasks_count 0為了wait_type如果waiting_task_counts和wait_time_ms與正常情況相比有顯著變化，則可以確定存在I/O問題。獲取SQL Server平穩(wěn)運行時性能計數(shù)器和主要DMV查詢輸出的基線非常重要。這些wai

22、t_types可以指示您的I/O子系統(tǒng)是否遇到瓶頸。使用以下DMV查詢來查找當(dāng)前掛起的I/O請求。請定期執(zhí)行此查詢以檢查I/O子系統(tǒng)的運行狀況，并隔離I/O瓶頸中涉及的物理磁盤。復(fù)制代碼選擇database_id，file_id，io_stall，io_pending_ms_ticks，scheduler_address從系統(tǒng)dm_io_virtual_file_stats（null，null）T1，sys.dm_io_pending_io_requests T2在t1.file_handle = t2.io_handle在正常情況下，該查詢通常不返回任何內(nèi)容如果此查詢返回一些行，則需要進一步

23、調(diào)查。您還可以執(zhí)行下面的DMV查詢以查找I/O相關(guān)查詢。復(fù)制代碼選擇前5（total_logical_reads / execution_count）作為avg_logical_reads，（total_logical_writes / execution_count）作為avg_logical_writes，（total_physical_reads / execution_count）作為avg_physical_reads，execution_count，statement_start_offset，p.query_plan，q.text從sys.dm_exec_query_stats跨

24、應(yīng)用系統(tǒng)。dm_exec_query_plan（plan_handle）P跨應(yīng)用系統(tǒng)。dm_exec_sql_text（plan_handle）Q以（total_logical_reads + total_logical_writes）/ execution_count desc下面的DMV查詢可用于查找哪些批處理/請求生成的I/O最多。如下所示的DMV查詢可用于查找可生成最多I/O的前五個請求。調(diào)整這些查詢將提高系統(tǒng)性能。復(fù)制代碼選擇前5（total_logical_reads / execution_count）作為avg_logical_reads，（total_logical_writes / execution_count）作為avg_logical_writes，（total_physical_reads / execution_count）作為avg_phys_reads，execution_count，statement_start_offset作為stmt_start_offset，sql_handle，plan_handle從sys.dm_exec_query_stats以（total_logical_reads + total_logical_writes）描述阻塞運行下面的查詢可確定阻塞的會話。復(fù)制代碼選擇blocking_session_id