October 4, 2007 at 4:58 pm
Physical statistics for all indexes, Indexes, Operation recommended. It says reorganized or rebuild. Does it mean indexes should be reorganized or rebuild? Why should they and what would happen if I don't?
October 5, 2007 at 2:35 am
Vika (10/4/2007)
Physical statistics for all indexes, Indexes, Operation recommended. It says reorganized or rebuild. Does it mean indexes should be reorganized or rebuild? Why should they and what would happen if I don't?
You probably will want to have a look at http://msdn2.microsoft.com/en-us/library/ms189858.aspx
Regards,
Andras
October 5, 2007 at 1:04 pm
Andras,
I read that. I run rebuid for the index that was recommended to be rebuilt and it didn't help, it still suggested me to rebuild it in the reports. Index was still devided into 50% - 50%.
Are you from Pribaltika?
October 8, 2007 at 5:46 am
Vika (10/5/2007)
Andras,I read that. I run rebuid for the index that was recommended to be rebuilt and it didn't help, it still suggested me to rebuild it in the reports. Index was still devided into 50% - 50%.
Hi Vika,
What does sys.dm_db_index_physical_stats say about this index? The report is based on what this dynamic management view returns.
Does the parent table have a clustered index? Also, how large is this table? (for small tables there is no real need to worry about fragmentation :))
Are you from Pribaltika?
no, from Hungary 🙂
Regards,
Andras
October 8, 2007 at 5:56 am
at small sizes indexes and tables are usually not worth rebuilding and if you do the analysis results can be strange. I usually don't bother with anythign under 8 pages in size. For tables a usual sign is the fragmentation gets reported as 25%, 33% or 50% , you rebuild and it shows 50%, so you rebuild again and it shows 33% ( sometimes 75% ) I've never bothered to investigate but if the index is less than 8 pages I wouldn't worry.
Rebuilding and re-organising are different, I'd always go for rebuild if I have the available window to do so.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 8, 2007 at 9:15 am
Yes,
the person who made a table used a clastered index. I would make just one primary key and use three other columns as foreign keys. But he used a combination of three columns as a primary key. Those columns are primary keys in some other tables. What scares me is that now database is almost empty but that performance will be affected when we go live. Page fullness is 99.39% and total fragmentation is 80% (!), pages 5.
Should such index be re-built? I tried to rebuild it and it didn't help, still 80%. If I delete this index and re-create it again, can it help? What could cause such fragmentation. I was asked to restore live database from the backup from the QA server. Then they asked me to take live database backup and restore it back on the QA server. Then again from QA to live. Can this fragmentation be caused by such crazy actions?
I am originally from Russia and now in US. - Victoria
October 8, 2007 at 9:28 am
as I say I think the table is too small to be effectively defragmented. I wouldn't worry about any figures returned from a 5 page table. Can't comment on a three column PK - sounds probable to me.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply