Log in | Register

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

SQL Server 2016

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

SQL Server 2016 – USE HINT without having SA permission

Hello Friends,

Trace Flags are really important considerations in SQL Server where we wants to change some default behavior at instance level or query level. If we want to enable the trace flag globally then we can enable it for the instance level. We can also use the trace flags at specific query level as […]

By |April 17th, 2017|Categories: Security and Encryption, SQL Server|Tags: , |Comments Off on SQL Server 2016 – USE HINT without having SA permission

SQL Server 2016 – sys.dm_db_incremental_stats_properties for Incremental Stats

Hello Friends,

During various troubleshooting scenarios, we generally check the statistics information on the objects. These statistics information helps us a lot to detect the root cause. There is a DMF that we use to check the statistics information i.e. sys.dm_db_stats_properties. This DMF requires the object id and stats id to return the details.

SQL Server […]

By |April 7th, 2017|Categories: Database Administration, Database Development, Indexes & Statistics|Tags: , |Comments Off on SQL Server 2016 – sys.dm_db_incremental_stats_properties for Incremental Stats

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 […]

By |October 17th, 2016|Categories: Database Administration, Performance Tuning, SQL Server Internals & Architecture|Tags: , , |Comments Off on SQL Server 2016 – Batch Mode Processing with Serial Plan

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 […]

By |October 10th, 2016|Categories: Database Administration, Performance Tuning, SQL Server Internals & Architecture|Tags: , , |Comments Off on SQL Server 2016 – Trace Flag 9453 – Disable Batch Mode Processing

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 – New Columns in sys.dm_exec_query_stats

Hello Friends,

There are lots of features and improvements announced by Microsoft in SQL Server 2016, one of them is adding new columns in sys.dm_exec_query_stats. These new columns capture information about threads and memory. First, I’ll show you the new added columns for collecting threads and Degree of parallelism level information.

Here total_dop represent the total […]

SQL Sever 2016 – DBCC CHECKDB WITH MAXDOP

Hello Friends, There are lots of feature and improvements announced by Microsoft in SQL Server 2016, one of them is restricting the number of processors for DBCC CHECKDB. We know that MAXDOP setting is generally taken care by Instance level setting ‘MAX Degree of Parallelism’ under advanced tab of server property. I’ll show you the […]

By |September 18th, 2016|Categories: Database Administration, Database Maintenance, Performance, SQL Server|Tags: , |Comments Off on SQL Sever 2016 – DBCC CHECKDB WITH MAXDOP

SQL Server 2016 – Query Time Based Data on Temporal Table

Hello Friends,

In my previous blog posts, we have seen multiple ways for the creation of temporal tables and DML operation impact. Today we will have a look at Query Time Based Data on Temporal Table. First, let me create the table and then I’ll perform some DML operations, so that can query the data.

Now […]

By |September 11th, 2016|Categories: Auditing, Database Administration|Tags: , |Comments Off on SQL Server 2016 – Query Time Based Data on Temporal Table

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 […]

By |August 27th, 2016|Categories: Database Engine, SQL Server, SQL Server Internals & Architecture|Tags: , |Comments Off on SQL Server 2016 – SQL Server checkpoint

SQL Server 2016 – DML Operations on Temporal Table

Hello Friends,

In my previous blog posts, we have seen multiple ways for the creation of temporal table. You can go to those posts by clicking on below links:

SQL Server 2016 – Temporal Tables Part 1

SQL Server 2016 – Temporal Tables Part 2

SQL Server 2016 – Temporal Tables Part 3

Today, we will take a look […]

By |July 31st, 2016|Categories: Auditing, Database Administration, Database Development, Database Engine|Tags: , |Comments Off on SQL Server 2016 – DML Operations on Temporal Table
Newsletter_Footer