August 24, 2009 at 12:37 am
Hello SQL Experts
How to check when the indexes of a particular table \ all the tables of the DB was rebuilt??
Can you suggest me a good site to "create a maintenance plan" to "Rebuild the indexes"..?
Regards
Sourav
Thanks.
August 24, 2009 at 2:24 am
there are couple of SSC articles and scripts that should get you started :
http://www.sqlservercentral.com/scripts/31857/
http://www.sqlservercentral.com/scripts/Rebuild+Index/65933/
http://www.sqlservercentral.com/scripts/SQL+Server+2005/61278/
Just search SSC on "rebuild index" and the search result will show # of results.
You can use sys.dm_db_index_physical_stats DMV to check fragmentation, but AFAIK SQLServer doesn't keep info when an index has last been rebuilt.
(You could generate that yourself if you use one of the above solutions and create your own table to persist that info)
There is also DMV sys.dm_db_index_usage_stats that will provide some info on index usage since the current start of the sqlserver instance.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 24, 2009 at 7:48 am
The closest you can come to finding the last rebuild is to use the view mentioned by ALZDBA and the STATS_DATE() function as the statistics are updated whenever an index is rebuilt. Statistics are NOT updated when an index is reorganized.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 24, 2009 at 8:08 am
Keep in mind that if auto update statistics is on (on by default) there will be other incentives (usage driven) that cause this auto update process to update the statistics.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 25, 2009 at 7:04 am
Sourav (8/24/2009)
Hello SQL ExpertsHow to check when the indexes of a particular table \ all the tables of the DB was rebuilt??
Can you suggest me a good site to "create a maintenance plan" to "Rebuild the indexes"..?
Regards
Sourav
To be honest, in creating a maintenance plan or script to rebuild indexes, you can start with Books Online, look up sys.dm_db_index_physical_stats. They have an example D, which has has a sample script that I tend to use, with a few modifications such as rebuilding the index with online. For 90% of the databases out there, that should be fine. If your database is massive, you'll have to tweak the code and execution days. To be honest, depending on your database, you don't even have to index every day, meaning you can have certain days for certain tables, etc.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply