What is Phantom read?

           Phantom means unexpected or unrealistic. It occurs basically when two identical queries are executed, and the set of rows returned by the second query is different from the first. Let’s have a simple example; suppose your banking policy got changed and according to that the minimum balance should be 150$ instead of 100$ for each account type, anyways this is not a big deal for a data base administrator. He will perform an update statement for each account type where the minimum balance is less than 150$ and updates the value to 150$. But unfortunately when the manager checks the database, he got one record with minimum balance less than 150$ in the same table. The DBA got surprised, how come this is possible as he performed the update statement on the whole table.

This is called Phantom read. The occurrence of Phantom reads are very rare as it needs proper circumstances and timing for such type of events as in the above example, someone may have inserted one new record with the minimum balance less than 150$ at the very same time when the DBA executed the UPDATE statement. And as it is a new record, it didn’t interfere with the UPDATE transaction and executed successfully. This type of Phantom reads can be avoided using higher level of isolation i.e. SERIALIZABLE .

What is a Dirty Read?

              A dirty read takes no notice of any lock taken by another process. The read is officially “dirty” when it reads data that is uncommitted. This can become problematic if the uncommitted transaction fails or for some other reason is rolled back.

              Imagine a scenario in which you are shopping on a website and place an item into your basket and proceed to payment. The site's checkout process decrements the stock by one and starts to charge your card all in the one transaction. At that time, a second unrelated process starts. The website's back office stock interface runs and makes a dirty read of all the product inventory levels, reading the reduced value. Unfortunately, there is a problem with your transaction (insufficient funds), and your purchase transaction is rolled back. The website stock level has now reverted to the original level, but the stock interface has just reported a different value.

Let see following example to understand in detail:

 /* SESSION 1 */

 
BEGIN TRANSACTION;
UPDATE Person.Person
SET FirstName = 'James'
WHERE LastName = 'Jones';
WAITFOR DELAY '00:00:05.000';
ROLLBACK TRANSACTION;
SELECT FirstName
,LastName
FROM Person.Person
WHERE LastName = 'Jones';

 /* SESSION 2 */

 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
SELECT FirstName ,LastName 
FROM Person.Person 
WHERE LastName = 'Jones';

Once Session 1 is running, quickly switch over to a second session and execute the following SQL statement. The SQL in this second session will perform a dirty read. If you time it right and execute this query while the transaction in Session 1 is open (it has not yet been rolled back), then your output will match Figure 1 and every person with a surname of “Jones” now has a first name of “James”:

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.