Indexes

  • I can see only when the indexes got created. However, I am looking is there a way to find who created the indexes?

  • Pretty sure SQL doesn't log that information.  In general, I have found that I don't care who created an index, I just care that the correct indexes are created.  Only a handful of people have permissions to create indexes where I work, so if a new index pops up, I have a rough idea as to who created it and can have a quick meeting to discuss the index if needed.  I find it is better to educate those who have the permissions to create indexes and use a "bad" index as a learning opportunity to the team rather than singling out an individual and making them feel embarrassed for being called out on a bad index.

    Now, if you need to know who created them going forward, you can create a trigger for that (I believe).  For all existing indexes though, I am not aware of any way to look up who created them.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • sqlguru wrote:

    I can see only when the indexes got created. However, I am looking is there a way to find who created the indexes?

    No.  You could create and add a database trigger to log who made schema changes.

    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/

  • Extended Events is our friend.

    Take a look at the object_created event. You could filter by object_type and only see when indexes get created.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey wrote:

    Extended Events is our friend.

    Take a look at the object_created event. You could filter by object_type and only see when indexes get created.

    That will only work after Extended Events have been set up, right ?

    I don't think OP can find out about existing indexes.

  • Yeah. There really isn't a way to do this after the fact.

     

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • What do you think about schema changes history? How long does it keep the history?

  • sqlguru wrote:

    What do you think about schema changes history? How long does it keep the history?

    How long does what keep the history?

    If you are talking about the report built-in to SQL Server, that uses the default trace.  That keeps 5 rollover files of 20 MB each.  So, in a lot of systems, not very long.

    If you create your own extended events or database triggers, you can control that.  With extended events, there is still a rollover that is configurable, but you could save the output to a table.  The output of a trigger would go to a table.

     

    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/

  • sqlguru wrote:

    What do you think about schema changes history? How long does it keep the history?

    If I recall correctly (and I might not), that's based on the default trace.  If your machine is as busy as mine, that information can last less than a couple of minutes.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply