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
Questions tagged [latch]
14 questions
6
votes
1 answer
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).
Output from the job:
Date and time: 2022-01-22…
Eitan Blumin
- 483
- 4
- 11
6
votes
1 answer
SELECT xyz INTO #Temp from SSMS is 5x longer in one environment than another
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 copied those rows to a Test server.
Using SNAPSHOT…
Kyle Chandler
- 63
- 4
5
votes
1 answer
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 Isolation level, is the only lock taken out a Schema…
George.Palacios
- 5,590
- 23
- 48
5
votes
1 answer
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 CHECKDB job failing last night on one of the servers…
Ian_H
- 1,674
- 10
- 17
5
votes
3 answers
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 (higher than 90% on all 16 cores all the time)…
matskm
- 53
- 1
- 1
- 3
3
votes
1 answer
What does it mean if all of my wait_resources in the System Health Extended Event are the same pointer?
It's notoriously impossible to decode what the wait_resource pointer in the System Health Extended Event means. But if all of my long waits in that Extended Event for a certain time period are reporting the same pointer, then does that indicate…
J. Mini
- 1,161
- 8
- 32
2
votes
1 answer
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 setting 1 hour. When completes, it does under 15…
Newbie-DBA
- 804
- 6
- 22
2
votes
1 answer
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. When app submit a request, SQL server will look for…
jack
- 79
- 3
- 7
2
votes
1 answer
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 Pool. Can someone please explain what could…
Adit Bhatia
- 21
- 2
2
votes
1 answer
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, the SQL Server service suddenly gets into…
Magier
- 4,827
- 8
- 48
- 91
2
votes
1 answer
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 LATCH_EX and %15 ASYNC_NETWORK_IO.
I understand that…
Tom
- 1,569
- 6
- 29
- 43
1
vote
1 answer
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/files-and-file-groups-in-sql-server/
I currently…
variable
- 3,590
- 4
- 37
- 100
0
votes
1 answer
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 (non-I/O) it reports:
wait_time_ms =…
Robert West
- 21
- 2
0
votes
1 answer
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 , ACCESS_METHODS_DATASET_PARENT
We didn't have such a…
Milad Firouzi
- 139
- 1
- 3
- 12