Find tables unused since last SQL Server restart

Question

Find tables unused since last SQL Server restart

Ryan McCauley

Right there with Babe

Points: 773
More actions
January 28, 2010 at 7:08 pm

#216101

Comments posted to this topic are about the item Find tables unused since last SQL Server restart
Ryan

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It's not since the last SQL start, it's since the last database start. If the DB is closed, taken offline, detached or restored, those stats are also cleared. Watch out for DBs set to autoclose

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

fjmorales Default port Points: 1451 More actions · Answer 2

I’m trying to run this t-sql but it gives me this error:

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near '?'.

Msg 102, Level 15, State 1, Line 9

Incorrect syntax near '?'.

Msg 102, Level 15, State 1, Line 14

Incorrect syntax near '?'.

Msg 102, Level 15, State 1, Line 19

Incorrect syntax near '?'.

John McC Hall of Fame Points: 3410 More actions · Answer 3

Same error here.

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

It looks like there are some non-printable characters that aren't spaces but look like spaces. Edit the whole thing onto a single line then re-format. Seems to work

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

John McC Hall of Fame Points: 3410 More actions · Answer 5

Using http://extras.sqlservercentral.com/prettifier/prettifier.aspx

WITH LastActivity (ObjectID, LastAction) AS

(

SELECT OBJECT_ID AS TableName,

last_user_seek AS LastAction

FROM sys.dm_db_index_usage_stats u

WHERE database_id = DB_ID(DB_NAME())

UNION

SELECT OBJ

ECT_ID AS TableName,

last_user_scan AS LastAction

FROM sys.dm_db_index_usage_stats u

WHERE database_id = DB_ID(DB_NAME())

UNION

SELECT OBJECT_ID AS TableName,

last_user_lookup AS LastAction

FROM sys.dm_db_index_usage_stats u

WHERE database_id = DB_ID(DB_NAME())

)

SELECT 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)

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:

jfordyce SSC Journeyman Points: 84 More actions · Answer 6

Is there a version of this script that would show the last time a SP was executed?? I'm really in need of that one 🙂

fjmorales Default port Points: 1451 More actions · Answer 7

fjmorales

Default port

Points: 1451

February 16, 2010 at 12:06 pm

#1119452

Thanks!!!

Ryan McCauley Right there with Babe Points: 773 More actions · Answer 8

Thanks for re-formatting the query - I'll see if I can get that fixed on the posted version of the script.

As far as Stored procs, it looks like you can do the same kind of thing as long as it's still in the cache, but not if the server has been restarted or the cache flushed since it was last run (much as with this script):

SELECT OBJECT_NAME(sys.dm_exec_sql_text.objectid),

sys.dm_exec_query_stats.*

FROM sys.dm_exec_query_stats

CROSS APPLY sys.dm_exec_sql_text (sys.dm_exec_query_stats.sql_handle)

WHERE sys.dm_exec_sql_text.dbid = db_id()

AND OBJECT_NAME(sys.dm_exec_sql_text.objectid) = 'yoursp'

Ryan

David-CH SSC Enthusiast Points: 164 More actions · Answer 9

copy the script onto Notepad. Remove all those tiny rectangular shapes (which were interpreted as spaces which cause errors), then copy the whole script back onto SSMS, parse again. You should get no error this time.:-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

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, MVP, M.Sc (Comp Sci)
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

Chris Holding Newbie Points: 6 More actions · Answer 11

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

Mo-455357 Grasshopper Points: 16 More actions · Answer 12

Is there to find unused functions and procedures as well? Or time since last call to these objects?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

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, MVP, M.Sc (Comp Sci)
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

bgrover Old Hand Points: 382 More actions · Answer 14

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 (DatabaseNamesysname,

TableNamesysname,

LastSelectdatetime)

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