FIX: The performance of a DML operation that fires a trigger may decrease when the trigger execution plan recompiles repeatedly (870972)

MORE INFORMATION

• Run the following Transact-SQL statement in SQL Query Analyzer on the instance of SQL Server:

  DBCC TRACEON (9055, -1)

• Add -T9055 to the SQL Server startup parameters.

Steps to reproduce the behavior

1. Start SQL Query Analyzer, and then connect to an instance of SQL Server 2000.
2. Create a table and an update trigger on the table. Name the table as cache_test and name the update trigger as cache_test_update. To do this, run the following Transact-SQL statements:

   use pubs
   go
   set nocount on
   go
   if exists (select 1 from sysobjects where id = object_id('dbo.cache_test'))
   drop table dbo.cache_test
   go
   create table dbo.cache_test
   (
   id int not null,
   descr varchar(30) not null,
   rowcreateddt datetime not null default getdate(),
   rowcreatedby varchar(30) not null default right(system_user,30),
   testcolumn varchar(30) null,
   primary key (id)
   )
   go
   
   create trigger dbo.cache_test_update
   on dbo.cache_test
   for update
   as
   if exists ( select 1 from inserted where id = 98654 )
   print 'test'
   return
   go

3. Insert some records in the cache_test table, and then remove all elements from the procedure cache. To do this, run the following Transact-SQL statements:

   insert into cache_test (id, descr, testcolumn) select 1,'Descr 1','Test 1'
   insert into cache_test (id, descr, testcolumn) select 2,'Descr 2','Test 2'
   insert into cache_test (id, descr, testcolumn) select 3,'Descr 3','Test 3'
   insert into cache_test (id, descr, testcolumn) select 4,'Descr 4','Test 4'
   insert into cache_test (id, descr, testcolumn) select 5,'Descr 5','Test 5'
   insert into cache_test (id, descr, testcolumn) select 6,'Descr 6','Test 6'
   insert into cache_test (id, descr, testcolumn) select 7,'Descr 7','Test 7'
   insert into cache_test (id, descr, testcolumn) select 8,'Descr 8','Test 8'
   insert into cache_test (id, descr, testcolumn) select 9,'Descr 9','Test 9'
   insert into cache_test (id, descr, testcolumn) select 10,'Descr 10','Test 10'
   go
   
   dbcc freeproccache

4. Update a single row in the table cache_test, and then see the plan for this single row update that is in the cache. To do this, run the following Transact-SQL statements:

   update cache_test
   set testcolumn = 'testing '+cast(id as varchar)
   where id between 1 and 1
   go
   
   select SetOpts,UseCounts,* from master..SyscacheObjects where ObjId=Object_ID('cache_test_update') order by SetOpts, CacheObjType
   go

   You notice an output that is similar to the following:
   

   SetOpts UseCounts bucketid cacheobjtype     objtype  objid      dbid dbidexec uid refcounts usecounts pagesused lasttime maxexectime avgexectime lastreads lastwrites setopts langid dateformat status sqlbytes sql
   5371	1	  10394	   Compiled Plan    Trigger  1029578706	5    5	      1	  2	    1	      1	        0	 0	     0	         0	   0	      5371    0	     1	        0	34	cache_test_update
   5371	1	  10394	   Executable Plan  Trigger  1029578706	5    5	      1	  1	    1	      1	        0	 0	     0	         0	   0	      5371    0	     1	        0	34	cache_test_update
   

5. Update four rows in the cache_test table, and then see the plan for this multiple-row update that is in the cache. To do this, run the following Transact-SQL statements:

   update cache_test
   set testcolumn = 'testing '+cast(id as varchar)
   where id between 1 and 4
   go
   
   select SetOpts,UseCounts,* from master..SyscacheObjects where ObjId=Object_ID('cache_test_update') order by SetOpts, CacheObjType
   go

   You will notice an output that is similar to the following:
   

   SetOpts UseCounts bucketid cacheobjtype     objtype  objid      dbid dbidexec uid refcounts usecounts pagesused lasttime maxexectime avgexectime lastreads lastwrites setopts langid dateformat status sqlbytes sql
   4347	1	  10394	   Compiled Plan    Trigger  1029578706	5    5	      1	  2	    1	      1	        0	 0	     0	         0	   0	      4347    0	     1	        0	34	cache_test_update
   4347	1	  10394	   Executable Plan  Trigger  1029578706	5    5	      1	  1	    1	      1	        0	 0	     0	         0	   0	      4347    0	     1	        0	34	cache_test_update
   5371	1	  10394	   Compiled Plan    Trigger  1029578706	5    5	      1	  2	    1	      1	        0	 0	     0	         0	   0	      5371    0	     1	        0	34	cache_test_update
   5371	1	  10394	   Executable Plan  Trigger  1029578706	5    5	      1	  1	    1	      1 	0	 0	     0	         0	   0	      5371    0	     1	        0	34	cache_test_update
   

6. Update five rows) in the cache_test table, and then see the plan for this multiple-row update that is in the cache. To do this, run the following Transact-SQL statements:

   update cache_test
   set testcolumn = 'testing '+cast(id as varchar)
   where id between 1 and 5
   go
   
   select SetOpts,UseCounts,* from master..SyscacheObjects where ObjId=Object_ID('cache_test_update') order by SetOpts, CacheObjType
   go
   

   You notice an output that is similar to the following:
   

   SetOpts UseCounts bucketid cacheobjtype     objtype  objid      dbid dbidexec uid refcounts usecounts pagesused lasttime maxexectime avgexectime lastreads lastwrites setopts langid dateformat status sqlbytes sql
   4347	1	  10394	   Compiled Plan    Trigger  1029578706	5    5	      1	  2	    1	      1	        0	 0	     0	         0	   0	      4347    0	     1	        0	34	cache_test_update
   4347	2	  10394	   Executable Plan  Trigger  1029578706	5    5	      1	  1	    2	      1	        0	 0	     0	         0	   0	      4347    0	     1	        0	34	cache_test_update
   

   
   If you compare this output with the output in step 5, you may notice the following conditions that could lead to the decrease in performance of the trigger execution:
   • The execution plan that corresponds to the multiple row update in the output of step 5 is deleted. This behavior occurs because the execution plan that corresponds to the multiple row update is recompiled to create a new execution plan.
   • Even though the execution plan appears to have been recompiled, the usecounts column indicates that the Executable Plan has been used two times. That is not correct.
   • Additionally, the execution plan that corresponds to the single row update has been deleted from the cache.