Index Rebuild

Question

Post reply

Index Rebuild

ningaraju.n

SSCrazy

Points: 2322
More actions
October 25, 2010 at 9:23 pm

#240144

Hi All,
I need to know when(date and time) the given index has been rebuild or reorganized or De
fragmented?
Please do the favor

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

S3ssion SSChasing Mays Points: 602 More actions · Answer 1

Run the following, it will produce "Last_Updated"

DBCC SHOW_STATISTICS ('TABLENAME',INDEXNAME );

| If in Doubt...don't do it!! |

Adiga One Orange Chip Points: 27284 More actions · Answer 2

STATS_DATE() gives you the date on which the statistics for an index were update (update stats or alter index). Here is an example

SELECT

B.Name,A.Name as IndexName,

STATS_DATE ( a.id , indid ) as IndexCreatedDate

From sysindexes A, sysobjects B

where a.id=b.id

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

That info is not recorded anywhere automatically.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ningaraju.n SSCrazy Points: 2322 More actions · Answer 4

Then how can we get that info. Because i have to know which indexes need to be De fragmented ,

any other ways?

Adiga One Orange Chip Points: 27284 More actions · Answer 5

Here is what mentioned in MSDN

"STATS_DATE Returns the date of the most recent update for statistics on a table or indexed view."

Read more on this here

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

ningaraju.n SSCrazy Points: 2322 More actions · Answer 6

ningaraju.n

SSCrazy

Points: 2322

October 26, 2010 at 5:39 am

#1242166

Thanks pradeep

steveb. SSC-Forever Points: 46733 More actions · Answer 7

you can not get that information from sql, however you should base your index rebuilds based upon the current level of fragmentation rather than use the last date of rebuild.

Adiga One Orange Chip Points: 27284 More actions · Answer 8

Agree with Steve. Re-indexing the tables based on the value of avg_fragmentation_in_percent column in the below query is the efficient way of rebuilding indexing in SQL Server 2005 and above.

select * from sys.dm_db_index_physical_stats (db_id(), 0, null, 0, 'SAMPLED')

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga