March 7, 2014 at 10:52 am
I would like to rebuild indexes.Will the tables be automatically available for use available once the rebuild completes or do I have to do anything to make them available?I am thinking of creating a maintenance plan in MS SQL server management studio to rebuild indexes.I have come across 2 different thoughts regarding rebuilding indexes and updating statistics .One thought says updating statistics is not necessary after rebuilding statistics as a rebuild automatically updates statistics and another thought says we need to update statistics.Under what circumstances do these 2 different thoughts hold true?
I ran this query:Select
DB_NAME(ips.database_id) DBname,ips.object_id,
OBJECT_NAME(ips.object_id) ObjName,
i.name InxName,
ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(db_id('Ntier_NWHMC'),
default, default, default, default) ips
INNER JOIN sys.indexes i
ONips.index_id = i.index_id AND
ips.object_id = i.object_id
WHERE
ips.object_id > 99 AND
ips.avg_fragmentation_in_percent >= 10
--ips.index_id > 0
order by ips.avg_fragmentation_in_percent desc
which gave the list of object names with their fragmentation.But when I actually see their fragmentation levels in Management studio it says a different story.The results hardly match with a wide variation
Ex:Query says 99% fragmented on the objects but the management studio says 2%.
Which one should I trust?And what should I do to make these 2 match in the database?
Thanks
March 7, 2014 at 1:17 pm
Use this script and the results will match ..
SELECT
db_name() AS DbName,
SCHEMA_NAME(B.schema_id) AS SchemaName
, B.name AS TableName
, C.name AS IndexName
, A.INDEX_DEPTH as Index_depth
, C.fill_factor AS IndexFillFactor
, D.rows AS RowsCount
, A.avg_fragmentation_in_percent
, A.page_count
, GetDate() as [TimeStamp]
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN sys.objects B
ON A.object_id = B.object_id
INNER JOIN sys.indexes C
ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D
ON B.object_id = D.object_id AND A.index_id = D.index_id
WHERE C.index_id > 0 and A.INDEX_DEPTH >2
ORDER BY A.avg_fragmentation_in_percent DESC
Tables will be automatically available for use once the rebuild completes. No extra steps needed.
Instead of using Maintenance Plans try to use Index Maintenance solution from Ola Hallengren which is customizable and gives you more control and better logging.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
It depends on your environment. Even though Rebuild Index will update statistics it doesn't update NonIndex-column statistics. May be if you can update statistics on a nightly basis you need not do it immediately after the index rebuilds.
--
SQLBuddy
March 7, 2014 at 1:21 pm
Yes, when you rebuild an index the table/index becomes available as soon as the index rebuild is completed.
Statistics based on the index rebuilt are updated when an index rebuild occurs, but SQL Server also allows you to create statistics on non-indexed columns and, if left at the default, will automatically create statistics on columns the optimizer thinks will help. These column level statistics are NOT updated when an index is rebuilt thus you need to update them.
I recommend using Ola Hallengren's maintenance scripts[/url] for this type of work. Michelle Ufford also has a good index maintenance script [/url]as well.
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply