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
0 votes
1 answer
69 views

What causes SQL Server worker thread to voluntarily yield [duplicate]

I'm reading about SOS_SCHEDULER_YIELD and after lots of readings about internals, like clock intervals, threads and quantums I've got ok understanding of how it works. But still one fundamental ...
user avatar
0 votes
1 answer
61 views

How does blocking increase sos_scheduler_yield wait?

What is the correlation between blocking and CPU? Because I am reaching the conclusion that the blocking is causing my sos_scheduler_yield wait type to increase, but I would like to understand the ...
user avatar
  • 585
10 votes
1 answer
564 views

Non-yielding scheduler error in large memory server if the lock pages in memory option is disabled

We are seeing a lot of Non-yielding scheduler errors on a SQL Server VM. Reading this link on Microsoft Docs, it's recommended we enable Lock Pages in Memory. I do not have much experience with Lock ...
user avatar
0 votes
0 answers
77 views

Aurora MySQL 5.7 waits

Have an Aurora MySQL 5.7 DB that is currently basically just the destination for a data replication stream (home-grown). The new application that will provide a UI into the data isn't yet live. We're ...
user avatar
  • 414
0 votes
1 answer
153 views

Wait state: Statistics

We have 200 databases in one instance of MariaDB with a total of 370.000 tables, since we upgraded to MariaDB 10.5.11, we see most of time passed by mysql is in wait states: Statistics. According ...
user avatar
0 votes
0 answers
194 views

SQL Server Performance of a user defined table type

I have a stored procedure that is being passed a User Defined Table Type to retrieve 1 to many records back from a table that is filtered down by a join instead of building out dynamic sql to generate ...
user avatar
  • 1,731
1 vote
1 answer
107 views

signal_wait_time_ms > wait_time_ms for SOS_SCHEDULER_YIELD in sys.dm_os_wait_stats

Question regarding SOS_SCHEDULER_YIELD when using sys.dm_os_wait_stats: Should signal_wait_time_ms not be exactly the same as wait_time_ms for the SOS_SCHEDULER_YIELD wait type? For other wait types ...
user avatar
  • 585
1 vote
1 answer
153 views

Sp_BlitzWho can get blocked...?

I was playing around with sp_BlitzWho and got blocked by another session. The session was from the DB were sp_BlitzWho is... I was planning to automate it and save the results to a table, so I could ...
user avatar
  • 585
0 votes
1 answer
202 views

Is this an excessively large 'Lock' time and is it indicative of a problem?

Given: Production environment Application Servers using Hibernate SQL Server Studio Manager v17.5 SQL Server 2016 in a clustered HAG setup SQL Servers do NOT have the Query Store feature enabled The ...
user avatar
  • 443
0 votes
1 answer
127 views

High Latch Wait time with Logon trigger enabled while full backups are running

I have 14 production servers with SQL 2016 standard version, 128 gb of RAM, 16 CPU and SSD drives. Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461) - 13.0.5865.1 (X64) Oct 31 2020 02:43:57 ...
user avatar
9 votes
2 answers
216 views

Instance performance variations around SOS_SCHEDULER_YIELD behavior during single-row INSERT statements

I have a shared SQL Server 2016 dev instance in a VM which has poor INSERT performance compared to a shared SQL Server 2012 dev instance on an (supposed to be) identical VM. It also compares poorly ...
user avatar
  • 6,582
1 vote
1 answer
151 views

Job waiting for resources - is it better to kill it or let it wait

Our server is very busy sometimes and I can see my jobs are waiting for resources. Is it better to kill it and start it after e.g. one hour or let it wait until it finishes? Right now there is a rule ...
user avatar
  • 585
-1 votes
1 answer
123 views

High PREEMPTIVE_OS Wait Stats [duplicate]

How to reduce PREEMPTIVE_OS_AUTHENTICATIONOPS wait type stats in SQL Server, any advice on it would be greatly appreciated. Thanks
user avatar
  • 1
1 vote
0 answers
169 views

What would cause slow disk access on an AWS EBS SQL Server? (PAGEIOLATCH_SH)

