Questions tagged [waits]
The waits tag has no usage guidance.
64 questions
0
votes
2
answers
130
views
What wait types would terrible RCSI performance cause?
Read Committed Snapshot Isolation (RCSI) is well-understood. The key way that it can cause performance bottlenecks is if the version chains get too long. Are there any wait type that indicate this ...
- 801
0
votes
1
answer
66
views
MySQL Amazon Aurora - why is CPU low while max vCPU is high?
In MySQL Amazon Aurora, is it normal for CPU to be be low while other metrics are above the max vCPU line?
- 143
0
votes
0
answers
137
views
How to fix high px deq slave session stats in Oracle RDS
We are seeing some high percentage of "px deq slave session stats" waits in our Production database.(this maintained on AWS RDS). Due to which outage is happening many times
from searching ...
- 13
-2
votes
1
answer
456
views
Huge PREEMPTIVE_OS_GETPROCADDRESS Waits From sp_OAMethod. How to debug?
Some queries are hitting my CPU extremely hard. sp_WhoIsActive reports that sp_OAMethod is the cause (the sql_text column points to it) and that it has of huge waits of the ...
- 801
0
votes
1
answer
395
views
High CPU Usage and MySQL Server Inaccessibility during Magento flat reindexing
We use the following configuration:
Engine: Aurora MySQL v8.0
Instance Type: db.r6g.4xlarge
vCPU: 16
Memory: 128.0 GiB
Role: Writer Instance
I'm encountering a critical issue during the reindexing ...
- 1
4
votes
2
answers
3k
views
Does a query that is suspended due to an ongoing ASYNC_NETWORK_IO cause blocking?
I am looking at the dm_exec_sessions and requests DMVs on a production server. There are some queries with status 'suspended', a duration of 12 minutes, and a wait type of ASYNC_NETWORK_IO.
I ...
- 3,499
1
vote
0
answers
172
views
Which SQL Server performance condition DB alert can be used to find "RESOURCE_SEMAPHORE" waits?
I'm trying 2 SQL Server Alerts:
SQL Server:Memory Manager / Memory Grants Pending / Waits in progress / rises above / Value: 5
SQL Server:wait Statistics / Memory grant queue waits / Waits in progress ...
- 111
0
votes
1
answer
303
views
Diagnose the exact cause of WAIT_ON_SYNC_STATISTICS_REFRESH
We are currently facing query timeout issues with our online API .
The related request is an ad hoc select query referencing two tables.
In Redgate, we see that each time the request is blocked, it's ...
4
votes
1
answer
1k
views
Extended event - slow queries and waits
I have an extended event which filters on my slow queries. I have created the following script for it.
CREATE EVENT SESSION [SlowQueriesAndStatementsLargerThan3Seconds] ON SERVER
ADD EVENT sqlserver....
- 2,482
0
votes
2
answers
569
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,499
0
votes
1
answer
249
views
XE_SERVICES_RWLOCK in top waits
We start facing some uncommon waits after the last release. XE_SERVICES_RWLOCK waits starts to appear in our top waits. Investigation shows that this waits happen during insert to our main OLTP tables....
- 1,533
0
votes
2
answers
563
views
PAGEIOLATCH wait and Page Life Expectancy very low
Training for SQL Server, has following scenario:
SQL Server on Azure VM, DS-series machine, it has 32 GB of RAM
Server has below performance conditions:
- very high PAGELATCH_IO waits
- average Page ...
- 5,964
2
votes
1
answer
748
views
What can cause delay in SELECT statement execution and high ASYNC_NETWORK_IO even with "disregard results after execution"
We are facing performance problems on our SQL Servers.
7 servers, all running the same application, all have the same problem.
SELECT @@VERSION
Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0....
- 2,845
2
votes
1
answer
2k
views
How to determine right value for innodb_sync_array_size
How do I determine the right value for innodb_sync_array_size in MySQL? We have version 5.7 deployed on AWS RDS with 32 vCPUs, and it sees about 26 concurrent threads on average. When I measure waits ...
- 223
2
votes
2
answers
3k
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 ...
- 109
5
votes
1
answer
5k
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 ...
- 39.5k
2
votes
2
answers
2k
views
dbcc checkdb runs for interminably long time, 50% of the time
I've got a nightly maintenance job (largely taken from the ola hallengren maintenance package) that runs database backups and runs an an integrity check on every database.
The integrity check part is ...
- 21
0
votes
0
answers
210
views
Why would sys.dm_exec_requests show a query in Database A blocking a query in Database B when neither query references the same tables?
I have a query I use in-house to monitor slow running processes in the data layer. It essentially looks at a few fields from the following system tables and views joined in the following way:
SELECT ...
- 39.5k
-2
votes
1
answer
310
views
SQL Server wait problems [closed]
In my production SQL Server 2012 (Standard), top 4 waits are:
CXPACKET
SOS_SCHEDULER_YIELD
LATCH_EX
WRITELOG.
And:
MAXDOP :0
Cost Threshold for Parallelism: 5
I know here, parallelism is ...
- 65
2
votes
1
answer
2k
views
SQL Server - High buffer time
I'm noticing a high number of waitingRequestsCount on SQL server. The latch_class is buffer. See attached screenshots. Going through SQL server dashboard, I see high number (32221334) waits for Buffer ...
- 21
3
votes
1
answer
8k
views
Postgres wait_event_type Extension
I have a function that was written in Language SQL (not plpgsql) that is scheduled to be called nightly from pgAgent. It was running fine until last night. The function usually takes under 3 minutes ...
- 73
2
votes
1
answer
3k
views
SSIS packages running forever without completing. OLEDB Source query suspended
We have several packages that will intermittently run until they are killed.
When looking at the Activity Monitor we can see the source query for one of the data flows is in a suspended state with ...
- 48
4
votes
3
answers
15k
views
Postgres 10 Query waits on IO: BufFileWrite causing failure to get new database connections
I have a query (attached with query plan) that is run every 5-7 seconds from every device that is running our dashboard (typically, 500+ devices at peak time). This query, at the outset looks like it ...
- 201
0
votes
1
answer
1k
views
sp_WhoIsActive waiting resource
I have a huge query in SQL Server 2016 – when I run the query for a subset of records, it completes in 15 minutes. When I extrapolate it to total number of records, it is supposed to be complete in 1 ...
- 900
3
votes
1
answer
1k
views
Identify Query Waiting on resources
I have some query with CXCONSUMER wait time when i ran sp_whoisactive as shown in the screenshot below.
When i checked the status it is suspended.That means it is waiting for some process to release ...
- 1,335
1
vote
1
answer
248
views
Multi-statement Table Valued Function causing massive PAGELATCH_EX waits
We are getting insane PAGELATCH_EX waits because of a Multi-Statement TVF. I've found that the temp table created by the function is on tempdb file #1 and doesn’t ever change (always '#A465FC0D'). It ...
- 111
2
votes
3
answers
46
views
Linking Waits to a process/statement
I'm starting to take on more of a DBA role and trying to get tooled up with scripts and knowledge to help with this.
I have been trying the sp_blizt procs in a dev environment. A key area I'm trying ...
0
votes
2
answers
1k
views
Strangely high writes per second
I'm experiencing some extremely strange behavior on one of our OLTP Servers. Just to provide a bit of background, we have an 'OLTP' which has many large and wide tables. We have 560 tables within it ...
- 373
0
votes
1
answer
77
views
Monitor volume of data returned to the client application
I've identified ASYNC_NETWORK_IO as our top wait by some distance averaging between 70-77% of wait time for our client application SQL Server. This has been put forward towards the development team ...
- 373
2
votes
1
answer
515
views
Using Resource Governor and allocate more memory to satisfy high Memory Grant request by queries?
On a SQL Server 2016 database server, I have few queries which are really slow and which ask for more memory (queries are not optimized). This is making the whole database slow as lots of users are ...
- 1,335
1
vote
1
answer
192
views
Does logical reads in RAM appears in wait stats or where?
Concerning execution model of SQLOS (RUNNING state, RUNNABLE queue, WAITER list), what will be the state of a task when there is a logical read of pages in RAM currently going on?
If it's a WAITER ...
- 945
1
vote
0
answers
291
views
Waiting Tasks on Mirror
I just migrated my primary's synchronous mirror to a new server and I've noticed that there are 150 to 230 waiting tasks on the mirrored instance (based on the activity monitor.) I don't recall if ...
- 1,781
10
votes
2
answers
478
views
Increased waits during checkpoint after upgrading to better storage
When we migrated from an older all flash array, to a newer all flash array (different, but well established vendor), we started seeing increased waits in SQL Sentry during checkpoints.
Version: ...
- 111
15
votes
2
answers
3k
views
How can wait times be higher than clock time?
When I'm tracking waits with sp_BlitzFirst, I get this detail:
<?ClickToSeeDetails --
For 20 seconds over the last 5 seconds, SQL Server was waiting on this
particular bottleneck.
-- ?>
...
- 153
2
votes
2
answers
387
views
Lots of short term blockings on SQL Server 2008-R2 database
Seeking experts advise on short term blocking which we are seeing for almost a week now.
DB is 15 TB and this Table B where we have lots of blocking - almost 300 in no is 2 TB.
While using ...
- 2,210
2
votes
1
answer
740
views
Relation of PAGELATCH waits with PAGEIOLATCH and WRITELOG
I am seeing increase in PAGELATCH, PAGEIOLATCH and WRITELOG waits after migration to SQL Server 2017 (from SQL Server 2008). While I can explain increase in PAGEIOLATCH and WRITELOG (we've other disks ...
- 945
1
vote
0
answers
525
views
Sys.dm_io_virtual_file_stats shows high latency for tempdb writes
We just got a new SAN and I was looking at sys.dm_io_virtual_file_stats to make sure we are seeing an improvement compared to our last SAN. I noticed that all the tempdb files are seeing avg write ...
4
votes
1
answer
6k
views
MySQL - wait_timeout variable
I've been trying to resolve an issue which I think may be helped by changing the wait_timeout variable in MySQL (5.1.67) on my CentOS machine.
So, I updated /etc/my.cnf with the value I want (180), ...
- 143
-1
votes
2
answers
2k
views
Is there any method of looking for Head Blocking history in SQL 2012?
Is there any method of looking for Head Blocking history via DMVs. I have been able to find the subsequent wait details via Extended Events but there doesn't seem to be much information about what ...
- 73
3
votes
1
answer
2k
views
High ASYNC_NETWORK_IO when user locks computer with Excel open
I have a user who gets in at 8AM and leaves at 6PM every day, and every day I see the same strange pattern in SolarWinds DPA.
It seems that Excel is refreshing the Query Table when the user locks ...
- 465
2
votes
1
answer
5k
views
MySQL default wait_timeout is 28800 but in my server I can see more than that value
Im using AWS MySQL Aurora, the current wait_timeout is 28800 which is the default one, and interactive_waitimeout is also 28800.
But I can see more than the default value.
- 1,960
8
votes
2
answers
30k
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 ...
- 183
4
votes
2
answers
2k
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] - [...
- 1,399
0
votes
1
answer
54
views
Store wait_stats automatically and query it whenever required
I'm learning wait_stats and came up with thought. Is it possible to store wait stats in a table or create a stored procedure or query which will return wait_stats % when we execute query or stored ...
- 73
2
votes
1
answer
305
views
Hundreds of sessions queued in suspended state
We experience hundreds of sessions going in suspended state on rare occasions.
When it happens there is no increase in terms of resource usage - CPU or memory. Rather there is a small amount of ...
- 1,550
3
votes
1
answer
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 ...
- 1,550
1
vote
0
answers
136
views
Counters to distinguish Insert Hotspot / Page Split by seeing PAGELATCH_EX
Insert hotspot and excessive page splits are common reasons for PAGELATCH_EX.
What are the other counters that can be used to confirm whether the wait is from insert hotspot or page split?
Note: I ...
- 900
3
votes
1
answer
874
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 ...
- 171
0
votes
1
answer
126
views
Does dm_exec_procedure_stats.total_worker_time include signal waits?
Does sys.dm_exec_procedure_stats.total_worker_time include signal waits or only time spent by procedure on scheduler?
The reason of question - when number of cores was increased on the server signal ...
2
votes
0
answers
1k
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 ...
- 1,373