Skip to content

SELECT

What is a Page Split

Tables, and indexes are organized in SQL Server into 8K chunks called pages. If you have rows that are 100 bytes each, you can fit about 80 of those rows into a given page. If you update one of those rows to have more data that contains a variable length field like VARCHAR, NVARCHAR, and others, that will cause the page to overflow forcing a page split. The page split takes about half of the data and moves it into a new page, leaving about half in the original page. Another action that causes a page split is to insert a row that based on the indexing would go into a page that is nearly full, if the inserted row doesn’t fit a page split occurs.

If there is room, and your update or insert doesn’t require a split, this is pretty quick to do since SQL Server is just updating one page and then writing it to disk, and to the transaction log. But if the updated or inserted row doesn’t fit, SQL Server needs to allocate a new page, move about half the rows, and then write both pages to disk and to the transaction log. Additionally the pages in all the indexes that point to the data pages need to be updated. Let’s say your table had 1 clustered index, 4 nonclustered indexes, at a minimum 7 pages would be updated, 1 for the clustered index structure, 4 for the nonclustered indexes, and 2 in the data pages in the clustered index. In this specific example the page split would cause a minimum of 7 times the I/O as an insert or update that didn’t require a page split.

Read More »What is a Page Split

Database Corruption Challenge Week 7 – Alternate Solution

The alternate solution to the Database Corruption Challenge this week was created by Patrick Flynn. This solution is the only solution to successfully recover all the data without using any of the backups. If the challenge had been structured differently and all you had was just the database files and no backups, this solution would work just fine.

As you read through this solution, you will notice the use of Common Table Expressions throughout the solution. Patrick is the first person that I have seen write TSQL code that uses Common Table Expressions more than I do. (I mean that as a compliment Patrick)

To start with let’s add the UDF_Convert_Hex_to_Binary function into the master database to match what Patrick had in his system.

All code provided by Patrick, comments and screenshots by me.

-- Step 0 - Create Supporting function
USE [master]
GO
CREATE FUNCTION [dbo].[UDF_Convert_Hex_to_Binary]
(
      @HEX VARBINARY(MAX)
)
RETURNS VARCHAR(MAX)
BEGIN

	DECLARE @BINARY VARCHAR(MAX)

	;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
	N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
	N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
	N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
	FROM N3 AS X, N3 AS Y)

	SELECT @BINARY=ISNULL(@BINARY,'')
	+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 128) % 2)
	+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 64) % 2)
	+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 32) % 2)
	+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 16) % 2)
	+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 8) % 2)
	+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 4) % 2)
	+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 2) % 2)
	+ CONVERT(NVARCHAR(1), SUBSTRING(@HEX, Nums.n, 1) % 2) 

	FROM N4 Nums
	WHERE Nums.n<=LEN(@HEX) 

	RETURN @BINARY
END
GO

Once that function is created, let’s drop the previous CorruptionChallenge7 database, because this is probably not our first time running through the solution. If you have a different way of checking for a database and dropping it, please see my blog post related to this topic earlier today.

Read More »Database Corruption Challenge Week 7 – Alternate Solution

Week 5 – Alternate Solution

Here is how I solved Week 5 of the Database Corruption Challenge. The following steps were tested and confirmed working on SQL Server 2008R2, SQL Server 2012, and SQL Server 2014.

 

To oversimplify, here are the steps:

  • Restore the last known good database.
  • Shut down the database, and copy off the last good database files.
  • Replace some files and restart it. (Hack Attach)
  • Next realizing that the boot page was corrupt page 1:9.
  • Shut down the database.
  • Copy the boot page from the last good database files and place it in the corrupt file.
  • Restart the database.
  • Realize there is other corruption.
  • Fix the other corruption.

Read More »Week 5 – Alternate Solution

Corruption Challenge Week 4 – The Winning Solution

Congratulations to Randolph West who won the corruption challenge this week with the following solution which restored all of the data.

First he restored the database to get started. Note some of his code and comments have been reformatted to better fit in the blog format.

Restore database. I use KEEP_CDC and KEEP_REPLICATION because of the hints you dropped in your blog.
The hint is that there is 100% chance of data recovery, so I will look for Change Data Capture tables.

USE master;
GO
--DROP DATABASE [CorruptionChallenge4] ;
RESTORE DATABASE [CorruptionChallenge4]
   FROM DISK = N'C:\DBBackups\CorruptionChallenge4_Corrupt.bak'
   WITH FILE = 1,
        MOVE N'CorruptionChallenge4'
		  TO N'C:\SQL_DATA\CorruptionChallenge4.mdf',
        MOVE N'UserObjects'
		  TO N'C:\SQL_DATA\CorruptionChallenge4_UserObjects.ndf',
        MOVE N'CorruptionChallenge4_log'
		  TO N'C:\SQL_DATA\CorruptionChallenge4_log.ldf',
        NOUNLOAD,
    REPLACE,
    STATS = 5,
    KEEP_CDC,
    KEEP_REPLICATION;

Next he ran CheckDB to see what is wrong with this database.

Read More »Corruption Challenge Week 4 – The Winning Solution