Tell me, exactly where is my SQL Server slowing down?

You can use the following statement to use the sys.dm_os_wait_stats this DMV to get the statistical value of the thread's waiting information (the line thread is waiting and so on, etc.).

 with[Waits] as(SELECT[Wait_type],        [Wait_time_ms]/ 1000.0 as[WaitS],        ([Wait_time_ms]- [Signal_wait_time_ms]) / 1000.0 as[ResourceS],        [Signal_wait_time_ms]/ 1000.0 as[Signals],        [Waiting_tasks_count] as[Waitcount],        100.0* [Wait_time_ms]/ SUM([Wait_time_ms])  Over()  as[Percentage],        Row_number()  Over(ORDER by[Wait_time_ms]DESC)  as[RowNum] fromSYS.Dm_os_wait_statsWHERE[Wait_type]Not in (N ' Broker_eventhandler ',             N ' broker_receive_waitfor ',        N ' Broker_task_stop ',                N ' Broker_to_flush ',        N ' Broker_transmitter ',              N ' Checkpoint_queue ',        N ' chkpt ',                           N ' clr_auto_event ',        N ' clr_manual_event ',                N ' Clr_semaphore ',        N ' dbmirror_dbm_event ',              N ' Dbmirror_events_queue ',        N ' Dbmirror_worker_queue ',           N ' Dbmirroring_cmd ',        N ' Dirty_page_poll ',                 N ' Dispatcher_queue_semaphore ',        N ' Execsync ',                        N ' fsagent ',        N ' ft_ifts_scheduler_idle_wait ',     N ' Ft_iftshc_mutex ',        N ' Hadr_clusapi_call ',               N ' hadr_filestream_iomgr_iocompletion ',        N ' hadr_logcapture_wait ',            N ' Hadr_notification_dequeue ',        N ' Hadr_timer_task ',                 N ' Hadr_work_queue ',        N ' Ksource_wakeup ',                  N ' Lazywriter_sleep ',        N ' Logmgr_queue ',                    N ' Ondemand_task_queue ',        N ' pwait_all_components_initialized ',        N ' Qds_persist_task_main_loop_sleep ',        N ' Qds_cleanup_stale_queries_task_main_loop_sleep ',        N ' Request_for_deadlock_search ',     N ' Resource_queue ',        N ' Server_idle_check ',               N ' Sleep_bpool_flush ',        N ' Sleep_dbstartup ',                 N ' Sleep_dcomstartup ',        N ' Sleep_masterdbready ',             N ' Sleep_mastermdready ',        N ' sleep_masterupgraded ',            N ' Sleep_msdbstartup ',        N ' Sleep_systemtask ',                N ' Sleep_task ',        N ' Sleep_tempdbstartup ',             N ' sni_http_accept ',        N ' Sp_server_diagnostics_sleep ',     N ' Sqltrace_buffer_flush ',        N ' Sqltrace_incremental_flush_sleep ',        N ' sqltrace_wait_entries ',           N ' wait_for_results ',        N ' WAITFOR ',                         N ' Waitfor_taskshutdown ',        N ' wait_xtp_host_wait ',              N ' Wait_xtp_offline_ckpt_new_log ',        N ' Wait_xtp_ckpt_close ',             N ' Xe_dispatcher_join ',        N ' xe_dispatcher_wait ',              N ' xe_timer_event ') and[Waiting_tasks_count]>0)SELECTMAX([W1].[Wait_type])  as[WaitType],    CAST(MAX([W1].[WaitS]) As DECIMAL(16,2))  as[wait_s],    CAST(MAX([W1].[ResourceS]) As DECIMAL(16,2))  as[resource_s],    CAST(MAX([W1].[Signals]) As DECIMAL(16,2))  as[signal_s],    MAX([W1].[Waitcount])  as[Waitcount],    CAST(MAX([W1].[Percentage]) As DECIMAL(5,2))  as[Percentage],    CAST((MAX([W1].[WaitS]) / MAX([W1].[Waitcount])) As DECIMAL(16,4))  as[avgwait_s],    CAST((MAX([W1].[ResourceS]) / MAX([W1].[Waitcount])) As DECIMAL(16,4))  as[avgres_s],    CAST((MAX([W1].[Signals]) / MAX([W1].[Waitcount])) As DECIMAL(16,4))  as[avgsig_s] from[Waits] as[W1]INNER JOIN[Waits] as[W2] on[W2].[RowNum]<=[W1].[RowNum]GROUP by[W1].[RowNum] havingSUM([W2].[Percentage]) - MAX([W1].[Percentage]) <95; --Percentage thresholdGO

Some notes:

SQL Server permanently tracks why the thread being executed is in a wait state. You can get this information from SQL Server, and then narrow the scope of the investigation for performance issues.

Some people start by investigating performance issues and look at what the current threads are waiting for, and then try to figure out why. And the fact is, ' waiting ' is always happening , and the SQL Scheduleing system works that way. Let's take a look at Scheduleing's general workflow.

The thread that is using the CPU (in the running state) will stop when it needs some kind of resource. The thread is moved to an unordered list of threads called suspended. At the same time, the next thread waiting for the CPU in the runnable queue (FIFO) becomes the running state. If a thread in the suspended list gets notified that the resource it wants is available, it goes to the bottom of the runnable queue. In this way, the threads are like clocks from running to suspended, to runnable, to running loops, until the task is completed.

SQL Server tracks the time (called the wait Time) from the running state to the back running state, and when it is spent in the runnable queue (called Signal wait times). We need to get the time in the suspended queue by reducing signal wait times from the entire wait period.

Here is an excerpt of some common wait types and explanations. For more information on wait types, see the original.

• PAGEIOLATCH_XX: Thread is waiting for a data page to read from disk to buffer pool.
• Lck_m_x: The thread is waiting to be given an exclusive lock on a resource.
• Cxpacket: When a thread in a parallel thread executes, the entire query will be block, and the value will be incremented. See the original in detail.
• Writelog: The log Management system waits for log to be flush to disk.

Sources of information

=========================

Wait statistics, or please tell me where it hurts

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

Tell me, exactly where is my SQL Server slowing down?