October 6, 2010 at 10:18 am
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
October 6, 2010 at 10:41 am
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
October 6, 2010 at 10:46 am
You could take a look at this:
http://msdn.microsoft.com/en-us/library/aa964124(SQL.90).aspx
October 6, 2010 at 11:07 am
Lowell (10/6/2010)
anything long termyou'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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply