Last Index Rebuild - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Last Index Rebuild

Author

Topic

daniel.nospam
Starting Member

11 Posts

Posted - 2011-11-17 : 07:42:57

Is there any metadata info which tells when a INDEX was last reorganised or rebuilt?

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-11-17 : 08:53:12

No

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

daniel.nospam
Starting Member

11 Posts

Posted - 2011-11-17 : 09:07:19

Thanks. Thought so.

Kristen
Test

22859 Posts

Posted - 2011-11-17 : 10:20:53

modify_date in sys.objects perhaps? Doubt it for Reorganise, perhaps for Rebuild?

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-17 : 11:47:41

Try this..


SELECT
object_name(si.[object_id]) AS [TableName] 
, CASE 
WHEN si.[index_id] = 0 then 'Heap'
WHEN si.[index_id] = 1 then 'CL'
WHEN si.[index_id] BETWEEN 2 AND 250 THEN 'NC ' + RIGHT('00' + convert(varchar, si.[index_id]), 3)
ELSE ''
END AS [IndexType] 
, si.[name] AS [IndexName] 
, si.[index_id] AS [IndexID] 
, CASE
WHEN si.[index_id] BETWEEN 1 AND 250 AND STATS_DATE (si.[object_id], si.[index_id]) < DATEADD(m, -1, getdate()) 
THEN '!! More than a month OLD !!'
WHEN si.[index_id] BETWEEN 1 AND 250 AND STATS_DATE (si.[object_id], si.[index_id]) < DATEADD(wk, -1, getdate()) 
THEN '! Within the past month !'
WHEN si.[index_id] BETWEEN 1 AND 250 THEN 'Stats recent'
ELSE ''
END AS [Warning] 
, STATS_DATE (si.[object_id], si.[index_id]) AS [Last Stats Update]
FROM
sys.indexes AS si
WHERE
OBJECTPROPERTY(si.[object_id], 'IsUserTable') = 1
ORDER
BY [TableName], si.[index_id]

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-11-17 : 13:25:30

quote:
Originally posted by Kristen

modify_date in sys.objects perhaps? Doubt it for Reorganise, perhaps for Rebuild?

modify_date will be affected by numerous operation other than REBUILD (ALTER INDEX SET as an example). so it is not right to use it.

quote:

Originally posted by sqlfresher2k7

Try this..


SELECT
object_name(si.[object_id]) AS [TableName] 
, CASE 
WHEN si.[index_id] = 0 then 'Heap'
WHEN si.[index_id] = 1 then 'CL'
WHEN si.[index_id] BETWEEN 2 AND 250 THEN 'NC ' + RIGHT('00' + convert(varchar, si.[index_id]), 3)
ELSE ''
END AS [IndexType] 
, si.[name] AS [IndexName] 
, si.[index_id] AS [IndexID] 
, CASE
WHEN si.[index_id] BETWEEN 1 AND 250 AND STATS_DATE (si.[object_id], si.[index_id]) < DATEADD(m, -1, getdate()) 
THEN '!! More than a month OLD !!'
WHEN si.[index_id] BETWEEN 1 AND 250 AND STATS_DATE (si.[object_id], si.[index_id]) < DATEADD(wk, -1, getdate()) 
THEN '! Within the past month !'
WHEN si.[index_id] BETWEEN 1 AND 250 THEN 'Stats recent'
ELSE ''
END AS [Warning] 
, STATS_DATE (si.[object_id], si.[index_id]) AS [Last Stats Update]
FROM
sys.indexes AS si
WHERE
OBJECTPROPERTY(si.[object_id], 'IsUserTable') = 1
ORDER
BY [TableName], si.[index_id]

This query is about statistics update date, and it is not safe to use it for the last rebuild/reorganize date.

The following script will demonstrate why it is not good to use either of the suggested approaches.


IF OBJECT_ID(N'dbo.T1', N'U') IS NOT NULL
   DROP TABLE dbo.T1;

CREATE TABLE dbo.T1
(
   id INT NOT NULL IDENTITY,
   
   CONSTRAINT T1__PK
      PRIMARY KEY(id),
   
   number INT NOT NULL,
   
   CONSTRAINT T1__UQ__number
      UNIQUE(number)
);

INSERT INTO dbo.T1(number)
   SELECT number
     FROM master..spt_values AS V
    WHERE V.type = N'P';

