November 12, 2019 at 11:53 pm
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?
November 13, 2019 at 2:22 pm
what would "select @@version" return?
November 13, 2019 at 7:46 pm
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?
November 13, 2019 at 8:31 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 13, 2019 at 8:36 pm
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply