Archive for April, 2010

CHECKSUM Vs FileStream April 28th, 2010

Vinod Kumar

In the previous posts I have discussed briefly about the CHECKSUM options. Fundamentally, Database options allow you to establish checksum, torn page or no audit detection levels at a database level. File stream file groups do not apply the database level options to the files it stores as part of NTFS volume.

It makes sense, as to checksum files would require the SQL Server process to read a file and calculate the checksum as it is streaming the big chunk from disk.  This could be a very expensive operation as file streams is targeted at files in excess of 1MB, document and video storage. This increased load on the system could quickly become a performance factor. Inline stored blobs receive checksum at the individual page level, reducing the overhead and spreading the activity.

Sometimes, looking at technology in isolation and not understanding its impact on other subsystems can become fatal in our core understanding of the features used.

Continue reading...


 

Script: Checking for DAC connections April 26th, 2010

Vinod Kumar

I hope you are all aware of the DAC (Dedicated Admin Connection) concept which we introduced with SQL Server 2005+ onwards. It is an awesome backdoor entry into SQL Server when things are not going good on the Server. It is an awesome way to troubleshoot SQL Server when it is unresponsive. As a DBA, you might want to know if anyone is using this special dedicated connection somewhere on your environment. Then here is a simple query that returns you this information …

SELECT t2.session_id, t1.name
FROM sys.tcp_endpoints AS t1
JOIN sys.dm_exec_sessions as t2
    ON t1.endpoint_id = t2.endpoint_id
WHERE t1.name like ‘Dedicated Admin Connection’

Continue reading...


 

Torn Page Vs CHECKSUM April 23rd, 2010

Vinod Kumar

This also comes from the various questions that were asked after my session at TechEd India 2010. Confusions exists what to turn ON. My suggestions from SQL Server 2005+ will be to use the CHECKSUM ON.

A term torn page comes from the idea of a torn page in a book. A SQL Server database page is 8Kb in size. This page can be thought of in terms of 512 byte units called sectors, which is the lowest unit of I/O for disk drive. So a SQL Server page is 16 sectors. Since the disk will actually write the 8Kb SQL Server page in 16 sectors, it is conceivable that during some type of power failure (without backupsSmile), one of the 16 sectors may not get persisted to the disk media. Think of a page in a book where part of the page is torn. Same idea.

Does this mean SQL Server cannot handle power failures? Absolutely not. Remember that SQL Server simply uses the Windows API to write a page to disk. It is up the disk system to ensure the write is successful when the API returns success back to SQL Server. But what happen is SQL Server initiated the Windows API call and a system power failure occurs while the API call is being executed? If the computer or disk system has some type of alternate power source, then a problem probably will not occur (of course these alternate sources could have a problem as well). But it is always possible for the disk driver to lose power. Since it writes data in 512 byte sectors, it may or may not have written all 16 sectors for a page before the power failure occurs. SQL Server deploys a technique to detect whether a page has been torn when the page is read from disk. This technique is enabled for a database using the ALTER DATABASE option TORN_PAGE_DETECTION. This option is ON by default for SQL Server 2000 databases (but not for 7.0 databases). There are numerous occasions I have seen people get into torn-page-corruption and have no clue … Anyways, that is a different discussion. Moving to the concept of CHECKSUM from SQL Server 2005+ …

Checksum stands for the concept of calculating a unique value based on the bytes stored on a page before the page is written to disk. The concept is that when the page is read back from disk, the checksum can be recalculated (minus the checksum already on the page) and then checked against the checksum stored on the page. If the values are not the same, the page must have changed after it was sent to the disk system or the disk system did not return the right data.

The thinking behind checksum is that the underlying OS or disk system must be at fault for a damaged page since the checksum value was correct before writing it to disk. The only possible window for a problem within the application is the time it takes to calculate the checksum and sending the buffer to an API call or a flaw in the checksum check algorithm. Checksum will become a very important part of root cause analysis for data corruption with SQL Server 2005+. Understanding IO errors esp 823, 824 errors can easily be worked out if we enabled CHECKSUM.

There are backup enhancements that get added as part of CHECKSUM from SQL Server 2005 onwards and I will discuss the same on a later post.

Continue reading...


 

CHECKSUM and TempDB April 22nd, 2010

Vinod Kumar

I just finished my sessions last week at TechEd India 2010 but during the session as I spoke about Recovery and Consistency of data with SQL Server and the enhancements of SQL Server 2005 adding CHECKSUM to the database, I found not many to know its importance. There has been an enhancement to SQL Server 2008 specifically around this which I thought is worth a mention over the blog.

The database option page_verify_option can be set to CHECKSUM in tempdb database starting with SQL Server 2008. This is a great enhancement that can be leveraged while troubleshooting unexplained behavior that involves tempdb.

For new installations, the page_verify_option is set to CHECKSUM by default for tempdb as well as other system databases. For upgraded installations, the page_verify_option is set to NONE by default. There is a PBM policy that checks this and I recommend you to turn this on.

Continue reading...


 

TechEd India 2010 April 6th, 2010

Vinod Kumar

I am all geared for TechEd India 2010 scheduled to happen next week in Bangalore. If you are in India don’t forget to meet me at the Event from April 12th – 14th. More details on the technical tracks are at www.microsoftteched.in … I am managing close to 4 different tracks this time and I am doing two sessions on the Database track. It is completely action packed and don’t miss the action !!!

Are you coming to the event?

Continue reading...