No specific explanation was provided last time. The wide and wide have been asking this question. The following is a supplement:
If you use SQL2000, this problem will be more serious. Trigger operations must be part of external transactions. Therefore, both the instered and deleted virtual tables are written to the transaction log. Because logs are written in sequence, other transactions are prevented from writing new and old records into logs. At the same time, reading logs may also be blocked due to other writes. This undoubtedly has a great impact on concurrent operations.
Sqlserver treats the trigger as a stored procedure internally, except for the inability to enter parameters and other restrictions. Another issue worth considering is the execution plan of the trigger, which is not compiled with the re-Compilation of the process. The last time I demonstrated an incorrect execution plan example due to an incorrect cache, I do not know that this calculation is not counted as a problem.
Currently, the new and old records of sql2005 are not stored in logs, but stored in tempdb using the new row version technology. This brings concurrency advantages to a certain extent, but maintaining these version records also requires additional overhead. At the same time, searching for records in such a version Chain List also results in performance loss. Currently, the update, delete, and insert operations of 2005 all carry the output clause, which can be used to replace the trigger operation. However, output has a restriction that it cannot be directly into a constrained table. If there is a constraint, you can save the front and back records to a temporary place for further processing.
The following code demonstrates the situation observed when output and trigger are used. Both use tempdb. However, output stores these records in its own space, while trigger stores the records in a public space, just like a private car library and a public parking lot. Therefore, output is more dedicated to processing a request. The Code is as follows:
-- Updated all the products in London to 1.5 times the original price.
Use northwind;
Go
-- Create a price change history table
If object_id ('price _ history ', 'U') is not null
Drop table price_history
Go
Select 1 as productid, unitprice as oldprice, unitprice as newprice, getdate () as date
Into price_history
From DBO. Products
Where 1 = 0
Go
If object_id ('trg _ products_u ', 'tr') is not null
Drop trigger trg_products_u;
Go
-- SYS. dm_tran_version_store is used to store row version records. This record will be cleared by the cleanup thread within one minute without being referenced.
Select * From SYS. dm_tran_version_store -- make sure there is no version record
-- Make sure that no other update trigger exists in products.
-- After the update is complete, no row version is recorded,
-- If you turn on the performance counter to track the free space in tempdb of sqlserver: transactions object, you will find that it also uses tempdb.
-- Because this update does not have many records, the results cannot be seen. You can try a new sales. salesorderdetail table.
Update P
Set unitprice = unitprice * 1.5
Output deleted. productid, deleted. unitprice, inserted. unitprice, getdate () into price_history
From DBO. Products P
Join DBO. Suppliers s
On P. supplierid = S. supplierid
Where S. City = n' London'
Select * From SYS. dm_tran_version_store
Go
-- Now we create an update trigger to complete this function
If object_id ('trg _ products_u ', 'tr') is not null
Drop trigger trg_products_u;
Go
Create trigger trg_products_u on DBO. Products for update
As
-- If the updated version is not unitprice or is not updated, the system returns
If not update (unitprice) or @ rowcount = 0
Return;
Else
Insert into price_history
Select I. productid, D. unitprice, I. unitprice, getdate ()
From inserted I
Join deleted d
On I. productid = D. productid
Go
-- When a trigger is used, there are 6 records in the version record after the update is completed.
-- Because the supplier in 'London has three products, the total number of new and old records is 6.
Update P
Set unitprice = unitprice * 1.5
From DBO. Products P
Join DBO. Suppliers s
On P. supplierid = S. supplierid
Where S. City = n' London'
Select * From SYS. dm_tran_version_store
DBCC freeproccache; -- clears the Cache during the process to observe the cache plan of the trigger.
Go
-- Create a stored procedure for displaying and re-Compiling
Set ansi_nulls on
Go
Set quoted_identifier on
Go
If object_id ('prc _ updateproductprice', 'P') is not null
Drop proc prc_updateproductprice;
Go
Create procedure prc_updateproductprice
With recompile
As
Begin
Set nocount on;
Update P
Set unitprice = unitprice * 1.5
From DBO. Products P
Join DBO. Suppliers s
On P. supplierid = S. supplierid
Where S. City = n' London'
End
Go
Exec prc_updateproductprice
Go
-- After repeated execution of the above process, it is found that the execution plan of the trigger will not be re-compiled due to the re-Compilation of the process.
-- This may cause the optimizer to incorrectly select the execution plan because of the cache.
-- I don't know the result.
Select usecounts, cacheobjtype, objtype, text
From SYS. dm_exec_cached_plans
Cross apply SYS. dm_exec_ SQL _text (plan_handle)
Go
