Log in | Register

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

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

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

How to resolve High Compilations/Second problem in SQL Server

Hi Friends and Geeks!

Sharing an experience on resolution of High Compilations/Second performance counter in SQL Server from one of my consulting assignments.

The threshold for Compilations/Second is mentioned as 10% of Batch Requests/Second by various MVPs and in our case it was around 40 to 50%

Investigation Process

Investigate for queries with same query_hash but multiple plan_handles using […]

SQL Server 2016 – Instant File Initialization

Hi Friends,

Today, I am going to show you an interesting thing about SQL Server 2016 – Instant File Initialization. We all knew about the Instant file Initialization feature. For those who are not aware about it can click here.

As a DBA, we know the importance of this feature. Instant File Initialization feature helps SQL […]

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

The correct cardinality estimation for table variable using trace flag 2453

Hi Friends,

My today’s blog post is focused on the correct cardinality estimation for table variable using trace flag 2453. In one of my previous blog post, we have seen the use of OPTION (RECOMPILE) query hint for correct cardinality estimation in case of table variable. Now the question is, Is there any way to […]

SQL Server 2016 CTP – Alter Column Online Operation

Hi Friends,

Various new features and improvements are coming with SQL Server 2016. Various performance improvements are also added to the CTP versions. Today, I’ll show you one of them i.e. Alter Table … Alter Column online operation.

Blocking is one of the major problems when you work on performance tuning for SQL Server. We knows […]

Nested Loop Join – Temp Table versus Table variable

Hi Friends,

In my previous blog post, I have shown you the working of nested loop join in SQL Server. Today, I am going to show you the Nested Loop Join – Temp Table versus Table variable.

Before executing a query, SQL Server generates an estimated execution plan on the basis of various server settings and […]

Reserved threads and Used threads in parallelism

HI Friends,

We know about parallelism in SQL Server i.e. SQL Server uses multiple threads for the processing of a request to make it faster.  Two main important settings for the decision of going with parallelism are:

MAXDOP: Maximum degree of parallelism, This value represents, how many threads can be used for parallel processing.

The Cost threshold […]

Forward and Backward Scan

Hi Friends,

First of all wish you all a very Happy New Year 2016. I hope this new year will bring lots of joy and happiness in your life. Today, I’ll show you the Forward and Backward Scan.

We all knew that whenever we  create an index (Clustered or Non-Clustered) on a table in SQL Server […]

Read-Ahead Reads in SQL Server

Hi Friends,

There is a mechanism known as Read-Ahead Reads in SQL Server to read the pages in a buffer before getting the request from query. By default, this feature is enabled in the SQL Server. Have you ever tested the performance impact in terms of IO and Execution time? So today I am going […]

Newsletter_Footer