Tag : query design

The join order has been enforced because a local join hint is used – Warning in SQL Server 3

When we use a local JOIN hint in T-SQL query, sometimes, SQL Server throws a warning message as “Warning: The join order has been enforced because a local join hint is used.“. For example, below is a query with a local join hint:

SELECT * FROM Person.Person A 
INNER MERGE JOIN HumanResources.Employee B 
ON A.BusinessEntityID = B.BusinessEntityID

After execution, we will get below warning message with result set:

Warning Message

Warning Message

In above code, we are using a local JOIN hint “INNER MERGE JOIN” which forces this query to override the physical join (which could be nested loop or merge or hash join) chosen by SQL Server with the physical join given in the query hint (which is MERGE join in this case). If you want to read more about the physical joins, visit my post “Nested loop, Merge, and Hash Joins in SQL Server“.… More

Query Performance Tuning – General Guidelines

Query performance tuning – Introduction

Query performance tuning is a process of improving system performance to make it faster and more scalable. It is a creative and iterative process which has a clear and well-defined objective. To achieve this goal, we have to follow certain defined steps.

Why we need this

Enterprise data changes frequently over time, which may impact the performance of existing queries. In most of the cases, a slight change in the index or in the query can improve the performance of the query significantly. In such cases, the improvement in performance can be much better than a CPU or memory upgrade.

How to tune a query

Performance tuning process has certain steps to follow in a defined way to achieve the clearly defined objective.

Query Performace Tuning - General Guidelines - Flow chart

Query performance tuning

In the above flow chart, you see that we begin by setting objective, followed by getting the current performance … More