InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 19:24:04 GMT20http://www.sqlservercentral.com/Forums/Topic1423256-392-1.aspx[quote][b]Jason-299789 (2/25/2013)[/b][hr]wouldn't the Database level Audit be enough, as this seems to handle events for Select, Insert, Update, and Delete Actions? [/quote]That should work too, might have to aggregate, I don't know offhand how the audit shows results.Mon, 25 Feb 2013 08:41:13 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1423256-392-1.aspx[quote][b]GilaMonster (2/25/2013)[/b][hr][quote][b]Sean Lange (2/25/2013)[/b][hr]That only shows index usage. If your queries are not sargable or you have a heap your results may not be exactly what you are looking for.[/quote]A scan is a use of an index, and the index usage DMV does include heaps and clusters. SELECT * from HeapTable will put an entry in that DMV.[/quote]I didn't realize that heaps would end up in there. Thanks for the correction Gail.Mon, 25 Feb 2013 08:36:06 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1423256-392-1.aspxGail,wouldn't the Database level Audit be enough, as this seems to handle events for Select, Insert, Update, and Delete Actions? Or would the overhead be significant?Mon, 25 Feb 2013 08:17:32 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1423256-392-1.aspx[quote][b]Sean Lange (2/25/2013)[/b][hr]That only shows index usage. If your queries are not sargable or you have a heap your results may not be exactly what you are looking for.[/quote]A scan is a use of an index, and the index usage DMV does include heaps and clusters. SELECT * from HeapTable will put an entry in that DMV.Mon, 25 Feb 2013 08:09:24 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1423256-392-1.aspxThis kind of thing is incredibly difficult to do.Probably best thing you can do is set up an extended events session for Object accessed (I think it's in the 2008 XE), and put that to a event bucketizer target (bucketize on the object id). Watch the overhead though, that's a frequent eventNow that won't tell you what tables aren't used, it will tell you which tables are known to be used. Run that for long enough (where long enough covers an entire business cycle) and you can use that as a place to start for deciding which tables are not known to be used. Doesn't mean they aren't, just means they might not be,Mon, 25 Feb 2013 08:07:45 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1423256-392-1.aspxHow can a correct that?Mon, 25 Feb 2013 08:01:03 GMTdndaughteryhttp://www.sqlservercentral.com/Forums/Topic1423256-392-1.aspx[quote][b]dndaughtery (2/22/2013)[/b][hr]Found it![/quote]Be careful now. That only shows index usage. If your queries are not sargable or you have a heap your results may not be exactly what you are looking for.Mon, 25 Feb 2013 07:10:12 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1423256-392-1.aspxFound it!USE MyDBGOSET ANSI_WARNINGS OFF;SET NOCOUNT ON;GOWITH 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)where OBJECT_SCHEMA_NAME([object_id]) = 's012sod'GROUP BY OBJECT_SCHEMA_NAME([object_id]), OBJECT_NAME([object_id])ORDER BY last_read asc;Fri, 22 Feb 2013 14:55:56 GMTdndaughteryhttp://www.sqlservercentral.com/Forums/Topic1423256-392-1.aspxThis is for an adhoc enviroment for report development.Fri, 22 Feb 2013 14:27:03 GMTdndaughteryhttp://www.sqlservercentral.com/Forums/Topic1423256-392-1.aspxHow are you going to handle those once a year type of things? I can understand wanting to do this periodically but weekly? Are you really going to drop tables if they haven't been accessed within the last week? What about times when overall activity is low, like the holidays? This should not be an automated type of process. It requires manual intervention or you will end up dropping something that is needed.Fri, 22 Feb 2013 14:22:26 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1423256-392-1.aspxBut Im needing to do it on a weekly basisFri, 22 Feb 2013 14:10:16 GMTdndaughteryhttp://www.sqlservercentral.com/Forums/Topic1423256-392-1.aspxYes Sean thats exactly what m trying to do. Trying to find out which table can be dropped.Fri, 22 Feb 2013 14:07:43 GMTdndaughteryhttp://www.sqlservercentral.com/Forums/Topic1423256-392-1.aspx[quote][b]dndaughtery (2/22/2013)[/b][hr]I need a query to show all tables in a database sorted by last datetime queried ordered oldest first. Can someone help me with this?[/quote]There is nothing by default in sql that tracks when tables are accessed. Are you trying to find tables are likely to be no longer needed? This kind of thing can be really tricky because there are usually processes that run only once a year but are vital.Fri, 22 Feb 2013 14:04:54 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1423256-392-1.aspxI need a query to show all tables in a database sorted by last datetime queried ordered oldest first. Can someone help me with this?Fri, 22 Feb 2013 13:58:11 GMTdndaughtery