SQL Server Performance

weird index issue

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by dba_boston, Jan 7, 2010.

  1. dba_boston Member

    we have weekly refresh of our db, right after refresh, we find one function in application is hanging, then we find out the script of that function.
    we decide to add an index to one table which is main part of that involved script, then the problem went away. After the problem gone, even we drop that index, we will not have any problem untill next week after the refresh, the same problem come back again.
    then we will do the same, add index, then drop.
    Any one has any idea?
    It looks like that it does not matter if sql use the added index, but put the table into memory by adding the index that really matters?
    Thanks.
  2. FrankKalis Moderator

    Why do you drop the index when it seems to be beneficial? Is this a clustered or nonclustered index? Also, is this table heavily used and data is frequently added, updated, or deleted?
  3. dba_boston Member

    Thanks for your reply.
    it is a nonclustered index. We have a existing similar index already. to drop it is to test if that really make things work.
    db refresh is something about the failover, not restore from db backup
    thanks
  4. rohit2900 Member

    [quote user="dba_boston"]
    it is a nonclustered index. We have a existing similar index already.
    [/quote]
    What does this mean??? you're creating and additional index with the same defination which is already there???
    [quote user="dba_boston"]
    db refresh is something about the failover, not restore from db backup
    [/quote]
    Can you please confirm as what does this failover means???
  5. moh_hassan20 New Member

    what the size of the table , size of the transaction% on that table.?

  6. MohammedU New Member

    It could be statistics issue, because when you create an index statistics of that column will be updated.
    Next time instead of creating an index try to update the statistics and see what happens...
    Did you see the query plan after adding the index? is it using the new index and is there any query plan change after dropping the index?
  7. Adriaan New Member

    What do you mean by "refresh of our db"? Are you doing a back-up and then a restore from that back-up?

Share This Page