Options

Database clean up

N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
Any recommended best practices anyone care to share? Right now I am going through ~100 tables in a DW looking for usage of the tables and to see if any tables are just floating out there not being used. I found some seek, scan and update queries to see the activity of the table. Any other recommendations while going through this activity? Right now I am capturing each table in a spreadsheet capturing the PK, FK and other constraints and if the table has data etc. Can you think of anything else that would be helpful? Thanks!

Comments

  • Options
    NotHackingYouNotHackingYou Member Posts: 1,460 ■■■■■■■■□□
    In SSMS right click on the table and choose 'View Dependencies'. Not always 100% indicative of how the developers have linked the tables but at least shows you some of the relationships.
    When you go the extra mile, there's no traffic.
  • Options
    N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    Very nice this will help with my analysis. Thanks - I am always learning something new in SSMS.
  • Options
    jibbajabbajibbajabba Member Posts: 4,317 ■■■■■■■■□□
    Drop all tables and only restore the ones people complain about :p:p
    My own knowledge base made public: http://open902.com :p
  • Options
    N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    LOL no thanks! Carl what do you use to export too? Everyone is saying paste to clipboard but I am unable to paste to MS Paint. Tried Word that was a monster fail. I am looking at some tools that connect through ODBC or JDBC but haven't found anything I am in love with yet. Thoughts?
  • Options
    NightShade03NightShade03 Member Posts: 1,383 ■■■■■■■□□□
    First thought here is to install something like NewRelic on your application servers. The insights and crazy metrics you'll get out of such a tool is amazing, plus they can pinpoint not only application problems, but exact lines of the code that querying the DB. You can use this to tune the parameters of the DB, storage engines, application servers, etc. Once you've addressed the performance aspect then I would turn to tables.

    Tables you can do a **** of the schema only (it's different for every DB, but the concept is the same). Once you **** the schemas you'll hopefully be able to see the PK and FK fields for each table and map those back to dependencies if required. Not sure how deep you want to go, but another common task here would be to script a script/expression that validates the data in each field to ensure it is conforming to the specific schema for that table/field. This is more of a data validation exercise then DB tuning....but they go hand in hand.

    Finally I'd put some monitoring software (you can also use NewRelic for this) in order to track some metrics for the DB server/cluster itself. Are there performance issues? Old hardware that isn't keeping? You get the idea. By the end of this you should have a well oiled machine with some baselines metrics to help you measure and maintain the DB tables and server better for the long term.
  • Options
    N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    I found something called SchemaCrawler it seems to work with GraphViz. Night I'll review your post at home tonight have another project meeting to head too. Thanks for replying!
  • Options
    N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    Just a quick question - We have a ton of "backup tables" in our prod schema. I see that their only dependence is to themselves, anything else I should be aware of? I am going to post my findings to our DBA for removal so it will get another set of eyes. I am trying to make life easier on the DBA. Isn't that what a Business analyst supposed to do? :)
Sign In or Register to comment.