Log in | Register

SQL Server Internals & Architecture Archives - SQL Server Blogs, Events, Webcasts, Videos, SQL Server learning & education SQL Server Internals & Architecture Archives - SQL Server Blogs, Events, Webcasts, Videos, SQL Server learning & education

SQL Server Internals & Architecture

SQLServerGeeks Annual Summit 2016 IS HAPPENING IN BANGALORE. HAVE YOU REGISTERED?

SQL Server – Join Operation

In this blog we’ll find how SQL Server works when we do join operation. SQL Server optimizer chooses one of the below physical operator to perform logical join operation.

Hash Match
Merge Join
Nested Loop Join

As far as performance is concern let me tell you, we can’t say which one will best.Each operator has its own advantages […]

SQL Server 2016 – Batch Mode Processing with Serial Plan

Hello Friends,

There are lots of features and improvements announced by Microsoft in SQL Server 2016, one of them is providing batch mode processing with 900 rows batch size. SQL Server 2016 uses batch mode processing even if the execution plan is a serial plan which was not possible with previous versions. Due to this […]

SQL Server 2016 – Trace Flag 9453 – Disable Batch Mode Processing

Hello Friends,

In my previous blog post, we have seen the SQL Server 2016 Sort operator with batch mode processing. To compare the performance for Batch mode versus Row mode for Sort operator, I used a trace flag 9347 which disables the batch mode processing for sort operator. Now the question is: Is there any […]

SQL Server 2016 – Sort Operator with Batch Mode Processing

Hello Friends,

There are lots of features and improvements announced by Microsoft in SQL Server 2016, one of them is providing the sort operator with batch mode processing. In the programming world, we know that  performing row by row processing take longer time as compared to perform the same processing in batch mode. If the […]

SQL Server 2016 – SQL Server checkpoint

Hi Friends,  In today’s blog post we will learn about the importance of SQL Server checkpoint and changes in SQL Server 2016. I’ll start with some basics.

How SQL Server works?

To understand the checkpoint, it’s important to understand the working of SQL Server. Let’s say, I want to update a few records of a table in […]

SQL Server 2016 – Trace Flag 1118 Changes

Hi Friends,

My today’s blog post is focused on trace flag 1118 changes in SQL Server 2016. Every DBA knows, this trace flag is generally used to allocate dedicated or uniform extent rather than mixed extents for databases to minimize the contention of mixed extent allocation. You can read the same in one of my […]

SQL Server 2016 – Trace Flag 1117 Changes

Hi Friends,

My today’s blog post is focused on trace flag 1117 changes in SQL Server 2016. We all know that there are multiple trace flags in SQL Server. These trace flags are generally used to change the default behavior of SQL Server. One of these trace flag is 1117. This trace flag is generally […]

Mixing OLTP and DW – A Tale of Seven Wonders – Wonder 6

Hello Geeks,

Welcome to the new series of seven wonders of SQL Server. These are not the wonders which make you say Aww. These are the ones which make you really wonder why. I will be covering the seven of such jaw dropping situations where you wonder what is happening and few bizarre reasons why […]

Heap versus Clustered Table

Hi Friends,

Today we are going to discuss on Heap versus Clustered Table. Many times we as a DBA have given the suggestions that avoid the use of Heap [Table without clustered index] in the database. There are lots of reasons for recommending the Clustered Table [Table with clustered index] rather than Heap. One of […]

Hidden Gems of SQL Server 2014 – Managed Lock Priority

Problem Statement:
Theoretically online index rebuild does not block. Practically boom… They do. Online index rebuild operations does not take down your indexes. But there is a point in the index rebuild where they build the new index structure and switch from your old index structure to new one. This is a simple metadata pointer […]

Newsletter_Footer