Questions tagged [latch]
A latch is a lightweight lock on an in-memory structure to prevent concurrent writes to an in-memory data page in a concurrent update scenario
13 questions
6
votes
1
answer
4k
views
DBCC CHECKDB on a user database: Time-out occurred while waiting for buffer latch type 2 for page (X:XXX), database ID 2
Our Ola Hallengren IntegrityCheck job failed due to a buffer latch timeout while running DBCC CHECKDB on a user database.
However, the buffer latch timeout reported was in TempDB (database ID 2).
...
- 483
6
votes
1
answer
95
views
SELECT xyz INTO #Temp from SSMS is 5x longer in one environment than another [closed]
First time posting, I hope I'm doing it right.
I'm a "dba by accident" who is learning a lot but admittedly still has a lot more to learn. Here is my brain dump:
Prod server has 5 million rows. I ...
5
votes
3
answers
17k
views
How to reduce huge CXPACKET & LATCH_EX (ACCESS_METHODS_DATASET_PARENT) wait times?
Problem
We have been experiencing high levels of user disruption due to SQL timeouts accross our systems since the beginning of the year.
The SQL-Server instance in question has very high CPU usage (...
- 53
5
votes
1
answer
435
views
NOLOCK or Read Uncommitted locking / latching behaviours
I've read many, many different takes on how NOLOCK or the read uncommitted isolation levels function in terms of locks / latches taken.
When using a SELECT with NOLOCK or in the Read Uncommitted ...
- 5,570
5
votes
1
answer
628
views
Database ID of a non-existent database - time-out waiting for buffer latch on page appearing in error log
Background:
I've recently started at a new shop with lots of databases in various states. I arrived this morning (having just been added to the SQL DBA mailing group) to find an email about the ...
- 1,666
2
votes
1
answer
2k
views
Differences of latch and lock
Recently when i was trying to find information about latch_ex wait type, i come across one blog as below which stated about latch and lock.
After reading this blog , i m just curious in one thing. ...
- 79
2
votes
1
answer
277
views
LATCH_EX and CX_PACKETS for a stored proc seen when timing out
I am trying to troubleshoot an issue with below things noticed:-
There is this stored proc which gets timed out on some random days while other days or after retry may complete faster. Time out ...
- 794
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
2
votes
1
answer
13k
views
Non-yielding Scheduler Error with dump caused by heavy queries on SQL 2016
After bringing our databases to new servers with new hardware and new os (2012 server) and new SQL Server version (2016 sp1) we face the following issue:
During nightly reporting database update runs,...
- 4,807
2
votes
1
answer
213
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 ...
- 1,569
1
vote
1
answer
74
views
What is an example to understand that having multiple data files helps with latch pages in parallel?
You will always benefit from multiple data files, because then SQL
Server is able to latch multiple system pages in parallel in the
Buffer Pool.
Link: https://www.sqlpassion.at/archive/2016/08/29/...
- 3,489
0
votes
1
answer
2k
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 , ...
- 129
0
votes
1
answer
47
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 (...
- 21