Gethyn Ellis, SQL Consultant, Northdoor
26th September 2019
Well, Microsoft has taken the new features that came in SQL 2016 and 2017 and added to and enhanced them to produce a database offering that can do so much more for you than anything we’ve seen before. Including the ability to do Realtime analytics on both your traditional SQL Server relational database engine and on semi-structured and unstructured data using Big Data Clusters
To quote a famous Microsoft SQL Server employee from SQLBits… There are several enhancements and improvements in the “Good old Grandad SQL Server.”
The Intelligent Query Processing, which was introduced with SQL 2016 has been improved.
Previous versions of SQL Server have always assumed that a table variable will only have one row in it which can cause bad execution plans to be created. Introducing Table Variable Deferred Compilation. Now the saving of the execution plan will be delayed until after the first run of the query to see how many rows go into the table variable. The next time the query is run the query processor will have a better idea of how to create the execution plan.
Approx Count Distinct
If you’ve ever had to do select distinct you know how long it can take to return data. SQL Server 2019 now has a new command called APPROX_COUNT_DISTINCT. This is much more lightweight and can return a number much faster based on the table statistics. This is great if you have a very large table and just need a rough value, but the name gives it away, it is an approximation, so it is no good if you need to know the exact value.
Scalar UDF Inlining
It is widely known that using user-defined functions in a query can destroy the performance and as such is highly discouraged. Well in SQL Server 2019 the query processor will now use the UDF in a better way to quote the Microsoft documentation “transforms scalar UDFs into relational expressions and embeds them in the calling SQL query.” which allows for vastly improved performance for UDFs.
In-memory OLTP for system tables.
You can now use memory optimised (In-Memory OLTP) tables in TEMPDB. This means we can avoid or at least reduce contention in TEMPDB, one can be one of the big bottlenecks on a SQL Server and can make a huge difference to performance.
Memory grant feedback.
If the statistics on your table are wrong or stale, then the query processor may assign too much or too little memory to a query. The processor will now take notice of this and reallocate the memory next time that query is run.
Always encrypted has suffered from not being able to do range searches. This has now been fixed by something called secure enclaves. This is a protected area of memory on the SQL server which the server can use to decrypt the encrypted data so that I can do range searches then return the encrypted values to the client. It does require Windows Server 2019 and specific server hardware
Static Data Masking. There were plenty of ways of getting around dynamic data masking to view the or work out the underlying data. Static data masking is an enhancement to SSMS that allows you to create a copy of the database with masked data so there is no way of reverse engineering the original data.
Sensitivity classification: The need to classify personal identifiable data has become much more important since the introduction of GDPR. You can add a label and information type to individual columns so you can now tag columns with a description such as GDPR or just important internal data
You can then create SQL Audits so you can now see who accessed confidential data and when. Really useful for your GDPR audits.
Many server level configurations, such as Optimize for ad hoc Workloads can now be set at the database level providing a more granular configuration for your consolidated SQL Servers
We now have a DMV and DMF, called PageResCracker, that allows you to see data page information without the need to use DBCC commands. These don’t require sysadmin permissions and make investigating page locks much easier.
You can now get estimates of potential compression of a new ColumnStore index before you create it.
Resumable / online operations have been expanded. Previously only re-indexes could be done online but we can create index with online option. We can also pause and resume the index creation and rebuilds. Allowing re-indexing operations to be spread over several maintenance windows rather than it carrying on into the peak time workload.
We can now do online creation and rebuilding of clustered columnstore indexes. This is an enterprise only feature.
Accelerated Database Recovery. When a database or a large update query has crashed it can sometimes take a long time for SQL to recover the database back to a working state. Now the database has new features so recovery can now be completed almost instantly regardless of how big the database transaction is.
And the most requested improvement to SQL server-ever.
The “String or binary data will be truncated” error message will now tell you which table and column that is causing the problem and the truncated value that can’t be inserted. This does require a trace flag (460).