A faster CHECKDB – Part IV (SQL CLR UDTs)

[mssql-support]

First published on MSDN on Jan 26, 2015

I have been working on the various aspects of DBCC performance and SQL CLR based User Defined Data Types .    I encountered a few issues that I have outlined below.

1. Memory Grant Bug

There is a bug, prior to SQL Server 2014, causing the memory grant for the DBCC operations (checktable or checkdb per table) to be improperly estimated.  Placing the session, running the DBCC command, in a specific resource workload group allows you to limit the memory grant size and increase the DBCC performance.  Reference the following link for more details” http://blogs.msdn.com/b/psssql/archive/2014/11/10/a-faster-checkdb-part-iii.aspx I used a grant cap of 20GB on a 128 CPU, 512GB system with success rather than accepting the 90GB default grant request.

Note: This issue has been corrected in SQL 2012 ( https://support.microsoft.com/en-us/kb/3007556 ) SQL Server 2012 SP2 - CU4

2. Blob Handle Factory

Fix released (Microsoft Bug Id: 3939015).  Before the fix the DBCC command(s) created an internal structure (Blob Handles for each SQL CLR UDT based column as rows were processed) and failed to mark it for reuse when done processing the row.  Each spatial row/column would look over the blob handle list, create a new entry and add it to the list.   This resulted in wasted memory and CPU as the list continued to get larger and larger and the entries could not be reused.    The fix allows proper reuse of the BHF.

The customer indicated it took 22 days to complete on the current SQL Server 2012 build.   The QFE now completes DBCC checkdb in 15.5 hours. (DL980 G7 with 512GB RAM)

Download: http://support.microsoft.com/kb/3007556/en-us (SQL Server 2012 SP2 – CU4)
Article: http://support.microsoft.com/kb/3029825

3. Parallelism

DBCC does use parallel, internal queries when possible.   In studying this closer I found that running individual checktable(s), from multiple sessions can decrease the overall DBCC maintenance window.

DBCC checkdb loops over each table and executes the fact queries.  The SQL engine many elect to execute the fact queries in parallel.    However, CheckDB only processes a single table at a time.   On a larger system you may be able to take advantage of multiple checktable invocations on different schedulers.

The fact queries used by DBCC command are prevented from using parallelism if a large UDT column is present.   Spatial is somewhat analogous to varbinary(max) and falls into this limitation.   This means the internal, DBCC, fact query against your largest table runs with a serial plan.

If you manually shard the table, creating a covering view it may allow you to execute concurrent dbcc, index rebuild and other operations faster.

Parallel Check Table Example:

DBCC CHECKALLOC
DBCC CHECKCATALOG
Parallel.ForEach(table in sys.tables) --         Make sure the number of parallel executions is appropriate for resource consumption on the SQL Server
{
DBCC CHECKTABLE(…) with …
}

Shard Example:

create view vwMyTable
as

select <<column list>> from MyTable_1
union all
select <<column list>> from MyTable_2   …..

4. Trace Flag 2566  (Ignore Data Purity Checks)

Once data purity checks have been successful on upgraded databases or the database was created on newer versions the DATA_PURITY check becomes ‘On By Default.’      DBCC DBINFO(<<DBNAME>>) shows the dbi_dbccFlags .   The 0x2 bit indicates data purity will be run by default when checkdb or checktable is executed.     You can avoid these checks using –T2566 or dbcc traceon(2566) in the same batch as checkdb or checktable execution.

The trace flag skips the purity checks as long as the checkdb or checktable command does not specify WITH DATA_PURITY, overriding the trace flag behavior.

My testing only shows a nominal performance gain.

Additional DBCC References

Part 1: http://blogs.msdn.com/b/psssql/archive/2011/12/20/a-faster-checkdb-part-i.aspx
Part 2: http://blogs.msdn.com/b/psssql/archive/2012/02/23/a-faster-checkdb-part-ii.aspx
Part 3: http://blogs.msdn.com/b/psssql/archive/2014/11/10/a-faster-checkdb-part-iii.aspx

Spatial

Over the last 18 months Microsoft addressed various spatial performance issues.  The QFE build, mentioned above, contains the following corrections that you should consider enabling to improve overall spatial performance on the servers.

In a nutshell: Apply latest Service Pack and latest CU for SQL Server and in addition, enable STARTUP trace flags (- T8048 and - T6531 )

Details:

Reference link	Title	Comments (Warning: mileage will vary based on data pattern)
http://support.microsoft.com/kb/2887888 http://support.microsoft.com/kb/2887899 http://support.microsoft.com/kb/2896720 http://blogs.msdn.com/b/psssql/archive/2013/11/19/spatial-indexing-from-4-days-to-4-hours.as...	FIX: Slow performance in SQL Server when you build an index on a spatial data type of a large table in a SQL Server 2012 or SQL Server 2014 instance	Took an index build from 72 hours to 4 hours and requires trace flag –T8048.
http://support.microsoft.com/kb/2786212	FIX: Access violation occurs when you run a spatial query over a linked server in SQL Server 2008 R2 or in SQL Server 2012
http://support.microsoft.com/kb/3005300	FIX: High CPU consumption when you use spatial data type and associated methods in SQL Server 2012 or SQL Server 2014	May improve performance by 10% or more for spatial methods.  Requires trace flag –T6531
http://support.microsoft.com/kb/2977271	FIX: Performance improvement for SQL Server Spatial data access in SQL Server 2012	Query that used to take 20+ hours < 2 hours.
Blob Handle QFE   KB: 3029825
http://social.technet.microsoft.com/wiki/contents/articles/9694.tuning-spatial-point-data-qu...	Tuning Spatial Point Data Queries in SQL Server 2012

Bob Dorr -  Principal SQL Server Escalation Engineer