Category: New SQL Version

Tempdb Performance Improvements in SQL Server 2022 are Dramatic

  By ,

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… Continue Reading

Approximate COUNT DISTINCT

  By

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… Continue Reading

Static Data Masking (SSMS 18.0 Preview)

  By ,

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.… Continue Reading

SQL Server Vulnerability Assessment

  By ,

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… Continue Reading

SQL Data Discovery and Classification in SSMS 17.5

  By

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… Continue Reading

New Resumable Online Index Create SQL Server 2019

  By ,

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… Continue Reading

System-Versioned Temporal Tables

  By

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… Continue Reading