Can I count the number of times a SELECT occurs on a table?

Question

Can I count the number of times a SELECT occurs on a table?

Viewing 4 posts - 16 through 18 (of 18 total)

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

Jersey Moe SSChasing Mays Points: 652 More actions · Answer 1

I went down the same path and also did not come across any software or canned process to log table \ view access via a 'SELECT'.

What worked best is to create a profile trace which outputs to a table.This trace must be run thru the Profiler app and not via line command. SInce the line command method does not have a switch to output to a table.

Each day, insert from table "daily_trace" to "accum_trace". Because a trace can not append data to the same table. Then run start the trace.

Afterwards you can parse thru the accum_trace at your own pace.

Jersey..

Michael L John One Orange Chip Points: 27060 More actions · Answer 2

I came up with a better(???) solution. I started a trace that included the text data as part of the trace and dumped it to a table.

I then created a table (Activity) with two fields, table_name and table_count.

I then wrote a cursor that loops through the text data in the table, and then a second inner cursor that loops through sysobjects getting the name of tables and stored procedures in the database. Using charindex, I parsed out the table/proc name from the text field. If it was found, I either inserted it into the Activity table or incremented the count field.

Right now, there are over 3000 tables and procedures in the database. After running the trace for a only few hours today, I determined that only 130 tables or procedures are being accessed.

Once I get the script fine tuned, I'll post it on this site.

Thanks for all of the suggestions, and keep them coming!

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

I seriously think that you don't need a cursor for this. Do a initial insert of all the objects with a count of 0.

Then do a

select ObjectName, count(*) as Total from Activity cross join objects where textdata like '%' + ObjectName + '%' group by ObjectName

This will be usable to do the update :

update T set T.Total = dtTotals.Total from Totals T inner join (previous query) dtTotals on ...

don't forget to delete the trace data... or flag it so that you don't count it twice.

Also you'll have to consider variables, names that can come in other object names (SPEmploye, SPEmploye_Insert). This is where all the work is, and I never really got around to perfecting it enough to let it run totally by itself. I always had to do a few checks manually.

Lynn Ransdell SSC Enthusiast Points: 150 More actions · Answer 4

Michael,

You can remove the tedium of manually altering each of the SP's (or searching the trace output) by writing a script to perform the heavy lifting... VBScript works nicely for tasks like this.

Personally, I'd opt for adding the necessary code to the SP's. I think this will put less stress on the server that continuously profiling. Additionally, you could also capture SP usage.

If you do modify the SPs, I recommend marking the inserted code with a before and after comment banner consisting of a unique string. This will make it much easier to automate the removal of that code later when you're finished with it (or, you could just modify the SP that is being invoked by all the other SP's to simply do nothing... that will make it easier to turn on/off as needed.

HTH