What is Parameter Sniffing?

                  If a SQL query has parameters, SQL Server creates an execution plan tailored to them to improve performance, via a process called 'parameter sniffing'. This plan is stored and reused since it is usually the best execution plan. Just occasionally, it isn't, and you can then hit performance problems.
                 SQL  Server tries to optimize the execution of your stored procedures by creating compiled execution plans.  An execution plan for a stored procedure is created the first time a stored procedure is executed.  When the SQL Server database engine compiles a stored procedure it looks at the parameter values being passed and creates an execution plan based on these parameters.  The process of looking at parameter values when compiling a stored procedure is commonly called “parameter sniffing”.  Parameter sniffing can lead to inefficient execution plans sometimes; especially          when a stored procedure is called with parameter values that have different cardinality.    

          Parameter sniffing is the process whereby  SQL Server creates an optimal plan for a stored procedure by using the calling parameters that are passed the first time a stored procedure is executed.  By “first time”, I really mean whenever SQL Server is forced  to compile or recompile  a stored procedures because it is not in the procedure cache. Every subsequent call to the same store procedure with the same parameters will also get an optimal plan, whereas calls with different parameter values may not always get an optimal plan.   

Not all execution plans are created equal.  Execution plans are optimized based what they need to do.  The SQL Server engine looks at a query and determines the optimal strategy for execution.  It looks at what the query is doing, uses the parameter values to look at the statistics, does some calculations and eventually decides on what steps are required to resolve the query.  This is a simplified explanation of how an execution plan is created.  The important point for us is that those parameters passed are used to determine how SQL Server will process the query.   An optimal execution plan for one set of parameters might be an index scan operation, whereas another set of parameters might be better resolved using an index seek operation. 

Use a Derived Table in Place of IN Predicate With Aggregate Functions

              Using a derived table in place of the IN predicate when we are aggregating data allows us to only have to process certain table records once therefore reducing the amount of resources required to execute a query.When we use the IN predicate we first have to process the data in our subquery then we are processing a lot of the same data again (depending on the WHERE clause) in our main query. If we can use a derived table to do most of the work we can avoid the double processing of data. Before we take a look at an example to illustrate this point we'll need to add an index to our Parent table so the results are not skewed by having to do a table scan.

e.g
Let's look at a query that uses the IN predicate to return the second largest value from a table. One way to do this would be as follows.

1.SELECT MIN(IntDataColumn)

  FROM [dbo].[Parent]
 WHERE ParentID IN (SELECT TOP 2 ParentID 
                      FROM [dbo].[Parent] 
                    ORDER BY IntDataColumn DESC)

2.SELECT MIN(IntDataColumn) 

  FROM (SELECT TOP 2 IntDataColumn 
          FROM [dbo].[Parent] 
        ORDER BY IntDataColumn DESC) AS A

SELECT * vs SELECT 1 with EXISTS clause

     There is one essential difference between the use of SELECT  * and SELECT 1.  SELECT * will expand the column list and then throw what isn’t needed out.  Now, don’t take, “throw what isn’t needed out” literally.  The compilation of the query will simply determine which columns are relevant and to be used.   With SELECT 1, this step isn’t performed during compilation..
It’s important to note that compilation is where the effects of this occur.  The runtime versions of these different methods will be used functionally, the same with the same performance.

Generally we will not observe more difference between them.Using select 1 is best practice instead of select

e.g.

if exists (select * from test )
print 'test passed'
go
if exists (select 1 from test )
print 'test passed'

will Join Order Can Affect the Query Plan ?

     Yes.  The order in which the tables in  queries are joined it will have a dramatic effect on how the query performs. If your query happens to join all the large tables first and then joins to a smaller table later this can cause a lot of unnecessary processing by the SQL engine.
     Generally speaking the SQL Server Optimizer will try to first join the tables that allows it to work with the smallest result set possible. To do this the optimizer will try to figure out which join order provides the smallest result set early in the processing but in very complex queries it does not try all possible combinations as this can become quite resource intensive. As a best practice you should try to order your table join so the join that reduces the result set the most is joined first.