Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Proper Indexing For Trigger Initiated Inserted Scan Expand / Collapse
Posted Thursday, September 13, 2012 7:05 AM


Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:03 AM
Points: 13, Visits: 131
Is there a best practice for creating index to reduce the IO for inserted scans that originate from a trigger? I have a table with roughly 2 million rows that has an insert and an update trigger that fires every time a new row is inserted or when an existing row is updated. From the estimated execution plan, the EstimatedIO for these scans is ~64. Currently, the table only has a clustered primary key index. Is there a specific way to approach index for inserted scans to reduce the overall IO of the process?

My only thought right now is to read the inserted row into a temp table, and then create a clustered index on the primary key of the temp table. The thinking is that the trigger is making subsequent calls for the inserted row, which causes a table scan to retrieve the data. Anything behind this logic?
Post #1358529
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse