Why Database Integrity Checking (CHECKDB) is needed

DBCC CHECKDB is a resource expensive process, which can cause performance problem when run concurrently with business workload. However, it's a must to run it periodically, because it has the non-replaceable ability to check the logical and physical integrity of all the objects in your database. DBCC CHECKDB essentially includes DBCC CHECKALLOC, DBCC CHECKCATALOG, and DBCC CHECKTABLE on every table and view in the database.
Someone suggested stop to run DBCC CHECKDB, and instead rely only on the WITH CHECKSUM option of database full backup. By the way, CHECKSUM can only detect page corruption within the I/O subsystem (disk), but not any corruption within the memory (RAM). CHECKSUM is the default page protection, which means that when a data page is flushed to disk (usually by a periodic CHECKPOINT), the last to do is a checksum is calculated for the 8KB data page contents and written into the page's header. When, subsequently, SQL Server reads a data page into memory from disk, the first to do is that it recalculates the page checksum and checks it against the value stored in the page header. If the checksums do not match, SQL Server knows that something in the I/O subsystem. SQL Server will also verify the page checksums during database backup, but only when the WITH CHECKSUM specified on the BACKUP statement.
So why isn't using WITH CHECKSUM with BACKUP a substitute for consistency checking using DBCC CHECKDB, as both operations will check page checksums? Consider the following scenario: SQL Server reads a data page into memory and then modifies it. Unfortunately, after the page is modified, but before the next checkpoint operation, a faulty memory chip causes a corruption in the 8KB block of memory that is holding the modified data page. When the checkpoint occurs, it calculates a page checksum over the data file page contents, including the portion corrupted by the faulty memory chip, writes the page checksum into the page header, and the page is written out to disk. Later, a query causes SQL Server to read this data page from disk and so it validates the page checksum and, assuming nothing went wrong at the I/O-subsystem, the checksum values will match, and it will not detect any problem. The page is corrupt in memory, but the page checksum algorithm cannot detect it. Similarly, a backup with checksum will not detect in-memory corruption; the backup operation simply reads the data pages, calculates the checksum, compares the value to what it was when the page was last written to disk, finds the values match, and writes the in-memory corrupted page into the backup file.
DBCC CHECKDB interprets the page contents (for example, validating row structures, validating column values, checking linkages in indexes), and so should always be able to detect a corrupt page, even if the corruption happened while in memory. We need to run regular consistency checks by DBCC CHECKDB, but not necessarily on the production server. A good alternative is to restore database backup on another server and then run CHECKDB on it. Or if your database is mirrored, you can run CHECKDB on a database snapshot that was created from the mirror. With respect to corruption, one great feature in SQL Server 2008 is the ability to automatically repair corrupt pages during database mirroring, you can see the article "Automatic Page Repair During a Database Mirroring Session" for more information.
To summarize, DBCC CHECKDB consistency check should always be run regularly, using whatever method allows you to run them that does not impact, or at least minimizes the impact on, your production workload.

Query Tuning example 1 - NOT IN

Here's a real life example showing NOT IN really hurts query performance.
Below is the SQL code snippet extracted from a query to generate a report, which is written by a programmer from external vendor (I don't wanna mention which one, although I'm quite disappointed at it's work):
SELECT ... FROM ... WHERE ... AND demand.time_expense_sheet_id NOT IN (SELECT order_id FROM c_order_line) ...
The c_order_line table contains 1 million of rows in our production environment. That query takes 2 minutes to execute. My boss asked me to troubleshoot it, as users complain again and again that the report cannot be loaded and timeout error raised.
First, I used SQL Profiler to trace what queries are really running, and which query is the slowest one.

I pick the slowest query, execute it on testing environment by using Management Studio, with the "Include Actual Execution Plan" enabled. Then I execute the query, and open the returned actual execution plan by SQL Sentry Plan Explorer (a free tools you must love), and identified the top operation was a Index Scan on that huge table c_order_line.

By checking the SQL query, I found that table was being used in a NOT IN clause. So I send these findings to that favorable vendor (just kidding), they replied me that NOT IN checking may be unnecessary (-_-"). Later they sent me the fix, just removed that NOT IN clause, that report now can be generated within 10 seconds. My users feel happy, my boss feel happy :)

Get SQL Server Database Disk Space Usage

CREATE TABLE #DBInfo
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
Status sysname,
Updateability sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate datetime,
maxsize int,
growth int
)

DECLARE @command VARCHAR(5000)

SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + '''' + ' AS DatabaseName,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +
       'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,
GETDATE() as PollDate, maxsize,
CAST(sysfiles.growth/128.0 AS int) AS growth
FROM dbo.sysfiles'
INSERT INTO #DBInfo
   (ServerName,
   DatabaseName,
   FileSizeMB,
   LogicalFileName,
   PhysicalFileName,
   Status,
   Updateability,
   RecoveryMode,
   FreeSpaceMB,
   FreeSpacePct,
   PollDate,
   maxsize,
   growth)
EXEC sp_MSForEachDB @command

SELECT
   ServerName,
   DatabaseName,
   RTRIM(LogicalFileName) AS LogicalFileName,
   FileSizeMB,
   LEFT(PhysicalFileName, 1) AS Drive,
   PhysicalFileName,
   Status,
   Updateability,
   RecoveryMode,
   FreeSpaceMB,
   FreeSpacePct,
   PollDate,
   maxsize,
   growth
FROM #DBInfo
ORDER BY
   ServerName,
   DatabaseName

DROP TABLE #DBInfo

EXEC master..xp_fixeddrives

Get Table or Index name from resource Id returned by blocked process/deadlock report XML

Blocked Process Report and Deadlock Graph XML only report the affected object (table/index) as ID number, either object id, HOBT id, or page id. Below demonstrates how to resolve into object name.


Object ID (OBJECT:db_id:object_id)
SELECT OBJECT_NAME(db_id, object_id);

HOBT ID (KEY:db_id:hobt_id)
SELECT o.name AS TableName, i.name AS IndexName, SCHEMA_NAME(o.schema_id) AS SchemaName FROM sys.partitions p JOIN sys.objects o ON p.OBJECT_ID = o.OBJECT_ID JOIN sys.indexes i ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id WHERE p.hobt_id = hobt_id

Page ID (PAGE:db_id:file_id:page_id) or Row ID (RID:db_id:file_id:page_id:slot)
DBCC TRACEON(3604)
GO
DBCC PAGE(db_id, file_id, page_id)
The object id will be shown in the Metadata: Object_Id = ???
The index id will be shown in the Metadata: IndexId = ???
Then you can check it by OBJECT_NAME function and sysindex dmv.

Ref.: http://support.microsoft.com/kb/224453

My forth article "SQL Coding Practices"

My forth article SQL Coding Practices, completed!

SQL Server specific Performance Monitor (PerfMon) Counters

Processor:
- Process (sqlservr): % Processor Time (desired value: < 80% per core)
- Process (sqlservr): %Privileged Time (desired value: < 30%)

Memory:
- SQL Server:Buffer Manager: Buffer Cache Hit Ratio (desired value: > 90%)
- SQL Server:Buffer Manager: Page Life Expectancy (desired value: > 300 secs)

Disk:
- SQL Server:Latches: Total Latch Wait Time (ms)
- SQL Server:Latches: Latch Waits/sec (desired value: (Total Latch Wait Time) / (Latch Waits/Sec) < 10)

My third article "Create Transactional Replication from Backup"

My third article Create Transactional Replication from Backup, completed!