SQL Server Worker Threads

Here is my take on SQL Server Worker Threads and Max Worker Thread Setting.

What are Worker Threads

SQL Server, serve "Requests". Request is a logical representation of query or batch or system task (e.g. log update).

Requests can be served via one or more tasks. If it is serial request, one task works at a given time. But if it is a parallel request, multiple tasks can works at a given time.

SQL server spawn, worker to carry on a task. "Worker" is a logical representation of operating system thread. Worker threads are the backbone of SQL Server’s multitasking capabilities.

Max Worker Threads

If you go into SQL Server Properties page -> Processors section, you see a setting called "Maximum worker threads".

This controls how many maximum worker threads SQL server can spawn at a given time. Well Zero doesn't mean there will be none.

When setting is set to zero, SQL server use following formular to calculate maximum number or worker threads it can have:

On 86x processor -> 256 + ((No of Logical Processors – 4) * 8)

On 64x Processor -> 512 + ((No of Logical Processors – 4) * 16)

So if you have 64bit machine with 12 Logical processors, max worker count will be 640.

You can find this out easily by executing following query:

SELECT max_workers_count

FROM sys.dm_os_sys_info

You can set this value to a custom value. But you will find most of experts suggest to keep it default value and look for other issues for resolution.

References: Configure the max worker threads (server configuration option) - SQL Server | Microsoft Learn

Expert articles:

BrentOzar -> https://www.brentozar.com/archive/2020/01/three-reasons-to-temporarily-change-max-worker-threads/

Pinal Dave -> https://blog.sqlauthority.com/2018/06/15/sql-server-optimal-value-max-worker-threads/

Logging in .Net Core 7 Web App

Recently I have upgraded .Net Core Web API project which was written in .Net Core 2.2 to .Net version 8.

It is a quite a jump, so had to re-write few classes to get it working.

When it finally runs, I realized that logging is not working on the upgraded app. App use to log to Windows Event Log.

Reading through this article, I found out there are few things need changing.

First of all .Net 8, web apps starts with WebApplication.CreateBuilder method. By default this method adds following logging providers:

• Console
• Debug
• EventSource
• EventLog (of course this only works on Windows platforms)

So, by default EventLog is added, but why didn't I get logging output? I have tried putting EventLog settings on applicationSettings.json, but didn't work.

Reading through above article, I noticed that you need to specify settings on code level:

var builder = WebApplication.CreateBuilder();

builder.Logging.AddEventLog(eventLogSettings =>

{

    eventLogSettings.SourceName = "MyLogs";

    eventLogSettings.LogName = "Application";

});

Once this is added logging started to work. 

Note that if you don't specify "SourceName", it is defaulted to ".NET Runtime". So if you need to say source on the Windows Event log to say custom name, it need to specify above code. 

Also make sure you have created the Event Source before starting to logging to Windows Event log. I use following powershell command to add event source to Windows Event Log.

New-EventLog -source <<your source name>> -LogName Application

One other thing you need to pay attention is logging level. Logging level can be specified in code or in applicationSettings.json file.

As explained in above article - "Unlike the other providers, the EventLog provider does not inherit the default non-provider settings. If EventLog log settings aren't specified, they default to LogLevel.Warning."

Spools In SQL Server

Best and most simple explanation I could find on web for Spool is - "Spool is a physical operator that shows up in the execution plan. Query optimizer uses spools when it thinks that it's better to put data in the temp table rather than hitting a table again and again using seeks and scans" - from this Q&A.

Query optimizer reads and calculate computed data and put it on a worktable in tempDb when it thinks reading data from original table is too much cost (cost based optimization).

There are several types of spool operators in a execution plan:

* Table Spools -> Physical Operator

* Index Spools -> Physical Operator

* Row Count Spools -> Physical Operator

* Window Spools -> Logical/Physical Operator

* Eager Spools -> Logical Operator

* Lazy Spools -> Logical Operator

Table Spool - Read a table and create a work table that only exists for the life time of the query. 

Index Spool - Reads a table(s) and create a work table and also create a non-clustered index on it. Exists only for the life time of the query.

Row Count Spool - Reads a table and returns count or rows, used when need to check existence of rows rather than data.

Window Spool - Expand each row into set of window associate with it (window functions)

Eager Spool - Scans entire input (table) and stores, even when ask for first row (eager operation)

Lazy Spool - Build the work table in lazy manner, i.e. row is stored when parent operator ask for it.

I used following articles to extract above for my knowledge and recommend you to read in deep if you like more details:

* https://msbiskills.com/2015/09/02/types-of-spools-in-execution-plan-deep-dive/ -> by Pawan Khowal

* https://learn.microsoft.com/en-us/answers/questions/463552/execution-plan-for-table-spool

How to get Input Tree on SSMS output on SQL Server

This is my first blog for Year 2024!

Recently I have the opportunity to go through "Query Optimizer Deep Drive" article series by Paul White.

It was one of the very good and soundly technical series on Query Optimization. However, thing got my most attention was Query Optimizer's input tree and how we can get it output on SSMS output window.

On SQL Server's Optimizer Pipe line, first component is "Input Tree" (Bound Logical Tree). Input Tree make it easier for optimizer to understand the query better. For example, different developers can write same query in different way. But if we can get it break down to logical steps and standardize it will be muck easier for next phases in Optimizer.

By reading and understanding the Input tree or logical steps, we can also understand how query will going to perform on SQL server and hence help to optimize the query.

So how do we make it appear on output?

As per above mentioned article we can use undocumented trace flag 8605 for this. If you want tree output to appear on SSMS output window use the trace flag 3604.

Here is my query to test this. It is a simple contact table with First name and Surname.

SELECT c.CONTACT_FirstNames, c.CONTACT_Surname FROM CONTACT c

OPTION 

(

    RECOMPILE, 

    QUERYTRACEON 3604,

    QUERYTRACEON 8605

);

And then you can see the following output on the SSMS output window.

*** Converted Tree: ***

    LogOp_Project QCOL: [c].CONTACT_FirstNames QCOL: [c].CONTACT_Surname

        LogOp_Get TBL: CONTACT(alias TBL: c) CONTACT TableID=1668825653 TableReferenceID=0 IsRow: COL: IsBaseRow1000 

        AncOp_PrjList 

*******************

(52624 rows affected)

Completion time: 2024-01-15T09:27:30.1054801+00:00

DKIM - Setting It Up ...

In order to use DKIM, first thing you need is generate private and public keys.

There are many sites/tools you can use for this. But I have used the online DKIM key generating utility hosted EasyDARMAC .

On this site you need to specify your domain and the selector you use for DKIM. Selector is just identifier to identify DKIM record (specially if you have multiple domains and DKIMs). You can use any string as selector, but most of the time we use "mail".

Key length of the DKIM determine how hard it will be to crack. I suggest you use higher key length.

When click on the Generate button, it generate, text for the DNS TXT entry, private key and public key.

DNS TXT entry will be like this:

This need to send to your ISP to setup DNS TXT entry in your domain.

Second part is private key. You shouldn't reveal this to anyone outside of your organization and must be kept as secret. Because if any malicious hacker get into this, that person can send email behalf of your domain.

You can save this private key string to .pem file and use it in your software to sign the email. We have used popular email software called Chilkat. They have comprehensive SDK to sign emails using DKIM private key. Check their DKIM reference here and you can also find example code here.

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