Search

DBCC PAGE incorrect output with filtered indexes by Luciano Moreira

Active

6
0
Sign in
to vote
Type: Bug
ID: 776144
Opened: 1/9/2013 9:48:55 AM
Access Restriction: Public
1
Workaround(s)
1
User(s) can reproduce this bug
When I use DBCC PAGE to check a page of an index with format 3, it shows as expected (on grid, with level, ChildPageId, key, row size, …). But when I create a non-clustered index with a filter, the same DBCC PAGE doesn’t format the output anymore.

This was verified using Microsoft SQL Server 2012 - 11.0.2218.0 (X64). With SQL Server 2008 and 2008 R2 I couldn't reproduce the problem.

I know DBCC PAGE is undocumented, but want to make sure Microsoft knows about it.
Details (expand)

Product Language

English

Version

SQL Server 2012 - Developer Edition

Category

SQL Engine

Operating System

Windows 7 Ultimate (SP1)

Operating System Language

US English

Steps to Reproduce

This sample script will do the trick. Of course, root page will probably chance.

USE tempdb
GO

IF OBJECT_ID('dbo.MyTable', 'U') IS NOT NULL
     DROP TABLE dbo.MyTable
GO

CREATE TABLE dbo.MyTable (
     ID INT IDENTITY NOT NULL PRIMARY KEY
     , Name CHAR(100) NOT NULL DEFAULT ('DBCC PAGE')
     , SomeDate DATETIME2 NOT NULL DEFAULT(SYSDATETIME())
)
GO

INSERT INTO dbo.MyTable DEFAULT VALUES
GO 30000

INSERT INTO dbo.MyTable ( Name, SomeDate )
SELECT Name, DATEADD(YY, -1, SomeDate)
FROM dbo.MyTable
GO

SELECT
     OBJECT_NAME(object_id) AS ObjectName
     , AU.*
     , P.*
FROM SYS.system_internals_allocation_units AS AU
INNER JOIN SYS.Partitions AS P
ON AU.Container_id = P.Partition_id
WHERE Object_ID IN (object_id('MyTable'))
ORDER BY object_id, type
GO

DBCC TRACEON(3604)
DBCC PAGE(Tempdb, 1, 1383, 3)
GO

-- output ok

CREATE NONCLUSTERED INDEX idxNCL_MyTable_SomeDate
ON dbo.MyTable (SomeDate)
WHERE SomeDate > '2013-01-01'
go

-- Same root page for CL, as expected
SELECT
     OBJECT_NAME(object_id) AS ObjectName
     , AU.*
     , P.*
FROM SYS.system_internals_allocation_units AS AU
INNER JOIN SYS.Partitions AS P
ON AU.Container_id = P.Partition_id
WHERE Object_ID IN (object_id('MyTable'))
ORDER BY object_id, type
GO

DBCC PAGE(Tempdb, 1, 1383, 3)
GO

-- output not ok

DROP INDEX MyTable.idxNCL_MyTable_SomeDate

-- output ok again
DBCC PAGE(Tempdb, 1, 1383, 3)
GO

Actual Results

DBCC PAGE output with format similar to option 1

Expected Results

DBCC PAGE output with index structure in a grid

Platform

X64

Virtualization

 
File Attachments
0 attachments
Sign in to post a comment.
Posted by Microsoft on 3/29/2013 at 10:51 AM
Hi,
Thank you for your feedback and bringing this behaviour to our attention. At the moment, there are no plans to enhance this command as it is undocumented. However we will keep this in mind in the future.
Posted by Kendra Little on 2/22/2013 at 10:31 PM
I just hit this issue. Took me a while to narrow it down to the filtered index, but it's the exact same thing.

When the issue occurs, there's a stack dump written to the SQL Server log (Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION)
Sign in to post a workaround.
Posted by Kendra Little on 2/22/2013 at 11:09 PM
This is obviously only a suitable workaround for a demo, but you can disable or drop the filtered index. You will then be able to see DBCC PAGE output with dump style 3 for all other indexes on the table.

(In other words, the issue isn't permanent and you can just disable the single NC.)