StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTrigger rebuilding index after it exceed given fragmentation level
text
Body
copied!<p>Because I have to maintain couple databases and over 1000 tables heavily loaded of DML operations I had a thought to maintain indexes fragmentation level below some value automatically using triggers.</p> <p>It's only thought I don't have time now to do it so no my code will be provided. Of course I have some general schema in my mind how to do it. I'm able to build a trigger, I know how to find indexes fragmentation value. But without lot of testing and extensive documentation reading I don't know how to do it less buggy and the most optimal as possible. It could be even imposibble because I know little of triggers and their restrictions and some operations needed to achive this task could be disallowed in trigger.</p> <p>On example some things bothers me:</p> <ul> <li>use online or offline indexes </li> <li>does such trigger can crash the query</li> <li>because of timeout is there possibility to perform trigger not after some single row update but when the full query is finished so it can't crush because of index rebuild in a fly</li> </ul> <p>So how to achieve such task in most optimal manner? Any info about how such trigger influence queries performance will also be appreciated.</p> <p><strong>EDIT:</strong> Because of request I'll provide some code. It could be full solution but it generates error message when trigger is triggered: <em>'Cannot execute ALTER INDEX on/using table 'schema.table' since the table is the target table or part of cascading actions of a currently executing trigger'</em></p> <pre><code>CREATE TRIGGER defragmentator ON [schema].[table] AFTER INSERT, UPDATE AS DECLARE @fragRate float; SELECT @fragRate = a.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(N'MyDB'), OBJECT_ID(N'[schema].[table]'), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id WHERE b.name = 'IDX_name'; IF @fragRate > 50 ALTER INDEX IDX_name ON [schema].[table] REBUILD GO </code></pre> <p>How to improve that?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload