Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [waits]

55 questions
35
votes
2 answers

Find Queries that Are Causing Waits

Given a certain kind of wait, how do you find which queries are causing those waits safely on a production SQL 2008 R2 Server? In this particular case I am wondering about async_network_io.
Kyle Brandt
  • 2,335
  • 8
  • 29
  • 37
15
votes
2 answers

How can wait times be higher than clock time?

When I'm tracking waits with sp_BlitzFirst, I get this detail: Should that read "for 20 times over the last 5 seconds?" …
Robert Rice
  • 153
  • 1
  • 7
14
votes
2 answers

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 alter, and not letting it go. Our query is a…
JMTyler
  • 243
  • 2
  • 6
10
votes
2 answers

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: SQL Server 2012 Sp4 On our old storage our waits…
Doley
  • 111
  • 5
8
votes
1 answer

Specific TempDB insert of UserDB select results in SOS_SCHEDULER_YIELD to ENCRYPTION_SCAN

One of our production systems has exhibited a problem with a single insertion statement into a temp table from our user database. When I comment out the insert/select, the stored proc in question runs in a timely fashion, so I am confident in my…
Robert Gannon
  • 434
  • 3
  • 10
8
votes
2 answers

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 the same time Activity Monitor shows a huge…
JFL
  • 183
  • 1
  • 1
  • 6
5
votes
1 answer

Need help with ASYNC_NETWORK_IO

We have a SQL Server 2005 server that is serving up 20 websites, one of which is our custom CMS, and a few other micro sites. I am new to this and just started looking at the databases here. The problem is that we are having issues with the SQL…
Tony
  • 111
  • 4
4
votes
1 answer

Print message comes out in batches

I have simple query WHILE ( @counter < 50 ) BEGIN BEGIN TRANSACTION PRINT 'Counter = ' + CONVERT(VARCHAR(4), @counter); WITH CTE AS ( SELECT TOP 50000 * FROM MyTable …
Vladimir Oselsky
  • 839
  • 5
  • 12
  • 21
4
votes
3 answers

Why decrease the wait_timeout configuration parameter in MySQL?

Question I'm working on an application that uses a connection pool in Apache Tomcat to communicate with MySQL. I was wondering why would you want to use a smaller wait_timeout than the default of 28,800 seconds? I see plenty of downsides to…
Usman Mutawakil
  • 143
  • 1
  • 1
  • 4
4
votes
1 answer

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 with enough memory and would like to dig in and…
J.D.
  • 22,196
  • 3
  • 34
  • 78
4
votes
1 answer

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), and restarted MySQL, but now I'm surprised to…
Cheddar
  • 143
  • 1
  • 3
3
votes
1 answer

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/survey-tempdb-and-transaction-log-io-latencies/. …
Kenneth Fisher
  • 23,537
  • 9
  • 54
  • 106
3
votes
1 answer

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 to execute, but when I came in this morning it…
David Beckman
  • 73
  • 9
3
votes
1 answer

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 resource.As far as my understanding CXPACKET is…
user9516827
  • 1,265
  • 1
  • 8
  • 27
3
votes
2 answers

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] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS], …
Pரதீப்
  • 1,229
  • 4
  • 17
  • 33
1
2 3 4