Post reply

Tsql script 2012 Unused and needed indexes, usage, pages etc... and creates the index needed

  • December 20, 2012 at 9:04 am

    #286510

    Tsql script 2012 Unused and needed indexes, usage, pages etc... and creates the index needed

    I know there is a lot of queires out there, I, looking for a all in one if someone has one in the library somewhere.

    I need to find the index usage, not used and pages etc... and that will create the syntax on the ones that they are needed on.

    Thanks in advance

  • December 20, 2012 at 9:40 am

    #1570597

    If you're thinking of automatically, with no investigation, creating any indexes suggested by the missing index DMVs and dropping any that the index usage stats say are unused, don't.

    Missing indexes are suggestions, not directives. Automatically create and you'll end up with a huge amount of redundant and useless indexes.

    Index usage stats are just since the database was last started, if the server is rebooted you lose history and could easily drop indexes that are needed for month end or year end jobs.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 20, 2012 at 10:35 am

    #1570616

    Im looking for the syntax to grab the information and create the syntax then we can review it.This will give the syntax to drop and the syntax to run then that output is to create the login. Im looking for all the information on unused indexex and needed indexex with the sytnax in the result for our review. Does that make sense?

    For example we have this one for logins that have changed by a range

    SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged'

    , 'DROP LOGIN [' + name + '];' AS 'RUN ON NEW SERVER'

    , 'EXEC sp_help_revlogin @login_name =[' + name + '];' AS 'RUN ON OLD SERVER, THEN RUN OUTPUT OF THAT ON NEW SERVER'

    FROM sys.sql_logins

    WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') > DATEADD(dd, -5, GETDATE())

    Order by PasswordChanged desc

    Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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