Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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

Filter by
Sorted by
Tagged with
1 vote
1 answer
55 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/...
variable's user avatar
  • 3,634
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). ...
Eitan Blumin's user avatar
2 votes
1 answer
240 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 ...
Newbie-DBA's user avatar
2 votes
1 answer
1k 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. ...
jack's user avatar
  • 79
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 ...
Adit Bhatia's user avatar
5 votes
1 answer
414 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 ...
George.Palacios's user avatar
0 votes
1 answer
43 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 (...
Robert West's user avatar
2 votes
1 answer
12k 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,...
Magier's user avatar
  • 4,787
5 votes
1 answer
571 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 ...
Ian_H's user avatar
  • 1,656
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 , ...
Milad Firouzi's user avatar
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 (...
matskm's user avatar
  • 53
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 ...
Kyle Chandler's user avatar
2 votes
1 answer
212 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 ...
Tom's user avatar
  • 1,549