February 10, 2016 at 4:05 am
How do we get which tables / procedure / views / functions are not used more than 2 months in the DB?
February 10, 2016 at 4:16 am
You need to set up some sort of auditing that captures every use of those objects. You can't do it retrospectively. Beware, you may find yourself capturing an awful lot of audit data!
John
February 10, 2016 at 4:26 am
Maybe try this:
Tables:
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'db_name')
AND OBJECT_ID=OBJECT_ID('table_name')
Procedures:
SELECT * FROM sys.objects
WHERE TYPE = 'P' AND DATEDIFF(D,modify_date, GETDATE()) < 7
Databases:
WITH agg AS
(
SELECT
max(last_user_seek) last_user_seek,
max(last_user_scan) last_user_scan,
max(last_user_lookup) last_user_lookup,
max(last_user_update) last_user_update,
sd.name dbname
FROM
sys.dm_db_index_usage_stats, master..sysdatabases sd
WHERE
database_id = sd.dbid group by sd.name
)
SELECT
dbname,
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT dbname, last_user_seek, NULL FROM agg
UNION ALL
SELECT dbname, last_user_scan, NULL FROM agg
UNION ALL
SELECT dbname, last_user_lookup, NULL FROM agg
UNION ALL
SELECT dbname, NULL, last_user_update FROM agg
) AS x (dbname, last_read, last_write)
GROUP BY
dbname
ORDER BY 2;
February 10, 2016 at 4:50 am
Nice queries, but you should include the caveats so that the original poster knows what they're getting.
The first two queries will show if a table or a stored procedure have been modified, not if a stored procedure has been called as part of a query.
The second query using index usage stats will show the use of tables, but the dynamic management view (DMV) in use, sys.dm_db_index_usage_stats, gets reset if the server is restarted, or the database is detached or shutdown. That said, this is still a good reference for which tables have been accessed. You just need to know the limits.
The best way to know what queries are run, which stored procedures and functions are used, is to set up auditing, which, in 2008, means using trace events.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2016 at 5:07 am
Thanks for all swift response I hope we better to go with DB level audit and wait for few months and let us query from the Output file.
Because : some of table don't have clustered or non cluseterd index so we cannot say100% , and also if Proc cache changed or cleared it would be partial also :-).
I am going to start DB Audit with DDL and changes and Access of any schema pattern.
February 11, 2016 at 6:43 am
I do use the following 'BLOB' of code to 'track' usage. (Edit : Sorry, tables only).
This is run repeatedly and shows the usage of tables between two calls.
Here the code is geared towards the short term 'monitoring' and shows which tables are actually used.
Taking a 'permanent' copy of the ##before table and storing this, this script can be altered to do the monitoring over a larger period. And changing what is visible will allow you to see the tables which have not been touched during the period. (Select the tables not present in ##Changed_counts)
--
-- ben brugman
-- aka: stef ten bras
-- 20160211
-- script to monitor usage in a database.
--
if object_id('tempdb..##before') is not null drop table ##before
if object_id('tempdb..##AFTER') is not null select * into ##before from ##AFTER
if object_id('tempdb..##AFTER') is not null drop table ##AFTER
-----------------------------------------------------------------------------------------------------------------
SELECT SCHEMA_NAME(o.schema_id) AS [schema]
,object_name(i.object_id ) AS
,p.rows
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
,i.type_desc
,i.name
,i.index_id
,'-----' [-----]
-- ,o.*
,'------' [------]
-- ,i.*
,'-------' [-------]
into ##AFTER
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE --i.type_desc = 'HEAP' and
SCHEMA_NAME(o.schema_id) <> 'sys' and database_id = db_id()
ORDER BY p.rows desc
-- select * from ##after
exec ('if object_id(''tempdb..##before'') is null select * into ##before from ##AFTER')
--
-- Isolate all rows in the dataset which have altered.
-- Keep them in ##Changed_counts
--
if exists(select * from tempdb.INFORMATION_SCHEMA.TABLES where TABLE_NAME = '##Changed_counts') drop table ##Changed_counts
select * into ##Changed_counts from (
select * from ##AFTER
except
select * from ##before ) as xxx
-- select * from ##changed_counts
--
-- Show all rows which are altered
--
select
a.
as table_name
,A.NAME AS INDEX_NAME
,A.INDEX_ID
,a.user_seeks - b.user_seeks delta_seeks
,a.user_scans - b.user_scans delta_scans
,a.user_lookups - b.user_lookups delta_lookups
,a.user_updates - b.user_updates delta_updates
, a.*
from ##Changed_counts A join ##before B on a.
= b.
AND A.INDEX_ID = B.INDEX_ID
if exists(select * from tempdb.INFORMATION_SCHEMA.TABLES where TABLE_NAME = '##Changed_counts') drop table ##Changed_counts
I find this piece of coding usefull for several monitoring functions.
Please inform us, if this was/is usefull.
Ben
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply