Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Find tables unused since last SQL Server restart Expand / Collapse
Author
Message
Posted Tuesday, February 16, 2010 3:24 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:10 PM
Points: 42,986, Visits: 36,141
There are a number of things that can cause a stored proc's plan to be dropped from cache. Stats updates, index rebuilds, table alterations, etc. Be very careful relying on what's in sys.dm_exec_cached_plans
http://sqlinthewild.co.za/index.php/2009/07/10/why-the-dmvs-are-not-a-replacement-for-sql-trace/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #866524
Posted Thursday, November 4, 2010 3:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 5, 2014 6:33 AM
Points: 1, Visits: 55
This works for SQL 2005 and above but I'm having problems with SQL 2000; can anyone rewrite for SQL 2000?

Is there and equivalent for sys.dm_db_index_usage_stats etc in SQL 2000?

Thanks in advance
Post #1015736
Posted Thursday, November 4, 2010 7:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 7, 2014 9:15 AM
Points: 3, Visits: 10
Is there to find unused functions and procedures as well? Or time since last call to these objects?

Thanks
Post #1015853
Posted Thursday, November 4, 2010 8:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:10 PM
Points: 42,986, Visits: 36,141
Chris Holding (11/4/2010)
This works for SQL 2005 and above but I'm having problems with SQL 2000; can anyone rewrite for SQL 2000?


No, because the information is not there in SQL 2000.
If you want this kind of info in SQL 2000, you'll need a trace (SQL Trace)

Is there and equivalent for sys.dm_db_index_usage_stats etc in SQL 2000?

No



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1015883
Posted Friday, November 5, 2010 7:06 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 24, 2014 10:35 AM
Points: 362, Visits: 61
So I added a bit to it and made it so it will run aginst every database on the server. Hope this helps someone.

CREATE TABLE #LastAccess (DatabaseName	sysname,
TableName sysname,
LastSelect datetime)

DECLARE @SQL varchar(max)
SET @SQL = 'USE ?; '
+ 'WITH LastActivity (ObjectID, LastAction) AS '
+ '( '
+ 'SELECT object_id, '
+ 'last_user_seek as LastAction '
+ 'FROM sys.dm_db_index_usage_stats '
+ 'WHERE database_id = db_id(db_name()) '
+ 'UNION SELECT object_id, '
+ 'last_user_scan as LastAction '
+ 'FROM sys.dm_db_index_usage_stats '
+ 'WHERE database_id = db_id(db_name()) '
+ 'UNION SELECT object_id, '
+ 'last_user_lookup as LastAction '
+ 'FROM sys.dm_db_index_usage_stats '
+ 'WHERE database_id = db_id(db_name()) '
+ ') '
+ 'INSERT INTO #LastAccess '
+ 'SELECT DB_NAME() AS DatabaseName, '
+ 'OBJECT_NAME(so.object_id) AS TableName, '
+ 'MAX(la.LastAction) as LastSelect '
+ 'FROM sys.objects so '
+ 'LEFT JOIN LastActivity la '
+ 'ON so.object_id = la.ObjectID '
+ 'WHERE so.type = ''U'' '
+ 'AND so.object_id > 100 '
+ 'GROUP BY OBJECT_NAME(so.object_id) '
+ 'ORDER BY OBJECT_NAME(so.object_id) '

EXEC sp_MSforeachdb @SQL

SELECT * FROM #LastAccess




Post #1016508
Posted Thursday, July 21, 2011 10:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2012 11:28 PM
Points: 8, Visits: 19
Many thanks for such useful script !!!!
Post #1146421
Posted Thursday, August 4, 2011 9:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 27, 2012 10:47 AM
Points: 1, Visits: 16
Very helpful. Thanks for the script
Post #1154324
Posted Thursday, August 4, 2011 9:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:44 AM
Points: 21,405, Visits: 9,617
You need to wrap the first ? with [?] so that non-standard db names don't fail.
Post #1154343
Posted Friday, August 12, 2011 8:02 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 24, 2014 10:35 AM
Points: 362, Visits: 61
Yes, thanks good catch!


Post #1159221
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse