SQL Hints: NOLOCK and NOWAIT Explained

As we know SQL Server uses pessimistic locking to protect data inconsistency. As part of its pessimistic locking strategy, it ensures data can be accessed safely, without the risk of data corruption, dirty reads or unrepeatable reads using locks. e.g. when you update any row within a transaction, locks are applied at row level which...

Pagination in SQL Server 2012 and above

Prior to SQL Server 2012, implementing pagination with millions of records was a big challenge for any developer. A commonly used workaround to implement pagination in SQL Server  was the ROW_NUMBER() function. The ROW_NUMBER function, like many of its siblings (i.e. RANK etc.), is a window function that returns a sequential integer value per row...

Adaptive Query Processing – SQL 2017 and Above

Before SQL Server 2017, the behavior of the SQL Server query processing engine was to analyze the query first, create the plan and then execute it. If the plan was somehow not appropriate, the query-processing engine was not able to change it while executing the query or even after it. Sometimes the query execution plans...

SQL Server Physical Joins

SQL Server uses following physical joins to retrieve the data from tables: Nested Loops Nested Loop joins the tables by making the one with least rows as an outer table to optimize performance. For each row in this outer table a one-by-one comparison is done to all the rows in the inner table. If there...

SQL Server Database Migration to AWS Aurora MySQL

It is not easy to convince a SQL DBA to migrate database from MS SQL to non-MS SQL specially when you have spent years with Microsoft SQL Server and believe me it took me some time to realize that this is the right way moving forward to reduce operational cost. Please see I am not...

SQL Server – OLTP vs OLAP

In this blog post I will explain the differences between OLTP & OLAP and how should we configure SQL Server and its workload in both the cases. OLTP (Online Transaction Processing) OLTP is designed to serve as a persistent data store for business or front-end applications. OLTP administers day to day transaction of an organization....

Data Page Restore to resolve Page Level Corruption

SQL Page Level Corruption can happen due to several reasons such as Server failure, disk failure etc. These data corruption is always a nightmare for DBAs, but this can be corrected if you have right backups in place which is the most important and essential thing for a DBA. Let's put it in this way...

Partial or Piecemeal Database Restore in MS SQL Server

Problem Statement: These days data is growing rapidly and maintaining its availability as per defined RTO & RPO has become biggest challenge for a DBA. This blog post will help you to design the recovery of such database. Suppose, we have a database over 10TB, which is a combination of transactional and historical data (not...

Database Migration from on-prem to Azure SQL

Why should you migrate your on-prem database to cloud:  Here are following factors, you would consider when migrating an on-prem database to cloud: You want to modernize current IT asset base. You want to prepare your database platform for future needs. You want to lower your database infrastructure costs. You want to increase Business Agility....

SQL Server Index Internals – Heap and Clustered Index

We always talk about clustered index, non-clustered indexes, heaps etc. as they are quite essential for enhancing the database performance. In this blog post I will demonstrate, how does SQL Server store the data in physical pages in case of Heap and Clustered Index. What is Heap? As explained in my previous blog (Understanding SQL...