by Scott Ellis and Erik Darling
Published on: Nov 28, 2014 @ 11:16
VERSION of Microsoft SQL : Microsoft SQL Server 2008 R2 (SP3) – 10.50.6000.34
Background
Recently we [a software vendor] added some persisted, computed columns to our software database, which is MS SQL 2008 and 2012. We (my friend Erik and I – this is not an officially sanctioned test) wanted to know how much longer DBCC checkDB tests would take, and if we could put a percentage on it. See the last paragraph for the answer if you want to skip to the good part and avoid reading all these accursed words.
Here are the basics of the computed columns. An index is subsequently created on each computed column, each index contains 3 keys. The computed column is the leading key in all three of them.
Here is the computation in the table definition on each computed column:
[redactedComputedColumnName] AS (CONVERT([binary](20),hashbytes('SHA1',case when isnull([hashedField],'')='' THEN CONVERT([nvarchar](30),[ID],0) ELSE upper('S'+rtrim([hashedField])) end),0)) PERSISTED NOT NULLThe Rationale
Why did we want to do this test: This test seeks to identify the percent difference in duration of a DBCC check with and without computed columns. We sought to determine if we could create a percentage difference based on the size of the single, very large table that houses the computed columns, and then apply that as a multiplier across thousands of databases.
Previously, these columns were populated by a trigger, so adding computed columns has increased the duration of DBCC. We did the right thing and removed the trigger because it was a never-ending source of trauma when inserting massive amounts of data, which is a common occurrence in this software.
The Experiment
To begin, we restored a 5 GB database* that is representative of the types of databases about which many people will be curious. We ran this test against a database attached to SQL on an 8 core, 50 GB RAM SQL Server 2008 R2. The table we examined is 3.398 GB. The database server runs on an Intel Xeon E5 2.6 GHz virtual machine. Here is the restore code we used, if you are into this sort of thing:
RESTORE DATABASE [DBCC_TEST5GB]
FROM DISK = '\\Backups_SQL\RELO001\ERIK_COMP_TEST\FULL\v.NoComputedCols.bak'
WITH BUFFERCOUNT = 500, MAXTRANSFERSIZE = 4194304, STATS
Restore Time: 670553 pages in 115.857 seconds (45.216 MB/sec) Size of BAK file: 1.32GB (compressed backup)
We used BUFFERCOUNT of 500 and a MAXTRANSFERSIZE of 4 MB because we wanted to beat the shit out of our IO. Use this code (really, anything you find here) at your own peril. The word “STATS” allows you to see how far long the restore has progressed.
The we set:
STATISTICS TIME ON
--DBCC TRACEON (2549, -1) --DBCC TRACEON (2562, -1) --DBCC TRACEON (1118, -1)
You can look these up at:
http://blogs.msdn.com/b/saponsqlserver/archive/2011/12/22/faster-dbcc-checkdb-released-in-sql-2008-r2-sp1-traceflag-2562-amp-2549.aspx and http://sqlperformance.com/2012/11/io-subsystem/minimize-impact-of-checkdb
Baseline 1: DBCC against a 5 GB, no Computed Columns Database
We need to know how long a DBCC check will take on a “normal” database. To get to a version of the software that has computed columns, we upgraded from an earlier version. First we did the command against a previous version of the database:
/* DBCC command for V.noComputedCols database */
/* DBCC command for 7.5 database */ DBCC CHECKDB ([DBCC_TEST5GB]) WITH NO_INFOMSGS, ALL_ERRORMSGS /* Run DBCC check without indexes */
Duration: 103985 ms. CPU time: 71663 ms.
Baseline 2: DBCC against a 5 GB, database with computed columns disabled.
The previous database was upgraded to a version with computed columns. To establish foundation, we took a baseline with Computed columns disabled. This was to look for possible differences introduced by the upgrade.
/* Disable indexes on computed columns */ ALTER INDEX [IX_1003669_RI] ON [EDDSDBO].[Document] DISABLE ALTER INDEX [IX_1003671_RI] ON [EDDSDBO].[Document] DISABLE ALTER INDEX [IX_1035456_RI] ON [EDDSDBO].[Document] DISABLE
Ran a DBCC command:
/* DBCC command for disabled Computed Columns database */ DBCC CHECKDB ([DBCC_TEST5GB_CC]) WITH NO_INFOMSGS, ALL_ERRORMSGS /* Run DBCC check without indexes */
Results:
Duration: 33003 ms. CPU time: 70056 ms.
Experiment 1: DBCC with Computed columns on against a 5 GB database
To get the database back to where it needs to be, we rebuild the computed column indexes.
/* Rebuild indexes */ ALTER INDEX [IX_redacted_1] ON [EDDSDBO].[Document] REBUILD ALTER INDEX [IX_redacted_2] ON [EDDSDBO].[Document] REBUILD ALTER INDEX [IX_redacted_3] ON [EDDSDBO].[Document] REBUILD
Then run the DBCC
/* DBCC command for enabled Computed Columns database */ DBCC CHECKDB ([DBCC_TEST5GB_CC]) WITH NO_INFOMSGS, ALL_ERRORMSGS/* Run DBCC check without indexes */
Duration: 147264 ms. CPU time: 81358 ms.
Baseline 3 : 430 GB database with disabled computed column indexes.
Next, to establish our multiplier, we ran against a 428.48 GB Database. Having already established that the CheckDB time ran with negligible differences, we ran it again with the same three computed columns, but with them disabled.
The table with the computed columns sported a hefty 269.418 gigabytes.
/* Disable indexes on computed columns */ ALTER INDEX [IX_redacted_1] ON [redacted].[EDDSDBO].[Document] DISABLE ALTER INDEX [IX_redacted_2] ON [redacted].[EDDSDBO].[Document] DISABLE ALTER INDEX [IX_redacted_3] ON [redacted].[EDDSDBO].[Document] DISABLE
/* Run DBCC */ DBCC CHECKDB ([DBCC_TEST430GB]) WITH NO_INFOMSGS, ALL_ERRORMSGS /* Indexes Disabled
SQL Server Execution Times:
Duration: 9705874 ms. CPU Time: 4391135 ms. This (9705874 ms) is 2.69 hours (161.7 minutes)
/* Rebuild indexes */
ALTER INDEX [IX_redacted_1] ON [EDDSDBO].[Document] REBUILD WITH (ONLINE = ON) ALTER INDEX [IX_redacted_2] ON [EDDSDBO].[Document] REBUILD WITH (ONLINE = ON) ALTER INDEX [IX_redacted_3] ON [EDDSDBO].[Document] REBUILD WITH (ONLINE = ON)
Index Rebuild time: 00:01:59.003 (about 2 minutes)
Experiment 2: Run CheckDB on large, production-grade data set with enabled computed column indexes
/* Run DBCC */ /* DBCC command for 8.2 database */ DBCC CHECKDB ([DBCC_TEST430GB]) WITH NO_INFOMSGS, ALL_ERRORMSGS
SQL Server Execution Times:
Duration: 10846102 ms. CPU time: 4689156 ms.
Conclusions
To fully understand what is happening during a DBCC CheckDB of database computed columns, check out this blog post from Paul Randall.
Paul’s post does indicate a much greater performance hit than what we saw. This could be due to some confluence of the calculation involved, the number of them, the size of the data, the index, trace flags in use, and the underlying hardware. Paul suggests that dropping and re-enabling the indexes is not a “palatable solution.” I submitted an inquiry to him about this, asking why it is unpalatable (which quite literally means not very tasty), and he responded “Because someone might have indexes that take a long time to rebuild, might not have online, might be enforcing a constraint…”
This got me to thinking that, since with this particular database software, there is no constraint, that many people running this product have Enterprise, and many administrators have offloaded the DBCC process to an extraneous SQL server, disabling the indexes may be an option. Certainly, in an offloaded situation, disabling and leaving them disabled may leave only a mildly unpalatable aftertaste.
For those of you who are doing consistency checking in production, the only real issue that you can experience is if users actually attempt to access and use the database while the check is in progress and the indexes are disabled. If your column has great potential to be used a lot, then we don’t recommend a production disabling of this index.
Also realize that if this index is corrupted in your production system, you run the risk of a user running across it and then you have to rebuild it during production hours. The risk is low, but it is there.
Many infrastructure admins that run corruption checks on very large** databases run them in an offloaded way. They restore the databases to cheaper hardware and let the checks take a day or two to run. For those people, disabling these indexes is a perfectly viable solution. If even the remote capacity exists that someone may hit those indexes WHILE they are dropped and WHILE you are doing a DBCC check DON’T DO IT. This not a viable option for a production system and if you are caught doing it by this software infrastructure architect, I will scold you 🙂
Ultimately, whether you have Enterprise or not is irrelevant – you do far more damage to query capabilities for the one or two hours that the indexes are offline than you do for the two minutes it takes to rebuild them. Moral of the story :
If you are offloading DBCC and want it to go faster, automate a computed column index disable. If not, if you are performing DBCC in prod, A) Stop doing that B) leave these indexes enabled.
The percent performance improvement, for these particular databases, seems to be in the neighborhood of XX%- YY%.
*No, you can not have, ever, no matter how often you ask me for it
** By very large I mean any size database that causes you stress!
