SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reindex and Update Statistics History


Reindex and Update Statistics History

Author
Message
Sandhya-371593
Sandhya-371593
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 214
Hi,

Is there any way I could get the information like when the reindexing and update statistics was done on a particular database?

Regards
Sandhya
Gethyn Ellis
Gethyn Ellis
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3928 Visits: 2916
You can view when the stats were last updated for a particular set of stats by expanding the database in SSMS, expand the tables folder, then the statistics folder, and then right click on the set of stats and select properties.

Gethyn Ellisgethynellis.com
bitbucket-25253
bitbucket-25253
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23907 Visits: 25280
Refer to:
Index Related Dynamic Management Views and Functions (Transact-SQL)
at:
http://technet.microsoft.com/en-us/library/ms187974.aspx

This code works in 2005, but I have not tested in 2008 ... give it a try if it does you have some of what you asked for. If it does / does not work, please post and then my knowledge will increase.

SELECT 'Index Name' = i.name, OBJECT_NAME(i.id) AS 'Table Name',
Case Indid
WHEN 1 THEN 'Clustered'
ELSE 'NonClustered'
End 'Type',
'Last Updated' = STATS_DATE(i.id, i.indid),rowmodctr AS
'# Rows inserted deleted or updated', --, o.type
i.keys
FROM sysobjects o, sysindexes i
WHERE o.id = i.id AND (o.type <> 'S' AND indid <> 0 AND indid <> 255) Gets date of last statistics update number of rows added, deleted or updated since last update



Same caveat as above, but this will also report on Auto Created Statistic

SELECT o.name AS Table_Name,i.name AS Index_Name,STATS_DATE(o.id,i.indid) AS Date_Updated
FROM sysobjects o
JOIN sysindexes i ON i.id = o.id
WHERE xtype = 'U' AND i.name IS NOT NULL
ORDER BY o.name ASC,i.name ASC



Again tested on 2005 needs to be tested on 2008. Again asking you to report back if it does / does not work in 2008
This will tell you the date and a lot more than what you would want to know about indexes.

 SELECT  o.name as 'Table', i.name as 'Index', c.name as 'Column',
'Index Type' =
CASE
WHEN PATINDEX('%_wa_sys_%',i.name) = 0
THEN 'Index'
ELSE 'Statisical'
END,
'Primary' =
CASE
WHEN (i.status & 0x800)= 0
THEN 'No'
ELSE 'Yes'
END,
'Clustered' =
CASE WHEN (i.status & 0x10)= 0
THEN 'No'
ELSE 'Yes'
END,
'Unique' =
CASE WHEN (i.status & 0x2) = 0
THEN 'No'
ELSE 'Yes'
END,
'Ignore Dup Key' =
CASE WHEN (i.status & 0x1) = 0
THEN 'No'
ELSE 'Yes'
END,
'Ignore Dup Row' =
CASE WHEN (i.status & 0x4)= 0
THEN 'No'
ELSE 'Yes'
END,
'No Recompute' =
CASE WHEN (i.status & 0x1000000) = 0
THEN 'No'
ELSE 'Yes'
END,
'Computed' =
CASE WHEN (c.iscomputed) = 0
THEN 'No'
ELSE 'Yes'
END,
'Nullable' =
CASE WHEN (c.isnullable) = 0
THEN 'No'
ELSE 'Yes'
END,
i.OrigFillFactor AS 'Orig Fill Factor', i.rowcnt as 'Est.RowCount', i.reserved * cast(8 as bigint) as ReservedKB,
i.used * cast(8 as bigint) as UsedKB, t.name as 'Column Type',
'Precision' =
Case c.xprec WHEN 0
THEN ' '
ELSE CAST(c.xprec as VARCHAR(3))
END,
'Scale' =
Case c.xscale
WHEN 0
THEN ' '
ELSE CAST(c.xscale as VARCHAR(3))
END,
c.Length as 'Length', 'Updated' = STATS_DATE(i.id,i.indid)
from sysobjects o with(nolock) inner join sysindexes i with(nolock) on o.id = i.id
inner join sysindexkeys k with(nolock) on i.id = k.id and I.indid = K.indid
inner join syscolumns c with(nolock) on k.id = c.id and K.colid = c.colid
inner join systypes t with(nolock) on c.xtype = t.xtype where o.xtype <> 'S' -- Ignore system objects
Order By o.name, i.name



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Gethyn Ellis
Gethyn Ellis
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3928 Visits: 2916
I just ran the script on my 2008 developer edition instance and it worked fine...as long as it answers the OP's question.

Gethyn Ellisgethynellis.com
bitbucket-25253
bitbucket-25253
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23907 Visits: 25280
GRE (Gethyn Ellis)

Thanks for the feed back. Edited my posting to add 2 additional scripts would you be so kind as to give them a test.

Again I thank you ,and so will the original poster of the question and any others who stumble upon this forum.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Gethyn Ellis
Gethyn Ellis
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3928 Visits: 2916
bitbucket-25253 (3/16/2010)
GRE (Gethyn Ellis)

Thanks for the feed back. Edited my posting to add 2 additional scripts would you be so kind as to give them a test.

Again I thank you ,and so will the original poster of the question and any others who stumble upon this forum.


Both additional Scripts run OK on 2008 against the AdventureworksDW db on SQL2008

The very first script (Script 1) returned 88 rows

The 2nd script (Script 2) returned 78 rows -- for some reason I was expecting this to more than the first script?

The final script (Script 3) returns 122 rows

Gethyn Ellisgethynellis.com
bitbucket-25253
bitbucket-25253
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23907 Visits: 25280
GRE (Gethyn Ellis)

Again many thanks .... now I have learned more and I hope the person asking the original question has also learned something new.

You assistance in testing is greatly appreciated.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Paul White
Paul White
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54020 Visits: 11391
Sandhya-371593 (3/16/2010)
Is there any way I could get the information like when the reindexing and update statistics was done on a particular database?

Sandhya,

Statistics are always created and updated with an index:


SELECT [schema_name] = SCHEMA_NAME(T.[schema_id]),
table_name = T.name,
T.type_desc,
index_or_statistics_name = S.name,
is_auto_stats = S.auto_created,
user_created = S.user_created,
last_updated = STATS_DATE(T.[object_id], S.stats_id)
FROM sys.tables T
JOIN sys.stats S
ON S.[object_id] = T.[object_id]
ORDER BY
T.[schema_id],
T.name,
S.stats_id;





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Sandhya-371593
Sandhya-371593
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 214
It worked for me both on 2005 and 2008. Thanks so much !!!
Paul White
Paul White
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54020 Visits: 11391
Sandhya-371593 (3/17/2010)
It worked for me both on 2005 and 2008. Thanks so much !!!

No worries.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search