Tuesday, February 12, 2013 2:48 AM
We have a ODS system where data from OLTP is synced using delta captured thru CDC job.
In the process we check for record existence using primary key based on which we perform update or insert. Source and target schema are identical.
While updating we are updating all columns of the row irrespective of whether the value was changed or not in each column, (as we felt it will be a cumbersome task to check for each column change and generate a dynamic update stmt). So essentially we are overwriting the record with same values 95% of time.
I need a experts opinion on impact of our update design on the system - log, Indexes, performance etc. Plus any suggestion for better approach.
SQL 2008 R2 version.
- Edited by Mahesh Dasari Tuesday, February 12, 2013 2:49 AM
Tuesday, February 12, 2013 5:48 AM
When an update happens to a column that do not change the column value, this is called 'Non-updating updates'. The behaviour and performance vary slightly for clustered index tables and heap tables (with non clustered indexes).
For non-updating updates avoid extra logging and page flushing if the updating column is not part of clustering key. If the update is a clustering key, then this will be logged. For more details on this Paul White has an interesting post on this subject:
The following article describe new optomization introduced from SQL Server 2005: