Total Pageviews

Tuesday, April 2, 2013

DBCC CHECKDB – “After death comes the doctor”

It is a common practice for a lot of us to run DBCC CHECKDB before backing up the database. Doing so on a small database performance impact may be negligible. However, for a large and high-end OLTP system, impact is noticeable and significant.

The point is why do we run DBCC CHECKDB and what does it achieve? Generally DBCC CHECKDB is considered as a maintenance command (corruption detection and repairing) to make sure that a database is in a consistent state. it can be used to repair a suspect or corrupt database with or without losing data. Losing a single byte of production data is not desirable and running DBCC CHECKDB isn’t the only way to make sure if the database is in good shape.

In this article we will be focusing on some other options and a easy to follow checklist which will help to prevent a possible database corruption.

What is database corruption?
When one or more pages cannot be read by the database engine, the database will be marked as Suspect or Restore pending. Generally there are two types of page corruptions that can happen– logical and physical.

(a)    Physical error: 823
A Windows read or write request has failed. In the read case, SQL Server will have already retried the read request four times. This error is often the result of a hardware error, but may be caused by the device driver. This is known as CRC (cyclic redundancy check) failure.
(b)   Logical error: 824
This error indicates that Windows reports that the page is successfully read from disk, but SQL Server has discovered something wrong with the page. This error is similar to error 823 except that Windows did not detect the error. This usually indicates a problem in the I/O subsystem, such as a failing disk drive, disk firmware problems, faulty device driver, and so on. This is known as torn page detection.
(c)    Restore Pending: 829
A page has been marked as restore pending.

Why do database corruptions happen?
SQL Server Engine highly cares about database corruption and although Microsoft put excellent and tremendous effort to protect user databases from every angle, database corruption still may happen due to the following identified reasons:

(a)    Faulty disk and controller system
(b)   Outdated bios firmware
(c)    Disk Caching issue
(d)   inappropriate system driver
(e)   Power supply failure

How to prevent database corruption:
There is no precise answer to this. Generally, database corruption happens due to faulty hardware and it is more or less random in nature. Therefore proactive monitoring of various logged events and taking action against suspicious occurrences is the key to prevent database corruption.

Starting from SQL 2005, there are features in Database Engine which reduces the likelihood of page level corruption, one is "Checksum" mechanism and another is "automatic page repair".

(a)    PAGE_VERIFY  CHECKSUM (SQL 2005, and up): This is a per database setting.  When CHECKSUM is enabled for the PAGE_VERIFY database option, the SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header.

However, there is a glitch about this mechanism - the checksum are computed on the pages that are written only after the database has been enabled for checksum and re-computed when such pages are read back. Since the database was not initially created with checksum enabled, there will be pages in the database that have no checksums.

(b)   Database Mirroring (SQL 2005 SP1, and up): Automatic page repair, this feature is based on the fact that the principal and mirror databases are exactly the same. So, if a page becomes corrupt on the principal, SQL Server should be able to read the page from the mirror and use it to fix the principal. Similarly, if a page becomes corrupt on the mirror, the page can be read from the principal to fix the mirror.

(c)    AlwaysON (SQL 2012): Automatic page repair is supported by AlwaysON Availability Groups. After certain types of errors corrupt a page, making it unreadable, an availability replica (primary or secondary) attempts to automatically recover the page. The partner/replica that cannot read the page requests a fresh copy of the page from its partner or from another replica. If this request succeeds, the unreadable page is replaced by the readable copy, and this usually resolves the error.

Performance Impact:
Running DBCC CHECKDB against large database will incur significant performance impact. DBCC CHECKDB statement typically must read each allocated page from disk into memory so that it can be checked. By design this command will check allocation and structural integrity of all tables, indexes, indexed views and as well as text and image objects. Below are three major impacted areas.

(a)    Significant memory usage
(b)   Extensive I/O activities
(c)    Heavy spooling to tempdb

DBCC CHECKDB does not create any blocking and it works on a snapshot taken internally. Please note that executing DBCC CHECKDB automatically executes DBCC CHECKTABLE for each table in the database, as well as DBCC CHECKALLOC and DBCC CHECKCATALOG, eliminating the need to run them separately.

What to monitor?
Along with others routine checks, we should include the following areas in order to understand the system and database health.

(a)    SQL Server ERROR LOG for ERROR 823, 824
(b)   Windows System Events (disk, iSCSI, ftdisk, etc)
(c)    Querying msdb for possible events: SELECT * FROM msdb..suspect_pages
(d)   AlwaysON Availability Groups: SELECT * FROM sys.dm_hadr_auto_page_repair
(e)   Database mirroring: SELECT * FROM sys.dm_db_mirroring_auto_page_repair

Last ran of DBCC CHECKDB against all databases:
/***********************************************************
** Last ran of DBCC CHECKDB against all databases
***********************************************************/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @databases TABLE
        (
          Id INT IDENTITY(1, 1)
                 PRIMARY KEY ,
          ParentObject VARCHAR(255) ,
          Object VARCHAR(255) ,
          Field VARCHAR(255) ,
          Value VARCHAR(255)
        )

INSERT  INTO @databases
        ( ParentObject ,
          Object ,
          Field ,
          Value
        )
        EXEC sp_msforeachdb N'DBCC DBINFO(''?'') WITH TABLERESULTS;';

INSERT  INTO @databases
        ( ParentObject ,
          Object ,
          Field ,
          Value
        )

        SELECT  'Final Record' ,
                'Final Record' ,
                'dbi_dbname' ,
                'Final Record';

WITH    database_name ( Id, Field, Value, DBID )
          AS ( SELECT   Id ,
                        Field ,
                        Value ,
                        ROW_NUMBER() OVER ( PARTITION BY Field ORDER BY ID )
               FROM     @databases
               WHERE    Field = 'dbi_dbname'
             ),
        Last_DBCC_CHECKDB ( Id, Field, Value )
          AS ( SELECT   Id ,
                        Field ,
                        Value
               FROM     @databases
               WHERE    Field = 'dbi_dbccLastKnownGood'
             )

     SELECT DISTINCT
            db1.Value [database_name] ,
            ldc.Value [last_DBCC_CHECKDB]
     FROM   Last_DBCC_CHECKDB ldc
            INNER JOIN database_name db1 ON ldc.Id > db1.Id
            INNER JOIN database_name db2 ON ldc.Id < db2.Id
                                            AND db2.DBID = db1.DBID + 1
     ORDER BY db1.Value;

Automated Monitoring and SQL Alert Feature:
DBCC CHEKDB is a more reactive and corrective kind of command which tells us the sad story of a database’s consistency after it corrupted. It will be too late to take action when the corruption is discovered. 

In order to get early notifications regarding database issues, we can easily implement a notification service task which will let us know immediately when an issue occurred in a database.

How to enable Alert System: 
1.       Configure Database Mail and Create a mail profile
2.       Create operator who will be notified in the event of issue
3.       In SQL Server Agent attach the mail profile to the Alert System
4.        SQL Agent Server needs to be restarted to take effect of above changes
5.       Expand SQL Agent tree nodes and choose Alert and create New Alert
6.       In the Alert Dialog box – choose “SQL Server Event Alert”
7.       For “Severity” level there are couples of options, select “Fatal” type such as 023, 024, 025, etc.

Below is a Notification Configuration screenshot for alerting a database corruption error in SQL Server Instance level for all databases.


We will receive the following alert message in case of any integrity issue.

DESCRIPTION:   SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x94a8f51f; actual: 0x0275a963). It occurred during a read of page (1:0) in database ID 7 at offset 0000000000000000 in file 'C:\Temp\TestDB.mdf'.  Additional messages in the SQL Server error log or system event 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.

Database Corruption related message can be found in SQL Server ERROR LOG:

2013-04-02 11:30:15.920 spid57 Setting database option ONLINE to ON for database 'TestDB'.
2013-04-02 11:30:15.920 spid57 Starting up database 'TestDB'.
2013-04-02 11:30:15.940 spid57 Error: 824, Severity: 24, State: 6.
2013-04-02 11:30:15.940 spid57 SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x94a8f51f; actual: 0x0275a963). It occurred during a read of page (1:0) in database ID 7 at offset 0000000000000000 in file 'C:\Temp\TestDB.mdf'. Additional messages


Result of querying msdb for all possible recorded events happened in SQL Server instance level:

 
Recommendations:
Preventing database corruptions are one of the critical tasks of any DBA’s or SysAdmin’s job responsibilities. Along with the regular database backups and system checks described above, Automatic Page Repair features of database mirroring or AlwaysON can be used to prevent page level corruption.

Summary:
Honestly, no matter whatever we do, regular database and frequent transaction log backup is the only safeguard against database corruption or any disastrous situation. And even though, if the backup has not been restored successfully somewhere and periodically, nobody can guarantee that the backup is good enough to restore the database– we are not prepared for a database disaster.

So “After corruption comes the DBCC CHECKDB” is similar to “After death comes the Doctor”.

Learn from Guru: 
SQL Server DBCC CHECKDB
http://www.sqlskills.com/blogs/paul/category/checkdb-from-every-angle/

Description of using disk drive caches with SQL Server that every database administrator should know

Saturday, March 30, 2013

TCP Chimney Offloads and SQL Server Implementation

A lot of us often see the following type of sporadic messages although everything is running as usual. After the application or user receives the network related messages, there will be some trouble occurring with the system however it will behave normally again after each event.

Some logged messages can be found in SQL Server Error Log and Windows Event logs, some can be popped up within the application and others can be detected by querying the SQL Server Extended Event.

Some observed Error Messages:
·        ERROR [08S01] [Microsoft][SQL NativeClient]Communication link failure
·        System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
·        Network error 0x40 occurred while sending data to the client on process ID XX batch ID 0. A common cause for this error is if the client disconnected without reading the entire response from the server. This connection will be terminated.
·        A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
·        A fatal error occurred while reading the input stream from the network. The session will be terminated (input error: XXX, output error: XXX).
·        Network error code 0x2746 occurred while establishing a connection; the connection has been closed. 


Possible mystery behind Network Latency:
There could be a number of reasons why this happens. Following are some of the potential areas where network latency and overheads can occur sporadically or continuously.

A.      Implicit Transaction: Implicit transaction mode generates a continuous chain of transactions which causes a delay to commit or rollback each transaction. This significantly degrades the performance and reduces concurrency in a high throughput OLTP system.

B.      Network bandwidth bound application: Database Mirroring, Always ON and Transitional Replication are usually Network bound application processes. Reading and writing large amounts of data or documenting continuously may increase latency and degrade network performance.

C.      TCP Chimney offloads: “TCP Chimney Offload transfers Transmission Control Protocol (TCP) traffic processing, such as packet segmentation and reassembly processing tasks, from a computer’s CPU to a network adapter that supports TCP Chimney Offload. Moving TCP/IP processing from the CPU to the network adapter can free the CPU to perform more application-level functions. TCP Chimney Offload can offload the processing for both TCP/IPv4 and TCP/IPv6 connections if supported by the network adapter”. Thus if this setting is incorrect on the both server OS and NIC level, then performance issues are guaranteed.

D.     NIC Teaming: "NIC teaming makes two or more physical NICs appear as a single virtual one to the application, which isolates application from failures of an individual NIC. There are two types of teaming:

1.    Switch independent (the switch does not know the NICs are teamed) and
2.    Switch dependent (the network switch participates in the teaming).

NIC teaming can also result in bandwidth aggregation such that, for example, four 1GB/Sec NICs can provide an aggregate 4GB/Sec throughput. In the event of problems, the teaming has to be disabled in order to verify it is not causing the problem."

E.      Jumbo Frames: Jumbo frames are only available on gigabit networks, and all devices in the network path must support them (switches, routers, NICs, and so on). If all the networking hardware doesn't support end-to-end Jumbo Frames they should not be used and should be disabled.

"Jumbo frames" extends Ethernet to 9000 bytes and is large enough to carry an 8 KB application datagram (e.g. NFS) plus packet header overhead. If Jumbo frames is supported on a Gigabit Network then “Network Packet Size” configuration in SQL Server can be increased to 8192 in a high throughput environment.

To check whether the jumbo frames is supported by the target machine, execute following command:

ping <IP or server name> -f –l 9000

Figure: Jumbo frames support check


F.       Maximum Transmission Unit (MTU): The Maximum Transmission Unit (MTU) feature is an advanced configuration that allows determining the largest data size permitted on a network connection. Generally, if the MTU is too large for the connection, computer will experience packet loss or dropping connection.

To determine the correct MTU size for a network, we have to do a specific ping test on the destination (target server).For Windows environment, use the following command for the ping test:

Syntax: ping [server or IP address] –f –l xxxx
Example: ping 192.168.0.50 –f –l 1472

This test can start from 1472 until we can reach the exact packet size which will not result to the prompt “Packet needs to be fragmented but DF set.” You may adjust (decrease/increase) the value by 10.

Implicit Transaction and TCP Chimney OffLoads:
The DML centric application which mostly depends on implicit transactions of SQL Server generates a lot of LOG I/O which initiates further delay on log writing process and introduces network latency.  Turning of TCP Chimney offloads is one of the “first things to do” that can be considered in order to eliminate any possibilities in this area.

“TCP Chimney Offloads” functionality does not work with virtualized Windows OS (vmware and hyper-v), therefore it can be disabled permanently.

This is one of the potential overlooked areas by many sysadmin, which needs further investigation if the received error message looks similar to the issues which have been mentioned before in the article. Although it is not the best practice, it is still recommended by many Network Experts that the “TCP Chimney Offloads” setting must be disabled in both Windows OS and NIC level to reduce latency in SQL Server implementation whether it is virtual or physical.

Windows OS and TCP Chimney offloads:
In Windows 2003 - TCP Chimney Offload is enabled by default.
In Windows 2008 - TCP Chimney Offload is disabled by default.
In Windows 2008 R2 - TCP Chimney Offload is automatic by default.
In Windows 2012 -TCP Chimney Offload is disabled by default.

How to check TCP Chimney Offloads:
·         In Windows 2003 use the following command:
netsh interface ip show offload
·         In Windows 2008 and above use the following command:
netsh int tcp show global

Figure: Screenshot from Windows 2008 R2



Screenshot from Windows 2012


XE Events (Extended Events) Query:
/***************************************************************
** Errors reported along with the error counts
****************************************************************/

SET NOCOUNT ON

-- Store the XML data in a temporary table
SELECT  CAST(xet.target_data AS XML) AS XMLDATA
INTO    #xeTmpTbl
FROM    sys.dm_xe_session_targets xet
        JOIN sys.dm_xe_sessions xe ON ( xe.address = xet.event_session_address )
WHERE   xe.name = 'system_health'

-- Get statistical information about all the errors reported
;WITH    myXEinfo ( EventXML )
          AS ( SELECT   C.query('.') EventXML
               FROM     #xeTmpTbl a
                        CROSS APPLY a.XMLDATA.nodes('/RingBufferTarget/event') AS T ( C )
             ),
        myXEErrorInfo ( EventTime, ErrorNum )
          AS ( SELECT   EventXML.value('(/event/@timestamp)[1]', 'datetime') AS EventTime ,
                        EventXML.value('(/event/data/value)[1]', 'int') AS ErrorNum
               FROM     myXEinfo
               WHERE    EventXML.value('(/event/@name)[1]', 'varchar(255)') = 'error_reported'
             )
    SELECT  ErrorNum ,
            MAX(EventTime) AS LastRecordedEvent ,
            MIN(EventTime) AS FirstRecordedEvent ,
            COUNT(*) AS Occurrences ,
            b.[text] AS ErrDescription
    FROM    myXEErrorInfo a
            INNER JOIN sys.messages b ON a.ErrorNum = b.message_id
    WHERE   b.language_id = SERVERPROPERTY('LCID')
    GROUP BY a.ErrorNum ,
            b.[text]

--  Get information about each of the errors reported
 ;
WITH    myXEinfo ( EventXML )
          AS ( SELECT   C.query('.') EventXML
               FROM     #xeTmpTbl a
                        CROSS APPLY a.XMLDATA.nodes('/RingBufferTarget/event') AS T ( C )
               WHERE    C.query('.').value('(/event/@name)[1]', 'varchar(255)') = 'error_reported'
             )
    SELECT  EventXML.value('(/event/@timestamp)[1]', 'datetime') AS EventTime ,
            EventXML.value('(/event/data/value)[1]', 'int') AS ErrNum ,
            EventXML.value('(/event/data/value)[2]', 'int') AS ErrSeverity ,
            EventXML.value('(/event/data/value)[3]', 'int') AS ErrState ,
            EventXML.value('(/event/data/value)[5]', 'varchar(max)') AS ErrText
            --EventXML.value('(/event/action/value)[2]', 'varchar(10)') AS Session_ID
    FROM    myXEinfo
    ORDER BY EventTime DESC
--  Drop the temporary table
DROP TABLE #xeTmpTbl


/**************************************************************************
** Extract Ring Buffer Information for SQL Server 2008 instances and above
**************************************************************************/
SELECT  CONVERT (VARCHAR(30), GETDATE(), 121) AS run_time ,
        DATEADD(ms, ( a.[record_time] - sys.ms_ticks ), GETDATE()) AS [notification_time] ,
        a.* ,
        sys.ms_ticks AS [current_time]
FROM    ( SELECT    x.value('(//Record/Error/ErrorCode)[1]', 'varchar(30)') AS [error_code] ,
                    x.value('(//Record/Error/CallingAPIName)[1]', 'varchar(255)') AS [calling_API_name] ,
                    x.value('(//Record/Error/APIName)[1]', 'varchar(255)') AS [API_name] ,
                    x.value('(//Record/Error/SPID)[1]', 'int') AS [SPID] ,
                    x.value('(//Record/@id)[1]', 'bigint') AS [record_id] ,
                    x.value('(//Record/@type)[1]', 'varchar(30)') AS [type] ,
                    x.value('(//Record/@time)[1]', 'bigint') AS [record_time]
          FROM      ( SELECT    CAST (record AS XML)
                      FROM      sys.dm_os_ring_buffers
                      WHERE     ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR'
                    ) AS R ( x )
        ) a
        CROSS JOIN sys.dm_os_sys_info sys
ORDER BY a.[record_time] ASC

Reference:
·         Using TCP Chimney Offload
·         TCP Offloading again?!
·         Network Changes Affect Windows Server 2012
·         Information about the TCP Chimney Offload, Receive Side Scaling, and Network Direct Memory Access features in Windows Server 2008
·         Error message when an application connects to SQL Server on a server that is running Windows Server 2003: "General Network error," "Communication link failure," or "A transport-level error"
·         How to Disable TCP Chimney, TCPIP Offload Engine (TOE) or TCP Segmentation Offload (TSO).
·         TCP Chimney Offload – Possible Performance and Concurrency Impacts to SQL Server Workloads