ANSI_WARNINGS = OFF can break update statements with some SQL features

Working on a SQL 2017 database where an external, non-Microsoft application has direct access to read/write data via the ODBC Driver 17 for SQL Server.

They suddenly began to experience a regression, UPDATE statements were failing with the following message snippet:

 esu_sql_execute (SQLExecute) : [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]

UPDATE failed because the following SET options have incorrect settings:

'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications

and/or XML data type methods and/or spatial index operations.

esu_sql_execute (SQLExecute) : [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.

Right away, I figured that something in the database from the above list of options was incompatible with the legacy ANSI_WARNINGS = OFF setting the external developers were using in their code. And I was resigned quickly to the fact that something in SQL Server would have to change, as this project was nearing a time-sensitive milestone.

Though ANSI_WARNINGS = OFF is not recommended, changing it in an existing application is problematic. Development should begin with ANSI_WARNINGS = ON and ANSI_DEFAULTS = ON, but if it doesn't, changing these settings requires a LOT of regression testing, especially around the behavior of NULL values in aggregate functions, divide-by-zero and artithmetic error handling, and how trailing blanks are handled. So yeah, it gets messy and time-consuming.

As the error message above had said, I quickly looked through the database for the SQL features listed in the error message (above). All of these features of SQL Server have incompatibilities with the legacy ANSI_WARNINGS = OFF setting. 

Sure enough, we had added a very beneficial filtered index recently to help with some lookups on a table where only a minority of the records had a status we were interested in. 

The filter was removed, performance gain lost, the UPDATE statements started working again.

Our SQL Server 2019 Administration Inside Out Fun Author Survey

SQL Server 2019 Administration Inside Out

Honored again to be leading the talented SQL Server 2019 Administration Inside Out author team, and share some fun insights from the team as the book is in its final edits.

Technical book writing is such a mixed bag of emotions - long hours writing, existential doubt and validation, progress in seeing chapters move through editing stages, Sisyphean edits, wrangling metadata and chapter status, pride in the finished product, and finally some post-traumatic stress as the next major release of SQL Server approaches. It's been a pleasure to be the ersatz leader of the team, even though I'm not the first name on the book, a lot of work from everyone on the cover goes into producing the content, including our two beloved technical editors Meagan Longoria and Louis Davidson.

So a "fun" look back at our time on the book, spent mostly in Q4'19, seems appropriate as we near publishing.

A little bit on the cover order. We broke up the "shares" of workload on the book by chapter. Authors who revised/added to existing chapters received one share. For net new chapters (for example, our entirely new final chapter on Big Data and Machine Learning features of SQL Server 2019) an author received three shares. Combine all those shares together, and we came up with a cover order, descending. It doesn't necessarily translate to chapter count or effort, and I can attest personally that with all the new changes necessary for SQL Server 2019, existing chapters' revisions were considerably more work than we thought! Exciting that the book has a lot of new value even for existing chapters, and something to note for next time as we plan workloads...

Similar to our fun author survey from the 2017 book, we the author and tech editor team put answers to questions. This was a nice break while the chapters were progressing through their final edits. The 2019 edition of this book series should be on shelves in Q1'20 and is available for pre-order now everywhere books are sold.

As for this book's fun author survey, I found it interesting how half this team writes with music and half the team must not, how some of us write/edit in odd nooks and some in their home office enclaves, how some of us know how to spend free time and some of us have forgotten how, and how all of us love the new scalar function inlining feature of SQL Server 2019...

1. What music if any did you listen to while writing or editing?

What's New in SQL 2019? at the Data Architecture Virtual User Group and Q&A followup

Thanks to all 180+ of you who joined the Data Architecture Virtual User Group for a hour all about SQL Server 2019! We reviewed everyone's favorite new features of SQL Server 2019. There were lots of questions, we got off onto tangents and use cases for SSRS, licensing, Hybrid Buffer Manager, Accelerated Database Recovery, and memory-optimized TempDB metadata. I answered some more questions below. 

Kudos to Kenny Neal for organizing!
Slidedeck here: 

• PowerPoint on Github here

• Any referenced "toolbox" labs are here

Q&A Followup:

1. The Database Experimentation Assistant uses Distributed Replay to do AB testing for the same workload on different hardware/platforms. Yes, as long as you can connect the application to the SQL Server instance using Windows Auth, you can do AB testing between on-prem or Azure VM instances of SQL Server and Azure SQL Database or Azure SQL Managed Instance, and only between SQL Server on Windows and SQL Server on Linux. More info: https://docs.microsoft.com/en-us/sql/dea/database-experimentation-assistant-overview?view=sql-server-ver15

2. More info on the announcement that you can get SQL Server for free with Software Assurance if it's an Availability Group replica in Azure VM's: https://cloudblogs.microsoft.com/sqlserver/2019/10/30/new-high-availability-and-disaster-recovery-benefits-for-sql-server/

3. Yep, you can put the SSISDB in an Availability Group. You have been able to since 2012, it has gotten easier/better recently. https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-2017#always-on-for-ssis-catalog-ssisdb

4. Would "OPTIMIZE_FOR_SEQUENTIAL_KEY" help with BULK INSERTS? Parallel bulk inserts maybe. The sequential key hotspot PAGELATCH_EX issue is not I/O related (because it's  PAGELATCH_EX, not PAGEIOLATCH_EX), it's memory page related, and it's related to multiple parallel inserts, not just a lot of inserts. More info here: https://support.microsoft.com/en-us/help/4460004/how-to-resolve-last-page-insert-pagelatch-ex-contention-in-sql-server.

5. Parallel Data Warehouse (now inside of the Analytics Platform System appliance) is in a different lifecycle than SQL Server... I'm afraid I don't follow it and I don't know which of these features is getting over into the PDW.

6. There was a question I think about about Resource Governor and Hybrid Buffer Pool or PMEM (Persistent Memory such as Intel Optane DC). I wasn't sure there was a connection at the time. Still pretty sure there isn't. The memory governed by the Resource Governor is not buffer pool memory but query execution grant memory.

7. Accelerated Database Recovery is a SQL Server 2019 feature - it doesn't depend on database compatibility level! Though you do store the ADR version store in the user database (in a filegroup of your choosing), it works with any database compatibility level. More about ADR here: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-accelerated-database-recovery and also in our book, out soon!

8. Are there any drawbacks to enabling the memory-optimized tempdb metadata option? There are some fringe limitations in memory-optimized TempDB metadata that might be an issue for some, but probably not for most. Damir Matešić joined my webinar today and followed up with some more info on the limitations here: https://blog.matesic.info/post/Memory-Optimized-TempDB-Metadata.

If you have any additional questions, feel free to leave a comment here on this blog post or hit me up on twitter @william_A_dba.

Practical Personal Cybersecurity at Activate Conference Lightning Night

Photo: Shaniya W. of The Futures Fund.

Thanks to the organizers and many attendees of tonight's Activate Conference Lightning Night at LSU, the first of many series of short-format talks on technology put on by the creatives behind the annual Activate Conference in Baton Rouge.

It was great to see so many diverse faces from the Baton Rouge tech scene, including a big presence (and a teenage speaker!) from the Futures Fund!

You can download my slidedeck on Practical Personal Cybersecurity here on my Github.

Thanks again to everyone to was there, and a special thanks to Lynsey Gwin and Quinton Jason Jr for organizing! Please fill out their post-event survey here.

Hilarious and energetic @william_a_dba just gave a great talk on personal cybersecurity at @activ8conf pic.twitter.com/lt1ycqwccp

— Christine Assaf (@HRTact) January 15, 2020

"Human in the machine" by @hrtact was a powerful walk through our human responsibilities and needs in technology. Ended with a call for our creativity to enhance, not replace or outdate, real human communication. A strong addition to tonight's @activ8conf lineup of 10 speakers! pic.twitter.com/ng5suJrqTe

— William Assaf (@william_a_dba) January 15, 2020