***** Update 2018/0713 *****
The Connect item link below is, of course, dead now. No matter. Because this issue was fixed in SQL Server 2017 CU8 by this kb.
FIX: Access violation when you compile a query and histogram amendment is enabled with default Cardinality Estimation in SQL Server 2017
***** End Update *****
Often among the hardest of my decisions is whether I should spend more time trying to simplify a given problem by eliminating additional factors, shrinking the data needed for a repro, etc... or just put all that effort into investigation purely aimed at understanding the behavior. I expect that to be a long-term challenge :-)
I was quite happy with the way this particular one worked out, though. It started as a maze... access violations generated on a SQL Server 2016 instance with dozens of databases. The access violation came from an insert query using a synonym - with the underlying table in another database! (I didn't know that was possible - or *ever* desirable - until I saw it in this context.) The AV was occurring during a multi-step data transfer process and it was hard to isolate the data flowing into and out of the two databases in question. But after some finagling, I got the problem repro pretty doggone small. Reproduced the AVs on several CUs of SQL Server 2016 and on SQL Server 2017 RC2.
Let me offer a disclaimer before the code to produce the AV. I don't *think* this will do any persistent harm other than a terminated session and dumps like these below. Regardless, if you choose to reproduce the access violation, please only do so on a nonproduction system. The dump the AV created on my SQL Server 2017 system...
Here's the setup. Two 2-column tables in tempdb. One of those tables with a single row. The other table with 1 row - then stats auto-created - then a second row inserted.
So that's the setup.
After the setup, the offending query is quite simple.
And the result...
Ouch. That looks really painful.
An interesting sidenote: in the not-too-distant past, histogram amendments via trace flags 2389, 2390 and/or 4139 seemed to have been exclusively the realm of the legacy cardinality estimater. This Access Violation is a strong indication that is no longer the case.
Filed a Connect item for this. Fortunately, the workflow and data involved in this were amenable to a schema change to no longer use a column of type bit - allowed side-stepping this error for now.
SQL Server 2017 Access Violation - outdated bit column stats and histogram amendments