October 28, 2015 at 5:16 pm
Is there a way to disable certian index DDL operation (create, drop and alter index) for a list of tables in MS SQL Server 2008 R2?
What I was trying to do is to create a DDL trigger that catch these events and roll them back, but it seems that all ddl trigers are after triggers and if table is very large this cause performance issues.
The trigger I am currently using is the following:
CREATE TRIGGER index_guard
ON DATABASE
FOR CREATE_INDEX, DROP_INDEX, ALTER_INDEX
AS
DECLARE @object_name NVARCHAR(50);
DECLARE @table_name NVARCHAR(50);
DECLARE @target_object_type NVARCHAR(20);
DECLARE @object_type NVARCHAR(20);
DECLARE @lookup_value NVARCHAR(100);
DECLARE @protected_indexes TABLE (Name NVARCHAR(50))
INSERT INTO @protected_indexes
SELECT Name FROM (VALUES ('TABLE1/IX_IdName'), ('TABLE2/IX_NameId')) AS tbl(Name)
SELECT @object_name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)');
SELECT @table_name = EVENTDATA().value('(/EVENT_INSTANCE/TargetObjectName)[1]','nvarchar(max)');
SELECT @target_object_type = EVENTDATA().value('(/EVENT_INSTANCE/TargetObjectType)[1]','nvarchar(max)');
SELECT @object_type = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)');
IF @object_type = 'INDEX' AND @target_object_type = 'TABLE'
BEGIN
SET @lookup_value = @table_name + '/' + @object_name
IF EXISTS (SELECT 1 FROM @protected_indexes A WHERE A.Name = @lookup_value)
BEGIN
ROLLBACK
END
END
October 29, 2015 at 5:54 am
DDL triggers are great for tracking whodunnit info, but rolling back operations can have a performance impact, as you identified.
I think using a DDL trigger to prevent index operations is a bottom up approach; you are cleaning up after the fact, instead of a top down approach of preventing the problem up front.
is someone calling a reindexing script during production hours? or are these ad hoc people creating indexes? is this on production?
did you tell the offenders not to do that, or are you not confronting them directly? talking to the people doing it directly can often address the issue without scripts and triggers being needed.
after that, the right thing to do would be to remove any script or job that might be called that does index operations, or even remove sysuser/db_owner/db_ddladmin rights from the people who are doing this on a production system.
if you can't do that, then point their names out to management and show they are affecting production.
I might be thinking along lines that are not relevant, so maybe you can explain a bit more what the core issue is?
Lowell
October 29, 2015 at 6:40 am
October 29, 2015 at 8:08 am
Hi Lowell,
Thanks for your answer.
The thing I want to accomplish is far more complex.
We have an ERP system that uses its internal mechanisms to synchronize data model definition with database model.
To give you an example: You define a table, indexes, field types inside the application, then a synchronization job is run which creates tables, indexes, contraints, etc.
If a table definition is changed the sync job alters the table, if an index is changed an drop index and create index commands are send to DB engine.
This is how its work.
Now, this version of application does not allow to define included columns (there is no such functionality in standard application)
So I have created a customization that allows to define included columns.
Where is the problem:
The problem is that the application sync job is like a black box, I cannot modify it. The way it works is like that: every time it is run it compares the definition of an index in application model and in DB, if there are some differences the index is dropped and recreated.
I can add addtional functionality to sync job before it is run and/or after its run.
Unfortunately, my customization can't go so deep to change the application model definition.
So my thinking was that:
install the trigger that disables index operations for selected indexes (those that have included columns attached)
run black box sync job - hopefully some index drops and recreated can be skipped
run post job (where I create indexes with included columns)
be happy with increased performance 🙂
The whole mechanism works OK, except the fact that the index drops and recreates are still performed inside sync job (and rolled back) - actually it works now like this: drop index -> roll back -> create -> error (object already exists)
What I really need in a solution that would allow to silently skip create/drop index commands.
Another solution is that I write my own version of sync job but this is rather huge work.
October 29, 2015 at 8:12 am
Hi Jayanth,
Thanks for your answer.
Could you drop me some links for further reading how to use policy management with this issue (I described it in reply to Lowell's post)
Thanks in advance.
Regards,
Sebastian
October 29, 2015 at 8:20 am
A thought I had may work. It may be more intrusive than you would like, however.
You have identified the indexes/tables that are affected by this, correct?
Whenever you create a "custom" index, store the script that drops and re-creates the index in a .sql file, or a database table.
When the automated process creates the index, have the DDL trigger start a SQL job that calls the script to re-create these indexes the way that you want them. You would need to guard against recursion happening.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 29, 2015 at 8:47 am
Let's assume that the sync application is attempting to create an index on Customer table called ix_CustomerName. If you don't want that index, then you could try creating a filtered version of the same index and then disable it. Perhaps the application will skip it.
create index ix_Customer on Customer ( CustomerName )
where CustomerName = 'N/A';
alter index ix_Customer on Customer disable;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 29, 2015 at 8:55 am
to disable all non-clustered indexes on table 'x;'
declare @Ind varchar(255)
declare @sql varchar(8000)
DECLARE vend_cursor CURSOR
FOR SELECT Name FROM sys.indexes WHERE object_id = OBJECT_ID('MyTable')
and type_desc = 'NONCLUSTERED'
OPEN vend_cursor
FETCH NEXT FROM vend_cursor INTO @Ind
while @@FETCH_STATUS = 0
begin
set @sql = 'ALTER INDEX [' + @ind+ '] ON [dbo].[MyTable] DISABLE'
exec (@sql)
FETCH NEXT FROM vend_cursor INTO @Ind
end
close vend_cursor
to rebuild:
--Rebuild
declare @Ind varchar(255)
declare @sql varchar(8000)
DECLARE vend_cursor CURSOR
FOR SELECT Name FROM sys.indexes WHERE object_id = OBJECT_ID('MyTable')
and type_desc = 'NONCLUSTERED'
OPEN vend_cursor
FETCH NEXT FROM vend_cursor INTO @Ind
while @@FETCH_STATUS = 0
begin
set @sql = 'ALTER INDEX [' + @ind+ '] ON [dbo].[MyTable] REBUILD WITH (ONLINE = ON) '
exec (@sql)
FETCH NEXT FROM vend_cursor INTO @Ind
end
close vend_cursor
October 29, 2015 at 9:45 am
Jayanth_Kurup (10/29/2015)
You could use Policy based Management which will prevent any operations that violate the defined policy
Automatic prevent (OnChange:Prevent) policies use DDL triggers to enforce the policy, so it would be no different to creating your own DDL trigger for the events in question.
October 29, 2015 at 12:00 pm
I Imagine you need two conditions one to check for tables that have NC indexes that have included columns and then one to check indexes to confirm the have included columns
ExecuteSql('Numeric' ,'select Count(*) from sys.indexes i
inner join sys.index_columns ic
on ic.index_id=i.index_id
and i.object_id= ic.object_id
where object_name(i.object_id) = @@ObjectName
and i.type=2
group by i.object_id , i.index_id
having Sum(cast(is_included_column as tinyint)) =0')
This expression checks for tables that have included columns in them.
As Anthony pointed out these also create DDL triggers to enabled prevent changes if violation occurs. How the above script is written will determine if you want it to prevent or allow changes?
Here is a good link to get you started
http://blogs.msdn.com/b/sqlpbm/archive/2008/07/03/executesql.aspx
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply