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.
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.
Jeffrey Williams Problems are opportunities brilliantly disguised as insurmountable obstacles.
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.