August 16, 2010 at 2:47 am
I assume that there is a statement to turn off generating an index... but I don't know it... Is there anybody who can help me?
Thanks
Stefan
August 16, 2010 at 3:00 am
what do you mean by generating an index?? whcih index and how they are getting aut generated? did you mean auto updated?
----------
Ashish
August 16, 2010 at 3:16 am
I'm sorry about my short description.
Example: There is an table tmp.Stats (GUID,Name,...,dtCreated) with an INDEX IX_tmp_Stats_dtCreated.
When I know do multiple inserts I assume that the index will be updated each time. I want to prevent this.
I know that I can disable the INDEX with DISABLE, but is there another way to do this?
August 16, 2010 at 3:27 am
DROP INDEX ...
Strange, requirement. Why you want disable index? What is you trying to do? Single update/insert/delete? Batch process?
August 16, 2010 at 3:29 am
as you doing on t-sql, the same you can do by SSMS.
expand your table, expand indexes and then right click on the index which you want to disable and then from menu select disable.
The same thing as with t-sql.
but aother option...:w00t:
----------
Ashish
August 16, 2010 at 3:32 am
You'd need to disable the index. But then of course you'd need to rebuild the index after your mulitple inserts.
Adding a fillfactor value to the index will minimize the performance impact of the inserts to some extent but it won't prevent the index from being updated.
August 16, 2010 at 3:35 am
I want to disable updating index for one batch, the index should be online for other batches.
Example:
<BEGIN TRANSACTION>
<do not update index>
<CURSOR>
<UPDATE TABLE>
<RENEW INDEX>
<COMMIT TRANSACTION>
August 16, 2010 at 3:39 am
begin tran
alter index <index_name> on <table_name> disable;
<CURSOR>???? -- check the various articles/threads on why you might not want to do this ;)
<UPDATE TABLE>
alter index <index_name> on <table_name> rebuild;
commit tran
August 16, 2010 at 4:14 am
teutales (8/16/2010)
I want to disable updating index for one batch, the index should be online for other batches.
Can't be done. Either you disable the index, meaning you drop the index structure and just leave the metadata, meaning it's unavailable for everyone, then rebuild it after the batch, or you leave the index online meaning that the batch will update the index and it will be available for everyone else.
Why do you want to not update an index during a batch?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 16, 2010 at 5:14 am
Why do you want to not update an index during a batch?
because of duration... I thought this is a big brake...
thank you all for your replies!!!!
August 16, 2010 at 5:35 am
teutales (8/16/2010)
because of duration... I thought this is a big brake...
It might, it might not. Test and see. If you're not updating a significant part of the table, the recreation of the indexes may well be more time-consuming than the updates to them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 16, 2010 at 6:26 am
teutales (8/16/2010)
Why do you want to not update an index during a batch?
because of duration... I thought this is a big brake...
thank you all for your replies!!!!
Your cursor is more likely to be the cause of your performance issues than the index.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply