Performance tuning needs concentration in the following areas:
Deciding the performance baseline of your environment
Monitoring current performance and finding bottleneck
Resolving the bottleneck to get good performance
An Estimated Execution Plan is a kind of blue-print that defines how a query should actually perform, whereas an Actual Execution Plan is like a mirror that tells you what happened while executing the query. By looking at this fact you can find the bottleneck and try to resolve it. By comparing both execution plans, you can find out whether the query is actually performed as per the blue-print (Estimated Execution Plan) or not.
There are some important parts (operator) that we should refer to in the execution plan in the order to understand it and to find the pain point. Some of the very important operators in execution plan, which we are going to cover in this chapter, are as follows:
Join strategies: There are three physical join operators in SQL Server 2012, which...