Log
Scan appears only in triggers that access inserted
or deleted tables. These tables are dynamically built by scanning
the transaction log when the trigger is executed. Triggers are executed
only after insert, delete,
or update queries modify a table with a trigger
defined on it for the specific query type. The following example
is a delete query on the titles table,
which has a delete trigger called deltitle defined
on it:
use pubs2 go set showplan on go delete from titles where title_id = 'xxxx' QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is DELETE. 2 operator(s) under root |ROOT:EMIT Operator (VA = 2) |DELETE Operator (VA = 1) | The update mode is direct. | | |SCAN Operator (VA = 0) | | FROM TABLE | | titles | | Using Clustered Index. | | Index : titleidind | | Forward Scan. | | Positioning by key. | | Keys are: | | title_id ASC | | Using I/O Size 2 Kbytes for data pages. | | With LRU Buffer Replacement Strategy for data pages. | | TO TABLE | titles | Using I/O Size 2 Kbytes for data pages.
The showplan output up to this point is for the actual delete query. The output below is for the trigger, deltitle.
QUERY PLAN FOR STATEMENT 1 (at line 5). STEP 1 The type of query is COND. 6 operator(s) under root ROOT:EMIT Operator (VA = 6) |RESTRICT Operator (VA = 5)(0)(0)(0)(5)(0) | | |SCALAR AGGREGATE Operator (VA = 4) | | Evaluate Ungrouped COUNT AGGREGATE. | | | | |MERGE JOIN Operator (Join Type: Inner Join) (VA = 3) | | | Using Worktable2 for internal storage. | | | Key Count: 1 | | | Key Ordering: ASC | | | | | | |SORT Operator (VA = 1) | | | | Using Worktable1 for internal storage. | | | | | | | | |SCAN Operator (VA = 0) | | | | | FROM TABLE | | | | | titles | | | | | Log Scan. | | | | | Forward Scan. | | | | | Positioning at start of table. | | | | | Using I/O Size 2 Kbytes for data pages. | | | | | With MRU Buffer Replacement Strategy for data pages. | | | | | | |SCAN Operator (VA = 2) | | | | FROM TABLE | | | | salesdetail | | | | Index : titleidind | | | | Forward Scan. | | | | Positioning at index start. | | | | Index contains all needed columns. Base table will not be read. | | | | Using I/O Size 2 Kbytes for index leaf pages. | | | | With LRU Buffer Replacement Strategy for index leaf pages. QUERY PLAN FOR STATEMENT 2 (at line 8). STEP 1 The type of query is ROLLBACK TRANSACTION. QUERY PLAN FOR STATEMENT 3 (at line 9). STEP 1 The type of query is PRINT. QUERY PLAN FOR STATEMENT 4 (at line 0). STEP 1 The type of query is GOTO.
The procedure that defines the deltitle trigger consists of four SQL statements. Use sp_helptext deltitle to display the text of deltitle. The first statement in deltitle has been compiled into a query plan, the other three statements are compiled into legacy query plans and are executed by the procedural execution engine, not the query execution engine.
The showplan output for the SCAN operator
for the titles table indicates that it is doing
a scan of the log by printing Log Scan.
