sp_server_diagnostics

sp_server_diagnostics
sp_server_diagnostics是一个标准的sqlserver存储过程。sp_server_diagnostics只有一个参数--“重复间隔”。如果不指定这个参数，则sp_server_diagnostics返回一次诊断信息就结束了。重复间隔时间不能小于5秒。
sp_server_diagnostics是用来判断sqlserver健康状况、 触发故障转移的重要存储过程。

exec sp_server_diagnostics示例输出

2016-06-06 16:52:59.543	instance	system	1	clean	<system spinlockBackoffs="0" sickSpinlockType="none" sickSpinlockTypeAfterAv="none" latchWarnings="0" isAccessViolationOccurred="0" writeAccessViolationCount="0" totalDumpRequests="0" intervalDumpRequests="0" nonYieldingTasksReported="0" pageFaults="2567" systemCpuUtilization="3" sqlCpuUtilization="0" BadPagesDetected="0" BadPagesFixed="0" LastBadPageAddress="0x0"/>
2016-06-06 16:52:59.543	instance	resource	1	clean	<resource lastNotification="RESOURCE_MEMPHYSICAL_HIGH" outOfMemoryExceptions="0" isAnyPoolOutOfMemory="0" processOutOfMemoryPeriod="0"><memoryReport name="Process/System Counts" unit="Value"><entry description="Available Physical Memory" value="714432512"/><entry description="Available Virtual Memory" value="3788345344"/><entry description="Available Paging File" value="2830270464"/><entry description="Working Set" value="208400384"/><entry description="Percent of Committed Memory in WS" value="100"/><entry description="Page Faults" value="127375"/><entry description="System physical memory high" value="1"/><entry description="System physical memory low" value="0"/><entry description="Process physical memory low" value="0"/><entry description="Process virtual memory low" value="0"/></memoryReport><memoryReport name="Memory Manager" unit="KB"><entry description="VM Reserved" value="285524"/><entry description="VM Committed" value="162848"/><entry description="AWE Allocated" value="0"/><entry description="Large Pages Allocated" value="0"/><entry description="Emergency Memory" value="1024"/><entry description="Emergency Memory In Use" value="16"/><entry description="Target Committed" value="757472"/><entry description="Current Committed" value="162848"/><entry description="Pages Allocated" value="109176"/><entry description="Pages Reserved" value="0"/><entry description="Pages Free" value="35928"/><entry description="Pages In Use" value="76520"/><entry description="Page Alloc Potential" value="1911888"/><entry description="NUMA Growth Phase" value="0"/><entry description="Last OOM Factor" value="0"/><entry description="Last OS Error" value="0"/></memoryReport></resource>
2016-06-06 16:52:59.543	instance	query_processing	1	clean	<queryProcessing maxWorkers="256" workersCreated="47" workersIdle="16" tasksCompletedWithinInterval="14" pendingTasks="0" oldestPendingTaskWaitingTime="0" hasUnresolvableDeadlockOccurred="0" hasDeadlockedSchedulersOccurred="0" trackingNonYieldingScheduler="0x0"><topWaits><nonPreemptive><byCount><wait waitType="HADR_FILESTREAM_IOMGR_IOCOMPLETION" waits="195242" averageWaitTime="500" maxWaitTime="1503"/><wait waitType="OLEDB" waits="2438" averageWaitTime="0" maxWaitTime="103"/><wait waitType="PAGEIOLATCH_SH" waits="906" averageWaitTime="23" maxWaitTime="305"/><wait waitType="ASYNC_NETWORK_IO" waits="615" averageWaitTime="0" maxWaitTime="53"/><wait waitType="MSQL_XP" waits="422" averageWaitTime="7" maxWaitTime="1014"/><wait waitType="WRITELOG" waits="291" averageWaitTime="4" maxWaitTime="46"/><wait waitType="IO_COMPLETION" waits="223" averageWaitTime="50" maxWaitTime="263"/><wait waitType="WRITE_COMPLETION" waits="182" averageWaitTime="24" maxWaitTime="201"/><wait waitType="PAGELATCH_SH" waits="156" averageWaitTime="0" maxWaitTime="1"/><wait waitType="PAGELATCH_EX" waits="147" averageWaitTime="0" maxWaitTime="3"/></byCount><byDuration><wait waitType="CLR_AUTO_EVENT" waits="8" averageWaitTime="18040729" maxWaitTime="72159172"/><wait waitType="HADR_FILESTREAM_IOMGR_IOCOMPLETION" waits="195242" averageWaitTime="500" maxWaitTime="1503"/><wait waitType="FT_IFTS_RWLOCK" waits="1" averageWaitTime="55767" maxWaitTime="55767"/><wait waitType="SLEEP_MASTERDBREADY" waits="1" averageWaitTime="48954" maxWaitTime="48954"/><wait waitType="PWAIT_ALL_COMPONENTS_INITIALIZED" waits="3" averageWaitTime="13312" maxWaitTime="13363"/><wait waitType="FT_IFTSHC_MUTEX" waits="2" averageWaitTime="15000" maxWaitTime="30001"/><wait waitType="LCK_M_S" waits="4" averageWaitTime="7448" maxWaitTime="10803"/><wait waitType="PAGEIOLATCH_SH" waits="906" averageWaitTime="23" maxWaitTime="305"/><wait waitType="SLEEP_DBSTARTUP" waits="135" averageWaitTime="100" maxWaitTime="200"/><wait waitType="IO_COMPLETION" waits="223" averageWaitTime="50" maxWaitTime="263"/></byDuration></nonPreemptive><preemptive><byCount><wait waitType="PREEMPTIVE_OS_AUTHENTICATIONOPS" waits="4536" averageWaitTime="0" maxWaitTime="266"/><wait waitType="PREEMPTIVE_OS_QUERYREGISTRY" waits="1627" averageWaitTime="0" maxWaitTime="26"/><wait waitType="PREEMPTIVE_OS_AUTHORIZATIONOPS" waits="791" averageWaitTime="0" maxWaitTime="72"/><wait waitType="PREEMPTIVE_OS_DECRYPTMESSAGE" waits="776" averageWaitTime="0" maxWaitTime="1"/><wait waitType="PREEMPTIVE_OS_REVERTTOSELF" waits="773" averageWaitTime="0" maxWaitTime="2"/><wait waitType="PREEMPTIVE_OS_QUERYCONTEXTATTRIBUTES" waits="748" averageWaitTime="0" maxWaitTime="3"/><wait waitType="PREEMPTIVE_OS_DELETESECURITYCONTEXT" waits="739" averageWaitTime="0" maxWaitTime="4"/><wait waitType="PREEMPTIVE_OS_DISCONNECTNAMEDPIPE" waits="739" averageWaitTime="0" maxWaitTime="2"/><wait waitType="PREEMPTIVE_OS_GETPROCADDRESS" waits="422" averageWaitTime="0" maxWaitTime="0"/><wait waitType="PREEMPTIVE_OS_FILEOPS" waits="150" averageWaitTime="6" maxWaitTime="323"/></byCount><byDuration><wait waitType="PREEMPTIVE_OS_GENERICOPS" waits="17" averageWaitTime="3950" maxWaitTime="64227"/><wait waitType="PREEMPTIVE_OS_PIPEOPS" waits="1" averageWaitTime="64301" maxWaitTime="64301"/><wait waitType="PREEMPTIVE_OS_LIBRARYOPS" waits="1" averageWaitTime="34932" maxWaitTime="34932"/><wait waitType="PREEMPTIVE_OS_COMOPS" waits="1" averageWaitTime="3222" maxWaitTime="3222"/><wait waitType="PREEMPTIVE_OS_CRYPTACQUIRECONTEXT" waits="61" averageWaitTime="46" maxWaitTime="2826"/><wait waitType="PREEMPTIVE_OS_CRYPTOPS" waits="3" averageWaitTime="815" maxWaitTime="2073"/><wait waitType="PREEMPTIVE_OS_AUTHENTICATIONOPS" waits="4536" averageWaitTime="0" maxWaitTime="266"/><wait waitType="PREEMPTIVE_OS_QUERYREGISTRY" waits="1627" averageWaitTime="0" maxWaitTime="26"/><wait waitType="PREEMPTIVE_OS_FILEOPS" waits="150" averageWaitTime="6" maxWaitTime="323"/><wait waitType="PREEMPTIVE_OS_WRITEFILEGATHER" waits="11" averageWaitTime="41" maxWaitTime="168"/></byDuration></preemptive></topWaits><cpuIntensiveRequests><request sessionId="57" requestId="0" command="EXECUTE" taskAddress="0x3deb278" cpuUtilization="0" cpuTimeMs="16"/></cpuIntensiveRequests><pendingTasks></pendingTasks><blockingTasks></blockingTasks></queryProcessing>
2016-06-06 16:52:59.543	instance	io_subsystem	1	clean	<ioSubsystem ioLatchTimeouts="0" intervalLongIos="0" totalLongIos="0"><longestPendingRequests></longestPendingRequests></ioSubsystem>
2016-06-06 16:52:59.543	instance	events	0	unknown	<events><session startTime="2016-05-19T14:21:05.583" droppedEvents="0" largestDroppedEvent="0"><RingBufferTarget truncated="0" processingTime="0" totalEventsProcessed="0" eventCount="0" droppedCount="0" memoryUsed="0"></RingBufferTarget></session></events>