April 20, 2015 at 9:01 am
I'm troubleshooting a problem that occurred earlier this morning, amongst other things (high CPU, lots of queries running in parallel) there was a lot of blocking occuring with a resource wait of PAGELATCH_UP all on a particular page ID.
I ran DBCC PAGE but I'm having trouble working out what the page belongs to and whether this was
just another symptom, or potentially something to look at further
edit: this is NOT tempdb 🙂
PAGE: (5:511232)
BUFFER:
BUF @0x000000413C238340
bpage = 0x0000002C5759A000 bhash = 0x0000000000000000 bpageno = (5:511232)
bdbid = 2 breferences = 2047 bcputicks = 13248
bsampleCount = 75 bUse1 = 58685 bstat = 0x10b
blog = 0x9a9a9a9a bnext = 0x0000000000000000
PAGE HEADER:
Page @0x0000002C5759A000
m_pageId = (5:511232) m_headerVersion = 1 m_type = 8
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99
m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 90
m_slotCnt = 2 m_freeCnt = 6 m_freeData = 8182
m_reservedCnt = 0 m_lsn = (42377:27136:90) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 91863621
DB Frag ID = 1
Allocation Status
GAM (5:511232) = ALLOCATED SGAM (5:511233) = NOT ALLOCATED
PFS (5:509544) = 0x40 ALLOCATED 0_PCT_FULL DIFF (5:511238) = CHANGED
ML (5:511239) = NOT MIN_LOGGED
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
April 20, 2015 at 9:16 am
It's an allocation page, it doesn't belong to a specific object. In this case, it's a GAM page (Global allocation map) which tracks which extents are allocated and which are not in a portion of the database file.
Those can get hit very heavily in TempDB, not usually in a user database though. Doing lots and lots of table create/drop?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 20, 2015 at 9:31 am
In addition to what Gail offered, there are a number of options for DBCC PAGE, and I think you can get a more detailed set of output that could be helpful.
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/10/625659.aspx
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 20, 2015 at 9:34 am
TheSQLGuru (4/20/2015)
In addition to what Gail offered, there are a number of options for DBCC PAGE, and I think you can get a more detailed set of output that could be helpful.
Not sure how useful an 8kb bitmap will be because that's all a GAM page is, one bit for each extent in the portion of the file which it coves. There are no rows or anything readable on it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 20, 2015 at 1:06 pm
GAH!! That's what I get for (again) trying to answer questions with significant jet lag! 🙁
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 21, 2015 at 3:35 am
Hi Gail, Kevin - thanks for the replies.
This database doesn't have any create / drop tables, just in tempdb (multiple files there to avoid this issue)
April 21, 2015 at 3:43 am
N.D (4/21/2015)
This database doesn't have any create / drop table
Weird. You shouldn't be seeing severe GAM contention in a user DB... If it happens again, can you capture the queries that are involved? It may shed some light on what's happening and why.
Are you doing lots of data loads in parallel? (multiple sessions all inserting large quantities of data)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2015 at 4:07 am
Thanks - we're going to keep monitoring it, I don't have any of the affected queries unfortunately.
The CPU was being hammered at the time, is it possible that this could have contributed to thie contention? Although I guess I would see a processor wait instead.
April 21, 2015 at 4:10 am
N.D (4/21/2015)
The CPU was being hammered at the time, is it possible that this could have contributed to thie contention?
No. GAM pages are only updated (the pagelatch_up) when an extent is allocated to a table or an index. To get GAM contention, you need lots of tables/indexes being created/dropped or lots of data being added to existing tables so that SQL is allocating lots of 64kB chunks to existing tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2015 at 4:13 am
Thanks - that makes things a lot clearer 🙂
April 22, 2015 at 2:49 am
I have some more information -
The queries that are suffering from contention are selects. The db ID in sysprocesses definitely shows as the user database and we're now using a script that identifies them as GAM pages
We do have snapshot isolation enabled and read committed snapshot on
April 22, 2015 at 3:06 am
Now that's really weird. A select shouldn't need to be allocating pages and hence should not have to take UP latches on allocation pages.
Why do you have both snapshot and read committed snapshot on?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2015 at 3:11 am
This database is weird all over 🙂
I'll double check what's configured, I may have misheard, I know that it's using tempdb to help with contention and I always get them muddled up
April 22, 2015 at 3:14 am
N.D (4/22/2015)
I may have misheard, I know that it's using tempdb to help with contention and I always get them muddled up
Both Snapshot and Read Committed Snapshot do that, it's just odd to have them both enabled.
Normally either Read Committed Snapshot is enabled(making all sessions under read committed use row versions) or Snapshot is enabled and the DB left using locks in most places except where Snapshot Isolation Level is explicitly requested.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2015 at 5:19 am
In sysdatabases the following are listed
snapshot_isolation_state_desc = ON
is_read_committed_snapshot_on = 1
This setting was recommended by the vendor
I also have some further news about the page contention - we have a ridiculous number of cores on this server (80), so had a go at adding some more tempdb files (from 8 to 16)
We still have other issues, but that pagelatch_up contention has not reappeared ....... yet. I know it makes no sense :w00t:
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply