Optimizing Your Nightly DBCC Operations
Sometimes software, hardware, or other anomalies result in lost or corrupt data. Microsoft's Database Consistency Checker (DBCC) helps deal with these situations. Books Online (BOL) says that DBCC "is used to check the logical and physical consistency of a database, check memory usage, decrease the size of a database, check performance statistics, and so on.... DBCC helps ensure the physical and logical consistency of a database; however, DBCC is not corrective. It is recommended that you make periodic checks to ensure the logical and physical consistency of your data."
You need to run DBCC periodically to make sure your data is OK. Preventive maintenance is the name of the game. However, running DBCC has a performance penalty, so you can't run it all day looking for potential problems.
Joe Marler, senior Microsoft SQL Server support engineer and a SQL Server expert, offers some advice about running DBCC NEWALLOC instead of DBCC CHECKDB. He talks about some performance differences between the two commands and suggests a minimum frequency for running both commands. Here's what Joe says.
DBCC NEWALLOC checks for allocation inconsistencies and every page chain of every table and index, the most common and serious database problems. Because NEWALLOC I/O is sequential, it's much faster than CHECKDB. On a large Compaq ProLiant-class machine, NEWALLOC can often check 5GB per hour. Furthermore, NEWALLOC tends to scale upward linearly with the amount of data in use and imposes no locking concurrency overhead. Starting with SQL Server 6.5 Service Pack 2 (SP2), you can run NEWALLOC online--that is, without putting the database in single-user mode--without getting many spurious errors.
By contrast, CHECKDB requires lots of random I/O--about one I/O per row per nonclustered index. For this reason, CHECKDB takes a long time and doesn't scale predictably with database size, because scaling is a function of the number of nonclustered indexes and number of rows in the tables. Also, CHECKDB takes a share table lock, thus affecting concurrency significantly.
Many people don't run DBCC because it takes so long. They often seem unaware that NEWALLOC checks most of the serious things in the database, and it has no locking overhead. Although running both NEWALLOC and CHECKDB is best, you can often get by with running NEWALLOC often--and running CHECKDB less frequently (e.g., weekly).
Reader Tip: DBCC PERFLOG, Logins, and Passwords
In your November 1997 column in the second tip for using the DBCC PERFLOG output, you wrote, "Although we can't insert DBCC PERFLOG output directly into our tracking table, we insert the result set from a stored procedure." SQL Server 6.5 has the data piping ability with more than stored procedures. You can use the INSERT ... EXECUTE(<DYNAMIC_SQL_STMT>) command to pipe data into any table. In your DBCC PERFLOG example, you could do the following:
insert PerfLogOutput (DBName, LogSize, SpaceUsed, Status)
execute('dbcc perflog')
You can use any dynamic SQL statement to populate a table. The only restriction is that you can't return multiple result-sets. Here's another example:
insert PerfLogOutput (DBName, LogSize, SpaceUsed, Status)
Execute('Select ''Test'' as DBName, 0.0 AS LogSize, 0.0 AS SpaceUsed, convert(bit, 1) AS Status')
I wrote two stored procedures--sp_logininfo.sql and sp_droplogin_all.sql--
that you might find useful (the source code for these stored procedures is available on the Windows NT Magazine Web site). You can use sp_logininfo.sql to obtain information about a SQL Server login. If you execute the stored procedure with no parameters, it will report whether the current user is connected through a standard or an integrated/mixed type login--an important capability for Windows NT security. You can also store the output into variables to make it easy to use from other stored procedures. Another stored procedure, sp_droplogin_all.sql, drops a login from all databases, whether the login is a user or is using an alias as someone in that database.ing procedure. Microsoft designed thber column about sp_TransferPasswords, you can simplify the statement
SELECT name, password INTO #RemoteSysLogins FROM master..syslogins WHERE 0 = 1
to
SELECT convert(sysname, space(30)) AS name, convert(sysname, space(30)) as password INTO #RemoteSysLogins
Of course, you have to hard-code the lengths, but you can also use this feature to add dummy rows to a result-set. For example:
create table #some_table (seqno int identity not null)
insert #some_table default values
select convert(int, -1) as seqno
union all
select seqno from #some_table
—Umachandar Jayachandran, MCSE
umachandar@yahoo.com
This alternative SELECT syntax highlights the fact that a SELECT statement can return static values not retrieved from a table, but we don't agree that this approach is always simpler. The proposed change would have required us to explain both the convert and space functions and mention the undocumented sysname internal datatype. Our original SQL Server code is easy to read and shows a useful trick for populating an empty temp table by using a WHERE clause that will never evaluate to true.
—Karen and Brian
Q: In the August issue, you wrote about whether dumping a database on one server and then restoring it on another was safe. You said that a mismatched devices problem could occur if the data and log device allocation were different from the original. After the restore, can you tell (e.g., from the Database Consistency Checker--DBCC--checks) whether this problem has occurred?
Furthermore, what if I had a 100MB database with a 75MB data portion and a 25MB log spanned over four devices? Would consolidating the database on one 25MB log device and one 75MB device cause a problem if I had done a restore based on the original four-device database?
DBCC might report an error message, and you might be able to safely dump a database on one server and then restore it on another and have the device allocation match. But we're not aware of any foolproof detection method, so don't tempt fate by trying this procedure. You need to have a script of sp_help_revdatabase (see the question on backups in our March column) to re-create the databases in the correct order.
Regarding your second question, the number of devices doesn't technically have anything to do with difficulties in restores. The issue is database fragments--information from the syssegments table. Fragments will reflect the order in which you created the table, as in this example:
CREATE DB Test 1
Data = 10
Log = 10
Alter Table
Data = 100
We create a table with three fragments: 10 for data, then 10 for log, then 100 for data. The devices don't matter. The descriptions of sp_coalesce_fragments and sp_help_revdatabases in Books Online (BOL) might be useful for what you're trying to do.
Send your questions to us individually, or send them to us collectively at sqlqa@winntmag.com.