SQL Server Performance Forum – Threads Archive
Deleted and Inserted Trigger Queries
HelloWe have a 70GB database running in SQL server 2000. We have started to see performance degregation in the triggers when a select from inserted or deleted occurs outside of a declaration of a cursor. The CPU cost when querying against the deleted table is 769. That is three digits in front of the decimal. This occurs when we run a query like
Insert table (x, y, z)
select x, y, x from deleted. If we add the select x, y, z from deleted in a cursor declaration there is not a CPU cost recorded. Actions we have taken to resolve this so far.
1. We have reduced the size of the tempdb to 500MB,
2. we have moved transaction logs to a set of mirrored drives and shrunk the log files to 500MB or less,
3. we have move the table into its own datafile
4. we have rebuilt the index for the table and placed them in their own datafile. The data table contains 7.2GB and we have 700MB of indexes in stand alone datafiles.
1 & 2. I don’t see any reason or solution that if you shrink the TEMPDB or log will help any performance. In fact for such computations TEMPDB is used and to process log is used to record.
Links to fine tune the performance using triggers:
http://www.sql-server-performance.com/trigger_tuning.asp
http://www.sql-server-performance.com/nn_triggers.asp Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>