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
40 views

Techniques for increasing speed of simple select query using DBI R package to MS SQL server where ASYNC_NETWORK_IO is the bottleneck

I am executing a simple select query similar to: DBI::dbGetQuery(db, "SELECT date, x, y, FROM table") where the table contains a large amount of financial data. The db is a MS SQL server ...
nvt's user avatar
  • 1
0 votes
0 answers
58 views

SQL Server 2019 Log shipping standby Server stops responding and has a big "Index Searches/Sec" and "Locks" surge (running queries at 0)

We have 10 SQL Server 2019 production servers all configured the same way. We do log shipping (via scripts, not built-in SQL), to warm standby servers. Microsoft SQL Server 2019 (RTM-CU16-GDR) (...
Danielle Paquette-Harvey's user avatar
1 vote
1 answer
296 views

How to Get a PAGELATCH_SH in sql SERVER tempdb

I am learning memory optimization in SQL server . On the way I learned about PAGELATCH WAIT .But I don't know how it happens and I don't know how to Test it with optimisation . Can someone give me an ...
Viz Krishna's user avatar
1 vote
3 answers
1k views

Troubleshooting SOS_Scheduler_Yield waits

I am troubleshooting a particular server and collected the following wait stats information: The script I used to collect the information is here: https://www.sqlskills.com/blogs/paul/wait-statistics-...
Sicilian-Najdorf's user avatar
1 vote
0 answers
102 views

High parallel tempdb disk usage

We recently added some secondary tempdb files to reduce allocation contention, this seems to have worked; however we are now getting lots of slowdowns related to I/O (WRITELOG and SQLTRACE_FILE_BUFFER ...
Richard Harris's user avatar
0 votes
1 answer
293 views

ADF: TempDb to user table very slow

I have a Azure Data Factory Data Flow that writes ~250 Mio. rows (~100 GB) to a MS Sql Server 2019. Currently, I have set the 'Use TempDB' option to true: The first part of the job, i.e. writing all ...
guid's user avatar
  • 1
6 votes
3 answers
637 views

How to write script to kill MS Office database locks in MSSQL

I've recently inherited a MSSQL database as our DBA has moved on to greener pastures. I'm not a DBA and have limited to moderate SQL knowledge but have had to absorb some of his duties and have an ...
user2631683's user avatar
-2 votes
1 answer
88 views

What does suspended, with wait time of 450 seconds on CXPACKET indicate?

I am monitoring the requests and sessions using dmv's. I am seeing a session that is having suspended status, which means it is waiting for something like IO. Yet the wait time is showing as 450000 ms ...
variable's user avatar
  • 3,541
2 votes
1 answer
2k views

Query stuck on 0% ROLLBACK for 2 days

kill with statusonly returns: transaction rollback in progress. Estimated rollback completion: 0%. The query creates a temp table and runs on local server without using xp_cmdshell. CPU & reads ...
Kati's user avatar
  • 23
0 votes
2 answers
354 views

Does ASYNC_NETWORK_IO wait types result in thread locking until entire result is consumed by client application?

ASYNC_NETWORK_IO wait types are mostly caused because the client application cannot process the data that arrives from SQL Server fast enough. As the query executes, and as the results start getting ...
variable's user avatar
  • 3,541
0 votes
1 answer
258 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 ...
crookedteeth's user avatar
0 votes
1 answer
213 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 ...
xhr489's user avatar
  • 805
11 votes
1 answer
3k 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 ...
Brendan McCaffrey's user avatar
0 votes
1 answer
702 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 ...
Lionel Enkaoua's user avatar
0 votes
0 answers
685 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 ...
Aaron's user avatar
  • 1,771
1 vote
1 answer
298 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 ...
xhr489's user avatar
  • 805
1 vote
1 answer
340 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 ...
xhr489's user avatar
  • 805
0 votes
1 answer
823 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 ...
D-K's user avatar
  • 503
0 votes
1 answer
211 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 ...
Danielle Paquette-Harvey's user avatar
10 votes
2 answers
273 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 ...
Cade Roux's user avatar
  • 6,602
1 vote
1 answer
425 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 ...
xhr489's user avatar
  • 805
-1 votes
1 answer
391 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
Kash's user avatar
  • 1
1 vote
0 answers
356 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 ...
user113242's user avatar
1 vote
0 answers
253 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 ...
Jack Casas's user avatar
1 vote
0 answers
245 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 ...
Limak's user avatar
  • 61
4 votes
1 answer
530 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?
Rauf Asadov's user avatar
  • 1,253
13 votes
1 answer
603 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 ...
Paul White's user avatar
  • 83.7k
3 votes
1 answer
1k 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 ...
TheGameiswar's user avatar
  • 2,989
2 votes
2 answers
1k 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 ...
SJWales's user avatar
  • 99
5 votes
1 answer
4k 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 ...
J.D.'s user avatar
  • 37.1k
1 vote
2 answers
651 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 ...
Rauf Asadov's user avatar
  • 1,253
2 votes
1 answer
312 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 ...
Gio's user avatar
  • 107
1 vote
1 answer
2k 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 ...
inthenorth's user avatar
8 votes
2 answers
598 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 ...
Mike P.'s user avatar
  • 131
2 votes
1 answer
460 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 ...
sachin-SQLServernewbiee's user avatar
1 vote
0 answers
103 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 ...
PeterDaniels's user avatar
1 vote
2 answers
4k 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 ...
porya ras's user avatar
  • 113
8 votes
1 answer
4k 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 ...
George K's user avatar
  • 2,261
4 votes
1 answer
2k 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 ...
Marcello Miorelli's user avatar
6 votes
1 answer
1k 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 ...
Jogchum Rooda's user avatar
1 vote
1 answer
229 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?...
Ross Bush's user avatar
  • 505
4 votes
2 answers
925 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, ...
Sam's user avatar
  • 409
4 votes
2 answers
613 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" ...
Dan White's user avatar
  • 148
1 vote
1 answer
180 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 ...
SE1986's user avatar
  • 1,950
1 vote
1 answer
997 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 ...
user9516827's user avatar
  • 1,305
3 votes
0 answers
2k 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 ...
user93975's user avatar
  • 136
0 votes
1 answer
43 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 (...
Robert West's 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'...
Tom Pažourek's user avatar
1 vote
0 answers
117 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
Arun Raut's user avatar
5 votes
1 answer
495 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 ...
vorou's user avatar
  • 193