Rudyx - the Doctor. Also, there are at about a dozen other DBCC's executing at the same time on databases ranging in size from 5 Gb to 30 Gb on this same server during our nightkly maintenance. J Bagwell. It also depends on what else is running and whether or not the DBCC finds any errors. If our DBCC runs for more than an hour, we can almost guarantee that it is going to return consistency errors.
In this case, it can sometimes run for 4 or 5 hours before returning those errors. You must be logged in to reply to this topic. Login to reply. Post reply. It does not acquire table locks by default. Instead, it acquires schema locks that prevent metadata changes, but allow data changes.
Database shrinking: When a database grows on its own it will create free spces. Usually, it takes about 3 hours for a standard rebuilding to occur, but this time varies significantly. The time it takes to complete a rebuild depends on the size of your hard drive and the amount of space that is already full.
Some consoles are known to take upwards of 20 hours to finish. Validates the contents of every indexed view in the database. Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.
DBCC commands take input parameters and return values. It controls the number of processors used to run a single statement in parallel plan execution. The default value is 0, which enables SQL Server to use all available processors. The level 14 belongs to security level errors like a permission denied.
CHECKDB has to read every allocated page in the database so the bigger it is, the longer it will take to read all the pages. It reads every allocated page in the database. That's a lot of IO. CHECKDB takes great pains to do the most efficient IO it can and read the database pages in their physical order with plenty of readahead so that the disk heads move smoothly across the disks rather than jumping around randomly and incurring disk head seek delays. I've seen this in real-life several times.
If there are a lot of changes in the database while CHECKDB is running, the changed pages are pushed to the snapshot so that it remains consistent.
As the snapshot files are stored in the same location as the database files, every time a page is pushed to the snapshot, the disk head has to move, which interrupts the efficient IO described in 2. Also, whenever CHECKDB goes to read a page and it needs to read the page from the snapshot files instead of the database files, that's another disk head move, and another efficient IO interruption. Throughput capabilities of the IO subsystem This one's simple.
I've lost count of the number of times I've heard customers complain that CHECKDB or index rebuilds or other IO-heavy operations are running sloooowly only to find that the disk queue lengths are enormous and the IO subsystem it entirely unmatched to the server and workload. Running in parallel can give a significant performance boost to CHECKDB and lower run times, as long as the database is also spread over multiple files too so the IOs can be parallelized.
SAP typically recommends this to help with user query predictability. The way to do this is to turn on the documented trace flag There are a bunch of other checks that are only done if the features have been used in the database - e. The number and type of corruptions that exist in the database Again, this is similar to 7 and 8. If there are any corruptions present, there may be extra checks triggered to try to figure out more details of the corruptions.
For instance, for the non-clustered index checks, the algorithm is tuned very heavily for the case when there are no corruptions present the overwhelming majority of cases considering the millions of times CHECKDB is run every day around the world. When a non-clustered index corruption is detected, a more in-depth algorithm has to be used to figure out exactly where the corruption is, which involves re-scanning a bunch of data and so taking a bunch more time.
There are a few other algorithms like this too. Summary So you can see that there's no simple answer. Hope this helps! Improve this answer. Paul Randal Paul Randal 7, 1 1 gold badge 34 34 silver badges 45 45 bronze badges. Dear Mr. Smartypants: You win!! Thanks for the answer, and for writing CheckDB. Sad but true.
0コメント