Log in | Register

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

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

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

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

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

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

SQL Server 2016 CTP2 Query Data Store – Execution Plan Change Tracking

Hi Friends,

In my previous blog post, I had explained about SQL Server 2016 CTP2 Query Data Store feature Importance as well as how to enable it for our database.

Today I’ll show you the one important use of this feature. Tracking execution plans changes for a query execution. Query Data Stores the Execution plan changes […]

SQL Server 2016 CTP2 Query Data Store

Hi Friends,

There may be the situations when you got an email about a query which was running fine till yesterday but today it is running very slow. First question which come to our mind is why it was running fine earlier but now running slow. There may be various things you will check to […]

SQL Server 2016 CTP2 Live Query Statistics

Hi Friends,

This is my first Blog post on SQL Server 2016 CTP2 and 100th on SQLServerGeeks.com. Yes, I have completed my first century of blogs with this blog post.

Today, I am going to show you a new feature of SQL Server 2016 CTP2 Live Query Statistics. Till SQL Server 2014, we have seen the Execution […]

Newsletter_Footer