Questions tagged [wait-types]

Whenever the server is running queries, it’s tracking how much time it spends waiting on bottlenecks. These wait statistics are the easiest way to identify your bottleneck.

Filter by
Sorted by
Tagged with
3
votes
2answers
10k views

CXPACKET SUSPENDED and NULL wait type

I was was running the query in this article: http://sqlity.net/en/708/why-cxpacket-waits-are-not-your-performance-problem/ To see what my threads were waiting on in regards to a suspended query with ...
9
votes
1answer
567 views

Blocked sessions waiting with PAGELATCH_* wait types?

Edited : Why are sessions reporting being blocked but waiting with PAGELATCH_*, and not LCK_M_ related wait types? I previously assumed that SQL server would only report blocking sessions in the ...
3
votes
1answer
595 views

DIRTY_PAGE_POLL without activated Indirect Checkpoint

Since I encounter some performance issues on my SQL Server 2014 (12.0.2370.0 (X64)) I'm dealing with enormes amounts of different wait types. One of the top 5 wait type is "DIRTY_PAGE_POLL". I've ...
2
votes
2answers
403 views

SOS_SCHEDULER_YIELD - Prevent a Proc from giving up the Processor Time

I've got a process which about once a month at times unrelated (or as far as we can tell) goes into SOS_SCHEDULER_YIELD State. This process blocks our main data entry pipeline and brings the entire ...
1
vote
0answers
56 views

How to identify a process that has been cancelled?

How can you identify a process that has been cancelled? for example: I was running this dynamic sql - via a linked server: declare @sql nvarchar(max) declare @i int declare @z int DECLARE @vCrlf ...
6
votes
1answer
2k views

How to solve blocking on tempdb

I am having tempdb contention (I guess, at least) on one of the servers (SQL Server 2008 R2). Both the blocker and the waiters (10 of them) has PAGELATCH_EX wait type on database tempdb and all ...
3
votes
1answer
2k views

WAIT STATS - high Wait Time on ASYNC_IO_COMPLETION

I currently look at my wait stats. I followed best practices explained by Paul Randal and go through the documents he references. Looking at the statistic slices I take once / day for a while now ...
2
votes
0answers
930 views

How can I have long waits on Tracewrite, when I haven't run Profiler?

I recently setup a SQL Server which is used only ot run a sharepoint Server. I love to query my wait stats, just to see what we are waiting on. Most of the wait stats are fine, and I know where they ...
5
votes
1answer
5k views

What is the Wait Type “QDS_Shutdown_Queue” referring to?

I'm gathering wait types on a load test environment, and the second biggest wait type is QDS_SHUTDOWN_QUEUE. The stats are coming from sys.dm_os_wait_stats, but I can't find any documentation that ...
4
votes
1answer
638 views

How to find out how much i/o resources a task, session, request or transaction is currently using?

How to find out how much i/o resources a task, session, request or transaction is currently using? by task I mean the DMV called DMV sys.dm_os_tasks. by session I mean the DMV sys.dm_exec_sessions ...
3
votes
0answers
221 views

PAGEIOLATCH_SH and WRITELOG waits

We have a slow overnight process which I was asked to look at with the top waits being PAGEIOLATCH_SH and WRITELOG I have been collecting information from sys.dm_io_virtual_file_stats and can see ...
2
votes
1answer
189 views

Non Buffer Latch - Forefront Identity Manager

We are using Forefront Identity Manger and we are doing an initial load to which we are experiencing slow performance. I always start with wait types for such things and can see %30 CXPACKET, %25 ...
3
votes
1answer
343 views

Excessive CPU use in vendor SQL Server DB

Background: I've got a SQL Server 2012 DB provided by a vendor so modification of queries and tables is limited. We do own the DB though, so we can add and maintain indexes. Indexes haven't been ...
7
votes
1answer
307 views

Disk I/O and PAGEIOLATCH_XX

We have been having CPU issues with one of our servers lately and while we have been looking into this we have also noticed queries running slowly with waits of PAGEIOLATCH_XX. In particular, a ...
2
votes
2answers
450 views

High SOS_SCHEDULER_YIELD and Signal Waits

I have run Paul Randals wait script and can see SOS_SCHEDULER_YIELD is at 60% with an average signal wait of 10ms. I have also run a script to find out the what percentage of my waits are signal ...
4
votes
3answers
396 views

Why is sql server waiting for acknowledgment?

I have an application that often request a big set of rows [about 20K] from a sql view, in the mean time other applications can't update the table from where the data comes from, the update query's ...
4
votes
1answer
372 views

