NonClustered Index Corruption

Hi,
Yesterday I had some corruption in a nonclustered index on a table that
contains over 150 million records. After determining that the NC index was
the cause, I removed the NC index and reran the DBCC CHECKDB statement. The
results were no errors after 20 minutes. Today I recreated the NC index
(single column). I reran both the DBCC CHECKTABLE and DBCC CHECKDB (doing
both to make sure something else isn't causing the issue) - both hung again
(over 5 hours yesterday). Dropping the NC index again and the checks work
fine. Any ideas?
Thanks
JerryFirst off there is no need to run Checktable if you run CheckDB since that
includes it. You say they were hung. What exactly does that mean? Were
they being blocked? Was the spid doing anything? Is the NCI on a single
column, computed etc? In any case if you have hardware corruption going on
it will usually manifest itself in the most active process. Creating an
index on a 150M row table gives a lot of opportunity for corruption if the
hardware is suspect. If you have bad disk sectors then creating the index
again might very well use the same space on the drive.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
> Hi,
> Yesterday I had some corruption in a nonclustered index on a table that
> contains over 150 million records. After determining that the NC index
> was the cause, I removed the NC index and reran the DBCC CHECKDB
> statement. The results were no errors after 20 minutes. Today I
> recreated the NC index (single column). I reran both the DBCC CHECKTABLE
> and DBCC CHECKDB (doing both to make sure something else isn't causing the
> issue) - both hung again (over 5 hours yesterday). Dropping the NC index
> again and the checks work fine. Any ideas?
> Thanks
> Jerry
>|||Andrew,
As I said in the original post I was running both to ensure that some other
table/index etc... was not causing the issue...yes I knowthat DBCC CHECKDB
includes DBCC CHECKTABLE. Again as I stated in the original post the NC
index is on a single column - no it is not a computed column. Hung? A 20
minute operations (DBCC CHECKDB) takes more than 6 hours and never completes
and has to be stopped manually. It could be a hardware issue (RAID 5).
What operations would you suggest to determine if this could be a HW issue?
Thanks
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
> First off there is no need to run Checktable if you run CheckDB since that
> includes it. You say they were hung. What exactly does that mean? Were
> they being blocked? Was the spid doing anything? Is the NCI on a single
> column, computed etc? In any case if you have hardware corruption going
> on it will usually manifest itself in the most active process. Creating
> an index on a 150M row table gives a lot of opportunity for corruption if
> the hardware is suspect. If you have bad disk sectors then creating the
> index again might very well use the same space on the drive.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>|||The Disk Management tool shows the logical drive as "Healthy". The system
log in the Event Viewer shows no HW releated disk errors. I checked the
RAID tool on the server and no issues/errors were reported. The network
admin says that we will need to wait til afterhours to run chkdsk. Other
suggestions?
Thanks.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
> Andrew,
> As I said in the original post I was running both to ensure that some
> other table/index etc... was not causing the issue...yes I knowthat DBCC
> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original post
> the NC index is on a single column - no it is not a computed column.
> Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6 hours and
> never completes and has to be stopped manually. It could be a hardware
> issue (RAID 5). What operations would you suggest to determine if this
> could be a HW issue?
> Thanks
> Jerry
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>|||Jerry,
Sorry I didn't catch the single column. What level of DBCC did you run?
Try with the Physical_Only option first just to see if it goes through. In
any case unless it is blocked it should not just sit there doing nothing for
6 hours. Are you certain it was not just blocked? Hmmm, I would have
expected some errors if it were disk related. I would run chkdisk to be
sure anyway when you can. Next time you try it take a look at the SPID in
sysprocesses to see how much I/O CPU etc. it used before it "hangs".
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
> The Disk Management tool shows the logical drive as "Healthy". The system
> log in the Event Viewer shows no HW releated disk errors. I checked the
> RAID tool on the server and no issues/errors were reported. The network
> admin says that we will need to wait til afterhours to run chkdsk. Other
> suggestions?
> Thanks.
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>|||Andrew,
No worries. I was running DBCC CHECKDB (with the default options). I
dropped and recreated the nonclustered index and reran the DBCC CHECKDB
statment. This time the statement returned in a timely manner with no
errors reported. I'm thinking the drive(s). Does the SQL Server service
deed to be stopped before I have the network guy run the chkdsk command?
Thanks again.
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>|||I don't know for sure it it is required to be stopped but I would if I
could<g>. Make sure you have a good backup first.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> No worries. I was running DBCC CHECKDB (with the default options). I
> dropped and recreated the nonclustered index and reran the DBCC CHECKDB
> statment. This time the statement returned in a timely manner with no
> errors reported. I'm thinking the drive(s). Does the SQL Server service
> deed to be stopped before I have the network guy run the chkdsk command?
> Thanks again.
> Jerry
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
>|||Got it. Thanks Andrew.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23lGa7ujvFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I don't know for sure it it is required to be stopped but I would if I
>could<g>. Make sure you have a good backup first.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>|||It wasn't hung - it most likely found some non-clustered index errors and
returned to do a 'deep-dive' where it manually looks up the matching base
table row for each non-clustered index row, and vice-versa to determine
which rows are in error. The normal non-clustered index check mechanism uses
a sophisticated hashing mechanism to avoid such an n-squared algorithm (and
so is very fast) but if an error is discovered, the very-slow deep-dive is
necessary to determine which rows are in error.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>