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
2
votes
1answer
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 ...
2
votes
2answers
377 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 ...
2
votes
2answers
428 views

High SOS_SCHEDULER_YIELD and Signal Waits

I have run Paul Randals wait script and can see SOS_SCHEDULER_YIELD is at 60% with an average signal wait of 10ms. I have also run a script to find out the what percentage of my waits are signal ...
2
votes
1answer
240 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 ...
2
votes
1answer
256 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 ...
2
votes
1answer
185 views

Non Buffer Latch - Forefront Identity Manager

We are using Forefront Identity Manger and we are doing an initial load to which we are experiencing slow performance. I always start with wait types for such things and can see %30 CXPACKET, %25 ...
2
votes
4answers
8k views

Select from linked server to local server causing waits on both sides

The queries in question are here: http://pastie.org/4833351 Run individually, each of these queries runs through (albeit slow). Run as a group, it just sits there, showing either OLEDB waits on 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
0answers
893 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
vote
1answer
967 views

sql server service broker

For the last few days the service broker command which run from master database is consuming much more RESOURCES than before. The Impact on the Database is high (from the bad side), It causes for ...
1
vote
1answer
1k 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
1answer
94 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?...
1
vote
1answer
43 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
vote
1answer
313 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
vote
2answers
2k views

PAGEIOLATCH_SH and LCK_M_IX wait type

I'm seeking for a resolution into wait types I observe on one of my servers. I've been keeping wait statistics for a long time and can positively say that the top two are PAGEIOLATCH_SH and LCK_M_IX ...
1
vote
2answers
156 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 ...
1
vote
1answer
106 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? ...
1
vote
0answers
99 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
1
vote
0answers
100 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 ...
1
vote
0answers
55 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 ...
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,...
0
votes
1answer
758 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 , ...
0
votes
1answer
72 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 ...
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 ...
0
votes
0answers
4 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 ...
0
votes
1answer
22 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
0answers
1k 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 ...
0
votes
1answer
131 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 ...