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.
112
questions
0
votes
0
answers
34
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) (...
- 1,999
0
votes
1
answer
150
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 ...
- 89
1
vote
3
answers
225
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-...
- 133
1
vote
0
answers
81
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 ...
0
votes
1
answer
121
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 ...
- 1
6
votes
3
answers
572
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 ...
- 61
-2
votes
1
answer
60
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 ...
- 3,225
2
votes
1
answer
988
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 ...
- 23
0
votes
2
answers
222
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 ...
- 3,225
0
votes
1
answer
180
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 ...
0
votes
1
answer
145
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 ...
- 765
10
votes
1
answer
2k
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 ...
- 3,313
0
votes
1
answer
411
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 ...
0
votes
0
answers
482
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 ...
- 1,751
1
vote
1
answer
215
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 ...
- 765
1
vote
1
answer
286
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 ...
- 765
0
votes
1
answer
522
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 ...
- 463
0
votes
1
answer
182
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 ...
- 1,999
10
votes
2
answers
255
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 ...
- 6,592
1
vote
1
answer
330
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 ...
- 765
-1
votes
1
answer
265
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
- 1
1
vote
0
answers
251
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 ...
- 11
1
vote
0
answers
198
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 ...
- 203
1
vote
0
answers
168
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 ...
- 61
4
votes
1
answer
422
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?
- 1,233
12
votes
1
answer
574
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 ...
- 80.3k
3
votes
1
answer
847
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 ...
- 2,929
2
votes
2
answers
730
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 ...
- 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 ...
- 32k
1
vote
2
answers
500
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 ...
- 1,233
2
votes
1
answer
187
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 ...
- 107
1
vote
1
answer
1k
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 ...
- 19
8
votes
2
answers
444
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 ...
- 131
2
votes
1
answer
376
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 ...
1
vote
0
answers
93
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 ...
- 141
1
vote
2
answers
3k
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 ...
- 113
8
votes
1
answer
3k
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 ...
- 2,279
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 ...
- 15.6k
6
votes
1
answer
868
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
1
answer
207
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?...
- 453
4
votes
2
answers
816
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, ...
- 409
4
votes
2
answers
591
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" ...
- 148
1
vote
1
answer
124
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,741
1
vote
1
answer
931
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 ...
- 1,295
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 ...
- 136
0
votes
1
answer
42
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 (...
- 21
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'...
- 353
1
vote
0
answers
110
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
- 57
5
votes
1
answer
471
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 ...
- 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 ...
- 938