I have an EC2 instance (r5a.4xlarge) which should be able to do 2Gbps+ in EBS access. This machine has a 7TB mounted gp2 volume, with 16,000 IOPS, which is mounted and contains a SQL Server database ...
user avatar
1 vote
0 answers
118 views

SQL server 2016 (13.0.5026.0) Standard. High CPU ussage even without user queries

So SQL server CPU ussage is around 20% even when no users are making queries I query the sysprocesses, and i can see some internal spid's: select * from master.dbo.sysprocesses order by cpu desc Any ...
user avatar
1 vote
0 answers
99 views

Reduce file header block

I'm in the middle of my first Statspack analysis, and one of the most time consuming events is Buffer Busy Waits (around 30% time). I checked out the Buffer Wait statistics, and it seems like the most ...
user avatar
  • 61
4 votes
1 answer
326 views

What is the difference between ASYNC_IO_COMPLETION and IO_COMPLETION wait types?

Could someone explain what is the main difference between these two wait types?
user avatar
  • 1,163
12 votes
1 answer
503 views

Prevent THREADPOOL waits due to idle worker thread trimming

After reading Unusual THREADPOOL Waits by Josh Darnell, a Twitter user mentioned there is an undocumented trace flag to prevent trimming idle workers: The idea is that once SQL Server has created ...
user avatar
  • 69.1k
3 votes
1 answer
459 views

Why do I have THREADPOOL waits even though I have threads available?

We are seeing application time outs and with help of the First Responder Kit, I have captured details. One of the things that caught my attention was: Poison wait detected: Thread Pool So I ran ...
user avatar
  • 2,879
1 vote
1 answer
181 views

How to dig deeper into ASYNC_NETWORK_IO waits

I've been handed a graph that comes from SolarWinds showing top wait events broken down by day. The last 2 days there's a part of the daily color coded bar representing a total of 3 hours for ...
user avatar
  • 85
4 votes
1 answer
2k views

What steps can I take to determine if my server is under-provisioned for memory?

My server is running SQL Server 2016. The environment is fairly high workload with a lot of write transactions and reading of the data throughout the day. I have a hunch the server isn't provisioned ...
user avatar
  • 24.1k
1 vote
2 answers
246 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 ...
user avatar
  • 1,163
2 votes
1 answer
66 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 ...
user avatar
  • 97
1 vote
1 answer
956 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 ...
user avatar
8 votes
2 answers
310 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 ...
user avatar
  • 131
1 vote
1 answer
257 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 ...
user avatar
1 vote
0 answers
81 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 ...
user avatar
1 vote
2 answers
2k 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 ...
user avatar
  • 113
6 votes
1 answer
2k 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 ...
user avatar
  • 2,193
4 votes
1 answer
1k 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 ...
user avatar
6 votes
1 answer
713 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 ...
user avatar
1 vote
1 answer
170 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?...
user avatar
  • 407
4 votes
2 answers
601 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, ...
user avatar
  • 379
4 votes
2 answers
558 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" ...
user avatar
  • 148
1 vote
1 answer
78 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 ...
user avatar
  • 1,450
1 vote
1 answer
837 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 ...
user avatar
  • 1,285
3 votes
0 answers
1k 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 ...
user avatar
  • 136
0 votes
1 answer
39 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 (...
user avatar
1 vote
1 answer
3k 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'...
user avatar
1 vote
0 answers
108 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
user avatar
5 votes
1 answer
420 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 ...
user avatar
  • 193
0 votes
1 answer
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 ...
user avatar
  • 868
6 votes
2 answers
720 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 ...
user avatar
  • 77.9k
1 vote
0 answers
177 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 ...
user avatar
1 vote
2 answers
7k 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,...
user avatar
8 votes
2 answers
22k 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 ...
user avatar
  • 183
3 votes
2 answers
933 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 ...
user avatar
  • 1,051
3 votes
1 answer
71 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 ...
user avatar
3 votes
2 answers
1k 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] - [...
user avatar
4 votes
1 answer
7k 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 ...
user avatar
  • 41