March 3, 2009 at 9:21 am
I'm having a strange issue while trying to troubleshoot a slow stored procedure.
While running the stored procedure, I capture information using the following statement:
select * from master.dbo.sysprocesses with (nolock)
where spid = 61
Lastwaittype: PAGEIOLATCH_SH
waitresource: 8:1:3639600
I then run the following to get the object_id:
DBCC TRACEON (3604)
DBCC PAGE (8, 1, 3639600, 0)
DBCC TRACEOFF(3604)
GO
I get this:
...
m_objId = 99 m_indexId = 0
...
I run the following to get the object name:
SELECT object_name(99)
I get the following: ALLOCATION
What is this? I don't have a table by that name in my database.
Is there an "allocation" problem in my db, and how can I dig deeper?
Here is the full output of the DBCC PAGE command:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (1:3639600)
-----------------
BUFFER:
-------
BUF @0x016AA340
---------------
bpage = 0x5177A000 bhash = 0x00000000 bpageno = (1:3639600)
bdbid = 8 breferences = 1 bstat = 0x9
bspin = 0 bnext = 0x00000000
PAGE HEADER:
------------
Page @0x5177A000
----------------
m_pageId = (1:3639600) m_headerVersion = 1 m_type = 11
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 99 m_indexId = 0 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 0 m_slotCnt = 1
m_freeCnt = 2 m_freeData = 8188 m_reservedCnt = 0
m_lsn = (27549:8438:280) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 2
Allocation Status
-----------------
GAM (1:3578624) = ALLOCATED
SGAM (1:3578625) = NOT ALLOCATED
PFS (1:3639600) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:3578630) = CHANGED
ML (1:3578631) = NOT MIN_LOGGED
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 26, 2009 at 3:32 pm
Any ideas anyone, I haven't been able to find a satisfactory explanation to this yet.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 26, 2009 at 3:56 pm
GAM (1:3578624) = ALLOCATED
SGAM (1:3578625) = NOT ALLOCATED
These are in the physical index architecture check below for details from the owners of the relational engine.
Kind regards,
Gift Peddie
March 26, 2009 at 4:57 pm
Gift Peddie (3/26/2009)
GAM (1:3578624) = ALLOCATED
SGAM (1:3578625) = NOT ALLOCATED
These are in the physical index architecture check below for details from the owners of the relational engine.
I'm getting object_id = 99 and my query gets hung on this "ALLOCATION" object until I kill it.
Is something wrong with the storage engine? What could this mean?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 26, 2009 at 5:13 pm
I think here is how you find out what to do but I could be wrong because I am on the logical end.
https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx
Kind regards,
Gift Peddie
March 26, 2009 at 10:48 pm
Marios Philippopoulos (3/3/2009)
I run the following to get the object name:
SELECT object_name(99)I get the following: ALLOCATION
It's one of the GAM/SGAM/PFS pages. One of the allocation structures of the database.
This was fairly common to see on tempDB in SQL 2000, not so on user databases. What's the wait time that you're seeing on these waits? If it's a couple milliseconds, don't worry.
Are you doing lots of table creation?
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
March 27, 2009 at 7:09 am
GilaMonster (3/26/2009)
Marios Philippopoulos (3/3/2009)
I run the following to get the object name:
SELECT object_name(99)I get the following: ALLOCATION
It's one of the GAM/SGAM/PFS pages. One of the allocation structures of the database.
This was fairly common to see on tempDB in SQL 2000, not so on user databases. What's the wait time that you're seeing on these waits? If it's a couple milliseconds, don't worry.
Are you doing lots of table creation?
Thank you both for your input. Unfortunately, I don't have the specifics on wait time, but I do know that the waits occur when there is a large insert into a user table. I will post more details next time this occurs, possibly later today.
We actually rebooted the server last night. I know, desperate measures, but perhaps that has cleared the problem. I will know some time later today.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply