Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Rebuild Indexes Expand / Collapse
Posted Friday, March 7, 2014 10:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 21, 2016 2:19 AM
Points: 330, Visits: 1,195
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, InxName,
FROM sys.dm_db_index_physical_stats(db_id('Ntier_NWHMC'),
default, default, default, default) ips
INNER JOIN sys.indexes i
ON ips.index_id = i.index_id AND
ips.object_id = i.object_id
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?
Post #1548825
Posted Friday, March 7, 2014 1:17 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, January 23, 2015 2:58 PM
Points: 1,194, Visits: 2,243
Use this script and the results will match ..

db_name() AS DbName,
SCHEMA_NAME(B.schema_id) AS SchemaName
, AS TableName
, 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.

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.


Post #1548878
Posted Friday, March 7, 2014 1:21 PM



Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 10,808, Visits: 14,832
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 for this type of work. Michelle Ufford also has a good index maintenance script as well.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1548879
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse