How to check how many processes going on Database server?

                Today I am going to share one of the interesting experience.Few days before I was working on one of the release . We was using one DB server it has almost 20 database in it.Out of 20 databases 15 was in use.We was testing one functionality .Normally for that functionality system was taking 10 - 20 sec.We started testing and for that functionality it was taking 10 to 20 minute.We surprised what happened.Again we tried then it has taken  9-10 minute.Again we tested and found it is taking  time more than 20 minute.This is different behavior .we concluded there is no problem in code because it was giving correct result.Then question is what is problem.After further analysis we found there are other databases in server which are in use.It is taking more server resources.Following query was helped us identify root cause.

SELECT [Spid] = session_Id
      , ecid
      , [Database] = DB_NAME(sp.dbid)
      , [User] = nt_username
      , [Status] = er.status
      , [Wait] = wait_type
      , [Individual Query] = SUBSTRING (qt.text,
             er.statement_start_offset/2,
      (CASE WHEN er.statement_end_offset = -1
             THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
            ELSE er.statement_end_offset END -
                                er.statement_start_offset)/2)
      ,[Parent Query] = qt.text
      , Program = program_name
      , Hostname
      , nt_domain
      , start_time
    FROM sys.dm_exec_requests er
    INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
    WHERE session_Id > 50              -- Ignore system spids.
    AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.
    ORDER BY 1, 2

  Above query will tell us how many process are going on server.

What is temporary procedure in sql server?

    I was working on critical defect in production environment.I was having only read permission on server.
After investigation I realized I need to do code change in procedure suddenly I remember it is production server I don't have permission to create and drop object.I decided I will test new code in procedure only without any impact on production server.It sounds something magical but there is no magic .I achieved all this with concept of temporary procedure without touching production data.There is limitation as we are having only read permission We can't do DML operation on main table.So I achieved it by creating temp table.
Final output I checked with this dummy table .Interesting thing is  code worked got desired output.Let us discuss in detail
       Temporary Stored Procedures are similar to normal Stored Procedures, but as their name suggests, have a fleeting existence. There are two kinds of temporary Stored Procedures, local and global. Temporary Stored Procedures are created just like any other SP but the name must be prefixed with a hash (#) for a local temporary SP and two hashes (##) for a global temporary Stored Procedure.

A local temporary Stored Procedure is available only in the current session and is dropped when the session is closed or for a different session.

 Please comment if this information is useful.
 

What is table variable?

Table Variable

This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory. This also allows you to create primary key, identity at the time of Table variable declaration but not non-clustered index.

 GO
 DECLARE @TProduct TABLE
 (
 SNo INT IDENTITY(1,1),
 ProductID INT,
 Qty INT
 ) 
 --Insert data to Table variable @Product 
 INSERT INTO @TProduct(ProductID,Qty)
 SELECT DISTINCT ProductID, Qty FROM ProductsSales ORDER BY ProductID ASC 
 --Select data
 Select * from @TProduct
 
 --Next batch
 GO
 Select * from @TProduct --gives error in next batch

What is CTE and Recurssive CTE?

 CTE   (Common table expression)

            CTE stands for Common Table expressions. It was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result of complex sub-query. Unlike temporary table its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with semicolon.

SELECT *
FROM   (SELECT Addr.Address,
               Emp.Name,
               Emp.Age
        FROM   Address Addr
               INNER JOIN Employee Emp
                       ON Emp.EID = Addr.EID) Temp
WHERE  Temp.Age > 50
ORDER  BY Temp.NAME

By using CTE above query can be re-written as follows :

 ;WITH CTE1(Address, Name, Age)--Column names for CTE, which are optional

     AS (SELECT Addr.Address,

                Emp.Name,

                Emp.Age

         FROM   Address Addr

                INNER JOIN EMP Emp

                        ON Emp.EID = Addr.EID)

SELECT *

FROM   CTE1 --Using CTE

WHERE  CTE1.Age > 50

ORDER  BY CTE1.NAME

Recursive CTE  :

  Recursive is the process in which the query executes itself. It is used to get results based on the output of base query. We can use CTE as Recursive CTE (Common Table Expression).

 

Consider following query

 ;WITH CTE1(Address, Name, Age)--Column names for CTE, which are optional

     AS (SELECT Addr.Address,

                Emp.Name,

                Emp.Age

         FROM   Address Addr

                INNER JOIN EMP Emp

                        ON Emp.EID = Addr.EID)

SELECT *

FROM   CTE1 --Using CTE

WHERE  CTE1.Age > 50

ORDER  BY CTE1.NAME

 In the above example emp_cte is a common expression table, the base record for the cte is derived by the first sql query before union all. The result of the query gives you the employeeid which don’t have managerid. Second query after union all is executed repeatedly to get results and it will continue until it returns no rows. for above e.g. result will have employeeids which have managerid (ie, employeeid of the first result).  this is obtained by joining cte result with employee table on columns employeeid of cte with managerid of table employee.

this process is recursive and will continue till there is no managerid who doesn’t have employeeid

What is temporary table?

       In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside Tempdb database. Based on the scope and behavior temporary tables are of two types as given below-

Local Temp Table :

1. table name is stared with single hash ("#") sign.

   
   CREATE TABLE #Local
   (
    UserID int,
    Name varchar(50), 
    Address varchar(150)
   )
   GO
   insert into #Local values ( 1, 'Shailendra','Noida');
   GO
   Select * from #Local
   
   

   
   The scope of Local temp table exist to the current session of current user means to the current query window. If you will close the current query window or open a new query window and will try to find above created temp table, it will give you the error.

2. Global Temp Table

   Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash ("##") sign.

   
   CREATE TABLE ##Global 
   (
    UserID int,
    Name varchar(50), 
    Address varchar(150)
   )
   GO
   insert into ##Global values ( 1, 'Shailendra','Noida');
   GO
   Select * from ##Global 
    
   
   

   
   Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.

Performance of TempDB

                      Recently I was facing issue related with performance.Same operation we was performing on one server,it was taking more time to complete as compare to other server.After dig  analysis I found root cause which was really interesting .I would like to share here on my blog .
                  Root cause was files mapping for tempdb .On new server we found tempdb is mapped to only one file .Old system tempdb is mapped to more than one file.I will explain how I got the information.
Please refer following query

SELECT name AS FileName,
       SIZE*1.0/128 AS FileSizeinMB,
            CASE max_size
                WHEN 0 THEN 'Autogrowth is off.'
                WHEN -1 THEN 'Autogrowth is on.'
                ELSE 'Log file will grow to a maximum size of 2 TB.'
            END Autogrowth,
            growth AS 'GrowthValue',
            'GrowthIncrement' = CASE
                                    WHEN growth = 0 THEN 'Size is fixed and will not grow.'
                                    WHEN growth > 0
                                         AND is_percent_growth = 0 THEN 'Growth value is in 8-KB pages.'
                                    ELSE 'Growth value is a percentage.'
                                END
FROM tempdb.sys.database_files;

These query is very important to understand -
tempdb.sys.database_files : will give us list of file which are mapped to tempdb.

So the solution which I used to improve performance - We have added more file to tempdb
e.g.
         Important thing  data file (.mdf)    and log file  (.ldf)  should not be present on same disk.It should be present on different disk also auto increment  option for size should be on.

ALTER DATABASE tempdb
ADD FILE (NAME = tempdev2, FILENAME = 'W:\tempdb2.mdf', SIZE = 256);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev3, FILENAME = 'X:\tempdb3.mdf', SIZE = 256);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev4, FILENAME = 'Y:\tempdb4.mdf', SIZE = 256);

For imformation :
  What exactly tempdb stores?

• Global (##temp) or local (#temp) temporary tables, temporary table indexes, temporary stored procedures, table variables, tables returned in table-valued functions or cursors.
• Database Engine objects to complete a query such as work tables to store intermediate results for spools or sorting from particular GROUP BY, ORDER BY, or UNION queries.
• Row versioning values for online index processes, Multiple Active Result Sets (MARS) sessions, AFTER triggers and index operations (SORT_IN_TEMPDB).
• DBCC CHECKDB work tables.
• Large object (varchar(max), nvarchar(max), varbinary(max) text, ntext, image, xml) data type variables and parameters.

Understanding missing index in SQL server

   While analyzing deadlock , I was  reviewing the query execution plan and querying index related dynamic management views (DMVs), I noticed the problem is related with potential missing indexes on columns. The index related dynamic management views (DMVs).
I am interested to share that information on my blog
 I queried are as follow:

sys.dm_db_missing_index_details — Returns detailed information about missing indexes, including the table, columns used in equality operations, columns used in inequality operations, and columns used in include operations.

sys.dm_db_missing_index_group_stats — Returns information about groups of missing indexes, which SQL Server updates with each query execution (not based on query compilation or recompilation).

sys.dm_db_missing_index_groups — Returns information about missing indexes contained in a missing index group.
Using these dynamic management views (DMVs), I wrote the following query, which returns the list of possible missing indexes for all SQL Server user databases. The results are ordered by index advantage that helps you to identify how beneficial each index would be, if we create them on the table.

SELECT CAST(SERVERPROPERTY('ServerName') AS [nvarchar](256)) AS [SQLServer]
,db.[database_id] AS [DatabaseID]
,db.[name] AS [DatabaseName]
,id.[object_id] AS [ObjectID]
,id.[statement] AS [FullyQualifiedObjectName]
,id.[equality_columns] AS [EqualityColumns]
,id.[inequality_columns] AS [InEqualityColumns]
,id.[included_columns] AS [IncludedColumns]
,gs.[unique_compiles] AS [UniqueCompiles]
,gs.[user_seeks] AS [UserSeeks]
,gs.[user_scans] AS [UserScans]
,gs.[last_user_seek] AS [LastUserSeekTime]
,gs.[last_user_scan] AS [LastUserScanTime]
,gs.[avg_total_user_cost] AS [AvgTotalUserCost]
,gs.[avg_user_impact] AS [AvgUserImpact]
,gs.[system_seeks] AS [SystemSeeks]
,gs.[system_scans] AS [SystemScans]
,gs.[last_system_seek] AS [LastSystemSeekTime]
,gs.[last_system_scan] AS [LastSystemScanTime]
,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]
,gs.[avg_system_impact] AS [AvgSystemImpact]
,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
,'CREATE INDEX [Missing_IXNC_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
WHEN id.[equality_columns] IS NOT NULL
AND id.[inequality_columns] IS NOT NULL
THEN '_'
ELSE ''
END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
WHEN id.[equality_columns] IS NOT NULL
AND id.[inequality_columns] IS NOT NULL
THEN ','
ELSE ''
END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]
,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK)
ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK)
ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db WITH (NOLOCK)
ON db.[database_id] = id.[database_id]
WHERE id.[database_id] > 4 -- Remove this to see for entire instance
ORDER BY [IndexAdvantage] DESC
OPTION (RECOMPILE);

Obviously these missing indexes are the ones that the SQL Server optimizer identified during query compilation, and these missing index recommendations are specific recommendation targeting a specific query.  Consider submitting your workload and the proposed index to the Database Tuning Advisor for further evaluation that include partitioning, choice of clustered versus non-clustered index, and so on.

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.