Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting strange object name when running DBCC PAGE Expand / Collapse
Author
Message
Posted Tuesday, March 03, 2009 9:21 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 1,718, Visits: 3,139
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.

Post #667392
Posted Thursday, March 26, 2009 3:32 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 1,718, Visits: 3,139
Any ideas anyone, I haven't been able to find a satisfactory explanation to this yet.
Post #684640
Posted Thursday, March 26, 2009 3:56 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:38 AM
Points: 3,419, Visits: 14,163
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.

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/07/08/under-the-covers-gam-sgam-and-pfs-pages.aspx


Kind regards,
Gift Peddie
Post #684661
Posted Thursday, March 26, 2009 4:57 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 1,718, Visits: 3,139
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.

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/07/08/under-the-covers-gam-sgam-and-pfs-pages.aspx


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?
Post #684687
Posted Thursday, March 26, 2009 5:13 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:38 AM
Points: 3,419, Visits: 14,163
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
Post #684699
Posted Thursday, March 26, 2009 10:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 30,270, Visits: 23,044
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
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #684769
Posted Friday, March 27, 2009 7:09 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 1,718, Visits: 3,139
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.
Post #685012
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse