Setting up SMTP Email Service with smtp2go

With so many Email service providers it is hard to pick a one that provide good email delivering service with reliability and no down time.

I recently had a task to evaluate email service providers to integrate our software with. There are few SMTP service providers, such as "SendGrid", "Amazon SES", "Mailgun". But to my surprise small smtp service provider called "smtp2go" topped my evaluation, because of their responsiveness to customer matters and simplicity.

Here are quick guide on how to setup smtp service with smtp2go provider.

Create an account

First you need to create a account with smtp2go website. They provide "Free Plan", which gives you 1000 email per month. I suggest you use that to evaluate and test the service before you actually commit to paid plan - https://www.smtp2go.com/pricing/

You need a work email to sign up with. You will not be able to sign up with gmail/outlook/yahoo email account.

Verify Sender

After signup, before sending any emails, you need to verify sending email addresses. There are two ways to do:

1. Verify Single Email address

2. Verify sending email domain

Verify Single email address is simple, once you add an email address to verify, smtp2go send a verification link to the email address specified. Then you can click on it to verify it.

However, single email address doesn't provide much protection for spamming and reliable email delivery. It is only good for sending occasional notification email from a software application.

Recommended method is verify domain. When domain is verified, you can send email from any email address on that domain.

In order to verify domain, you will need to add 3 CNAM entries to to your domain's DNS record. These settings are provided by smtp2go domain verification wizard, so it is easy to handle this. If you don't have access to your domain's DNS, you will need to contact who ever manage that (e.g. if you purchased your domain from GoDaddy, you can configure these setting by your self by logging into GoDaddy control panel).

SMTP User / API Key

Once domain is verified, you have two ways to use the SMTP relay. 

First one is calling their API method and send email using their API. You will require to create API key to do this. Generating API key is easy and can be done on smtp2go control panel very easily.

Second approach is to create a SMTP user. You can add as many SMTP users as you like from control panel (if you have paid plan). You need to give a user name and password will be generated for you (you can overtype it though).

SMTP Settings

SMTP settings are same for everyone using smtp2go and clearly showed on the control panel.

Use above SMTP settings and SMTP user credentials you have created to send emails from your application.

There are very comprehensive set of support articles can be found on smtp2go site -> https://support.smtp2go.com/hc/en-gb

There are few dashboard reports which shows you the status of your email delivery. You can track which email addresses are getting bounce and rejected.

You can chat with their support team from the control panel it self. For me it was very responsive.

Overall I'm pretty satisfied with their service so far.

Prevent App Pool recycle when web config changes

We all know when we need to recycle and get web app restarted we can edit the web.config file. Sometime we(developers) even do dummy change to force it restart.

But what if you don't want to recycle it, but want to update the web.config file anyway. For example I had a situation where I want to update the web.config file, but didn't wanted to recycle/restart the web app immediately, because users were using it heavily. I just needed to do the web.config update now and restart the web app later (out of working hours).

That's when I heard the setting called - "Disable Recycling for Configuration Changes".

This setting can be found in "Advanced Settings" section of an app pool. So this only effective to one app pool at a time.

Default value for this is "False".

Setting this to true will cause app pool to NOT to restarts/recycle when web.config changes.

 

Query Hints -> OPTIMIZE VS RECOMPILE

I have known query hint OPTION(RECOMPILE) for while and most I've heard was it is bad and should be used with care.

As per my understanding when OPTION(RECOMPILE) is used within a stored procedure, plan for that stored procedure get compiled every time it runs (instead of caching the plan in plan cache for future use). Compiling plan is known to be very CPU expensive as it involves many steps, specially for large complex queries. So we should avoid it as much as possible. Using OPTION(RECOMPILE) is a trade off between plan compiling CPU time vs using bad/mediocre plan for set of values.

Recently I came across another related query hint - OPTION(OPTIMIZE FOR ...)

OPTIMIZE FOR hint can be used two ways.

OPTIMIZE FOR (@myparam1 = 1, @myparam2 = 2) -> i.e. specify set of parameters and values to optimize for. So SQL server compile and cache the plan that uses value 1 and 2 for above two parameters. It doesn't use what value came first at run time (like it normally does). By changing the values run time you can have multiple plans cached for same stored procedure.

Other approach is OPTIMIZE FOR UNKNOWN -> In this case SQL server tries to get best plan for each parameter set passed in, BUT it might decided in some cases compiling for new plan will cost me lot so I will use something similar I already have in cache. In other words it try to do the trade off and try to come up with plan that cost same every time. This great article by Kendra Little on BrentOzar website explains it much more details.

Again, as we come across regularly, in performance tuning world of SQL Server, there is nothing fixed, you always need to try and and see what is best for each case.

P.S. I'm not a SQL server expert (yet), I'm still learning, so please be kind enough to comment if something wrong. These notes are my own reference mostly.

Adding Windows Firewall Rules to a Group

Anyone who has used windows firewall, knows it is easier to create rules and configure, but hard to organize.

Though Windows Advanced Firewall interface shows a Group column, there is no easy way to set the group through UI.

In a recent task assigned to me, I wanted to organize my rules to several groups, that's when Powershell came in handy.

Let's say I have 5 rules I need to group. Currently they don't have a group.

I want to assign Rule 1, 2 and 3 to "My Group 1" and rule 4 and 5 to "My Group 2".

Get-NetFirewallRule -DisplayName 'My Rule 1' | ForEach { $_.Group = 'My Group 1'; Set-NetFirewallRule -InputObject $_ }

Get-NetFirewallRule -DisplayName 'My Rule 2' | ForEach { $_.Group = 'My Group 1'; Set-NetFirewallRule -InputObject $_ }

Get-NetFirewallRule -DisplayName 'My Rule 3' | ForEach { $_.Group = 'My Group 1'; Set-NetFirewallRule -InputObject $_ }

Get-NetFirewallRule -DisplayName 'My Rule 4' | ForEach { $_.Group = 'My Group 2'; Set-NetFirewallRule -InputObject $_ }

Get-NetFirewallRule -DisplayName 'My Rule 5' | ForEach { $_.Group = 'My Group 3'; Set-NetFirewallRule -InputObject $_ }

So in the firewall rule, first you fetch the firewall rule by using Get-NetFirewallRule cmdlet. Then for each result, you set the group to bying using $_.Group.

Result:

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.