Change Tracking May Leads to Blocking and Dead Locks

Changing Tracking: May not be a viable solution on OLTP databases.

CT (change tracking) primarily used for Synchronizing the data.

As per Books Online: It tracks what rows are changed. The latest data can be obtained directly from the table that is being tracked. It’s a light weight method to track the changes, unlike triggers which needs programming with additional tables and cleanup process."

But it’s not light weight in terms of the problems raised with blocking.

Change tracking itself creates few internal tables without having any control over them to maintain or troubleshoot during the issues raised with performance.

In this scenario, we wants to synchronize the tables.

Steps involved in synchronize 2 tables using change tracking are

1.     Populate the Target table with initial full set of data from the source table

2.     Identify the changes on the source tables using change tracking

3.     Synchronize both the tables with the changes (insert,update,delete) happens on the source table to the destination table

There are many methods available viz..Triggers, Time Stamp Tracking, Log shipping, Replication, Mirroring (with snapshot) etc. and each method has its own limitations

But if the solution has using change tracking then beware of the problem with deadlocks.

"A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock.

Image: Dead Lock Trace Captured from Profiler

Steps:

1.     Create a Test Database

2.     Create a test table with test data

3.     Enable Change Tracking on database

4.     Enable Change Tracking on Table

5.     Run Update Statements

6.     Catch Deadlocks

7.     Conclusion

8.     Alternate solutions

1.     Create a Test Database

Create database TestDB Go Use TestDB Go

2.     Create a test table with test data

USE TestDB GO create table [Customer](     IID INT IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED, [CustomerID] [int] NOT NULL , [PersonID] [int] NULL, [StoreID] [int] NULL, [TerritoryID] [int] NULL, [AccountNumber]  varchar(30), [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, [ModifiedDate] [datetime] NOT NULL) GO insert into [Customer] SELECT * FROM AdventureWorks2008.Sales.Customer

3.     Enable Change Tracking on database

ALTER DATABASE TESTDB SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);

4.     Enable Change Tracking on Table

USE TestDB GO ALTER TABLE customer  enable CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = Off);

5.      

6.     Run Update Statements

-- open a new session(1) and run the below statement while 1=1 BEGIN insert into [Customer] SELECT * FROM AdventureWorks2008.Sales.Customer END; -- open a 2nd new session(2) and run the below statement while 1=1 BEGIN     update Customer set PersonID =customerid END;

7.     Catch Deadlocks

select XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as DeadlockGraph FROM (select CAST(target_data as xml) as TargetData from sys.dm_xe_session_targets st join sys.dm_xe_sessions s on s.address = st.event_session_address where name = 'system_health' and target_name = 'ring_buffer') AS Data CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent) where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

8.     Conclusion:

make sure before implementing change tracking on the tables with high volume of concurrent insert and updates. Change tracking creates few internal tables which are the main sources of causing these deadlocks.
During every modification on source table , it creates one record in the internal change tracking table. So , If Transaction A inserting records every 1 second and at the same time Transaction B is updating the records than the concurrent blocking will be placed on the Internal tables between these 2 transactions which leads to deadlocks.

Choose this option wisely or write your own custom code using other features where you can take full control.
The major challenge involved in synchronizing the tables are identifying the modifications (updates) than the inserts/deletes as inserts/deletes can be identified by using an anchor table by comparing the primary keys in any data warehouse environment.

Major challenge here is identify the modifications, we have other options mentioned below to identify the modified rows where each has its own limitations. Choose your options wisely

i)       Binary checksum

ii)      Timestamp

iii)     Non-key attributes with in the table to keep track of the changes by the application like dt_modified

iv)    Triggers

v)     CDC – change data capture

vi)    CT – change tracking

vii)   T-Replication

viii)  3^rd party tools

Also refer the article written by Brentozar at the below link. His article pretty much covered about change tracking.

http://www.brentozar.com/archive/2014/06/performance-tuning-sql-server-change-tracking/

sql server partition table online from non partition table

step by step process to partition an existing table to a partitioned table



steps :

