November 10, 2014 at 3:36 pm
Comments posted to this topic are about the item Last Accessed Datetime for table - Most easiest way
November 13, 2014 at 12:28 pm
How interesting! Knowing the last datetime SELECTed is irrelevant to me but I think I might be able to use the last_user_update column as a way to know when to refresh an application's local cache for specific lookup tables. I have lastupdate columns on my tables but DELETEs have always been a problem since the rows don't exist any longer.
November 25, 2014 at 7:18 am
Vignesh,
Thank you for taking the time to create and share this script. I did find a problem though. The date returned for some tables were incorrect when I compared them with the data returned by querying sys.dm_db_index_usage_stats alone. For the table I was testing I found that 3 rows from 2 DIFFERENT databases were returned and the MAX value for last accessed was from the msdb database. What I found was that querying sys.dm_db_index_usage_stats returns results from ALL databases. I get the correct result by modifying the WHERE clause from this:
where b.name = isnull(@TableName, b.name)
To this:
where b.name = isnull(@TableName, b.name) AND a.database_id = DB_ID()
Thanks again.
Lee
March 7, 2016 at 5:24 am
Nice script, thank you.
Table Value Constructors were introduced in SQL 2008, so this script doesn't work with versions below that. I've rewritten the query inside the function so it will work with SQL Server 2005:
create function fn_get_when_table_lastaccessed
(@TableName varchar(max))
returns @returntable table (TableName varchar(max),LastAccessed datetime)
as
begin
if @TableName = '*'
set @TableName = ''
set @TableName = nullif(@TableName,'')
INSERT INTO @returntable(TableName, LastAccessed)
SELECT SCHEMA_NAME(B.SCHEMA_ID) +'.'+OBJECT_NAME(B.OBJECT_ID) as tbl_name,
MAX(A.last_accessed_datetime ) last_accessed_datetime
FROM sys.tables B
CROSS APPLY(SELECT A.last_user_seek last_accessed_datetime
FROM sys.dm_db_index_usage_stats A
WHERE A.OBJECT_ID = B.OBJECT_ID
AND A.database_id = DB_ID()
UNION ALL
SELECT A.last_user_scan
FROM sys.dm_db_index_usage_stats A
WHERE A.OBJECT_ID = B.OBJECT_ID
AND A.database_id = DB_ID()
UNION ALL
SELECT A.last_user_lookup
FROM sys.dm_db_index_usage_stats A
WHERE A.OBJECT_ID = B.OBJECT_ID
AND A.database_id = DB_ID()) AS A
WHERE B.name = ISNULL(@TableName, B.Name)
GROUP BY SCHEMA_NAME(B.SCHEMA_ID) +'.'+OBJECT_NAME(B.OBJECT_ID)
ORDER BY last_accessed_datetime DESC, 1
return;
end
GO
March 7, 2016 at 7:46 am
I got nulls in the last accessed column for about half of the tables in a database and I know many of them have been accessed daily.
March 7, 2016 at 9:47 am
dlchase (3/7/2016)
I got nulls in the last accessed column for about half of the tables in a database and I know many of them have been accessed daily.
If the table is a heap (has no clustered index) it may not use an index to get data so there would be no rows in the sys.dm_db_index_usage_stats.
March 7, 2016 at 9:57 am
Below is one of the tables that was null in last accessed.
CREATE TABLE [dbo].[Payments](
[PaymentID] [int] IDENTITY(1,1) NOT NULL,
[RecordID] [int] NOT NULL CONSTRAINT [DF_Payments_RecordID] DEFAULT (0),
[CustomerID] [int] NOT NULL CONSTRAINT [DF_Payments_CustomerID] DEFAULT (0),
[InsuranceID] [int] NOT NULL CONSTRAINT [DF_Payments_InsuranceID] DEFAULT (0),
[CheckRef] [varchar](12) NULL,
[DatePaid] [smalldatetime] NULL,
[AmountPaid] [smallmoney] NOT NULL CONSTRAINT [DF_Payments_AmountPaid] DEFAULT (0),
[Posted] [bit] NOT NULL CONSTRAINT [DF_Payments_Posted] DEFAULT (0),
[InsPay] [bit] NOT NULL CONSTRAINT [DF_Payments_InsPay] DEFAULT (0),
[PaymentFrom] [tinyint] NOT NULL CONSTRAINT [DF_Payments_PaymentFrom] DEFAULT (1),
[CustomerPaid] [smallmoney] NOT NULL CONSTRAINT [DF_Payments_CustomerPaid] DEFAULT (0),
[InsurancePaid] [smallmoney] NOT NULL CONSTRAINT [DF_Payments_InsurancePaid] DEFAULT (0),
[PayType] [tinyint] NOT NULL CONSTRAINT [DF_Payments_PayType] DEFAULT (0),
[AcctgPosted] [bit] NOT NULL CONSTRAINT [DF_Payments_AcctgPosted] DEFAULT (0),
[NonPost] [bit] NOT NULL CONSTRAINT [DF_Payments_NonPost] DEFAULT (0),
CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED
(
[PaymentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
March 7, 2016 at 10:02 am
What index does the execution plan say it uses when you access it?
March 7, 2016 at 10:06 am
PaymentID
March 7, 2016 at 10:09 am
The script Jonathan supplied should return all usage, including heaps which are in index usage stats as index_id = 0. Think of it like RAID 0; everything gets a number, but it's a 0 because it's not really an index.
Where you're probably having issues is that index usage stats get reset. Before SQL 2012 it took restarting the SQL service to clear it out, and I'm sure there's some way you can do it manually that I've never done. Starting in SQL 2012, this DMV also got reset for each index when it was rebuilt, but not on reorgs.
March 7, 2016 at 10:13 am
And does this return anything in any of the date columns?
SELECT T.name, I.name, U.*
FROM sys.tables T
INNER JOIN sys.dm_db_index_usage_stats U
ON U.OBJECT_ID = T.OBJECT_ID
INNER JOIN sys.indexes I ON I.index_id = U.index_id AND I.object_id=U.object_id
WHERE T.Name= 'Payments'
ORDER BY 1,2
March 7, 2016 at 10:49 am
No
March 8, 2016 at 5:44 am
dlchase (3/7/2016)
PaymentID
Try this:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
TableName = SCHEMA_NAME(t.[schema_id]) +'.'+t.Name,
IndexName = i.name,
s.last_user_seek,
s.last_user_scan,
s.last_user_lookup,
s.last_user_update
FROM sys.tables t
LEFT JOIN sys.dm_db_index_usage_stats s
ON s.[object_id] = t.[object_id]
AND s.database_id = DB_ID()
LEFT JOIN sys.indexes i
ON i.[object_id] = s.[object_id]
AND i.index_id = s.index_id
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 8, 2016 at 9:34 am
Thank you, that works great and I can tweak it to fit my needs.
March 10, 2016 at 4:40 pm
Thanks for this one.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply