Monday, 14 July 2014

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'

Wednesday, 2 July 2014

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. 

Friday, 2 May 2014

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.


Sunday, 13 April 2014

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.

Wednesday, 2 April 2014

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.