SELECT 'Before Anything' AS phase,
       STATS_DATE(S.object_id, S.stats_id) AS statistics_update_date,
       O.modify_date
  FROM sys.objects AS O
       INNER JOIN
       sys.indexes AS I
       ON I.object_id = O.object_id
       INNER JOIN
       sys.stats AS S
       ON S.object_id = O.object_id
          AND S.stats_id = I.index_id
 WHERE O.type = 'U'
   AND O.name = N'T1'
   AND I.name = N'T1__UQ__number';

WAITFOR DELAY '00:00:02';

ALTER INDEX T1__UQ__number
   ON dbo.T1
   REORGANIZE;

DECLARE @reorganize_time TIME = CONVERT (TIME, GETDATE())

SELECT 'After REORGANIZE' AS phase,
       STATS_DATE(S.object_id, S.stats_id) AS statistics_update_date,
       O.modify_date
  FROM sys.objects AS O
       INNER JOIN
       sys.indexes AS I
       ON I.object_id = O.object_id
       INNER JOIN
       sys.stats AS S
       ON S.object_id = O.object_id
          AND S.stats_id = I.index_id
 WHERE O.type = 'U'
   AND O.name = N'T1'
   AND I.name = N'T1__UQ__number';

WAITFOR DELAY '00:00:02';

ALTER INDEX T1__UQ__number
   ON dbo.T1
   REBUILD;

DECLARE @rebuild_time TIME = CONVERT (TIME, GETDATE())

SELECT 'After REBUILD' AS phase,
       STATS_DATE(S.object_id, S.stats_id) AS statistics_update_date,
       O.modify_date
  FROM sys.objects AS O
       INNER JOIN
       sys.indexes AS I
       ON I.object_id = O.object_id
       INNER JOIN
       sys.stats AS S
       ON S.object_id = O.object_id
          AND S.stats_id = I.index_id
 WHERE O.type = 'U'
   AND O.name = N'T1'
   AND I.name = N'T1__UQ__number';

WAITFOR DELAY '00:00:02';

UPDATE STATISTICS dbo.T1 T1__UQ__number;

SELECT 'After UPDATE STATISTICS' AS phase,
       STATS_DATE(S.object_id, S.stats_id) AS statistics_update_date,
       O.modify_date
  FROM sys.objects AS O
       INNER JOIN
       sys.indexes AS I
       ON I.object_id = O.object_id
       INNER JOIN
       sys.stats AS S
       ON S.object_id = O.object_id
          AND S.stats_id = I.index_id
 WHERE O.type = 'U'
   AND O.name = N'T1'
   AND I.name = N'T1__UQ__number';

WAITFOR DELAY '00:00:02';

ALTER INDEX T1__UQ__number
   ON dbo.T1
   SET (ALLOW_PAGE_LOCKS = ON);

SELECT 'After ALTER INDEX' AS phase,
       STATS_DATE(S.object_id, S.stats_id) AS statistics_update_date,
       O.modify_date
  FROM sys.objects AS O
       INNER JOIN
       sys.indexes AS I
       ON I.object_id = O.object_id
       INNER JOIN
       sys.stats AS S
       ON S.object_id = O.object_id
          AND S.stats_id = I.index_id
 WHERE O.type = 'U'
   AND O.name = N'T1'
   AND I.name = N'T1__UQ__number';

WAITFOR DELAY '00:00:02';

DELETE dbo.T1
 WHERE number > 1;
 
IF EXISTS(SELECT T.number
            FROM dbo.T1 AS T
           WHERE T.number > 1)
   SELECT 'Something wen wrong';

SELECT 'After DELETE and SELECT (trying to apply AUTO_UPDATE_STATISTICS)' AS phase,
       STATS_DATE(S.object_id, S.stats_id) AS statistics_update_date,
       O.modify_date
  FROM sys.objects AS O
       INNER JOIN
       sys.indexes AS I
       ON I.object_id = O.object_id
       INNER JOIN
       sys.stats AS S
       ON S.object_id = O.object_id
          AND S.stats_id = I.index_id
 WHERE O.type = 'U'
   AND O.name = N'T1'
   AND I.name = N'T1__UQ__number';

SELECT 'By the way actual times are: ' BTW,
       @rebuild_time AS rebuild_time,
       @reorganize_time AS reorganize_time;

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

Kristen
Test

22859 Posts

Posted - 2011-11-17 : 14:20:27

quote:
Originally posted by malpashaa

modify_date will be affected by numerous operation other than REBUILD (ALTER INDEX SET as an example). so it is not right to use it.

Sure; I don't know about everyone else, but we don't change settings, or anything else related to an index, on an hourly / daily basis ...

The likelihood is that if we change an index it will be to change columns etc. and that will cause a new index to be built - same as doing a reindex really.

So as a rule-of-thumb it may have some merit, but I haven't tested WHEN the modify date gets set, so it may not be any use in practice if it doesn't get set by ReIndex.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-11-17 : 19:51:49

Of absolutely no use to your current predicament but perhaps a thought for the future...
We put a trigger on the database which logs schema changes to a table. Included in the log is Who, What and When. It aids in troubleshooting to know if/when an object was changed and, of course, it is used to find the recipient of the prestigous "Hugh Phupped Duck" award.

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

Kristen
Test

22859 Posts

Posted - 2011-11-18 : 03:24:24

I ought to do that ... do you have some sample code lying around? Bound to be more polished than Blog-padding one-line examples ...

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-11-18 : 10:48:13

[CODE]CREATE trigger [ddl_ObjectChange]
on DATABASE
with execute as 'dbo'
after
CREATE_FUNCTION,
CREATE_INDEX,
CREATE_PROCEDURE,
CREATE_TABLE,
CREATE_TRIGGER,
CREATE_VIEW,
ALTER_FUNCTION,
ALTER_INDEX,
ALTER_PROCEDURE,
ALTER_TABLE,
ALTER_TRIGGER,
ALTER_VIEW,
DROP_FUNCTION,
DROP_INDEX,
DROP_PROCEDURE,
DROP_TABLE,
DROP_TRIGGER,
DROP_VIEW
as
begin
set NoCount ON

DECLARE @data XML;
set @data = EVENTDATA();

INSERT DBAdmin.dbo.dbaObjectChangeHistory (
ObjectDatabase,
ObjectName,
ChangeDate,

EventType,
DbaName,
TSqlCommand
)
VALUES (
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)'),
GETDATE(),

@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
)
end[/CODE]I did work at a place where we also preserved the original script for SPs, functions, et al in this table so that it was easier to rollback any boo-boos.

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

Kristen
Test

22859 Posts

Posted - 2011-11-18 : 11:00:54

Smashing

, many thanks B.K.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-11-18 : 11:35:02

Mi gusto!

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

daniel.nospam
Starting Member

11 Posts

Posted - 2011-11-18 : 11:40:13

Thanks Kristen, but sys.indexes is not an extension of sys.objects. It doesn't contain a modify_date, and the modify_date of the parent object (table) is no good for me.

daniel.nospam
Starting Member

11 Posts

Posted - 2011-11-18 : 11:51:41

Thanks Muhammad. Yeah I tried before with "sys.dm_db_index_usage_stats" and "STATS_DATE" but they don't give me the answer I need (as in my initial post).

Kristen
Test

22859 Posts

Posted - 2011-11-18 : 12:41:03

quote:
Originally posted by daniel.nospam

Thanks Kristen, but sys.indexes is not an extension of sys.objects. It doesn't contain a modify_date, and the modify_date of the parent object (table) is no good for me.

My mistake, sorry. I though sysindexes had an entry in sysobjects (for the Index itself) as well as a link to a parent-object (for the table)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-11-18 : 13:19:04

Trace for Object:Altered (trace_event_id = 164). Filter on ObjectType LIKE '%IX%' and/or IndexID NOT NULL.

Make sure to include StartTime.

Can add it to the default trace, so it automatically starts when SQL Server restarts.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-11-18 : 13:25:07

By the way, who cares when the last rebuild was? Just rebuild those that need it, and don't rebuild those that don't.

Or are you measuring how quickly they become fragmented?

Kristen
Test

22859 Posts

Posted - 2011-11-26 : 06:37:35

"Or are you measuring how quickly they become fragmented?"

We do that by recording the output from DBCC SHOWCONTIG into a table (and we also flag the ones that were sufficiently fragmented that they triggered a Rebuild) so we know when they were rebuilt, and how they fragmented over time.

Kristen
Test

22859 Posts

Posted - 2011-12-02 : 06:05:27

quote:
Originally posted by Bustaz Kool

[CODE]CREATE trigger [ddl_ObjectChange]
on DATABASE
with execute as 'dbo'
after
CREATE_FUNCTION,
CREATE_INDEX,
...

I went with


AFTER DDL_DATABASE_LEVEL_EVENTS

which seems to be working well, so far

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources