SQL 2019 – Indirect Checkpoint

Checkpoint in SQL server is the process by which dirt pages are written to disk. However it had a flaw that cause issues and the solution of which is indirect checkpoint. In the beginning we had automatic checkpoints. Here we assume a recover interval of 60 secs. Then in a very crude explanation we measure…

Accelerated database recovery – And how it’s like a car wash

Accelerated Database Recovery is a feature that DBAs will find very useful. Dba’s are often stuck in a situation where the transaction log file cannot be truncated because of a long running query or the database recovery takes longer than usual after shut down and restart. In such scenarios Accelerated database recovery is the feature…

SQL 2019-Data Classification and Vulnerability assessment

Data Classification With GDPR security has become a high priority for a large number of organizations the cost of not implementing robust security has been significantly increased and therefore companies that previously used to think security wasn’t a key part of their architecture now has to revisit that approach. But complying with GDPR and all…

Error when installing SQL 2019 – Developer Edition

Error Message Cannot insert the value NULL into column ‘CustomContext.ClusterId’, table ‘DWDiagnostics.dbo.pdw_component_health_data_lock’; column does not allow nulls. INSERT fails. Affected Components DB Engine , Replication , Polybase , Full Text Search , Java Connectors for HDFS , Data Quality Services Resolution Currently Under investigation Notes Post installation DB engine components are installed but server is…

SQL 2019 – Lightweight Query profiling

Lightweight query profiling is a feature that was introduced in SQL 2014. Before we can start talking about lightweight query profiling we need to understand the problem that it was solving. Prior to the introduction of lightweight query profiling database administrators had to rely on profile or running a trace in order to identify performance…

SQL 2019 – TDE SUSPEND RESUME

In this video we are going to talk about transparent data encryption. Transparent data encryption is a feature in SQL server that protects data at rest. Data at rest means your MDF and LDF files. These files reside on the hard disk and are easy prey to anyone with access the file system. By simply…

SQL 2019 -Batch Mode on row store

With the introduction of Columnstore indexes we got Vertipaq Compression and Batch mode as the execution mode. The latter results in much faster query execution where large number of rows need to be processed e.g aggregation in a DW query. Naturally the question then becomes why not use a Columnstore index if any way you…

Pattern matching vs Fuzzy Lookup in SQL Server

I recently came across a post in Linkedin about a script that performs Pattern Matching or “Fuzzy Lookup” within TSQL. The Script was definitely an elegant solution to scoring similar words but it still wasn’t exactly Fuzzy Lookup it was pattern matching. Before we continue I guess it will be better to first explain the…

Tales from the crypt: – Passing multiple int values into a variable

Reference Post https://social.msdn.microsoft.com/Forums/en-US/7c04a296-3c26-4a43-ae63-bc90139f4ab3/error-converting-data-type-nvarchar-to-int?forum=sqlgetstarted I was browsing through the forums and found this question being asked. While the reason for the error is fairly obvious I felt it needed further investigation so here it is. The question being asked is why does the below query work While the next one fails Naturally anyone with any TSQL…

Generating TPC H data for testing

A common requirement I have is to generate test data. Sometimes for trainings and sometimes for consulting work. As you may have seen from many previous video and blogs typically use Airlines Ontime dataset for large databases and Mockaroo for smaller datasets. However recently I wanted to try and see if I could achieve comparable…

Using Bookmarks in PowerBI

PowerBI is a robust reporting tool that has a number of easy to use and configurable options when it comes to reporting. One of the most frequently used features in PowerBI is the bookmarks capability. A bookmark as its name suggest provides way to snapshot a report page keeping in mind the filters and customizations…

SQL 2019- Script for working with Resumable online indexes

Below is the script used in the video demonstrating Resumable online indexes feature in SQL 2019. https://youtu.be/xJTfyERfTkk USE master IF EXISTS ( SELECT 1 FROM sys.databases WHERE name = ‘ResumableIX’ ) BEGIN DROP DATABASE ResumableIX END CREATE DATABASE resumableix GO USE ResumableIX GO CREATE TABLE resumableIxtbl ( Id INT identity(1, 1) ,customername VARCHAR(100) ,somerandomtext VARCHAR(1000)…

Tuple Mover Basics and the Updateable Columnstore index

When Microsoft introduced Columnstore indexes in SQL server 2012 a key limitation was the table could only be read from and not inserted. While the limitation itself has been overcome in subsequent versions of SQL server we need to understand the impact of how they work to explain performance issues when it comes to accessing…

Adaptive Joins SQL 2017

SELECT a.StateName ,a.CityName ,sum(flights) FROM Airports a INNER JOIN normalization_index n ON a.AirportSeqId = n.OriginAirportSeqID WHERE a.AirportSeqId > 1474703 GROUP BY a.StateName ,a.CityName SELECT a.StateName ,a.CityName ,sum(flights) FROM Airports a INNER JOIN normalization_index n ON a.AirportSeqId = n.OriginAirportSeqID WHERE a.AirportSeqId = 1451202 GROUP BY a.StateName ,a.CityName SELECT a.StateName ,a.CityName ,sum(flights) FROM Airports a INNER JOIN…