Questions tagged [waits]
The waits tag has no usage guidance.
61
questions
-2
votes
1
answer
67
views
+50
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 ...
- 45
0
votes
1
answer
145
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
2k
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,430
1
vote
0
answers
155
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 ...
- 81
0
votes
1
answer
160
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 ...
5
votes
1
answer
972
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,146
0
votes
2
answers
319
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,430
0
votes
1
answer
176
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,451
0
votes
2
answers
462
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,472
2
votes
1
answer
554
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,805
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 ...
- 211
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 ...
- 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 ...
- 35.1k
2
votes
2
answers
1k
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
175
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 ...
- 35.1k
-2
votes
1
answer
237
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
6k
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
2k
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
12k
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 ...
- 203
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 ...
- 899
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,295
1
vote
1
answer
214
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
42
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 ...
- 353
0
votes
1
answer
50
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 ...
- 353
2
votes
1
answer
444
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,295
1
vote
1
answer
183
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
274
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,771
10
votes
2
answers
432
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
372
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,169
2
votes
1
answer
687
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
471
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
5k
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
2
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 ...
- 425
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,910
8
votes
2
answers
28k
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,369
0
votes
1
answer
47
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
255
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,528
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,528
1
vote
0
answers
124
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 ...
- 899
3
votes
1
answer
829
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 ...
- 161
0
votes
1
answer
117
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,353
3
votes
1
answer
2k
views
SQL Server: dm_exec_requests.wait_time vs. dm_exec_sessions.last_request_end_time
We have a third-party Microsoft Access application that uses SQL Server 2012 as its data store. It is a well known issue that Access often does not tell SQL Server that it has received the results of ...
- 2,151
15
votes
2
answers
12k
views
Is it safe to cancel a PostgreSQL ALTER TABLE query that is waiting on a lock?
We started an ALTER TABLE query hours ago and only recently realized (via pg_stat_activity) that it is waiting on a lock. We discovered the other query that is holding a lock on the table we want to ...
- 253
3
votes
1
answer
2k
views
Confused about sys.dm_io_virtual_file_stats num_of_reads/writes column
I'm trying to use the sys.dm_io_virtual_file_stats DMF to generate so IO wait information across a number of my servers. I came across this post by Paul Randall: http://www.sqlskills.com/blogs/paul/...
- 24.1k