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
5
votes
1answer
109 views

Different Disk Drive Same Backend Storage

I have a database running on a VM which is getting hammered during a large load, in particular I can see waits of WRITELOG happening. My initial thought is to split the files out on to their own ...
1
vote
1answer
104 views

Scalability of concurrent log restore for different databases on SQL Server

Are there any locks or resource blocking/contention (beyond IO capacity) between two sessions, which restore logs for two different databases with norecovery? Same about restore logs with recovery? ...
0
votes
1answer
126 views

Large Sleep counts causing THREADPOOL wait?

When I run sp_whoisactive throughout the day, I usually see we have around 500-700 connections on our server. We are occasionally getting threadpool waits and high signal wait ratios (so we are thread ...
2
votes
1answer
237 views

How can I reduce OLEDB wait type in a SSAS linked server connection?

I have recently created a linked server to one of my SSAS servers. when I go ahead and open the catalogs, to see which ssas databases I have there, I use the following procedure: create procedure ...
9
votes
3answers
65k views

How to troubleshoot enq: TX - row lock contention?

I have the following situation. I have RAC. On both nodes there are the locks. On the First Node SID EVENT USERNAME BLOCKING_SESSION ROW_WAIT_OBJ# OBJECT_NAME ...
6
votes
1answer
398 views

SQL Server CLR procedures and SQLCLR_QUANTUM_PUNISHMENT

I have a CLR stored procedure running on SQL Server 2014. This stored procedure gathers some data (generally numbering in the tens-of-thousands of rows range), runs some computations on the data, ...
3
votes
0answers
145 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 ...
6
votes
1answer
204 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 ...
10
votes
2answers
2k views

LATCH_EX Waits on Resource METADATA_SEQUENCE_GENERATOR

We have a process that generates an inventory report. On the client side, the process splits of a configurable number of worker threads to build a chunk of data for the report that corresponds to one ...
1
vote
1answer
87 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?...
4
votes
2answers
181 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, ...
4
votes
2answers
198 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" ...
1
vote
1answer
42 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 ...
0
votes
1answer
2k 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 ...
1
vote
1answer
275 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 ...
3
votes
0answers
279 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 ...
0
votes
1answer
21 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 (...
0
votes
1answer
700 views

ACCESS_METHODS_DATASET_PARENT Problem

We had an AlwaysOn Async replica we had some problems so we deleted the availability group After that the database and CPU seem to work abnormally It's very slow and we have waittype = LATCH_EX , ...
1
vote
1answer
996 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'...
1
vote
0answers
92 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
12
votes
1answer
11k views

Troubleshooting SOS_SCHEDULER_YIELD wait

Running our corporate ERP (Dynamics AX 2012), I noticed our production environment seemed much slower than our development systems. After performing the same activities in both the development and ...
5
votes
1answer
235 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 ...
2
votes
1answer
1k 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 ...
4
votes
1answer
3k views

High Log Flush Wait Time and filling Transaction logs [closed]

I have problem with Log Flush Wait Time - as of now, they are averaging at 30000ms (min 0 max 35000); database itself is performing poorly, transaction logs are also growing beyond reasonable ...
6
votes
2answers
396 views

Why would I get PREEMPTIVE_OS_AUTHORIZATIONOPS using tSQLt when Working at Home?

I have an SSDT project containing tSQLt unit tests. I always find when working at home that publishing this and running all tests (from a post deploy script) is problematic (against both localdb and ...
1
vote
0answers
95 views

Negative values for Wait Time per Core per Sec in sp_BlitzFirst

I'm a developer in a firm that doesn't want to spend money on a DBA so i'm on my own with keeping the SQL servers healty. I checked some of Brent's videos and decided to check how hard the SQL servers ...
3
votes
1answer
2k views

SQL Server performance issue (CXPACKET wait type)

I have a performance issue with production SQL Server 2012 which has 24 cores and 32 GB of RAM. From wait type I can see CXPACKET is on the top based on the following SQL Server metrics, should I ...
0
votes
2answers
4k views

Identify Queries causing High Average Wait time (SQL Server 2008) [closed]

Recently we started to receive many alerts from SCOM : Alert: SQL 2008 DB Average Wait Time is too high. When reviewed the graph for this counter, we found that the alert is always ranging between 1,...
13
votes
3answers
31k views

High CXPACKET and LATCH_EX waits

I am having some performance issues with a data processing system which I am working on. I have gathered wait stats from a one hour peroid which show a large amount of CXPACKET and LATCH_EX wait ...
5
votes
2answers
10k 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 ...
4
votes
2answers
917 views

PAGEIOLATCH wait type

I'm trying to get a better understanding of how serious this wait type is. I'm using sp_WhoIsActive stored procedure. Our DBAs don't seem to know much about this wait type. But I often see it when I ...
3
votes
2answers
623 views

tsql Process running forever… what is it doing

Trying to create a big test DB, I have an SP that I created that uses a predefined list of tables (35 That I'm interested in) and multiplies the data in each one of them. In other words, A table ...
4
votes
1answer
597 views

Why is there ASYNC_NETWORK_IO wait type on Shared Memory connections?

I have an app (windows service) local to the SQL Server box. It calls a stored procedure on the SQL Server box that returns a ton of data and thus causes ASYNC_NETWORK_IO wait. The database ...
4
votes
3answers
1k views

Reading ASYNC_NETWORK_IO wait stats

I was troubleshooting some bad performance for a client yesterday and identified that there was a missing index for a particular set of queries that were heavily used (this was for an ERP applicaton: ...
3
votes
2answers
655 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] - [...
3
votes
1answer
63 views

Nightly EDW Build Wait Stats

We are trying to determine our best MAXDOP and Cost Threshold settings for an EDW box. I setup a job to clear the wait stats before the build started and then capture the wait stats after the build ...
3
votes
1answer
1k views

What is the DPT_ENTRY_LOCK wait type?

We have a SQL Server 2016 Availability Group with 3 servers. Twice in the past 5 days we have had performance issues. 8a is our primary server, and 8b and 8c are the secondaries. We started having ...
1
vote
2answers
152 views

SQLserver WaitType : EXECUTION_PIPE_EVENT_INTERNAL

One of our production Microsoft SQL Server 2014 (SP1-CU5) experiencing performance issue so I ran the ShortPeriodWaitStats.sql script from Paul S. Randal, SQLskills.com and the top WaitType is ...
16
votes
1answer
21k views

Is ASYNC_NETWORK_IO wait type anything to worry about?

In looking at the list of stored procedures that take long time to execute, one stands out as causing the most wait. However, most of that wait (81%) is ASYNC_NETWORK_IO and I know why: the stored ...
0
votes
0answers
994 views

LCK_M_S causing blocking with session id < 50

We are experiencing high LCK_M_S wait times conflicting with idle blocking sessions whose ids are less than 50. The waiting session is always a Select statement within a stored procedure. The ...
2
votes
0answers
2k views

Troubleshooting Preemptive_Xe_Dispatcher wait type

While looking for a reason behind slowness of the database server I accidentaly came across Preemptive_Xe_Dispatcher wait. Could you please suggest how this type of wait should be troubleshooted? ...
2
votes
1answer
237 views

Wait Stats, Server Up Time and CXPACKET

I have been looking at the following post and using the query to look at my top wait stats: http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ My top wait stat is ...
3
votes
2answers
9k views

CXPACKET SUSPENDED and NULL wait type

I was was running the query in this article: http://sqlity.net/en/708/why-cxpacket-waits-are-not-your-performance-problem/ To see what my threads were waiting on in regards to a suspended query with ...
9
votes
1answer
497 views

Blocked sessions waiting with PAGELATCH_* wait types?

Edited : Why are sessions reporting being blocked but waiting with PAGELATCH_*, and not LCK_M_ related wait types? I previously assumed that SQL server would only report blocking sessions in the ...
3
votes
1answer
543 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 ...
1
vote
2answers
350 views

SOS_SCHEDULER_YIELD - Prevent a Proc from giving up the Processor Time

I've got a process which about once a month at times unrelated (or as far as we can tell) goes into SOS_SCHEDULER_YIELD State. This process blocks our main data entry pipeline and brings the entire ...
1
vote
0answers
46 views

How to identify a process that has been cancelled?

How can you identify a process that has been cancelled? for example: I was running this dynamic sql - via a linked server: declare @sql nvarchar(max) declare @i int declare @z int DECLARE @vCrlf ...
6
votes
1answer
2k views

How to solve blocking on tempdb

I am having tempdb contention (I guess, at least) on one of the servers (SQL Server 2008 R2). Both the blocker and the waiters (10 of them) has PAGELATCH_EX wait type on database tempdb and all ...
3
votes
1answer
2k views

WAIT STATS - high Wait Time on ASYNC_IO_COMPLETION

I currently look at my wait stats. I followed best practices explained by Paul Randal and go through the documents he references. Looking at the statistic slices I take once / day for a while now ...
2
votes
0answers
852 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 ...