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

Query Tuning

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

Explore statistics histogram using SQL Server 2017

Prior to SQL Server 2016 SP1 CU2, we need to use DBCC SHOW_STATISTICS WITH HISTOGRAM to read Statistics histogram of a specific statistics. Since it is a DBCC command in order to filter any information or if you want to join with another table, we first need to save the out to a table.

Below […]

By |October 7th, 2017|Categories: Performance Tuning, Programming, Query Tuning, SQL Server, Transact-SQL|Tags: , , , , , |Comments Off on Explore statistics histogram using SQL Server 2017

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

Column order and equality operator on Index

Does Column order  in where clause matters?

Whenever I have any doubt, I always play around with it to clarify my doubt. While doing experiment with index operation, I learnt something and here I’m demonstrating what I learnt?

Let’s do some experiment,

In below experiment I’m using the AdevntureWorks database, which can be downloadable from CodePlex.

Demo – 1 […]

By |September 10th, 2016|Categories: Indexes & Statistics, Query Tuning, SQL Server|Tags: , , , , |Comments Off on Column order and equality operator on Index

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

By |August 24th, 2016|Categories: Performance, Performance Tuning, Query Tuning, SQL Server||Comments Off on How to resolve High Compilations/Second problem in SQL Server

SQL Server Index Hint

SQL Server query optimizer decides which index to use, when a query is requested. SQL Server optimizer is a cost based optimizer so it picks the index which has low cost based on performance. When we design an index for a query, optimizer will utilize the defined index to generate the plan for requested […]

By |July 2nd, 2016|Categories: Performance Tuning, Query Tuning|Tags: , , |Comments Off on SQL Server Index Hint

Clustered Index seek doesn’t guarantee optimal Query plan

I was working on removing duplicate indexes from a table. Before removing an index usually I check whether that index is used by any query or not, is there any index hint is used, looking at index usage statistics and many other factors.

If you search in web you will get many ways to identify […]

By |June 16th, 2016|Categories: Indexes & Statistics, Performance Tuning, Programming, Query Tuning, SQL Server|Tags: , , , |Comments Off on Clustered Index seek doesn’t guarantee optimal Query plan

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

By |May 28th, 2016|Categories: Database Administration, Database Development, Performance, Performance Tuning, Query Tuning|Tags: , , |Comments Off on Reserved threads and Used threads in parallelism

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

By |March 7th, 2016|Categories: Database Administration, Database Development, Database Engine, Indexes & Statistics, Performance, Query Tuning|Tags: , |Comments Off on The correct cardinality estimation for table variable using trace flag 2453

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

Newsletter_Footer