none
Resumable Online Index - sys.dm_db_database_page_allocations RRS feed

  • Question

  • Possibly a bug in Microsoft SQL Server 2019 (CTP3.0)


    The DMV sys.dm_db_database_page_allocations with Detailed mode reports following 

    Msg 824, Level 24, State 2, Line 1
    SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:457714; actual 0:0). It occurred during a read of page (1:457714) in database ID 6 at offset 0x000000df7e4000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\test.mdf'.  Additional messages in the SQL Server error log or operating system error log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

    Test data

    -- Create table to hold sample data
    CREATE TABLE Visits2
    (
    	ID         INT, 
    	I100       INT, 
    	I1000      INT, 
    	I10000     INT, 
    	I100000    INT,
    	I1000000   INT, 
    	I10000000  INT,
    	IP_Address VARCHAR(15),
    	VisitDate  DATE
    );
    GO
    -- Create Tally Table
    GO
    CREATE VIEW vw_Tally AS 
       --Itzik style tally table
       WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
         ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
         ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
         ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
         ,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
         ,lv5 AS (SELECT 0 g FROM lv4 a CROSS JOIN lv4 b) -- 4,294,967,296
         ,Tally (n) AS 
            (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv5)
       SELECT TOP (1000000) n
       FROM Tally
       ORDER BY n;
    	
    GO	
    -- Populate Visits2 with sample data
    SET NOCOUNT ON;
    DECLARE @Max bigint = (select ISNULL(max(ID),0) From Visits2);
    WHILE @Max < 60000000 BEGIN 
    	
       WITH TallyTable AS (
    	
       SELECT n + @Max as N, 
          CAST(RAND(CHECKSUM(NEWID())) * 255 as INT) + 1 AS A4,
          CAST(RAND(CHECKSUM(NEWID())) * 255 as INT) + 1 AS A3,
          CAST(RAND(CHECKSUM(NEWID())) * 255 as INT) + 1 AS A2, 
             1.0 + floor(1 * RAND(convert(varbinary, newid()))) AS A1,
          DATEADD(DD, 1.0 + floor(62 * 
            RAND(convert(varbinary, newid()))),'2018-07-01') AS VisitDate
    		FROM vw_Tally)
    	INSERT INTO Visits2 (ID, I100, I1000, I10000,	I100000,
       	I1000000, I10000000, IP_Address, VisitDate)
    	SELECT	n,n%100, n%1000, n%10000,n%100000,
               n%1000000, n%10000000, 
    	  CAST(A1 AS VARCHAR) + '.' + CAST(A2 AS VARCHAR) + 
                 '.' +  CAST(A3 AS VARCHAR) + 
                 '.' +  CAST(A4 AS VARCHAR), VisitDate
    	FROM TallyTable 
    	set @Max = (select ISNULL(max(ID),0) From Visits2);
    END


    Open New query window to run following 

    CREATE  INDEX NC_Visits2_1 ON dbo.Visits2  (I100)
    WITH (ONLINE = ON,RESUMABLE = ON);

    Wait for few seconds, open a new query windows and run the following

    ALTER INDEX NC_Visits2_1 ON dbo.Visits2 PAUSE;

    Open a 3rd query windows and execute.

    SELECT  *
      FROM sys.dm_db_database_page_allocations
           (
             DB_ID(),
             OBJECT_ID('dbo.Visits2'),
             NULL,
             NULL,
             'detailed'
           ) 

    The above select fails only when used with DETAILED mode. Anyone else getting the same error?



    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker




    • Edited by SimpleSQL Friday, July 12, 2019 10:58 PM
    Friday, July 12, 2019 8:54 PM

Answers

  • The above select fails only when used with DETAILED mode. Anyone else getting the same error?

    I was also able to reproduce these symptoms (CTP 3.1 on Ubuntu Linux). It seems the undocumented sys.dm_db_database_page_allocation DMV does not properly account for pages when the index creation is in the paused state.

    You can report this bug on the Azure Feedback Forum (https://feedback.azure.com/forums/908035-sql-server) for awareness but be aware that it may or may not get addressed. Fixing this is up to the discretion of the product team since it's an undocumented and unsupported feature (albeit useful).


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, July 13, 2019 11:11 AM
    Moderator

All replies

  • The above select fails only when used with DETAILED mode. Anyone else getting the same error?

    I was also able to reproduce these symptoms (CTP 3.1 on Ubuntu Linux). It seems the undocumented sys.dm_db_database_page_allocation DMV does not properly account for pages when the index creation is in the paused state.

    You can report this bug on the Azure Feedback Forum (https://feedback.azure.com/forums/908035-sql-server) for awareness but be aware that it may or may not get addressed. Fixing this is up to the discretion of the product team since it's an undocumented and unsupported feature (albeit useful).


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, July 13, 2019 11:11 AM
    Moderator
  • Thanks Dan. Reported it

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, July 15, 2019 7:39 AM
  • Hi SimpleSQL,

     

    Thank you for you reply. Have you solved this problem ?  In order to close this thread, please kindly mark useful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 31, 2019 9:14 AM