What is the PREEMPTIVE_REENLIST wait type in Microsoft SQL Server 2008 R2

This is not urgent, just curiosity. QUESTION: I was trying to troubleshoot a slow server today, and I came across a high figure for a wait type called PREEMPTIVE_REENLIST. I'm not able to find this ...
1
vote
2answers
2k views

PAGEIOLATCH_SH and LCK_M_IX wait type

I'm seeking for a resolution into wait types I observe on one of my servers. I've been keeping wait statistics for a long time and can positively say that the top two are PAGEIOLATCH_SH and LCK_M_IX ...
4
votes
1answer
13k views

resource wait SQLCLR - wait_type CLR_SEMAPHORE by SPID 0

UPDATE In the comments under Aaron's post is an extensive explanation why I want to find this out, please read it if you want to know. The system is running some ETL, reporting services and a ...
1
vote
1answer
980 views

sql server service broker

For the last few days the service broker command which run from master database is consuming much more RESOURCES than before. The Impact on the Database is high (from the bad side), It causes for ...
4
votes
2answers
583 views

Can WAITFOR increase CXPACKET waits?

If I have some T-SQL code like this: While @done<1 Begin WAITFOR DELAY '00:00:30'; Set @done=<some select statement> End Is this likely to increase waits for CXPACKET? Presumably the other ...
3
votes
3answers
3k views

Collecting Wait Stats

We currently use a monitoring tool which shows us our top wait stats either by number of waiting tasks or total wait time. Below are the wait stats by number of tasks waiting and also wait time per ...
3
votes
2answers
12k views

Ideal values for wait time in SQL server

This is related to my own post here The SQL server starts everyday at 8:30 AM and stops around 9:30 PM.( So wait times will reset everyday) SQL server will be used by around 60 users simultaneously(...
8
votes
2answers
2k views

Intermittent RESOURCE_SEMAPHORE_QUERY_COMPILE Wait Stats

I'm trying to troubleshoot some intermittent CPU spikes that we're witnessing on one of our production SQL Servers. We're running SQL Server 2008 R2 Standard Edition with 28 GB RAM and 4 CPU cores. ...
8
votes
2answers
2k views

SQL Server performance: PREEMPTIVE_OS_DELETESECURITYCONTEXT dominant wait type

I received a call yesterday from a customer who was complaining about high CPU usage on their SQL Server. We're using SQL Server 2012 64 bit SE. Server is running Windows Server 2008 R2 Standard, 2.20 ...
6
votes
1answer
3k views

What does the wait type PREEMPTIVE_XE_CALLBACKEXECUTE mean?

I have two instances in the last week where a DROP command for an Extended Events session has taken over a day to complete, sitting with the wait: PREEMPTIVE_XE_CALLBACKEXECUTE. Can anyone explain ...
7
votes
3answers
17k views

Investigating high SQL waits, especically LCK_M_U

Recently I have been struggling with SQL server performance and although we have fixed a huge multitude of basic errors in the config it is still performing badly. To clarify it is not overall ...
2
votes
4answers
8k views

Select from linked server to local server causing waits on both sides

The queries in question are here: http://pastie.org/4833351 Run individually, each of these queries runs through (albeit slow). Run as a group, it just sits there, showing either OLEDB waits on the ...
5
votes
1answer
3k views

Is the SQLTRACE_FILE_BUFFER wait type documented? What does it indicate?

We run a background task on one of our servers which polls sys.dm_exec_requests for long-running queries, gathering diagnostic information when it finds them, including wait type. I'm seeing a number ...
6
votes
1answer
925 views

Attempting to tune a Sharepoint site using SQL Server Waits and Queues

I'm pretty new to this performance tuning with Waits and Queues thing - fascinating, but also not always all that intuitive... Right now, a customer of mine has a SQL Server 2008 64-bit Enterprise ...
4
votes
3answers
6k views

PageIOLatch_Sh waits noticed after tuning an index

Lately, a lot of queries to the database are timing out. While investigating I found that queries are in the suspended state with wait_type as PageIOLatch_Sh. I have not noticed so many queries being ...
3
votes
1answer
2k views

async_network_io issues with BIDS

This is related to my previous question drastically different runtimes in BIDS and the SSRS Web Portal. I am struggling with refreshing some reports in BIDS because they run forever. I have now ...
34
votes
1answer
31k views

SLEEP_TASK Wait Type in SQL Server - What does it indicate?

I haven't seen the SLEEP_TASK wait type before, and today I seem to be getting a ton of them. I'm not the official DBA, just a SQL Server developer who knows some DBA stuff. We upgraded our servers ...