SQL Server Performance

Ideas for SQL "Cleaning" tool

Discussion in 'Third Party Tools' started by blockwood, Feb 12, 2004.

  1. blockwood New Member

    I have simple product, ApexSQL Clean, that analyzes unreferenced objects in database but we are going to rebuild it to improve the reference algorithm and add a lot of new "cleaning" features. Here is a short list. Can anybody think of anything else? Are these good items? Any feedback would be greatly appreciated

    The most useful things in ApexSQL Clean, I think, would be following:

    1. Recovering true dependences into "sysdepends"
    2. Showing for possible dropping unused objects, datatypes, users, roles
    3. Showing for possible dropping empty files and filegroups
    4. Showing for possible dropping duplicated indexes and fk
    5. Removing superfluous permissions (user and its role have the rights
    - it is dubbing; user doesn't work with a table but has the right;
    user has both transitive and direct rights)
    6. Renaming constraints into sensible names (FK_8927346594 ->
    FK_Table1_Col1_ref_Table2_Col5)
    7. Analising sizes of blob fields and configuring "text in row"
    if the case is favourable
    8. "Smoothing" collations of columns as the database default collation
    and "smoothing" all collations in a database as the server default collation
    9. Corrections user-login links (useful when a database has been attached
    or restored from another sql-server), removing "lost" users
    10. Showing for possible dropping roles/users that do not have


    Brian Lockwood
    ApexSQL Software
    http://www.apexsql.com
    Value Added SQL Server Tools
  2. fhanlon New Member

    How about removing logins that have no access to any databases and are part of no roles. ie not sysadmin or not setupadmin etc? By the way I have TSQL code to do this if you're interested.
  3. blockwood New Member

    thx! we'll add this to the list.

    Brian Lockwood
    ApexSQL Software
    http://www.apexsql.com
    Value Added SQL Server Tools
  4. bradmcgehee New Member

    This might be more troublesome, how about identifing unused indexes and drop them. Also, how about dropping unused column statistics, assuming they could be identified. How about unused jobs? How about identifying tables with no data in them (maybe this overlaps what has already been suggested?)

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com

Share This Page