Tables Utilization

Question

Tables Utilization

Khalid-151305

Right there with Babe

Points: 737
More actions
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

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

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

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/

Adam Machanic SSCoach Points: 15370 More actions · Answer 2

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

--
Adam Machanic
whoisactive

noeld SSC Guru Points: 96590 More actions · Answer 3

A not too crazy assumption is:

Your bigger tables are usually your most utilized tables

* Noel

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 4

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

Khalid-151305 Right there with Babe Points: 737 More actions · Answer 5

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

noeld SSC Guru Points: 96590 More actions · Answer 6

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

Adam Machanic SSCoach Points: 15370 More actions · Answer 7

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

noeld SSC Guru Points: 96590 More actions · Answer 8

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

Adam Machanic SSCoach Points: 15370 More actions · Answer 9

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

Khalid-151305 Right there with Babe Points: 737 More actions · Answer 10

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