Essentials and usage of NOLOCK hint in SQL Server

Essentials and usage of NOLOCK hint in SQL Server
4.3 (86.67%) 3 votes

The main idea of the SQL Server locking mechanism is that it controls the consistency of transactions. According to this principle, if a process wants to perform insert, delete or update operations, SQL Server engine locks the row or rows and does not allow another process until the transaction is completed. Under some circumstances, this locking mechanism can lead to performance problems such as high concurrent process pressures. So you can experience the deadlock (Deadlock is a concurrency problem in which two transactions want to access the same data simultaneously) problems in your database. In this article, we will focus on how to avoid lock issues with the help of NOLOCK hint. First, let’s learn the main essentials and details of dirty read methodology because the NOLOCK hint can cause dirty reading.

Read More

Query Store: Showing the Impact of Indexes on Inserts

Query Store: Showing the Impact of Indexes on Inserts
3.8 (75%) 4 votes

Introduction

It is common knowledge in database circles that indexes improve query performance either by satisfying the required result set entirely (Covering Indexes) or acting as lookups which easily direct the Query Engine to the exact location of the required data set. However, as experienced DBAs know, one should not be too enthusiastic about creating indexes in OLTP environments without understanding the nature of the workload. Using Query Store in SQL Server 2019 instance (Query Store was introduced in SQL Server 2016), it is quite easy to show the effect of an index on inserts. Read More

Traditional and Natively Compiled Scalar-Valued User-Defined Functions

Traditional and Natively Compiled Scalar-Valued User-Defined Functions
4.2 (84%) 15 votes

In the software programming world, there are several approaches used by developers that help them with effortless software development. Design patterns, object-oriented programming, test driven development are some of them. If we particularly mention the code reuse; “Reuse of a typed code. It is written once and can be used in most places. It is a basic concept of software engineering. It is a structure necessary for modular programming. ”

For this reason, user-defined functions in SQL Server help us to avoid rewriting of T-SQL queries. At the same time, they improve code readability. In this post, we will discuss the pros and cons (advantages and disadvantages) of the traditional and natively compiled scalar user-defined functions and make a simple performance test.

Read More

Compare Execution Plans in SQL Server

Compare Execution Plans in SQL Server
3.8 (76.36%) 11 votes

Database Administrator always makes an effort to tune SQL Server query performance. The first step in tuning query performance is to analyze the execution plan of a query. Upon some conditions, SQL Server Query Optimizer can create different execution plans. At this point, I would like to add some notes about SQL Server Query Optimizer. SQL Server Query Optimizer is a cost-based optimizer that analyzes execution plans and decides the optimal execution plan for a query. The significant keyword for the SQL Server Query Optimizer is an optimal execution plan which is not necessarily the best execution plan. That’s why, if SQL Server Query Optimizer tries to find out the best execution plan for every query, it takes extra time and it causes damage to SQL Server Engine performance. Read More

Registered Server, a Hidden Gem of SQL Server Management Studio. Part 2

Registered Server, a Hidden Gem of SQL Server Management Studio. Part 2
4.5 (90%) 2 votes

In my previous article, I have covered the following topics:

  1. What are Registered Servers and their usage.
  2. How to create and configure a Local Server Group and Central Management Studio.

In this article, I am going to demonstrate how to execute a query on multiple servers using Registered Servers. Also, I am going to cover how we can evaluate the database policy on multiple database servers using Registered Servers. Read More

Exploring SQL Server 2016 Query Store GUI

Exploring SQL Server 2016 Query Store GUI
5 (100%) 2 votes

Introduction

Query store is a new feature, introduced in SQL Server 2016, that allows database administrators to historically review queries and their associated plans using the GUI available in SQL Server Management Studio, as well as to analyze query performance using certain Dynamic Management Views. Query Store is a database scoped configuration option and is available for use if the compatibility level of the database in question is 130.

Read More

Performing Data Changes Audit Using Temporal Table

Performing Data Changes Audit Using Temporal Table
2 (40%) 1 vote

SQL Server 2016 has introduced a feature called ‘System versioned temporal table’. Using normal table, you can retrieve current data; while using a system-versioned temporal table, you can retrieve data which was deleted or updated in the past. To do that, a temporal table will create a history table. The history table will store old data with “start_time” and “end_time”. Which indicates a time period for which the record was active. Read More

SQL Server Table Partitioning

SQL Server Table Partitioning
5 (100%) 3 votes

Problem

In this article, we will focus on the demonstration of table partitioning. The simplest explanation of table partitioning can be called as dividing large tables into small ones. This topic provides scalability and manageability.

Why do we need table partitioning?

Assume that we have a table and it grows day by day. In this case, the table can cause some problems which need to be solved by the steps defined below:

  • Maintain this table. It will take a long time and consume more resources (CPU, IO etc.).
  • Back up.
  • Lock problems.

Read More