Getting into PostgreSQL

PostgreSQL is open source database management system which can compete with more established DBMSs like SQL server and MySQL. In recent years PostgreSQL has grown popularity exponentially. Specially, we can see lot of SQL server and MySQL database experts are turning their attention to it. This is why novice like me also should have a look at what's the fuzz.

There are many theories why PostgreSQL has suddenly become very popular. But I agree that one of the main factors to it is that MySQL community is beginning to distance them self from MySQL, because it is now owned by Oracle (vendor lock). 

In addition to that, I see SQL server new features are becoming less and less enthusiastic people who use it. For example SQL Server 2022, release was disaster to Microsoft, because few of the feature didn't work the way they expected. Plus we are now in middle of the 2024, but there is no sign of the next version of SQL server yet (bit odd comparing Microsoft previous release cycles). So SQL server experts are getting little agitated and they are looking for alternative ways to keep the future bright. When expert do that rest of the community also follows.

From PostgreSQL side also it has become more and more robust over the years to compete with main players in the field. Features like non relational query support and object relational support gives the main advantages.

Considering above, I have also started looking into PostgreSQL basics. In future blogs I will be blogging about my journey with PostgreSQL.

Check() Constraint in SQL Server

CHECK() Constraint in SQL server is a data validation technique. Using CHECK() constraints, we can minimise data issues that could cause by bad application code or by incorrect queries by user at the database level.

Consider a table like below:

CREATE TABLE CourseSchedule (

    courseId int NOT NULL,

    course_year int NOT NULL,

    course_start_date date,

    course_end_date date,

    PRIMARY KEY (courseId, course_year) )

Let's say we need users (or applications) to insert year as 4 digit year and year should be value between 2000 and 3000. Then we can define a check constraint like below.

CREATE TABLE CourseSchedule (

    courseId int NOT NULL,

    course_year int NOT NULL,

    course_start_date date,

    course_end_date date,

    PRIMARY KEY (courseId, course_year),

    CHECK(course_year >= 2000 and course_year <= 3000))

You can give a name to the constraint. This is really helpful when debugging issue. So you know which table and which column it happened.

E.g.

CREATE TABLE CourseSchedule (

    courseId int NOT NULL,

    course_year int NOT NULL,

    course_start_date date,

    course_end_date date,

    PRIMARY KEY (courseId, course_year),

    CONSTRAINT my_constraint1 CHECK(course_year >= 2000 and course_year <= 3000))

Check constraint can be declared in the line of the column, if it only applied to that column

E.g.

CREATE TABLE CourseSchedule (

    courseId int NOT NULL,

    course_year int NOT NULL CHECK(course_year >= 2000 AND course_year <= 3000), 

    course_start_date date,

    course_end_date date,

    PRIMARY KEY (courseId, course_year))

But if it reference multiple columns, it has to be on its own line.

E.g.

CREATE TABLE CourseSchedule (

    courseId int NOT NULL,

    course_year int NOT NULL,

    course_start_date date,

    course_end_date date,

    PRIMARY KEY (courseId, course_year),

    CONSTRAINT my_constraint2 CHECK(course_start_date <= course_end_date))

If you want to drop a check constraint:

ALTER TABLE CourseSchedule 

DROP CONSTRAINT my_constraint2

Temporary Stored Procedures

I have worked with stored procedures in SQL Server for more than decade now, but only now I got to know the existence of temporary stored procedures. Weird!

Temporary stored procedures works exactly the same way as temporary tables in SQL server.

So their name starts with # for local temporary stored procedures and ## for global temporary stored procedures.

E.g. #thisIsMyLocalTempSp

        ##thisIsMyGlobalTempSp

Same as temporary tables, local temporary stored procedures only available in current session and global temporary stored procedures is available to all sessions, but dropped when session that created it closes.

That make me think, will SQL server allow temporary functions and views too? Unfortunately (or fortunately) my research shows - No.

Note that temporary stored procedures are created on tempdb, so all the issues with tempdb will arise with them (just like temp tables).

So what is the use of temporary stored procedures? Main advantage is I think code re-use. For example if you have a very long stored proc, that uses piece of code over and over again (but you really don't want to create it as a permanent stored proc), you can create a temp stored proc and use it within permanent stored procedure.

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