1. create table orders
2. populate test data with past 12 months to current date
3. lock the table till the switch happens , create partition function
4. create partition scheme
5. create partition table
6. create check constraints , check constraints are needed to switch the table to new partition
7. switch the existing table to temp table and rename the temp to newly
   partitioned table and finally drop the existing table
8. insert new test data
9. check the rows on partition

------------------------------------------------------- -- Create test database ------------------------------------------------------- create database poc GO use POC ------------------------------------------------------- -- 1 Create the new Orders table. ------------------------------------------------------- CREATE TABLE Orders (iid int identity(1,1),   nage int,   CreatedDate datetime ) GO ------------------------------------------------------- -- 2 populate test data with past 12 months to current date ------------------------------------------------------- -- insert the orders from 2012 december to till this month insert into Orders select 10,'2012-dec-01' insert into Orders select 10,'2013-jan-01' insert into Orders select 10,'2013-jan-15' insert into Orders select 10,'2013-jan-31' insert into Orders select 10,'2013-feb-01' insert into Orders select 10,'2013-feb-15' insert into Orders select 10,'2013-feb-28' insert into Orders select 10,'2013-mar-01' insert into Orders select 10,'2013-mar-15' insert into Orders select 10,'2013-mar-31' insert into Orders select 10,'2013-apr-01' insert into Orders select 10,'2013-apr-15' ------------------------------------------------------- -- 3 create partition function -- create new table with partition. -- create partition starting next month, since you want to archive all the old data after 1 month or so ------------------------------------------------------- BEGIN TRAN select * from orders with (holdlock , tablockx) where 1=0 create partition function PF_MONTHLY_ORDERS (datetime) as RANGE RIGHT for values( '2001-01-01', -- from this date , get the minimum date -1 month '2013-05-01', -- to this date , get maxdate + 1 month to < this date it goes to 1st partition. if the current month is April(4th) '2013-06-01', -- add one month for next 12 months '2013-07-01', '2013-08-01','2013-09-01', '2013-10-01', '2013-11-01', '2013-12-01' ) ------------------------------------------------------- -- 4 create partition scheme ------------------------------------------------------- create PARTITION SCHEME PSCH_MONTHLY_ORDERS  as partition PF_MONTHLY_ORDERS  all to ([primary]) go ------------------------------------------------------- -- 5 create partition table -- ansi setting must be on as mentioned below ------------------------------------------------------- SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING OFF GO CREATE TABLE [Orders_temp] (iid int identity(1,1),   nage int,   CreatedDate datetime ) ON PSCH_MONTHLY_ORDERS(CreatedDate) GO SET ANSI_PADDING OFF GO ------------------------------------------------------- -- 6 create constraints on both the tables -- check constraints are needed to switch the tables , with out this its not possible to move the data ------------------------------------------------------- alter table orders add constraint chkPrtn1 check (CreatedDate >='2001-01-01'  and CreatedDate is not null) alter table orders add constraint chkPrtn2 check (CreatedDate < '2013-05-01' and CreatedDate is not null) alter table orders_temp add constraint chkPrtn3 check (CreatedDate >='2001-01-01'  and CreatedDate is not null) alter table orders_temp add   constraint chkPrtn4 check (CreatedDate < '2020-01-01' and CreatedDate is not null) -------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------- -- 7  switch the data to newly partioned table ------------------------------------------------------- alter table orders switch partition 1 to orders_temp partition 2 alter table orders_temp drop constraint chkPrtn3 alter table orders_temp drop constraint chkPrtn4 exec sp_rename orders ,orders_x exec sp_rename orders_temp ,orders select * from Orders COMMIT TRAN -- !! ROLLBACK OR COMMIT THE TRANSACTION -- !! CHECK AND DROP THE TABLE CpAuditLog_x -------------------------------------------------------------------------------------------------------------------------- -- 9 insert new test data insert into Orders select 10,'2013-apr-30' -- this will goes to 2 partition insert into Orders select 10,'2013-may -30' -- this will goes to 3 partition -------------------------------------------------------------------------------------------------------------------------- --  10 check the rows on partitition SELECT [CreatedDate],         $partition.PF_MONTHLY_ORDERS([CreatedDate])             AS 'Partition Number' FROM Orders SELECT $partition.PF_MONTHLY_ORDERS([CreatedDate])             AS 'Partition Number'     , min([CreatedDate]) AS 'Min Order Date'     , max([CreatedDate]) AS 'Max Order Date'     , count(*) AS 'Rows In Partition' FROM Orders GROUP BY $partition.PF_MONTHLY_ORDERS([CreatedDate]) SELECT sprv.value AS [Value], sprv.boundary_id AS [ID] ,* FROM sys.partition_functions AS spf INNER JOIN sys.partition_range_values sprv ON sprv.function_id=spf.function_id WHERE (spf.name=N'PF_MONTHLY_ORDERS') ORDER BY [ID] ASC ------ full schema query DECLARE @TableName sysname = 'orders'; SELECT p.partition_number, fg.name, p.rows,* FROM sys.partitions p     INNER JOIN sys.allocation_units au     ON au.container_id = p.hobt_id     INNER JOIN sys.filegroups fg     ON fg.data_space_id = au.data_space_id WHERE p.object_id = OBJECT_ID(@TableName) --------------other meta data queries DECLARE @TableName sysname = 'orders'; SELECT distinct p.partition_number, fg.name, p.rows,sprv.value AS [Value] FROM sys.partitions p     INNER JOIN sys.allocation_units au     ON au.container_id = p.hobt_id     INNER JOIN sys.filegroups fg           ON fg.data_space_id = au.data_space_id     INNER JOIN sys.destination_data_spaces dds           ON fg.data_space_id = dds.data_space_id     INNER JOIN sys.partition_schemes ps ON ps.data_space_id = dds.partition_scheme_id     INNER JOIN sys.partition_functions AS spf  ON spf.function_id = ps.function_id     INNER JOIN sys.partition_parameters pp ON pp.function_id=spf.function_id     INNER JOIN sys.partition_range_values sprv ON sprv.function_id=pp.function_id and sprv.parameter_id= pp.parameter_id WHERE p.object_id = OBJECT_ID(@TableName) and (spf.name=N'PF_MONTHLY_ORDERS') SELECT sprv.value AS [Value], sprv.boundary_id AS [ID] ,* FROM sys.partition_functions AS spf INNER JOIN sys.partition_range_values sprv ON sprv.function_id=spf.function_id WHERE (spf.name=N'PF_MONTHLY_ORDERS') ORDER BY [ID] ASC

sql server script to know when the table accessed last time

If the database has lot many unwanted tables which are not being used by any of the applications and if you want to identify the ununused objects , so that you can remove or move those objects to a separate database , then the system view sys.dm_db_index_usage_stats (sql server 2008) contains the information with when the table accessed last time.
the script SQL-1 will produce the list of the tables which are being used and non-used as well. the records in the column [recent_datetime] contains the list of tables which are non been used since the server started.
Note : the counters on this DMV starts / refresh when the server is restarted.

-- Query-1 with cte_recent as ( select SCHEMA_NAME(B.schema_id) +'.'+object_name(b.object_id) as tbl_name, (select MAX(last_user_dt) from (values (last_user_seek),(last_user_scan),(last_user_lookup)) as all_val(last_user_dt)) as access_datetime FROM sys.dm_db_index_usage_stats a right outer join sys.tables b on a.object_id =  b.object_id ) select tbl_name,max(access_datetime) as recent_datetime  from cte_recent group by tbl_name order by recent_datetime desc , 1

output

To keep a track of the tables which are non been used , its better to store the results in a separate table and merge the table before restart the sql server or as per your own schedule as the SQL Server installed on cluster then you probably schedule it as a job to wait for an year before making the decision to remove or move those tables to a different database.
create a table TABLES_LAST_ACCESSED with 2 columns as below script

--Query-2 create table TABLES_LAST_ACCESSED (TBL_NAME VARCHAR(256),recent_datetime DATETIME)

sync the table with the script (Query-3) below

