What about separate checks?
Again I dropped and re-created the database to get back to level playing field.
Let’s work through the DBCC check commands, after a command I will check dbi_dbccLastKnownGood to see if it gets updated.
Click through for details on when the dbccLastKnownGood value gets updated.
The “trick” to making this work is to encapsulate the DBCC command as a string, and to call it with the EXECUTE () function. This is used as part of an INSERT INTO / EXECUTE statement, so that the results from DBCC PAGE are inserted into a table (in this case a temporary table is used, although a table variable or permanent table can also be used). There are three simple steps to this process:
Create a table (permanent / temporary) or table variable to hold the output.
Insert into this table the results of the DBCC PAGE statement by using INSERT INTO / EXECUTE.
Select the data that you are looking for from the table.
Read on for his code as well as important caveats.
After a little digging, I discovered the related database user is a member of the db_ddladmin fixed database role. Members of that role are permitted to run DBCC REINDEX. Since I have existing (more sensible) code in place for index maintenance, I don’t want the DBCC REINDEX operations to continue. Here’s the problem: I can’t find a direct way to DENY a database user from running DBCC commands. T-SQL syntax doesn’t support something like DENY DBCC TO <user> or DENY DBCC REINDEX TO <user>. MSDN documentation tells me the equivalent ALTER INDEX command requires at minimum ALTER permission on the table or view. I guessed that revoking or denying ALTER TABLE privileges might prevent a user from executing DBCC DBREINDEX, but that does not appear to be the case.
Okay, let’s try this out. At the above BOL links, there are two DBCC commands that are documented to use the TABLERESULTS option: OPENTRAN and SHOWCONTIG. Testing all of the other DBCC commands shows that this option can also be used on the CHECKALLOC, CHECKDB, CHECKFILEGROUP and CHECKTABLE commands. I’m going to continue this post with using DBCC CHECKDB. If you check BOL, it does not mention the TABLERESULTS option.
As Wayne notes, you can do this for other DBCC commands as well.
DBCC OPTIMIZER_WHATIF can be used to pull down your resources or augment them. Often the differences in the execution plans have to do with parallelism and memory grants. This is an example of an execution plan running on an under powered development machine.
This is a good tool to help figure out what an execution plan probably would look like in production when your test environment is much smaller.
I am in the middle of some research regarding CHECKDB and learnt something new. From SQL Server 2014 onwards the database snapshot that is used to get to a transactionally-consistent point-in-time is no longer hidden.
Arun includes a sample to prove it.
So what does it look like?
I have no idea. I don’t know if it’s a DMV or a function, I don’t know what it’s called, and I don’t know what information it exposes. I also don’t know how it will get joined to other DMVs. There were no details offered up when the status changed. And I’m fine with that! I’m pretty psyched that it got enough traction to get a fix to begin with. If anyone from MS feels like shooting me an email with details, I won’t complain.
But since we don’t know, we’re free to speculate. Like all those History Channel shows about aliens and fake animals and where the Templars secretly buried Jesus’ gold teeth in Arizona. It’ll be fun!
It’ll be interesting to see the results.
When I wanted to research memory problem on a server and started to dig deeper into “DBCC MEMORYSTATUS” command.Very useful links to understand that command were from Microsoft:
During the research I’ve faced two problems:
1. I had to wait several seconds to get the full result set.
2. I had to scroll down and shuffle 115 different data sets to find the counter I want.
To eliminate both these problem all these different counters have to be in the same table/data set.
That will make research easier and data can be stored or compared with the base line.
Read on for the T-SQL script.
All the signs of CHECKDB Latch contention.
DBCC – OBJECT – METADATA this latch can be a major bottleneck for DBCC consistency checks when indexes on computed columns exist. As a side note DBCC_Multiobject scanner is used to get the next set of pages to process during a consistency check.
Read on for the details and Arun’s solution.
At first glance, if you set up log shipping the way most folks do, it looks like you can’t run CHECKDB on a subscriber:
On SQL Server Enterprise Edition, you can try working around it by creating a database snapshot – but that won’t work either:
Here’s the trick: your database needs to be in standby mode instead of norecovery. When you run restores with the standby option, you’re able to query the database in between restores.
This doesn’t obviate the need for running CHECKDB on a primary, but it can offload some of that work some of the time.