SQL Server Performance Forum – Threads Archive

Table usage

I’m looking for a way to map out usage of user tables in a database. I would like to find a way to track usage of a table over a 24 hour period so that I can create a report to schedule maintenance on individual tables. I need to be able to tell that it would be OK to work on table X between 3pm to 5pm each day but table Y has it’s period of least activity from 7pm to 9pm, or something like that. This is for a database that is used 24 hours a day, 7 days a week and downtime is virtually imposible to get scheduled. Any suggestions or assistance would be greatly appreciated.

Good question. I dont think there is a way where system table will maintain each and every hit into the table. Only way that I could think of is by starting trace and saving the results into a table. After this you can write some queries to run reports as needed,

I tried setting up a trace last week and it didn’t work out very well. I thought I could set the trace to only look at user tables by limiting the object type to user tables. The trace is using sysobjects and I looked in that table and saw that user tables were listed as object type U. I tried to use that a the limiting value in the trace and it wouldn’t accept it. It said it would only take numeric values. At that point I wasn’t sure how to move forward.

Well, did you try this? In the filters tab of profiler, when you select objectId or objectType filter, there is a check box in the bottom (Exclude System Ids) which appears. If you check it, it puts a filter on ObjectId (Greater than or Equal 100). Try this and let us know if that helped at all. Thanks
-Rajeev Lahoty

I tried that too. Even with that I am still unable to tell what table is being used. In fact I do not get any information in the object column of the Profiler results. Very frustrating!!

To monitor server usage you’ve PERFMON (SYSMON) and for Databases PROFILER is best tool and these type of requirements can be achieved using server side trace, rather than running from a client’s machine. If your company can afford the cost of third party tools then use Lumigent’s Entegra or findout relevant audit tools from the Tools pane above. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>