Setting a SQL Server Trace to find the number of times user tables are acsessed in a DataBase in a particular period of time.

• pkposani

  SSC-Addicted

  Points: 426

  More actions

  October 6, 2010 at 10:18 am

  #157241

  Hi All,

  I'm trying to find how many times that my User tables are accessed in my DB for a particular period of time.I've planned to set up a trace but unable to figure out what events I should use?

  Any help on this is greatly appreciated..

  Thx

  Pavan Posani

• Lowell

  SSC Guru

  Points: 323527

  More actions

  October 6, 2010 at 10:41 am

  #1232804

  not that easy, because the objectname themselves are not stored anywhere long term as far as SELECT/UPDATE/DELETE are involved; there are some DMV's that keep track of some recent events, but nothing over a long time.

  here's an example that assumes if the index was used, it was accessed:

  /*--results

  Schema Table_Or_View last_read last_write

  dbo spt_values 2010-10-04 13:05:59.787 NULL

  dbo TallyCalendar 2010-09-24 14:19:11.880 2010-09-24 14:19:11.880

  */

  WITH agg AS

  (

  SELECT

  [object_id],

  last_user_seek,

  last_user_scan,

  last_user_lookup,

  last_user_update

  FROM

  sys.dm_db_index_usage_stats

  WHERE

  database_id = DB_ID()

  )

  SELECT

  [Schema] = OBJECT_SCHEMA_NAME([object_id]),

  [Table_Or_View] = OBJECT_NAME([object_id]),

  last_read = MAX(last_read),

  last_write = MAX(last_write)

  FROM

  (

  SELECT [object_id], last_user_seek, NULL FROM agg

  UNION ALL

  SELECT [object_id], last_user_scan, NULL FROM agg

  UNION ALL

  SELECT [object_id], last_user_lookup, NULL FROM agg

  UNION ALL

  SELECT [object_id], NULL, last_user_update FROM agg

  ) AS x ([object_id], last_read, last_write)

  GROUP BY

  OBJECT_SCHEMA_NAME([object_id]),

  OBJECT_NAME([object_id])

  ORDER BY 1,2;

  anything long term

  you'll need to put something in place to audit the tables or trace all sql statements.

  For example if you create a server side DML trace like this one (which also creates a view) after it has been running a while, you could search the TextData column, which has all the commands executed, for a specific tablename like this:

  select * from sp_mytrace

  where textdata like '%MyTableName%'

  AND STARTTIME

  BETWEEN '2010-09-01 00:00:00.000'

  AND '2010-10-01 00:00:00.000'

  you could then modify it to have the counts you were looking for, but you need to visualize the data first.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• raistlinx

  Ten Centuries

  Points: 1345

  More actions

  October 6, 2010 at 10:46 am

  #1232813

  You could take a look at this:

  http://msdn.microsoft.com/en-us/library/aa964124(SQL.90).aspx

• Adi Cohn-120898

  SSC-Dedicated

  Points: 33944

  More actions

  October 6, 2010 at 11:07 am

  #1232833

  Lowell (10/6/2010)

  ---

  anything long term

  you'll need to put something in place to audit the tables or trace all sql statements.

  For example if you create a server side DML trace like this one (which also creates a view) after it has been running a while, you could search the TextData column, which has all the commands executed, for a specific tablename like this:

  select * from sp_mytrace

  where textdata like '%MyTableName%'

  AND STARTTIME

  BETWEEN '2010-09-01 00:00:00.000'

  AND '2010-10-01 00:00:00.000'

  you could then modify it to have the counts you were looking for, but you need to visualize the data first.

  The problem with trace is that I can use a query that used a view that referenced that table, but the trace won’t be able to see it. There are also other cases that can modify the results. For example if I have the table's name as a remark, it will show up in the trace. Unfortunately I don’t know of a way to get that information with SQL Server 2005’s tools (SQL Server 2008 had audit object that can be used for that). Most chances are that there is a third party tool that can give you the requested results, but I don’t know any. Maybe some of the other forums’ user can write about such a tool.

  Adi

  --------------------------------------------------------------
  To know how to ask questions and increase the chances of getting asnwers:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/