-- Query-3 with cte_recent as ( select SCHEMA_NAME(B.schema_id) +'.'+object_name(b.object_id) as tbl_name, (select MAX(last_user_dt) from (values (last_user_seek),(last_user_scan),(last_user_lookup)) as all_val(last_user_dt)) as access_datetime FROM sys.dm_db_index_usage_stats a right outer join sys.tables b on a.object_id =  b.object_id ) MERGE TABLES_LAST_ACCESSED AS target USING (select tbl_name,max(access_datetime) as recent_datetime  from cte_recent group by tbl_name) AS source ON (Target.tbl_name = Source.tbl_name) when matched then         UPDATE SET Target.recent_datetime = Source.recent_datetime when not matched then         INSERT (tbl_name,recent_datetime)VALUES (source.tbl_name,source.recent_datetime);

query any table by using select , insert , update or delete and execute the below statement. the tables which you accessed recently should be in the top.

SELECT * FROM  TABLES_LAST_ACCESSED ORDER BY 2 DESC

For more information on this DMV http://msdn.microsoft.com/en-us/library/ms188755.aspx




SQL Server 2005 : http://stackoverflow.com/questions/711394/how-do-you-find-the-last-time-a-database-was-accessed

removing identity property taking more time

How to remove the identity property.

to remove the identity property from a table , when you do through SSMS --> right click
on table --> select column --> properties --> * remove identity property.

But internally it will copy all the data into a temp table using insert into and then renames the existing , till this whole operation completes it holds an exclusive lock to not be modified by any other transactions.

but for larger tables it takes lot of time to copy the data and blocks the transactions.

we can replace the insert into with SWITCH command to copy the data and make it fast.
below are the steps.
-- 1 Create the table for simulation
--2 insert few records
--3 create another table with the same schema as original table but without any identity
--4 Move the entire data from original table to temp table
--5 Rename the existing table to tbl_y table
--6 Rename the temp tale to existing table
--7 drop the tbl_y

-------------------------------------------------------------- --1 Create the table for simulation CREATE TABLE  MY_TRANS (iid int identity(1,1),   nage int,   CreatedDate datetime ) GO -------------------------------------------------------------- --2 insert few records insert into MY_TRANS select 10,'2012-dec-01' GO 5 -------------------------------------------------------------- -- 3 create another table with the same schema as original table but without any identity CREATE TABLE [MY_TRANS_temp] (iid int  not null,   nage int,   CreatedDate datetime ) -------------------------------------------------------------- -- 4 -Move the entire data from original table to temp table alter table MY_TRANS switch partition 1 to MY_TRANS_temp GO   -------------------------------------------------------------- -- 5 Rename the existing table to tbl_y table EXEC SP_RENAME MY_TRANS , MY_TRANS_Y -- 6 Rename the temp tale to existing table EXEC sp_rename MY_TRANS_TEMP , MY_TRANS -- 7 drop the tbl_y DROP TABLE MY_TRANS_Y

Truncate vs Delete

TRUNCATE VS DELETE

	Truncate	Delete
Where clause	Can’t apply.	Yes.
Identity property	Resets the Identity property	identity property values will remain same
Triggers	Wont fire	Triggers will fire
Performance	Minimally logged transaction. There is a myth that Truncate is a non-logged operation and can’t include in “begin tran” that is not true. it only logs the page de-allocations . there is no such thing called "non-logged" operation in SQL Server.	fully logged transaction
References	Can’t execute truncate table when the table has references.	Can execute the delete statement on a table with references with an exception that there won’t be any corresponding records in referencing table.
On indexed views	Cant execute when the table has indexed view	Supports
Locks	Less no.of locks	More locks
T-Log Space	Less transaction log space	Size and no of logged transactions are more.
Replication	Can’t use when the table participating in transactional or merge replication	Supports

Handling Relational Data

Handling Relational Data In Relational Databases
Managing relational databases is sometimes tedious, especially when we have to upload the data into the relational schemas or delete one particular row from a table as since it’s relational, the referential integrity wont allows the parent table to delete unless the related rows delete from the foreign keys. The chain branches of parent tables are simple or complex as one node might have multiple branches.
The below procedure will give the tree structure, so the output of this procedure you can use to delete or insert or update the keys (especially in case of data masking). All you have to do dump the output of this stored procedure in to temp table and generate the generic queries or do it manually, if it’s simple.

create  procedure [dbo].[pr_schema] as 
set nocount on 

SELECT SCHEMA_NAME(SOF.SCHEMA_ID) AS FSC_NAME,T1.FKEYID AS FK,SCHEMA_NAME(SOR.SCHEMA_ID) AS RSC_NAME,T1.RKEYID AS PK 
into #sysref FROM sysforeignkeys T1 
left outer JOIN sys.objects SOF ON SOF.OBJECT_ID = T1.FKEYID 
left outer JOIN sys.objects SOR ON SOR.OBJECT_ID = T1.RKEYID 
-- below 3 lines is to avoid the problems with self reference 
LEFT OUTER JOIN SYSREFERENCES T2 
ON OBJECT_NAME(T1.FKEYID)+OBJECT_NAME(T1.RKEYID) = OBJECT_NAME(T2.RKEYID)+OBJECT_NAME(T2.FKEYID) 
WHERE T2.FKEYID IS NULL 
--******************************************************* 
-- Get the Max No.Of Joins foreign keys in a Chain 
declare @N VARCHAR(9); 
WITH CTE(FKEYID,LEVEL) 
    AS 
     (SELECT FKEYID,0 FROM sysforeignkeys 
    UNION ALL 
    select T1.PK ,LEVEL+1 FROM #sysref T1 JOIN CTE T2 ON T1.FK = T2.FKEYID ) 
-- Statement that executes the CTE 
SELECT @N = Max(level)  FROM CTE 
PRINT @N 
--******************************************************* 
Declare @V1 varchar(max) 
set @V1 = '' 
  
Declare @V2 varchar(max) 
set @V2 = '' 
declare @select varchar(max) 
set @select = '' 
Declare @OrderBy varchar(2) 
set  @OrderBy = @N+1 
WHILE NOT @N = 0 
BEGIN 
             set @V1 =  @V1 + ' right outer join #sysref A' +cast(@N as varchar(4)) + ' on a'+ @V2+'.pk =A'+cast(@N as varchar(4))+'.fk' 
             set @select =  @select+    'a'+ @N+'.FSC_NAME+''.''+object_name('+'a'+ @N+'.fk),' 
set @V2 = @N 
SET @N = @N -1 
CONTINUE 
END 
SET @select=  @select +  'A1.RSC_NAME+''.''+object_name(A1.PK)' 
exec('SELECT ' + @select +'  from #sysref a ' +@V1 + ' order by '+ @OrderBy ) 
print ('SELECT ' + @select +'  from #sysref a ' +@V1 + ' order by '+ @OrderBy ) 

sql server dynamic stored procedures

How to create dynamic parameters in SQL Server stored procedure

Below script is an example for how to create a procedure to pass dynamic parameters. This is not a new trick and many developers are using using the method. But probably not every one knows that it might leads to SQL Injection attacks.But still, one good thing about using .NET is that if you execute any sql statement from the front end then it will automatically calls the API function (sp_executesql)that rewrites the SQL statements to a parameterized query which is safe. so make sure the front-end technology which you are using has this capability to call the APIS while passing sql statements to backend.

 

-- execution script: Execute dbo.pr_customers 'te','tx',1
CREATE PROCEDURE dbo.Pr_customers(@firstname VARCHAR(90)=NULL,
                                  @lastname  VARCHAR(90)=NULL,
                                  @age       INT=NULL,
                                  @phone     INT=NULL)
AS
  BEGIN
      -- declare the variables to build the string dynamically
      DECLARE @Where VARCHAR(MAX),
              @SQL   VARCHAR(MAX)

      SET @Where = ' WHERE 1=1 '

      IF ( @firstname IS NOT NULL )
        SET @Where = @Where + ' AND firstname = ''' + @firstname + ''''

      -- make sure that you append four quotes(') for charecter parameters
      IF ( @lastname IS NOT NULL )
        SET @Where = @Where + ' AND lastname = ''' + @lastname + ''''

      IF ( @age IS NOT NULL )
        SET @Where = @Where + ' AND city = ' + CONVERT(VARCHAR, @age)

      -- for integer parameters you no need to append quotes(')
      IF ( @phone IS NOT NULL )
        SET @Where = @Where + ' AND country = ' + CONVERT(VARCHAR, @phone)

      SET @SQL = 'select * from customers ' + @Where

      EXEC (@SQL)
  END