Sunday, April 15, 2012

Data Corruption Part 1: Nonclustered Index Corruption

                In this mini-blog-series I will be going over the different victims of data corruption:  Mainly nonclustered indexes (this post), clustered indexes, and system tables.  The first part of this blog post is how to corrupt a nonclustered index in a TEST environment in order to learn and practice the resolution to such a problem.  The base database I’ll be using is AdventureWorks2012 (it can be found here).  I then restored a copy of AdventureWorks2012 to corrupt, and called it AdventureWorks2012_CorruptNCI:

restore database AdventureWorks2012_CorruptNCI
from disk = 'C:\DatabaseFiles\AdventureWorks\AdventureWorks2012_base.bak'
with
       move 'AdventureWorks2012_Data'
              to 'c:\MyDataDir\AdventureWorks2012_CorruptCI_Data.mdf',
       move 'AdventureWorks2012_Log'
              to 'c:\MyLogDir\AdventureWorks2012_CorruptCI_log.ldf',
       stats = 5
go


Corrupting the Data
                First off, I want to emphasize that we are corrupting data!  In other words, THIS NEEDS TO BE DONE IN AN ISOLATED TEST ENVIRONMENT WITH TEST DATA AND DATABASES.  What we want to do now is corrupt a nonclustered index in our new AdventureWorks2012_CorruptNCI database.  I randomly chose the Person.EmailAddress table, so I wanted to get all of the indexes on that table:

select
       t.name as table_name,
       i.name as index_name,
       i.index_id,
       i.type_desc
from sys.indexes i
inner join sys.tables t
on i.object_id = t.object_id
where t.name = 'EmailAddress'
order by i.index_id

                After viewing the result set, I see there is nonclustered index on this table called IX_EmailAddress_EmailAddress (note the index_id of 2):  That will be our victim.  Now we want to see all of the page information of this index.  In order to do this, we’ll utilize the DBCC IND command to output a record per index page showing us certain page metadata:

dbcc ind('adventureworks2012_corruptnci', 'person.emailaddress', 2)
go

                This shows us a list of all pages in the nonclustered index, IX_EmailAddress_EmailAddress.  Choose a page, any page.  This page will be the one that we target for data corruption.  I have randomly selected a data page with an ID of 18250 (your PagePID may vary).  Now we want to just get a glimpse of the data contained on that page, and to do that we’ll make use of the DBCC PAGE command:

dbcc page('adventureworks2012_corruptnci', 1, 18250, 3)
go

                Now that we know we want to corrupt our specific page (PagePID 18250 in my case), I’ll do a quick calculation to get the page offset in the file.  We know that a page is 8 KB = 8192 B:

select 18250 * 8192
-- page offset: 149504000

                I get a resulting page offset of 149504000.  In order to actually corrupt the containing data file, we need to take the database offline to release the NTFS lock on the file:

use master
go

alter database AdventureWorks2012_CorruptNCI
set offline
with rollback immediate
go

                Now for the fun part:  Corrupting the data.  You can use a hex editor to alter the contents of the data file, and the one I typically use for direct file manipulation is XVI32 Hex Editor (it’s free and exactly what you need for this).  It can be found here.  The following explicit instructions are directions for this particular application, so if you are not using the XVI32 Hex Editor, then you may need to do different steps. 

Open up the AdventureWorks2012_CorruptNCI data file that contains the nonclustered index.  Click on Address and then GoTo and input your page offset (ensure that the decimal format is selected).  In my case, I entered in 149504000.  Click OK and then XVI32 will take you right to the beginning of that particular page.  On the right you will see remnants of the actual data of the table (if you’ve opted to use the EmailAddress table as well, you’ll see AdventureWorks email addresses).  On the left will be the hex representation of that data.  Click anywhere on that data page (I recommend using the right side to navigate to a character), and then zero out (00) the hex value for that byte.  That is enough!  Our data is now corrupted.  Save and close the data file.

Back in SSMS, set the database online:

use master
go

alter database AdventureWorks2012_CorruptNCI
set online
go

                Now I want to just make sure that we definitely corrupted the index:

use AdventureWorks2012_CorruptNCI
go

select EmailAddress
from Person.EmailAddress

                This should give you an error stating that “SQL Server detected a logical consistency-based I/O error: incorrect checksum…….” so on and so forth.


Fixing a Corrupted Nonclustered Index
                So the above was just a really slick way to corrupt a nonclustered index in our test database.  We broke it, and now it’s time to fix it.  But in reality, we initially won’t know exactly what is corrupt.  So you run a routine DBCC CHECKDB, or a user has an error of sorts, and now it’s time to get to the bottom of it.  Running a DBCC CHECKDB will give you the information you’ll need as to what’s corrupt:

dbcc checkdb with no_infomsgs

                The biggest takeaway here is the partition ID of the corrupt table/index.  In my case, my DBCC CHECKDB tells me the issue is with partition number 72057594050445312.  So now I want to find out exactly what table and index that is for (if you did the above process of data corruption, you already know.  But just for explanation’s sake, view the query results):

select
       p.partition_id,
       o.name as table_name,
       i.name as index_name,
       i.type_desc
from sys.partitions p
inner join sys.objects o
on o.object_id = p.object_id
inner join sys.indexes i
on o.object_id = i.object_id
and p.index_id = i.index_id
where partition_id = 72057594050445312

                Bingo!  This tells me that the corrupt partition is indeed a nonclustered index on Person.EmailAddress, and the faulty index is IX_EmailAddress_EmailAddress.

                When a nonclustered index is corrupt, the first step is to try a rebuild (and, it goes without saying but a little investigation as to the cause of the data corruption is warranted and necessary, otherwise for things like a failing drive, it’ll happen again):

alter index IX_EmailAddress_EmailAddress
on Person.EmailAddress
rebuild
go

                If the rebuild isn’t possible, you may need to drop and recreate the index.  The easiest way to do this is through SSMS:  Right-Click on the index and select Script Index as DROP and CREATE to New Query Window.

                And, just for good measure I’ll run my DBCC CHECKDB to ensure that all is well.  And in my case, it is:

-- run DBCC CHECKDB to ensure the database is back to normal
dbcc checkdb with no_infomsgs


Summary
                When we run into corrupt data, our first step is to find out what exactly is that is corrupt.  Far too often we want to run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS, but that isn’t always the right answer.  What was explained above is when the victim of data corruption is a nonclustered index.  The first step is to try to rebuild the index if possible.  No harm, no foul.  In the next couple of blog posts I’ll be going over different victims of data corruption and their fixes.  If there are any questions, comments, or issues please feel free to leave a comment or email me at sqlsalt@gmail.com.

Code
                As always, the corresponding code can be found on my SQLSalt GitHub repository:


Comment
                Thanks to Paul Randal for his insightful comment and correction on my post, in regards to rebuild the corrupt index in lieu of jumping to a drop and create first (as well as scripting out the DROP and CREATE through SSMS).  Paul Randal is a leading expert in all things SQL Server.  Thanks, Paul!!  Much appreciated.  Quoted comment from Paul Randal for reference:
“Beware of dropping and recreating the index - if it's enforcing a constraint then the constraint could be violated while the index is dropped, meaning it can't be created again. Better to try rebuilding it first always. I'd also advise using SSMS to script-create the index before dropping it just in case the system tables change in a future release - you don't have to rely on your own code.”

Wednesday, April 11, 2012

Filtered Indexes


                There are many performance and space-saving strategies that we can employ as Database Administrators and Developers.  One of the tried-and-true methods is through the utilization of filtered indexes.  Below is how you would create a filtered index:

create index IX_YourIndexName
on YourTable(YourKeyColumn)
where YourColumn < 10
go

                In the above snippet, you can see that creating a filtered index is very much like creating any other index:  The alteration is through the use of the WHERE clause to narrow down the scope of the indexed rows.  Namely two advantages of filtered indexes are the saved space, as well as the maintenance of the index could potentially be less.

                When would you use a filtered index?  Often times we have tables that contain data that won’t ever be queried (or queried often enough for the storage consumption and indexing benefits).  For instance, if you only typically look for products that actually have a cost associated, you are narrowing down your query.  And because it may heed us to have an additional index on this table, the rows that have no cost associated with them will most likely never be retrieved (or not enough to warrant inclusion in the index).

                Here’s an example of real-time space-saving proof.  (The below example uses the AdventureWorks2012 sample database which can be found here)

                I wanted to choose a relatively large table in the AdventureWors2012 database to emphasize the filtered index advantage, so I chose the Sales.SalesOrderDetail table.  Run the below query to view a summary of the indexes on this table:

use AdventureWorks2012
go

select
       object_name(p.object_id) as table_name,
       i.name as index_name,
       i.type_desc as index_type,
       ps.row_count,
       ps.reserved_page_count,
       ps.reserved_page_count * 8 as reserved_space_kb
from sys.partitions p
inner join sys.indexes i
on p.object_id = i.object_id
and p.index_id = i.index_id
inner join sys.objects o
on p.object_id = o.object_id
inner join sys.dm_db_partition_stats ps
on p.partition_id = ps.partition_id
where o.is_ms_shipped = 0
and object_name(p.object_id) = 'salesorderdetail'
order by ps.row_count desc

                Now focus in on the nonclustered index called IX_SalesOrderDetail_ProductID:  It consumes a total of 2624 KB of space.  In order to get what the indexed columns are for this index, we can run the following simple query:

use AdventureWorks2012
go

select
       object_name(i.object_id) as table_name,
       i.name as index_name,
       i.type_desc,
       c.name as column_name,
       ic.is_included_column
from sys.indexes i
inner join sys.index_columns ic
on i.object_id = ic.object_id
and i.index_id = ic.index_id
inner join sys.objects o
on i.object_id = o.object_id
inner join sys.columns c
on c.object_id = i.object_id
and ic.column_id = c.column_id
where o.is_ms_shipped = 0
and object_name(i.object_id) = 'salesorderdetail'
order by table_name, i.type_desc, index_name

So now we know that the indexed column is ProductID (as the index name would have suggested).  Let’s go ahead and drop that index (for testing purposes only).

-- drop the out-of-the-box NCI on ProductID
drop index IX_SalesOrderDetail_ProductID
on Sales.SalesOrderDetail
go

                Why?  Well say that for whatever business domain reason we typically never query records in this table that have a ProductID of greater than 800.  We don’t want the overhead of maintaining this extra index, so we are going to drop it with hopes of a newer, smaller filtered index.  Now create the filtered index based off of our guidelines:

-- create the filtered index
create index IX_SalesOrderDetail_ProductID_Filtered
on Sales.SalesOrderDetail(ProductID)
where ProductID < 800
go

                There you have it.  Now we have a filtered index on the Sales.SalesOrderDetail.  Go ahead and run the index consumption query again to find out our space savings:

use AdventureWorks2012
go

select
       object_name(p.object_id) as table_name,
       i.name as index_name,
       i.type_desc as index_type,
       ps.row_count,
       ps.reserved_page_count,
       ps.reserved_page_count * 8 as reserved_space_kb
from sys.partitions p
inner join sys.indexes i
on p.object_id = i.object_id
and p.index_id = i.index_id
inner join sys.objects o
on p.object_id = o.object_id
inner join sys.dm_db_partition_stats ps
on p.partition_id = ps.partition_id
where o.is_ms_shipped = 0
and object_name(p.object_id) = 'salesorderdetail'
order by ps.row_count desc

                We should get a reserved_space_kb of now roughly 968 KB.  Due to the use of a filtered index (intelligently created, of course), we were able to knock down the size of that index to about 37% of the original size.

                Filtered indexes can be a great way to minimize wasted storage and index maintenance overhead.  With the knowledge of this tool and the appropriate application, it can yield impressive results.

                The applicable code for this blog post can be found on my GitHub SQLSalt repository.  The following links to the source code are:

                If there are any comments, questions, or issues please feel free to leave a comment or email me at sqlsalt@gmail.com.

Monday, April 9, 2012

Dedicated Administrator Connection

                There will be times when a standard connection to SQL Server isn’t possible and as a DBA you’ll need to connect to troubleshoot issues with the instance.  You can achieve this through the Dedicated Administrator Connection (known as the DAC).  The DAC has its own set of resources so that even upon memory contention, you will be able to obtain a connection (provided you are utilizing a login in the sysadmin fixed server role).

Enabling Remote Admin Connections
                By default, you can only connect to the instance’s DAC on the server locally (i.e. Network connections aren’t allowed).  This ensures the surface area of the instance is as small as possible out of the box.  But to enable remote connections to the DAC, you simply need to set the instance configuration appropriately:

-- set 'remote admin connections' enabled
exec sp_configure 'remote admin connections', 1
go
reconfigure
go

DAC Port
                When SQL Server starts up, it will establish a listening port for the DAC.  For a Default instance SQL Server will attempt to establish TCP 1434.  But if it can’t allocate this port, or if it is a named instance then SQL Server will dynamically reserve a port.  During instance startup when this happens, SQL Server logs the established port in the Error Log.  In order to find out what port this is, you can do the following:

exec sp_readerrorlog 0, 1, 'dedicated admin connection'
go

The Text field will show something like this:
Dedicated admin connection support was established for listening remotely on port 1434.

Connecting to the DAC
                There are two ways to connect to the DAC: through SQLCMD and SSMS.

SQLCMD
                To connect to the DAC through SQLCMD, you will need to utilize the –A switch.  A sample connection would look as follows:

      sqlcmd –S YourServer\YourInstance –U sa –P My$ecur3P@$$w0rd –A

SQL Server Management Studio (SSMS)
                To connect through SSMS, there’s a bit of a “gotcha”.  You can’t connect the Object Explorer to the DAC due to the nature of SSMS using multiple connections normally.  To get around this, you will need to follow the below steps:
1.       Click on the “New Query” button to open a New Query Window: 

2.       Right-click in the query window and mouse-over “Connection”.  If this window already has a connection, you can select “Change Connection…” or if not simply select “Connect…”: 

3.       Now you will be prompted with the connect dialog, and to connect to the DAC through SSMS you just need to prefix the ServerName\InstanceName with the string “admin:”, as follows: 



As shown above, the DAC is a relatively easy portion of SQL Server to analyze and connect to, but equally an essential instrument in the troubleshooting DBA’s toolbox.  If there are any comments, questions, or issues please feel free to leave a comment or email me at sqlsalt@gmail.com.

Code
                As always, this code/information can be found on my SQLSalt GitHub repository: