Too many databases????

• Studdy05

  Ten Centuries

  Points: 1375

  More actions

  August 22, 2005 at 9:42 am

  #110265

  I am looking at one large clustered production box, with a couple dozen databases on it - each with a hundred or so tables on it, and a few tables have at least 3 million rows.

  The boss says, "which one are we using the most, and can you give me a statistical breakdown of each?"

  I am not sure a script exists for such, and I may end up having to pull a very large profiler trace in to find this out.  Not really sure though.

  Nor can I abuse this box much, as it is THE production box so no locks/timeouts need to happen from me. 

  Where should I look for answers?  I have Googled, and BOL'd and read, but I am not seeing what I want.  What am I missing?

• peterhe

  SSChampion

  Points: 11469

  More actions

  August 23, 2005 at 8:52 am

  #584221

  You can use performance monitor on a client box and connect to the production. Use the counter log to monitor SQL/Database/Transactions / sec counter. You need to select this counter for each database you want to monitor.

  You can save the result to a file, and get total or average trasactions/sec for each database.

  The result just reflects the hit of clients on each database. If the transactions on different db varies greatly in terms of time and impact on IO, the reslut may not accurate.

   

  As you said, you can profiler too. Either run it on a client box or set up sever side trace will mitigate its impact on production server.

   

   

• Rudyx - the Doctor

  SSC-Forever

  Points: 43695

  More actions

  August 23, 2005 at 11:52 am

  #584315

  Be careful in presenting your statistics. 'using the most' needs to be quantified first !

  Based on what 'using the most' means can vary what profiler/perfmon information you gather, not to mention the frequency and volume. Also, the type of applications using the databases may also affect the definition of 'using the most'.

   

  Some examples of what 'using the most' could mean (to a database):

  1. most user connections to a database
  2. most stored procedure executions to a database
  3. most selects/insert/update/delete to a table by database (maybe top 10 tables)
  4. the longest running stored procedure(s) (maybe top 10)
  5. the most frequently used non-system stored procedure by database (maybe top 10)
  6. which database causes the greatest memory usage by process
  7. which database causes the greatest physical i/o by process

   

  Now differentate the type of databse and application:

  1. online (OLTP)
  2. WEB
  3. batch
  4. mixed

   

  As you see things can become quite 'murky' in a hurry. A bit more 'clarity' now will most likely save everyone a great deal of mis-information and mis-communication later.

  RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."