Capturing DBCC CHECKDB Output

If you haven’t experienced database corruption in your SQL Server career and had to pore over DBCC CHECKDB output, you’re a lucky soul.  If you encounter corruption, the most important thing you can have is the output, because this helps determine your next course of action.

Step 1

First, you need to be running CHECKDB on a regular basis.  If you’re going to ask what you mean by regular basis, I’ll direct you to a post of Paul’s that is still relevant: Importance of running regular consistency checks.  There are a few factors to consider, but in general I recommend at least once a week.  If you’re a 9 to 5 shop and have the maintenance window to run CHECKDB daily, feel free to do that.

Step 2

Second, running CHECKDB and capturing the output should be an automated process.  SQL Agent is your friend here, and you have a few options.

Option 1: Set up an Agent Job

Within SSMS, you can create an Agent job to run DBCC CHECKDB and write the output to a file.  I recommend the following syntax, which is good for most databases:

DBCC CHECKDB ('YourDBNameHere') WITH NO_INFOMSGS;
GO

Note: If you have a very large database (1TB or larger), or don’t have the maintenance window to run a complete CHECKDB, then you may want to look at another one of Paul’s posts for options, Consistency Checking Options for a VLDB.

Within the Agent job, it’s pretty straight-forward:

Running CHECKDB in an Agent job

Running CHECKDB in an Agent job

 

To make sure the CHECKDB output is saved, you want to write it out to a file, which you configure on the Advanced tab:

CHECKDB output file configuration

CHECKDB output file configuration

 

The configuration of the output file is important.  If you enter a path and file name like this:

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Log\checkdb_output.txt

then every time the job runs it overwrite the contents of this file.  You have an option to append the file, but then you have to make sure you manage the size of the file.  Instead, append the date and time to the file to make it unique:

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Log\checkdb_output_$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).txt

You also need to create a job to clean up output files regularly.  With this configuration, you can view the CHECKDB output at any time, as long as the file still exists in the folder specified.

Option 2: Use Ola’s Agent Job

The syntax above works for one database…but what if you have 10 user databases on your instance?  And what about system databases?  Well, you can add the commands for each database, or you can make life easy and use Ola Hallengren’s scripts for maintenance, which creates two jobs for integrity checks: one for user database, and one for system databases.

SQL Agent jobs created by Ola's maintenance script

SQL Agent jobs created by Ola’s maintenance script

 

Ola’s stored procedure, dbo. DatabaseIntegrityCheck, has multiple options for configuration.  By default it checks all tables and indexes, and outputs error messages, and the job writes an output file each time it executes (including date and time in the name) to the instance Log folder by default.  When multiple databases are checked, the output for all databases is included in one file.  It’s also worth noting that Ola’s scripts create an Output File Cleanup job to clean up those files.  By default NONE of these jobs are scheduled, so I recommend you review what the job does, confirm it is configured how you want, and then create a schedule.

Option 3: Use a Maintenance Plan

In every environment I manage directly, I use Ola’s scripts for maintenance tasks.  However, some folks may be more comfortable using Maintenance Plans.  If that’s true for you, then when you set up the plan for integrity checks, there are two things I recommend.  First, you typically do not want to run with the PHYSICAL_ONLY option unless the check takes an extremely long time to run and you’re trying to make the task fit into a maintenance window.  When you use the PHYSICAL_ONLY option the logical checks, inter-page checks, and things like DBCC CHECKCATALOG will be skipped.

Options in the CHECKDB maintenance task

Options in the CHECKDB maintenance task

 

Second, the maintenance task writes to an output file by default.  Within the UI, you can select the Reporting and Logging button to confirm the location.

Reporting and logging for CHECKDB maintenance plan

Reporting and logging for CHECKDB maintenance plan

 

By default, it writes to the Log folder for the SQL Server installation (e.g. C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\Log for SQL Server 2019).  Each file is created with the date and time as part of the filename.  As with other options, you need to ensure that these output files are cleaned up on a regular basis.

Other Items to Consider

If you run DBCC CHECKDB in SSMS via a machine you have RDP’d to, be aware that depending on the group policy your session can disconnect after a certain amount of time.  I’ve had this happen in client environments, and if you’re trying to run CHECKDB, it will stop and you won’t get any output…hence the suggestion to use an Agent job.

With any Agent job you use for integrity checks, I also recommend (dare I say insist?) that you enable notifications for job failure.  This is configured on the Notifcations tab of the job, and requires an Operator and database mail.  Create an Operator that writes to a team alias (e.g. DBAs@yourcompany.com) rather than just you.

Agent Job Notifications

Agent Job Notifications

 

Summary

If you are responsible for any SQL Server instance, you want to make sure of the following:

  1. You are running DBCC CHECKDB on a regular basis to look for integrity issues.
    1. This process should be automated.
  2. Your method for checking should include an option to write the CHECKDB output to a file that can be referenced later.
    1. If you find corruption, make a couple copies of this file.  If it gets deleted, you will have to run CHECKDB again to determine what is corrupt.
  3. Set up Notifications for the Agent job so you are notified if corruption is found.

I hope that the information in this post was review for you, but if it wasn’t, or if you have encountered corruption, feel free to reach out to see if we can help.

 

SQLskills 101: The Other Bad Thing About Clearing Procedure Cache

Every time you execute a query against SQL Server, it goes through optimization and compilation and a query plan is generated. Most of the time that plan goes into cache (there’s always exceptions, which is a discussion for a separate time), which means that the next time that query executes, SQL Server can use the same plan.  Now, re-using the same plan is typically a good thing; SQL Server doesn’t have to go through full optimization and compilation again.  But sometimes that plan isn’t ideal for the query and you want to remove it from the plan cache.  You can do this manually.  Plans can also age out if they’re not being used, but if I want to purposely evict a plan from cache, I have several options:

Ideally, you should remove only what’s absolutely necessary.  Using DBCC FREEPROCCACHE is a sledgehammer approach and typically creates a spike in CPU as all subsequent queries need to have their plans re-generated.  Glenn gives examples on how to use each statement (and others) in his post Eight Different Ways to Clear the SQL Server Plan Cache, and I want to show you one more thing that happens when you clear a plan (or all plans) from cache.

For this demo script, I recommend running it against a TEST/DEV/QA environment because I am removing plans from cache which can adversely affect performance.

We’ll  run one statement and one stored procedure multiple times against the WideWorldImporters database:

/*
Create a stored procedure to use for testing
*/
USE [WideWorldImporters];
GO

DROP PROCEDURE IF EXISTS [Application].[usp_GetCountryInfo];
GO

CREATE PROCEDURE [Application].[usp_GetCountryInfo]
@Country_Name NVARCHAR(60)
AS
SELECT *
FROM [Application].[Countries] 
JOIN [Application].[StateProvinces] [s]
ON [s].[CountryID] = .[CountryID]
WHERE .[CountryName] = @Country_Name;
GO

/*
Remove everything from cache
*/
DBCC FREEPROCCACHE;
GO

/*
Run the stored procedure 20 times
*/
EXECUTE [Application].[usp_GetCountryInfo] N'United States';
GO 20

/*
Run the query 20 times
*/
SELECT
[s].[StateProvinceName],
[s].[SalesTerritory],
[s].[LatestRecordedPopulation],
[s].[StateProvinceCode]
FROM [Application].[Countries] 
JOIN [Application].[StateProvinces] [s]
ON [s].[CountryID] = .[CountryID]
WHERE .[CountryName] = 'United States';
GO 20

Now let’s see what’s in cache, and what’s also in sys.dm_exec_query_stats (which gives us execution statistics).

SELECT
[qs].[last_execution_time],
[qs].[execution_count],
[qs].[total_logical_reads]/[qs].[execution_count] [AvgLogicalReads],
[qs].[max_logical_reads],
[t].,
[p].[query_plan]
FROM sys.dm_exec_query_stats [qs]
CROSS APPLY sys.dm_exec_sql_text([qs].sql_handle) [t]
CROSS APPLY sys.dm_exec_query_plan([qs].[plan_handle]) [p]
WHERE [t]. LIKE '%Application%';
GO
Text, plan, and query stats from the DMOs

Text, plan, and query stats from the DMOs

 

 

 

 

Cool, I see that each one executed 20 times, and in addition to the text and the plan I pulled information about I/Os.

Now let’s clear procedure cache, and we’ll take the good old sledgehammer approach because we’re lazy and run DBCC FREEPROCACHE.  Then run the query to see what’s in sys.dm_exec_query_stats…

DMO information *after* running DBCC FREEPROCCACHE

DMO information *after* running DBCC FREEPROCCACHE

 

 

 

 

 

 

Do you see what happened?  In addition to removing the plan, we also lost all the query execution stats from dm_exec_query_stats.  We have no history about query performance (unless we were writing it to another table on a regular basis, or have a third-party tool that captures baselines).  If you don’t believe me, just run SELECT * FROM sys.dm_exec_query_stats…all that data is gone (but will start to re-populate as you run queries and plans go into cache).

This is important to know! If you’re troubleshooting a performance issue and you want to try removing the plan from cache, be aware that you’re removing execution statistics too (and maybe save those off first in case you need them for reference/comparison later).

SQLskills SQL101: Updating SQL Server Statistics Part II – Scheduled Updates

In last week’s post I discussed the basics of how automatic updates to statistics occur in SQL Server.  This week I want to talk about scheduled (aka manual) updates, because as you might remember, we really want to control when statistics are updated.

In terms of updating statistics you have multiple options, including:

  • Update Statistics Task (Maintenance Plan)
  • sp_updatestats
  • UPDATE STATISTICS

For systems that do not have a full-time DBA, one of the easiest methods for managing statistics is the Update Statistics Task.  This task can be configured for all databases or certain databases, and you can determine what statistics it updates:

Update Statistics Task- deciding which statistics to update

Update Statistics Task – deciding which statistics to update

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

You might think you want to update All existing statistics.  If you just had a plan with just this task, that might be true.  But what I see most often is that someone configures the Rebuild Index task, and then has the Update Statistics task as the next step.  In that case, if you are running SQL Server 2014 and below, you want to update Column statistics only.  When you run the Rebuild Index task in SQL Server 2014, you rebuild all indexes, and when you rebuild an index, its statistic is updated with a fullscan.  Therefore, there is no need to update Index statistics after you rebuild all your indexes, but you do need to update column statistics.

This is a bit more complicated in SQL Server 2016.  The Rebuild Index task has more options in SQL Server 2016, which is nice for that specific task, but it makes providing guidance about statistics updates a bit trickier.  In SQL Server 2016 you can configure the Rebuild Index task so that it only rebuilds an index if a certain level of fragmentation exists.  Therefore, some of your indexes will rebuild (and thus have statistics updated) and some will not (and not have updated statistics).  How do you manage that with the Update Statistics task?  Well, in that case you probably select All existing statistics and update some statistics for a second time, which is really a waste.  Therefore, if you’re on SQL Server 2016, you probably want to look at more intelligent updates.

One method, which I would not say is intelligent, but it is an option, is to use sp_updatestats in a scheduled job that runs on a regular basis.  This command is one you run for a database, not for a specific statistic or index or table.  The sp_updatestats command will only update statistics if data has changed.  That sounds good, but the caveat is that only one (1) row has to have changed.  If I have a table with 2,000,000 rows, and only 5 rows have changed, I really don’t need to update statistics.

The other method is to use UPDATE STATISTICS in a scheduled job.  The UPDATE STATISTICS command can be run for individual statistics or for a table (updating all statistics for a table).  You can develop an intelligent method to use this command, which is what I recommend.  Rather than a blanket update to all statistics, or statistics where one row has changed, I prefer to update statistics that are outdated based on the amount of data that has changed.  Consider the aforementioned table with 2,000,000 rows.  If I let SQL Server update statistics automatically, I would need 400,500 rows to change.  It’s quite possible that with a table of that size I would want to statistics to update sooner – say after 200,000 rows had changed, or 10% of the table.

We can programmatically determine whether we need to update statistics using the sys.dm_db_stats_properties DMF.  This DMF tracks modifications, and also tells us how many rows were in the table when statistics were last updated, and the date statistics were updated. For example, if I update some rows in Sales.SalesOrderDetail, and then look at the output from the DMF, you can see that the modification counter matches the number of rows I changed* for the ProductID index:

USE [AdventureWorks2012];
GO

UPDATE [Sales].[SalesOrderDetail]
SET [ProductID] = [ProductID]
WHERE [ProductID] IN (921,873,712);
GO

SELECT
[so].[name] [TableName],
[ss].[name] [StatisticName],
[ss].[stats_id] [StatisticID],
[sp].[last_updated] [LastUpdated],
[sp].[rows] [RowsInTableWhenUpdated],
[sp].[rows_sampled] [RowsSampled],
[sp].[modification_counter] [NumberOfModifications]
FROM [sys].[stats] [ss]
JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]
CROSS APPLY [sys].[dm_db_stats_properties] ([so].[object_id], [ss].stats_id) [sp]
WHERE [so].[name] =  N'SalesOrderDetail';
GO
Output from sys.dm_db_stats_properties

Output from sys.dm_db_stats_properties

 

 

 

 

 

 

*You’re correct, I technically didn’t change ProductID to a new value, but SQL Server doesn’t know that.  Also, there’s a foreign key on that column which is why I can’t easily change it a random number.

Armed with this type of data, we can intelligently decide whether we should update statistics because a percentage of rows (rather than just a fixed number of rows) have changed.  In the example above, only 8% of data changed – probably not enough to require me to update statistics.  It’s quite possible that some statistics need to be updated daily because there is a high rate of change, and other statistics only need to be updated weekly or monthly because data doesn’t change much at all.

Ultimately, when it comes to scheduled updates of statistics, you can go the sledgehammer route (Update Statistics task or sp_updatestats) or the selective update route (UPDATE STATISTICS and sys.dm_db_stats_properties).  Using the Update Statistics task or sp_updatestats is easier if you’re not familiar with SQL Server and if you have the maintenance window and resources for it to run.  To be perfectly clear: if you’re a system administrator and want to update statistics, I’d rather you use this approach than nothing at all.  Presumably, if you don’t have a full-time DBA, you also don’t need the system to be available 24×7, so you can take the performance hit at night or on the weekend while all statistics update.  In that situation I’m ok with the approach.

But, if you are a DBA and you know how to write T-SQL, then you can absolutely write some code that programmatically looks at your statistics and decides what to update and what to skip.  Whatever method you use, just make sure your updates are scheduled to run regularly through an Agent Job, and make sure you have Auto Update Statistics enabled just in case the job doesn’t run and you don’t get notified for some reason (this would be Plan B, because it’s always good for DBAs to have a Plan B!).

Additional Resources