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
1
vote
2answers
57 views

What is the difference between PAGEIOLATCH_* and IO_COMPLETION?

according to this blog: SQL Server Books online defines IO_COMPLETION as “Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O ...
2
votes
1answer
38 views

Impacts of a change to the Page Verify option

There's no doubt about importance of doing regular integrity checks on production databases. I'm testing the impact of changing the maintenance plans and changing how databases are backed up. First of ...
1
vote
1answer
344 views

Job waiting on CXCONSUMER

We have got a procedure on SQL Server agent jobs and this job started to hang on transaction for many hours with wait stats CXCONSUMER and never complete. The problem is after we noticed that it was ...
0
votes
0answers
22 views

T-Replication Periodically Falls Behind

We have two SQL Server 2016 boxes. One uses transaction replication to the other. There is also a distribution server. The one subscriber has been periodically falling behind in replication and the ...
6
votes
1answer
144 views

SQL Server experiencing THREADPOOL waits while still having plenty of available workers

I recently took a look at my production SQL Server's cumulative threadpool waits, and it's literally days worth of waits. So I started doing some digging. I'm on a 32 core, 64 bit server (running on ...
1
vote
1answer
79 views

What is non-page data structure in SQL Server

I was monitoring one of the SQL Server in my client evnironment and I can see there is momentary blocking on SQL Server sessions with wait type as LATCH_EX and with wait resource as ...
0
votes
0answers
33 views

How do I troubleshoot FILESTREAM_WORKITEM_QUEUE waits in SQL Server 2014?

Env: SQL Server 2014 SP2 on Windows Server 2012 R2. I am seeing our top wait as FILESTREAM_WORKITEM_QUEUE, with average waits near 1s and over 5000 waits during a 2-hour period of time. I haven't ...
1
vote
2answers
468 views

SQL Server RAM usage is increasing and query executions is slowing down with time

I'm using SQL server on a server with 48 GB of RAM. When I start the SQL Server service, it's using 12% of the RAM at first, but after 24 hours it's reaching 90% and query executions begin slowing ...
5
votes
1answer
505 views

Restore stuck in ASYNC_IO_COMPLETION

Very frequently when I restore databases the restore operation gets stuck and the wait type is ASYNC_IO_COMPLETION. It can be in such state for 10-20-30 minutes untill it proceeds to ...
4
votes
1answer
612 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
403 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
117 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
386 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
337 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
50 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
490 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
632 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
28 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
2k 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
104 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
314 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
3k 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 ...
6
votes
2answers
523 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
125 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
6k 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,...
6
votes
2answers
14k 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 ...
3
votes
2answers
758 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
64 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
838 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] - [...
4
votes
1answer
4k 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
3k 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
484 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
169 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
1k 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
1k 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
3k 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
2k 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
1k 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
625 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
431 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
114 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
974 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
2answers
169 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
1k 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
658 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
639 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 ...