Tuesday, January 29, 2013

Tracing Database Insert/ Update and View operations


Recently i needed to track changes made to the database records. I did some research and found below information.

Tracing Insert/ Update

We have 3 options.

1. Write Sql Triggers for each table on inserts and updates – Whenever an insertion or an updation is done trigger is fired for the particular table.

Ex: 

CREATE TRIGGER dbo.trOnTableName
ON dbo.TableName
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- Here we insert data the data to a table so that later we can
--retrieve those data from the table
INSERT INTO tblTraceTable....
END 

Issues: There can be performance issues.
Need to write triggers for each and every table.

2. Add DateTime column to the table, from that we can retieve records for a particular date range.

Issues: Can not specifically say whether a update or a new record insert is done because whenever a update or a insert is done DateTime column is updated. Any way we know a change has been made to the record.

3. Last option is create a method on our application to insert records to a table whenever a record is modified.

Issues:
Need a extra write to the Database (Performance issue).
When doing a update need to read and insert the data after and before the record is modified(Here we’ll have to do several extra database reads and writes).
Modifying the existing application always require proper testing.

Tracing View Opearations.

We have 2 options.

1. We can use the below query on Sql Server Management Studio.

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
,dbname = db_name(dest.dbid)
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC 

Above query gets the historical queries from the sql server cache and display them.

Issues: Sql Server Cache is cleared automatically when the server need resources then the history is lost.
Cache is cleared whenever the server is restarted.
We can not sure about how much data is save in cache because the server clears it whenever it need resources.

2. Write a method in our application to insert a record when a search is done.

Issues: I think this is very unnecessary because for every search we need to do a database write. Overload to the application (affects the application performance)

No comments:
Write comments
Recommended Posts × +