Post reply

Tables Utilization

  • March 23, 2005 at 8:54 pm

    #362247

    Hi

     How I can find tables Utilization in my database through SQL Server 2000 profiler.  I want do find out how heavily my database tables are being used?Basically I want to rank tables according to their usage?

     

    Thanks

    Khalid

  • March 24, 2005 at 3:38 am

    #548687

    Thats quite tricky and would require heavy profiler usage, I've been asked for the same information and I suggested it would be a very intensive task even with profiler. You'd need to store all SQL with the i/o counts and then query the results sets.

    I understand it might be possible with SQL Power Tools to get this information.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • March 24, 2005 at 8:13 am

    #548760

    I have some ideas for this; can you more clearly define "utilization"? What, exactly, would you want to see?

    --
    Adam Machanic
    whoisactive

  • March 24, 2005 at 8:18 am

    #548764

    A not too crazy assumption is:

    Your bigger tables are usually your most utilized tables

     


    * Noel

  • March 25, 2005 at 1:32 am

    #548914

    There is an another quick and dirty check about the most used tables:

    You can check the rowmodctr columns in the sysindexes tables. This counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table. 



    Bye
    Gabor

  • March 25, 2005 at 7:32 am

    #548943

    Hi

        Thanks for your kind responses I would like to  clearify that Table Utilization does not mean bigger table . In my view Table Utilization mean how frequently table has been accessed that includes Select, Insert, Update, Delete and referenced? I think If we can count all the statements for each table than we can find the table utilization.

     

    Thanks

    Khalid

  • March 25, 2005 at 8:06 am

    #548954

    Khalid,

    Let me clarify my statement.

    I didn't say  Bigger table = Most Utilized

    I said Bigger Tables are usually the most utilized.

    This is the reasoning behind it:

    1. To have a lot of records you have to have a lot of inserts 

    2. When a DB is normalized correctly and is very useful for ..well.. the users the db ends up with large tables containing the most important and therefore accessed data

    3. There are always exceptions like audit tables and some static (lookup mapping) data tables

    if you really need "=" .Take a profiler trace into an sql table of every thing that was sent. Assign each statement a pk and create a subtable in which you insert the name of the tables/ views / sp called. Finally aggregate your results.

    In the end you will end up with a lot of effort getting to what may point was to start

    HTH

     


    * Noel

  • March 25, 2005 at 8:15 am

    #548957

    Parsing text for the data is not the way to go, IMO. I'm working on a solution to this on my end (this question is fairly common, actually), hopefully using some of the Lock events as a guide. The problem is that they're not consistent -- there can be many locks made during a single query -- however, maybe that's a good sign of utilization -- queries that require more data (and therefore, more locks (table locks not withstanding)) are more heavily utilizing the tables and therefore should count higher in the total rank. Or maybe that's just laziness talking -- Anyway, I'll report back soon if I figure something out.

    --
    Adam Machanic
    whoisactive

  • March 25, 2005 at 8:32 am

    #548960

    Adam,

    There is a published work of Adrew Zanevsky on detecting the most heavily used queries in which profiler traces are scrubbed to determine the usage and impact but they stop at the sp pattern level. May be this is a good place to start.

    On the other end I am pretty sure that using perl for the parsing is not a bad approach either.

    Oh well this is just to give you a hint that we are not alone on the same quest. 

    You can always use tools for this but I was just staying away from third party tools

     


    * Noel

  • March 25, 2005 at 8:38 am

    #548962

    I saw Andrew's article -- I also wrote one about tracing for unused stored procedures, in the same issue of SQL Server Professional:

    http://tinyurl.com/6ox35

    So now we can find most-used and least-used stored procedures; but neither of our articles covers anything at a high enough granularity to answer questions about table usage. This will be much easier in SS2005, by the way, thanks to the index stats dynamic management function that's being added (not yet implemented in the current beta, unfortunately)...

    I will also note: How do you think the third party tools do it? They have the same tools we have for interacting with SQL Server -- traces and performance counters. So there must be some magic formula. We just need to figure it out!

    --
    Adam Machanic
    whoisactive

  • March 25, 2005 at 12:11 pm

    #548987

    Hi

      Thanks for your responses. In my view table utilization means number of times the table has been accessed that includes Select, insert, update ,delete and referenced. if we can count the number of times the table has been accessed then we can calculate the Utilization.

     

    Thanks

    Khalid

Viewing 11 posts - 1 through 10 (of 10 total)

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