2.4 Optimizing DBCC CHECKDB (53m)
DBCC CHECKDB is easy with small databases – just run it every day. But as your database grows, this quickly stops becoming an option, and you need to start cutting corners. Learn how to cut them safely by running DBCC CHECKDB at a realistic frequency, discover how to run it faster, and see which SQL Server options like backup checksums and page checksums can give you CHECKDB-like protection without the long maintenance windows.
- 1.1 Intro and DBA Skills Quiz (33m)
- 1.2 Building and Testing Reliable SQL Servers Part 1 (25m)
- 1.2 Building and Testing Reliable SQL Servers Part 2 (21m)
- 1.3 Architecture for HA and DR Part 1
- 1.3 Architecture for HA and DR Part 2
- 1.4 Design Quorum for Failover Clusters (23m)
- 1.5 Triaging Failure in Availability Groups (32m)
- 1.6 Recovering from Failover Part 1 (40m)
- 1.6 Recovering from Failover Part 2 (12m)
- 1.7 Building an Inventory and a Support Matrix (39m)
- 2.1 Database Mirroring Field Medic Guide (40m)
- 2.2 Transaction Log Shipping Tips and Tricks (45m)
- 2.3 Troubleshooting Backup and Restore Problems (35m)
- 2.5 Availability Group Backup and CHECKDB Part 1 (8m)
- 2.5 Availability Group Backup and CHECKDB Part 2 (32m)
- 2.6 Cloud for the Senior DBA (38m)
- 2.7 Homework: Deciding Between Availability Solutions Part 1 (11m)
- 2.7 Homework: Deciding Between Availability Solutions Part 2 (27m)
- 3.1 Shared Storage Part 1 (28m)
- 3.1 Shared Storage Part 2 (32m)
- 3.2 Advanced SAN Features – Storage Tiering and Snapshots (31m)
- 3.3 Virtualization Management and Troubleshooting (61m)
- 3.4 Server Hardware Sizing (36m)
- 3.5 Homework Part 1 (11m)
- 3.5 Homework Part 2: Answers (28m)
- 3.6 Index Maintenance for Enterprise Environments (43m)
- 3.7 Recap and Q&A (28m)
8 Comments. Leave new
Excellent !! learned some fantastic things about CHECKDB and data corruption. Thank you Brent.
Very helpful, Thanks Brent! Quick question, for SQL server prior to 2014 and 2014SP1 as well I believe all CHECKDB operations are run with default of MAXDOP1. However post 2014 SP2 and above if one simply runs CHECKDB without any MAXDOP leaving default what is happening? Will it take my server level MAXDOP setting say 8 and what you said will flush the cached plans? or is that only applicable when I run CHECKDB WITH MAXDOP option? Sorry for bombarding with those questions but just confused on this part. Thank you!
Kapil – I’m not sure what you mean about that MAXDOP 1. CHECKDB has been parallel for quite a while.
Sorry I meant in older versions like SQL2012 CHECKDB running serially. I believe that parameter to give MAXDOP on CHECKDB started post 2014SP2. So my worry is when that plan will be flushed from cache, only when running DBCC CHECKDB with some MAXDOP parameter specified or every time when my DBCC CHECKDB runs on latest SQL servers as you said they have been parallel for a while? Thanks
I’m still not agreeing with you here. Let’s do this: go find the material that supports your claim that CHECKDB ran serially in those versions, and then run tests to get the answer to your question. (I don’t think you’re asking the right question, and I don’t think you have the right background info, and I want to respect your time by doing as little back-and-forth as possible.)
Thanks, My bad! Totally got confused with the slide where it says Is Check DB using parallelism and then read somewhere else something crappy. I thought running DBCC CHECKDB when specifying a MAXDOP parameter will flush the plan cache. But I think you were just referring when we change MAXDOP at instance level will flush the plan cache.
Hello Brent/Others,
is there any way to be notified if the checkdb finds corruption in the check? I created an agent job and corrupted a database on purpose and when i run the job it completes successfully and logs the errors, but at no point does it send a notification that it founds errors. any suggestions/links would be appreciated.
Thank you!
Sure, to set up alerts, read this: https://www.brentozar.com/blitz/configure-sql-server-alerts/