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. 

why MAXDOP table hint is important?Is it required to know?

           When SQL Server runs on a computer with more than one processor or CPU, it detects the best degree of parallelism, that is the number of processors employed to run a single statement, for each query that has a parallel execution plan. You can use the max degree of parallelism option to limit the number of processors to use for parallel plan execution and to prevent run-away queries from impacting SQL Server performance by using all available CPUs.  
             The MAXDOP hint is used to implicitly specify the maximum degree of parallelism to use for a specific statement.  The degree of parallelism is roughly defined as how many separate processors to utilize for a single query.  

SELECT *
FROM dbo.test_demo 
OPTION (MAXDOP 1)
This will force only one instance of the SPID to be spawned. To allow more instances of a SPID to be spawned replace the 1 with the maximum number.

This needs to be specified after each statement. It is not specific to the statement.

 what is default value for maxdop?
The default value for MAXDOP is 0 (zero) and can be set or viewed using (sp_configure). A value of 0 means that SQL Server will use all processors if a query runs in parallel.
We can change this value based on our requirment.
sp_configure 'max degree of parallelism' 5.
 This means if a query uses a parallel execution plan it will only use four of the available processors.

What is Dynamic management view?

 No one likes problem .DMV helps to understand the problem before it occurs.Really it helps

to quick discover the slowest SQL queries on SQL servers.It helps to get details of missing

indexes that could significantly improve the performance of queries. All these things and more

are easily possible, typically in a matter of seconds, using DMVs.

  DMVs are views on internal SQL Server metadata, which can be used to significantly improve

the performance of SQL queries, often by an order of magnitude.Fixing any problem is knowing

what the underlying problem is. DMVs can give precisely this information. DMVs will pinpoint

where many of problems are, often before they become painfully apparent.DMV are existing from

SQL Server 2005.After executing sql queries on a SQL Server database, SQL Server automatically

records information about the activity that is taking place, internally into structures in memory, this information can be accessed via DMVs. So DMVs are basically SQL views on some pretty important internal memory structures.DMV information includes metrics that relate to indexes, query execution,  service broker, replication, query notification, objects,input/output (I/O), full-text search, databases, database mirroring, change data capture(CDC),the operating system, common language runtime (CLR), transactions, security, extended events, resource governor and much more. 

     When any query execute  on sql server following information captured 

 1.The query’s cached plan 

 2.What indexes were used

 3.What indexes the query would like to use but are missing

 4.What resources the query waiting upon

 5.How much IO occurred (both physical and logical)

 6.How much time was spent actually executing the query

 7.How much time was spent waiting on other resources.

    In addition to DMVs, there are several related functions that work in conjunction with DMVs, called Dynamic Management Functions (DMFs). In many ways DMFs are similar to standard SQL

functions, being called repeatedly with a DMV supplied parameter.DMVs and DMFs have been an integral part of SQL Server since version 2005 onwards. In SQL Server 2005 there are 89 DMVs (and DMFs), and in SQL Server 2008 there are 136 DMVs.  It is possible to discover the range of these DMVs by examining their names, by using the following query:

SELECT name, type_desc FROM sys.system_objects WHERE name LIKE 'dm_%' ORDER BY name

    DMVs can solve  problem including  Diagnosing , Performance Tuning, and Monitoring.

It is possible to query the DMVs to diagnose many common problems including slowest queries, the commonest causes of waiting/blocking, unused indexes, files having the most I/O, and lowest re-use of cached plans.

Data Mismatch on WHERE Clause by functions : will cause performance issue?

Yes.It will cause performance issue.
   Any function such as collate, convert, substring etc. applied on a column might make impossible for SQL Server to use any index on this column. This because SQL Server query optimizer considers the column after the function as a new column, because the column values are not stored in the index pages as returned by the function. For these reasons, it is not possible to use indexes on these columns.

This is of course is not only for WHERE clause columns. Join, order, group by or having clause may have same problem. However functions in the select list does not affect index selection.

Some common problematic functions are :

COLLATE
CONVERT
SUBSTRING
LEFT
LTRIM
RTRIM
User defined functions.

Data Mismatch on WHERE Clause : will cause performance issue?

Yes.Mismatch in data type on where clause will cause performance problem.
                   Any datatype mismatch on WHERE clause might cause serious performance problems.Predicates on both sides of comparisons (for example on WHERE clause) always must match datatypes. It means that if the left side of predicate is integer than the right side needs to be integer. If the datatypes are different then SQL Server tries to make and implicit conversion to match the datatypes of both sides like below example.

If an implicit conversion is not possible SQL Server returns an error like below.

When an implicit conversion is possible, SQL Server automatically converts the data from one data type to another. Selection of the conversion direction depends on data loss possibility. SQL server choose the side which is to avoid data lost. For example, when a smallint is compared to an int, the smallint is implicitly converted to int before the comparison proceeds.

how to write case statement if column exist then return same column value other wise return null?

--Creating work table test11

if exists(select * from sys.tables where name='test11')
drop table  test11
go

 CREATE TABLE test11
  (
     ID INT
  )

go
--Creating function

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'fnColumnname')
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION fnColumnname
GO
CREATE FUNCTION fnColumnname(@Table_Name  NVARCHAR(100),
                          @Column_Name NVARCHAR(100))
returns VARCHAR(200)
AS
  BEGIN
      IF EXISTS (SELECT*
                 FROM   sys.columns
                 WHERE  Object_name(object_id) = @Table_Name
                        AND name = @Column_Name)
        BEGIN
            RETURN
              (SELECT @Table_Name + '.' + @Column_Name)
        END

      RETURN 'NULL'
  END
 go

  --Creating table for columns

 IF OBJECT_ID('tempdb..#Column_exists') IS NOT NULL
  DROP TABLE #Column_exists
go
SELECT name
INTO   #Column_exists
FROM   sys.columns
WHERE  Object_name(object_id) = 'test11'
go

 INSERT INTO test11
VALUES      (1)
go

Declare @lclsql varchar(max)
SET @lclsql='select  case  when (select count(*) from #Column_exists where name=''ID'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'ID')
       + ' end  ID ,case  when (select count(*) from #Column_exists where name=''NAME'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'NAME')
       + ' end  NAME,case  when (select count(*) from #Column_exists where name=''SAL'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'SAL')
       + ' end  SAL,case  when (select count(*) from #Column_exists where name=''DEPTNO'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'DEPTNO')
       + ' end  DEPTNO' + ' from test11;'

 exec(@lclsql)
--Result:
-- Now we have only one field and so other field will be NULL  
     
  go

 --Adding Name column
 alter table test11
 add  NAME varchar(100)

go

 IF OBJECT_ID('tempdb..#Column_exists') IS NOT NULL
  DROP TABLE #Column_exists

go

SELECT name
INTO   #Column_exists
FROM   sys.columns
WHERE  Object_name(object_id) = 'test11'
go

 INSERT INTO test11
VALUES      (1,'Shrikant')

go

Declare @lclsql varchar(max)
SET @lclsql='select  case  when (select count(*) from #Column_exists where name=''ID'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'ID')
       + ' end  ID ,case  when (select count(*) from #Column_exists where name=''NAME'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'NAME')
       + ' end  NAME,case  when (select count(*) from #Column_exists where name=''SAL'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'SAL')
       + ' end  SAL,case  when (select count(*) from #Column_exists where name=''DEPTNO'' )=0 then ''Null''  else '
       + dbo.fnColumnname('test11', 'DEPTNO')
       + ' end  DEPTNO' + ' from test11;'


exec( @lclsql)

go

What are different type sql server backup?

There are three sql server backup method .
1.Full backup.
                           A full backup contains all the data in a specific database or set of file groups or files, and also enough log to allow for recovering that data. It is the base of both differential backup and transaction log backup.
2.Differential backup.
                        A differential backup is not independent and it must be based on the latest full backup of the data. That means there should have a full backup as a base. A differential backup contains only the data that has changed since the differential base. Typically, differential backups are smaller and faster to create than the base of a full backup and also require less disk space to store backup images.Therefore, using differential backups can save available space and speed up the process of making frequent backups to decrease the risk of data loss. At restore time, the full backup is restored first, followed by the most recent differential backup.
3.Transaction Log Backups (Full and Bulk-Logged Recovery Models Only).     
                                The transaction log is a serial record of all the transactions that have been performed against the database since the transaction log was last backed up. With transaction log backups, you can recover the database to a specific point in time (for example, prior to entering unwanted data), or to the point of failure.The transaction log backups are only valuable under the full recovery model or bulk-logged recovery model. Each log backup covers the part of the transaction log that was active when the backup was created, and it includes all log records that were not backed up in a previous log backup. An uninterrupted sequence of log backups contains the complete log chain of the database, which is said to be unbroken. Under the full recovery model, and sometimes under the bulk-logged recovery model, an unbroken log chain lets you to restore the database to any point in time.Just like differential backup, transaction log backup is also based on full backup. Therefore, before you can create the first log backup, you must create a full backup, such as a database backup. Because it requires less disk space than full backup, you can create them more frequently than database backups.

SQL Statement Processing in SQL server

A SELECT statement is nonprocedural; it does not state the exact steps that the database server should use to retrieve the requested data. This means that the database server must analyze the statement to determine the most efficient way to extract the requested data. This is referred to as optimizing the SELECT statement. The component that does this is called the query optimizer. The input to the optimizer consists of the query, the database schema (table and index definitions), and the database statistics. The output of the optimizer is a query execution plan, sometimes referred to as a query plan or just a plan. The contents of a query plan are described in more detail later in this topic.

The inputs and outputs of the query optimizer during optimization of a single SELECT statement are illustrated in the following diagram:

A SELECT statement defines only the following:

• The format of the result set. This is specified mostly in the select list. However, other clauses such as ORDER BY and GROUP BY also affect the final form of the result set.
• The tables that contain the source data. This is specified in the FROM clause.
• How the tables are logically related for the purposes of the SELECT statement. This is defined in the join specifications, which may appear in the WHERE clause or in an ON clause following FROM.
• The conditions that the rows in the source tables must satisfy to qualify for the SELECT statement. These are specified in the WHERE and HAVING clauses.

                The process of selecting one execution plan from potentially many possible plans is referred to as optimization. The query optimizer is one of the most important components of a SQL database system. While some overhead is used by the query optimizer to analyze the query and select a plan, this overhead is typically saved several-fold when the query optimizer picks an efficient execution plan. 

              The SQL Server query optimizer is a cost-based optimizer. Each possible execution plan has an associated cost in terms of the amount of computing resources used. The query optimizer must analyze the possible plans and choose the one with the lowest estimated cost. Some complex SELECT statements have thousands of possible execution plans. In these cases, the query optimizer does not analyze all possible combinations. Instead, it uses complex algorithms to find an execution plan that has a cost reasonably close to the minimum possible cost.

The SQL Server query optimizer does not choose only the execution plan with the lowest resource cost; it chooses the plan that returns results to the user with a reasonable cost in resources and that returns the results the fastest. For example, processing a query in parallel typically uses more resources than processing it serially, but completes the query faster. The SQL Server optimizer will use a parallel execution plan to return results if the load on the server will not be adversely affected.