Discussion related to SQL Server page sizes and OS page sizes was started up while my new SQL Server class was being done. New batch is really good; they ask questions :).
SQL Server maintains data with 8KB data pages. If SQL Server has a table called dbo.Customer, based on the number of records, size of the record, data will be held on hundreds of thousands of 8KB data pages. Although SQL Server maintain 8KB pages, those pages have to be written to the disk, into 512 bytes sectors, which means, 8KB page will be broken into 16 sectors. In other words, data in a database page will be distributed among 16 sectors. SQL Server has no knowledge on the completion of this distribution (by default it assumes that 8KB is written to 16 sectors with no errors once the first sector is written [Correct me if I am wrong]). If something has happened during the write process, we will see errors when data is read back and we might not be able to find the exact problem, or exact IO path. Luckily, there is feature that checks whether those 16 pages are written properly when data is read back from the disk. Explanation of it is given below, but the question raised from my class was related to sector size. Is it 512 bytes???
Sector refers the minimum chunk of data that can be written or read to a hard drive (Read more on Sector: http://en.wikipedia.org/wiki/Disk_sector). K, back to the question, do we still see 512 bytes size sectors? I had no direct answer when the question was raised because my knowledge on hardware is bit poor. So, as usual, googled and found some info.
Here is what I found: It depends, 512 bytes was the standard size for the sector during 1956. Nowadays we get larger size of sectors such as 1KB to 4KB (or more?). In that case, for example, if SQL Server has been installed on a platform that supports 2KB sectors, one database page requires only 4 sectors.
How can we find damaged data pages if something has happened during the write process? It has to be done by PAGE_VERIFY database option. By default it is on and set to CHECKSUM. It can be set either to TORN_PAGE_DETECTION or NONE. CHECKSUM calculates the checksum value for the entire 8KB page and store it in the page header. When the page is read back from the drive, SQL Server calculates it again and matches with the previous stored value. If the value is different, it throws an error message (824). TORN_PAGE_DETECTION creates a specific bit for each sector (512 bytes or 4KB) and stores them in the database page header. These torn bits are checked when pages are read back. Error 824 will be thrown when unmatched torn bits are found. Details of the errors are logged in both SQL Server log and Windows log, allowing us to see the details of error. With most of cases, DBCC CHECKDB helps to resolves the issue.
As the SQL server stores the checksum value in the page header, the page header itself also had to be stored in a sector. If that sector is damaged or the index have been removed there won't be any way to get the data back.
ReplyDelete(Please specify whether my point is correct or not)
myCloud has raised a good question.
ReplyDeleteBut, we cannot avoid hardware failure at any cost. the alternative way to recover database information. Solution is in the below given link.
http://social.msdn.microsoft.com/Forums/en/sqldisasterrecovery/thread/5b937b5e-bfc9-435b-a2b3-fd4802639a38
Very good question.
ReplyDeleteWe cannot avoid hardware failure. But, we can retrieve our corrupted data using 3rd party tools. the alternative solution i found when i googled is given in the below link
http://social.msdn.microsoft.com/Forums/en/sqldisasterrecovery/thread/5b937b5e-bfc9-435b-a2b3-fd4802639a38
@MyCloud: Thanks for the comment. Yes, we have no way of avoiding all hardware problems even with some disaster recovery options. But PAGE_VERIFY will at least give us a lead to find out the problem, and might be able to solve it to.
ReplyDelete@Ayyapan: Thanks for the comment and for the great link.
Thank you for both Ayyappan and Dinesh. How ever at the very low level everything is 1s and 0s on the disk and cannot be recovered of the disk is damaged. :)
ReplyDelete@Dinesh Priyankara - This is me V.Thurupathan (Hope you remember)
@Thurupathan: Yes, everything is as 1s and 0s but there are ways of minimizing disaster. If you are expecting major disasters, concepts like clustering, mirroring can be used.
ReplyDeleteYes, I know you :). But I thought that you are an Indian when I saw the first comment.