Log in | Register

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

Performance Tuning


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

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 – Database Scoped Configurations

Hi Friends,

As a DBA, we know about various Instance level settings in SQL Server like MAXDOP, Cardinality Estimator related changes and various Trace Flags etc. There may be some scenarios where you want to enable these kind of settings at the database level rather than instance level. For Example, I want to set MAXDOP […]

DMV in SQL Server 2016 CTP – sys.dm_exec_session_wait_stats

Hi Friends,

Wait stats, I hope all of us who are working with SQL Server are aware about this thing. SQL Server DBA generally uses sys.dm_os_wait_stats DMV in SQL Server to collect the data about waits. This collection is very useful for troubleshooting of various performance problems. This DMV provides the cumulative information of wait […]

The correct cardinality estimation using table variable

Hi Friends,

In my previous blog post, we have seen that SQL Sever estimates number of rows in a table variable was 1. Now the question is, Is there any way to make the correct estimation for the number of rows while using table variable?

My today’s blog post is focused on the correct cardinality estimation […]

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

Nested Loop Join Internals

Hi Friends,

Today, I am going to show you Nested Loop Join Internals in terms of performance.

Generally, we write a query in SSMS and then submit it to SQL Server for execution. In the query, there may be join conditions like an inner join, outer join, self join, etc. all these are the logical joins […]

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

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

Introduction To Query Store In SQL Server 2016 – Part 1

Hi friends, in next few blogs we will explore another new feature named Query Store which

gets introduced in SQL Server 2016 CTP 2.0. This is a very useful feature for the DBA and developers from the performance point of view.

Query store feature can now give to answer of question like:

My query was running properly […]