Every Scooby-Doo adventure has its running gags. Shaggy and Scooby bolt from every ghost in sight, Velma’s always losing her glasses, and Scrappy jumps in yelling, “Let me at ‘em!” But in this episode, Scrappy’s favorite joke is about a villain he calls Jason. Of course, Jason isn’t really a monster at all—it’s JSON, the data type we’ve been wrangling inside SQL Server and pretty much everywhere else across our technology stacks. . And in SQL Server 2025, the gang finally figures out how to unmask this villain and catch him faster than ever. Jason Unmasked: JSON in Native Binary Format Scrappy may play it up like Jason’s a big…
-
-
No Scooby-Doo story is complete without footprints leading to a hidden passage. In SQL Server 2025, those footprints point us straight toward the next big feature: optimized locking. And like any good sleuth, we’re going to follow the trail step by step. But first, flip the levers that open the secret door: Turn on ADR before you hunt. Think of Accelerated Database Recovery (ADR) as the latch that unlocks the passage. You must enable ADR before you can enable optimized locking. (And if you ever need to turn ADR off, you’ll have to disable optimized locking first—no sneaking out the window!) RCSI = brighter flashlight. For the biggest win, enable…
-
Tempdb is always a topic for me whether it’s in my sessions or blogs I have written. However, I’ve never been so excited about it then I am when it comes to the dramatic performance changes introduced in SQL Server 2022. THEY HAVE SOLVED ONE OF OUR BIGGEST PERFORMANCE BOTTLE NECKS, System page latch concurrency. In SQL Server 2019 they addressed what’s known as metadata contention, when pages that belong to systems object take page latches while updating tables that track table metadata by introducing memory optimized tempdb. Additionally, the product team made improvements to object allocation contention. This is the contention for metadata pages used to manage space allocation…
-
We all have written queries that use COUNT DISTINCT to get the unique number of non-NULL values from a table. This process can generate a noticeable performance hit especially for larger tables with millions of rows. Many times, there is no way around this. To help mitigate this overhead SQL Server 2019 introduces us to approximating the distinct count with the new APPROX_COUNT_DISTINCT function. The function approximates the count within a 2% precision to the actual answer at a fraction of the time. Let’s see this in action. In this example, I am using the AdventureworksDW2016CTP3 sample database which you can download here. SET STATISTICS IO ON SELECT COUNT(DISTINCT([SalesOrderNumber])) as DISTINCTCOUNT…
-
Ever need to have a test database on hand that you can allow others to query “real like” data without actually giving them actual production data values? In SQL Server Management Studio (SSMS) 18.0 preview Microsoft introduces us to Static Data Masking. Static Data Masking is a new feature that allows you to create a cloned copy of your database and replace sensitive data with new data (fake data, referred to as masked). You can use this for things like development of business reports and analytics, trouble shooting, database development and even sharing data with outside teams or third parties. Unlike Dynamic Data Masking added in SQL Server 2016, this…
-
SQL Server Vulnerability Assessment (VA) in SQL Server Management Studio 17.4 or later lets SQL Server scan your databases for potential security vulnerabilities and can be run against SQL Server 2012 or higher. If you are not on a newer version on SSMS, don’t worry, you can download it here. Running any kind of scans against data always concerns me as performance impacts can really ruin your day. Luckily VA is light weight and runs without performance impacts while still giving you an in-depth view of where you could improved your SQL Server’s security. The process is designed to meet data privacy standards and compliance using knowledge base rules that…
-
Data exploration and documentation can be cumbersome, classifying data can be one of those tedious but necessary things. With the introduction of such things as GDPR there is an ever greater need to classify and label your sensitive data. Microsoft attempts to help us out with this in the new SQL Data Discovery and Classification feature in SQL Server Management Studio (SSMS). This a new feature is available in Azure SQL Database and SSMS 17.5 and higher. It can be used against databases in SQL Server 2008, compatibly mode 100, and greater. This feature will scan through your database tables and identifies columns that contain possible sensitive data. It then…
-
SQL Server 2019 brings a very exciting new feature that, is long overdue. Resumable online index create is one of my favorite new things. This paired with the Resumable Index Rebuilds introduced with SQL Server 2017 really gives database administrators much more control over index processes. Have you ever started to build a new index on very large table only to have users call and complain their process is hung, not completing, or system is slow? That’s when you realize you’re the cause because you tried to sneak in a new index. I have many times, because creating a new index can impact performance and can be a problematic process…
-
Every once in a while, I like to take a moment and learn something new about the latest SQL Server gizmos and gadgets. Today I came across system-versioned temporal tables and it peeked my interest, so I figured I’d investigate and share my finding with you. How many of you have need to track data changes over time? I’ve needed this many times for things like auditing, investigating data changes, data fixes, and trend analysis of values over time. Having to do this is the past has been a very daunting task at times and sometimes nearly impossible. This is where system-versioned temporal tables will really help out. They have…
