CheckDB on VLDB, Geography data type

  • el_Guapo

    Ten Centuries

    Points: 1203

    I've narrowed down a DBCC CheckDB issue to a set of tables with Geography data types.  These tables are 100s of millions of records.  The integrity checks on these tables are taking 30-40 hours to complete.  Other tables with similar row counts but no geography data type only take 10-20 minutes.  This is full checks, not physical only.  Physical only checks on these tables complete in 5-10 minutes.  I cannot find anything that would indicate why the logical check portion would take so long on tables with Geography data.

    Any thoughts out there?

  • Andrey

    Say Hey Kid

    Points: 691

    what would  "select @@version" return?

     

  • el_Guapo

    Ten Centuries

    Points: 1203

    Microsoft SQL Server 2014 - 12.0.2269.0 (X64)

    Jun 10 2015 03:35:45

    Copyright (c) Microsoft Corporation

    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

    I know it needs to be patched.  Possibly the culprit?

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88170

    Are those columns indexed?  If so - then most likely the reason for the issue is that SQL Server is performing logical consistency checks on the spatial indexes and that is why they are taking so long.

    You could try using NOINDEX to see if that improves the performance of the integrity check.  If that does work - then there may be an issue with one or more indexes that SQL Server is trying to reconcile and it is taking a long time.  It might be possible to drop and recreate the indexes - but that could take a very long time.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • el_Guapo

    Ten Centuries

    Points: 1203

    Not indexed.  NOINDEX performs just as poorly. So it's the table data itself.

    The only thing I could think is maybe because the geography data type is implemented via CLR that it's very slow to do the logical checks on it.  We're patching the server this weekend so we'll see what happens post patch with the performance.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply