May 29, 2008 at 1:22 pm
I've used the STATS_DATE function to determine the last time that statistics were updated. Issuing either "update statistics" or "alter index rebuild" modifies this date. Issuing a reorganize does not.
Is there any way to tell when an index was last rebuilt and/or reorganized? The STATS_DATE is close, but a little ambiguous since two actions I want to treat independently can cause the date to be modified.
Thanks!
Kyle
May 29, 2008 at 1:34 pm
“sys.objects” contains “modify_date”. This data can help you as in accordance with BOL “If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or altered.”
May 29, 2008 at 1:44 pm
I saw that date, but it doesn't appear to be modified during the rebuild/reorg operation.
use tempdb
go
create table accounts (corpID int not null, acctno int not null, isactive bit, acctname varchar(100))
go
alter table accounts add constraint PK_Accounts primary key clustered (corpid, acctno)
go
insert into accounts (corpid, acctno, isactive, acctname) values (1, 12345, 1, 'TEST')
insert into accounts (corpid, acctno, isactive, acctname) values (2, 12345, 1, 'TEST')
insert into accounts (corpid, acctno, isactive, acctname) values (3, 12345, 1, 'TEST')
go
SELECT
Tables.name AS [Table],
Indexes.name AS [Index],
STATS_DATE(Indexes.object_id, Indexes.index_id) AS [Updated]
FROM
sys.indexes Indexes JOIN
sys.tables Tables ON Indexes.object_id = Tables.object_id
WHERE
Indexes.type > 0 --No Heaps
ORDER BY
Tables.name ASC,
Indexes.name ASC
go
select modify_date, * from sys.objects where name like 'PK%'
go
alter index PK_Accounts ON accounts rebuild
go
SELECT
Tables.name AS [Table],
Indexes.name AS [Index],
STATS_DATE(Indexes.object_id, Indexes.index_id) AS [Updated]
FROM
sys.indexes Indexes JOIN
sys.tables Tables ON Indexes.object_id = Tables.object_id
WHERE
Indexes.type > 0 --No Heaps
ORDER BY
Tables.name ASC,
Indexes.name ASC
go
select modify_date, * from sys.objects where name like 'PK%'
go
drop table accounts
May 29, 2008 at 2:20 pm
check modify_date for table_name (not PK), i.e.
select modify_date, * from sys.objects where name = 'accounts'
and you will get what you are looking for 🙂
May 29, 2008 at 2:42 pm
Nice... 🙂
That's closer, but that date also changes when doing any modification on the table (add column, constraint, etc.).
If I rebuild a nonclustered index, the clustered index may not have been rebuilt -- yet the modified_date for the table will have changed.
Anywhere else to look?
May 29, 2008 at 3:27 pm
Kyle Neier (5/29/2008)
I've used the STATS_DATE function to determine the last time that statistics were updated. Issuing either "update statistics" or "alter index rebuild" modifies this date. Issuing a reorganize does not.Is there any way to tell when an index was last rebuilt and/or reorganized? The STATS_DATE is close, but a little ambiguous since two actions I want to treat independently can cause the date to be modified.
Thanks!
Kyle
Will this work?
Select
o.name As ObjectName
,i.name As IndexName
,i.type_desc As IndexType
,o.create_date
,o.modify_date
,stats_date(o.object_id, i.index_id) As stat_date
From
sys.objectso
Inner Join sys.indexesi On i.object_id = o.object_id
Where
o.type <> 'S';
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply