Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

3
votes
0answers
61 views

how does CXPACKETS relates to CXCONSUMER wait types?

when running a delete query to a table that has many foreign keys I get the following waits. I noticed that I have both CXPACKETS and CXCONSUMER wait types at the same time during of the execution ...
6
votes
1answer
172 views

Strange blocking on readable secondary after reboot

We are running an SQL Server 2014 Availability Group with 3 replicas, one synchronous (SQL2 for this matter) and one asynchronous secondary replica. We also configured read-only routing to the ...
1
vote
1answer
83 views

Where/When is the use of RBAR buffering related to ASYNC_NETWORK_IO waits negotiated between the Client/SQL Server [closed]

Where/When is the use of RBAR buffering related to ASYNC_NETWORK_IO waits negotiated between the Client/SQL Server, or Is it always utilized as in that is just how SQL Server spools data to the client?...
4
votes
2answers
106 views

CXPACKET wait on SQL Server

Recently I am experiencing performance issue on a SQL Server database and I have two queries that show me the wait as shown below, one of the databases on this server is a member of Always On replica, ...
4
votes
2answers
141 views

Help with wait type CHECK_TABLES_THREAD_BARRIER

I have a 2016 SQL Server freshly built - 13.0.5149.0 - OS 2016 (Microsoft Windows NT 6.3 (14393)). The environment is VMWare. No SQL Server HA configuration. It has a problem where CheckDB "stops" ...
1
vote
1answer
38 views

Why does a query on a local instance with local database files cause NETWORK_IO waits?

I have followed this tutorial to get the wait stats of a particular query while it is 'in flight' using extended events. Whilst the tutorial refers to an index rebuild, I just did a select statement ...
1
vote
1answer
217 views

Slow Query with RESOURCE_SEMAPHORE wait info [closed]

Hi I see lot of RESOURCE_SEMAPHORE as wait_info when i run sp_whoisactive in my sql database server.I see few CXCONSUMER as well.I learned that CXCONSUMER is because of parallel query execution.How to ...
3
votes
0answers
190 views

PAGEIOLATCH_EX Wait on Target table of insert..select query

We are running SQL Server 2012 on Windows 2008 R2 with 256GB RAM and 90% memory allocated to SQL Server. No other Apps run on the server. I have a scenario where I’ve to copy a Source (staging) table ...
0
votes
1answer
18 views

Wait Stats on Microsoft Azure test

On a particular test we're doing with Microsoft Azure, we are seeing a wait_category called: PAGE LATCH (non-I/O) that we're unfamiliar with. In our test, CaptureSeconds = 1595 and for PAGE LATCH (...
1
vote
1answer
766 views

Why is my SQL Server reporting CLR_AUTO_EVENT waits when I don't even use SQL CLR?

When I run this query, I see high wait time for CLR_AUTO_EVENT: SELECT * FROM sys.dm_os_wait_stats WHERE wait_type IN ( 'CLR_AUTO_EVENT', 'CLR_CRST', 'CLR_JOIN'...
1
vote
0answers
81 views

MySQL wait events start and end time

How to get wait events exact start and exact end time in MySQL 5.7 version ? Any help will be helpful to us
5
votes
1answer
199 views

Does sys.dm_os_waiting_tasks include RUNNABLE threads?

From MSDN: Returns information about the wait queue of tasks that are waiting on some resource. Is CPU considered a resource in this definition? I initially thought it's not, but what made me ...
0
votes
1answer
2k views

Oracle wait type enq: TM - contention

I’m administrating an Oracle 12c database which shows the wait type: enq: TM – contention in certain time periods (up to 30% of total waits). During this periods the applications does not allow new ...
7
votes
2answers
354 views

Why would I get PREEMPTIVE_OS_AUTHORIZATIONOPS using tSQLt when Working at Home?

I have an SSDT project containing tSQLt unit tests. I always find when working at home that publishing this and running all tests (from a post deploy script) is problematic (against both localdb and ...
1
vote
0answers
81 views

Negative values for Wait Time per Core per Sec in sp_BlitzFirst

I'm a developer in a firm that doesn't want to spend money on a DBA so i'm on my own with keeping the SQL servers healty. I checked some of Brent's videos and decided to check how hard the SQL servers ...
0
votes
2answers
4k views

Identify Queries causing High Average Wait time (SQL Server 2008) [closed]

Recently we started to receive many alerts from SCOM : Alert: SQL 2008 DB Average Wait Time is too high. When reviewed the graph for this counter, we found that the alert is always ranging between 1,...
5
votes
2answers
8k views

Huge “Network I/O” type resource waits

We have an in-house application, developed in WinDev, that uses a Microsoft SQL Server 2012 SP2 (build 11.0.5058) database running on Windows Server 2008R2. We are experiencing performance issues. At ...
4
votes
2answers
577 views

tsql Process running forever… what is it doing

Trying to create a big test DB, I have an SP that I created that uses a predefined list of tables (35 That I'm interested in) and multiplies the data in each one of them. In other words, A table ...
3
votes
1answer
63 views

Nightly EDW Build Wait Stats

We are trying to determine our best MAXDOP and Cost Threshold settings for an EDW box. I setup a job to clear the wait stats before the build started and then capture the wait stats after the build ...
3
votes
2answers
580 views

Wait types with queries

I have a query to find the wait types in which my system is suffering from. WITH [Waits] AS (SELECT [wait_type], [wait_time_ms] / 1000.0 AS [WaitS], ([wait_time_ms] - [...
5
votes
1answer
3k views

High Log Flush Wait Time and filling Transaction logs [closed]

I have problem with Log Flush Wait Time - as of now, they are averaging at 30000ms (min 0 max 35000); database itself is performing poorly, transaction logs are also growing beyond reasonable ...
4
votes
1answer
1k views

SQL Server performance issue (CXPACKET wait type)

I have a performance issue with production SQL Server 2012 which has 24 cores and 32 GB of RAM. From wait type I can see CXPACKET is on the top based on the following SQL Server metrics, should I ...
6
votes
1answer
374 views

SQL Server CLR procedures and SQLCLR_QUANTUM_PUNISHMENT

I have a CLR stored procedure running on SQL Server 2014. This stored procedure gathers some data (generally numbering in the tens-of-thousands of rows range), runs some computations on the data, ...
1
vote
2answers
148 views

SQLserver WaitType : EXECUTION_PIPE_EVENT_INTERNAL

One of our production Microsoft SQL Server 2014 (SP1-CU5) experiencing performance issue so I ran the ShortPeriodWaitStats.sql script from Paul S. Randal, SQLskills.com and the top WaitType is ...
3
votes
1answer
956 views

What is the DPT_ENTRY_LOCK wait type?

We have a SQL Server 2016 Availability Group with 3 servers. Twice in the past 5 days we have had performance issues. 8a is our primary server, and 8b and 8c are the secondaries. We started having ...
0
votes
0answers
890 views

LCK_M_S causing blocking with session id < 50

We are experiencing high LCK_M_S wait times conflicting with idle blocking sessions whose ids are less than 50. The waiting session is always a Select statement within a stored procedure. The ...
2
votes
0answers
2k views

Troubleshooting Preemptive_Xe_Dispatcher wait type

While looking for a reason behind slowness of the database server I accidentaly came across Preemptive_Xe_Dispatcher wait. Could you please suggest how this type of wait should be troubleshooted? ...
2
votes
1answer
1k views

Clarification on signal wait percent

I'm experiencing high Signal Wait percent while CPU utilisation is very low at one of our servers. I have gone through numerous articles online about Signal Waits and understand that it is the time ...
0
votes
1answer
630 views

ACCESS_METHODS_DATASET_PARENT Problem

We had an AlwaysOn Async replica we had some problems so we deleted the availability group After that the database and CPU seem to work abnormally It's very slow and we have waittype = LATCH_EX , ...
3
votes
1answer
488 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 ...
1
vote
2answers
305 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 ...
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 ...
1
vote
1answer
98 views

Scalability of concurrent log restore for different databases on SQL Server

Are there any locks or resource blocking/contention (beyond IO capacity) between two sessions, which restore logs for two different databases with norecovery? Same about restore logs with recovery? ...
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
795 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 ...
0
votes
1answer
111 views

Large Sleep counts causing THREADPOOL wait?

When I run sp_whoisactive throughout the day, I usually see we have around 500-700 connections on our server. We are occasionally getting threadpool waits and high signal wait ratios (so we are thread ...
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
3answers
1k views

Reading ASYNC_NETWORK_IO wait stats

I was troubleshooting some bad performance for a client yesterday and identified that there was a missing index for a particular set of queries that were heavily used (this was for an ERP applicaton: ...
4
votes
2answers
800 views

PAGEIOLATCH wait type

I'm trying to get a better understanding of how serious this wait type is. I'm using sp_WhoIsActive stored procedure. Our DBAs don't seem to know much about this wait type. But I often see it when I ...
4
votes
1answer
586 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 ...
9
votes
1answer
456 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 ...
1
vote
0answers
46 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 ...
1
vote
0answers
206 views

How can I reduce OLEDB wait type in a SSAS linked server connection?

I have recently created a linked server to one of my SSAS servers. when I go ahead and open the catalogs, to see which ssas databases I have there, I use the following procedure: create procedure ...
3
votes
0answers
208 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 ...
5
votes
1answer
102 views

Different Disk Drive Same Backend Storage

I have a database running on a VM which is getting hammered during a large load, in particular I can see waits of WRITELOG happening. My initial thought is to split the files out on to their own ...
2
votes
1answer
180 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
316 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
271 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
405 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 ...
3
votes
2answers
8k 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 ...