October 29, 2015 23:38 by
Peter
Now, I will guide you how to check last accessed and modified table data in SQL Data. And here is the script that I use:
/*************************************************/
SELECT OBJECT_NAME(OBJECT_ID) AS [Object_Name], last_user_update,last_user_scan ,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'Access') AND OBJECT_ID=OBJECT_ID('test')
/*************************************************/
--Note: This will return null if sql server restarted and after that data is not accessed
Demo:
/**** Create New Database ****/
Create Database Access
GO
use Access
GO
/**** Create Table ****/
Create Table test (id int , name varchar (100))
GO
/**** Insert New Record ****/
Insert into test values (1,'saurabh')
Insert into test values (2,'Sumit')
GO
/**** After insertion Check Stats ****/
SELECT OBJECT_NAME(OBJECT_ID) AS [Object_Name], last_user_update,last_user_scan ,*
FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID( 'Access') AND OBJECT_ID=OBJECT_ID('test')
Object_Name last_user_update last_user_scan
test 2015-10-29 08:39:54.100 NULL
Here last user scan is null because we are just inserted data, If we read data then we'll get read time also
So now we are going to read data and see if we get last user scan. Now, write the following code:
Select * from access.dbo.test
GO
/**** Checking Stats ****/
SELECT OBJECT_NAME(OBJECT_ID) AS [Object_Name], last_user_update,last_user_scan ,*
FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID( 'Access') AND OBJECT_ID=OBJECT_ID('test')
So here we just updated table , didnt read. You can see the result on the following picture:
Similarly if we want to see how current indexes are performing on table we can see user scan , user seek or look ups.
HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.