0

I've come across a production SQL Server 2008 box that has quite a few databases with PAGE_VERIFY option set to NONE. I'm going to set the PAGE_VERIFY option to CHECKSUM. If I understand correctly, I must rebuild the existing indexes for CHECKSUM to affect them. Also, that if I rebuild a clustered index the non-clustered indexes will be rebuilt as well.

  1. What will happen to the existing indexes if I can't rebuild them right away?
  2. Are the existing indexes still usable after setting CHECKSUM without a rebuild?

Thanks!

  • 1
    The new setting gets applied to pages as they are dirtied and written back to disk. There's no operational change otherwise. – Jon Seigel Jan 30 '14 at 20:53
1

"Also, that if I rebuild a clustered index the non-clustered indexes will be rebuilt as well." -- if you rebuild a clustered index, the non-clustered indexes won't be rebuild, please see Paul's blog post -- http://www.sqlskills.com/blogs/paul/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed/ -- even though it says 2005, it's true for any version, as far as I know. 1. Nothing will happen 2. Will be usable

Also can read in Paul's blog, Myth a Day -- http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1730-page-checksums/

As you and Jon, added -- there are 2 ways (from Paul's blog): 1. There is no process, background or otherwise, that can put a page checksum on each page. This is a major bummer (technical term :-) as it means you must perform index rebuilds or other size-of-data operations to actually put a page checksum on the pages. 2. A page checksum is only put on a page when it is read into memory, changed, and then written back out to disk.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.