DKIM - What is that?

This week, one of our client has asked to sign their emails which goes out from our software with DKIM. That's the first time I came across that term. So I got started with Google.

Found good explanation of what is DKIM in this article by CloudFlare.

DKIM stand for Domain Key Identified Mail. It is a mechanism use to reduce spam emails.

DKIM uses digital signature to identify the email is actually coming from the said domain. It has two parts:

• DNS record
• DKIM header included in the email

Email servers use DNS record with conjunction with DKIM header in the email to authenticate the email.

DNS record contains the public key for the signature.

DKIM header is the email signed by the email provider with the private key.

I will update this article when I get the chance to implement in our software.

Here is how it setup - Part 2

SQL Server Memory Grants - My Notes No 01

In order to run queries, SQL server need memory to store data. There fore when query is getting estimated, memory grant (amount of Memory SQL Server going to request) also get estimated.

Memory grant estimates are based on following (as per Erik Darlings this article):

• Number of rows -> How many rows query will read/write (again this is also an estimate at this stage)
• Size of the row -> how much memory one raw required
• Number of concurrent memory consuming operators -> how many operators will run at a time when query get executed (not all operators run same time, some need finishing other to start)

Variable Size Column Effect

Size of the row calculation get interesting with variable size (varchar/nvarchar like) columns. SQL server use half of the size of the variable size column. 

For example if column in varhchar(50) -> SQL server will assume column is half full and take 25 characters.

Memory Grant Info

If you run a query, which require a memory grant (some queries doesn't require it as SQL server can output the data as it reads), Memory grant information is shown on the execution plan.

To see this right click the Select operator and select properties:

Description of these properties can be found in this article by Pinal Dave.

Scan vs Seek in SQL Server

Recently I was able to go through Brent Ozar's "How to think like the SQL Server" free fundamental course and have to say content is very good.

Actually I have listen to the same course on YouTube several times. But this is the first time I have gone through it properly with Pen and Paper on my hand.

Learned few interesting things and one of the thing struck me most is on ever popular topic on scan and seek on SQL Server.

As per Brent, 

SCAN -> is where SQL Server start reading object (table) from one end. This can be from top/beginning or bottom/end.

SEEK -> is where SQL Server start reading the object (table) from specific point.

Generally when we say table scan, we think SQL server reads the whole table, but in reality it can be different. For example query like below:

SELECT TOP 10 * FROM dbo.Users

This will only read top 10 rows from the table, but it is a scan.

On the other hand seek can read the entire table. For example if we give SQL server to seek for a value which is not in the table, it will seek from the start or end and will go through entire table to find it.

Amazing isn't it.

More details from Brent's site -> https://www.brentozar.com/archive/2019/10/how-to-think-like-the-sql-server-engine-so-index-seeks-are-great-right/

Passing dynamic parameter to Base Constructor

 While working on a C# program this week, came across interesting scenario where I needed to pass parameter to base constructor of a class. Unfortunately, base constructor is developed by a third party, so we don't really couldn't change it.

Early days when we use this base class, we use to hard code the parameter to it in inherited class constructor. This week we had a new requirement, where that parameter required to be dynamic or read from configuration file.

To demonstrate it with example, let's assume my base class is ISOCar (where it build ISO standard car). I inherit this class and create my own modified car - let's call it MyBrandCar. Let's also assume base class has one constructor which accept engine capacity. So my code will be some like below:

public class MyBrandCar : ISOCar

{

    public MyBrandCar() : base(1.5)

    {

    }

}

My new requirement is, I need to read the engine capacity from a config file or calculate it from some other values from a config file.

So I was wondering what is the best way to approach this. Because constructor is called first and base constructor is called even before inherited class constructor, there was no way to fetch the value from the config file.

Little bit of Google suggested me to use static method. So I changed my code to following:

public class MyBrandCar : ISOCar

{

    public static decimal GetEngineCapacity()

    {

        // read the config and return the value

    }

    public MyBrandCar() : base(GetEngineCapacity())

    {

    }

}

Since static methods are executed even before constructors, this has worked.

Simple, but elegant way,

Downloading RDL files from SSRS Report Server

In work, I got a task to backup SSRS reports folder of a report server. Taking a different approach, I choose to depend on AI tools to help me out. I choose Bing Chat powered by ChatGPT.

Following is the PowerShell code generated by Bing Chat.

# Specify the URL of the report server

$reportServerUrl = "http://myreportserver01//reportserver"

# Specify the path of the report on the report server

$reportPath = "/My Report Folder 01"

# Specify the path where to save the RDL file (Note: double forward slashes)

$savePath = "D:\\BackupFolder\\Transfer\\ReportRDL\\"

# Create a new proxy to the report server (Note: we are using ReportingService2010 name space

$proxy = New-WebServiceProxy -Uri "$reportServerUrl/ReportService2010.asmx?wsdl" -Namespace SSRS.ReportingService2010 -UseDefaultCredential

# Get all reports in the folder

$reports = $proxy.ListChildren($reportPath, $false) | Where-Object { $_.TypeName -eq "Report" }

# Download each report

foreach ($report in $reports)

{

    # Get the report definition

    $reportDefinition = $proxy.GetItemDefinition($report.Path)

    # Save the report definition to disk

    [System.IO.File]::WriteAllBytes($savePath + $report.Name + ".rdl", $reportDefinition)

}

I also come across another away to download RDL files using "RS.exe" tool. It can be found in following url -> https://asgb1.freshdesk.com/support/solutions/articles/11000114924-download-ssrs-reports-rdl-files-using-rs-exe-utility

My Halloween Problem

2023 Halloween was just past, I came across this SQL Server related Halloween Problem accidently on an article I read.

Usually update operation has two separate cursors, one doing read and one doing the update.

If update operation caused row to updated in a way it cause index key also need updated and hence it's position changed, then there can be a occasions where read cursor might read it again and update again.

What is basically happening is when row is updated it change the position to match the index key and hence read by the read cursor again and updated again. 

Though it sound like it could happen very frequently, issue doesn't occur regularly on SQL server because it need to update index key and also phase separation happens while update operation doesn't allow it. Phase separation, make sure all matching rows are read first before updates are occur. However, for performance reasons, SQL server might decide some time to write some of the updated data before it read the next batch of data to be need to updated.

Above is a very high level and very simplified explanation (so that I can remember it). There are great articles on internet regarding this: 

https://www.sqlshack.com/the-halloween-problem-in-sql-server-and-suggested-solutions/

https://en.wikipedia.org/wiki/Halloween_Problem