ArcSDE & SQL Server Compatibility Level & Slow Data Loading

AnthroSys

Summary
- SQL Server Compatibility Level 130 (from 2012 Compatibility Level 110) resolves slow Data Access Issue

- Configuration - Windows 2016, SQL Server 2016 upgraded from SQL Server 2012, ArcGIS Desktop 10.6

- Pre-Condition - The SQL Server 2016 Server used to be SQL Server 2012

Issue
Feature class of 10 of thousands of points takes long to load

- Load time of 1 and 2 minutes

Troubleshoot
- Load to New SQL Server 2016 - Effect (Loads in 1 to 2 secs)
-- Only difference in settings - SQL Server Compatibility Level 
--- Original Server  - 110
--- Test Server - 130

Solution
- Change Original Server Compatibility Level from 110 to 130 - Resolved

ChetDobbins

Good morning, I am wondering if you captured any sort of tracing that might help explain why the performance is better with the higher database compatibility setting. Often Microsoft will tie optimizer and other general improvements to the compatibility level so what you are seeing could be related to that, but it does peak my curiosity about what queries are actually being affected.

It is always good to update the compat level though, whenever you upgrade SQL Server.