February 27, 2008 at 11:45 am
well is there any benefit in performance in doing that or it is not necessary?
also, i thought that when you rebuild an index in a table you are supposed to rebuild all of the indexes?
February 27, 2008 at 12:38 pm
well is there any benefit in performance in doing that or it is not necessary?
also, i thought that when you rebuild an index in a table you are supposed to rebuild all of the indexes?
Well in this case it too depends on your environment. I really do not see a problem with reindexing all indexes, as a matter of fact, I do it here at my current job.
There is going to be a minimal performance impact on rebuilding/reorganzing indexes that have little data if any. Will you gain increased performance on indexes with less than 10 data pages.. perhaps. By reindexing all indexes, you will see the greatest performance improvement, on indexes in the 40-50 data page range. These indexes will be excluded from your current query. If these indexes are used frequently by the application and never rebuilt, you will encounter fragmentation problems.
It is a balancing act. I for one would play with the number and not leave it at static 50. External fragmentation will degrade performance and should be carefully monitored for all indexes not just large ones.
April 9, 2008 at 9:08 pm
i have updated from 2000 to 2005 (I changed the compatibility level to 90). I have noticed that there are a lot of indexes that will not de-fragment. I have tried a bunch of different ways to rebuild the indexes and still no go. any help would be greatly appreciated.
April 13, 2008 at 10:27 am
About rebuilding and reorganizing indexes dynamically I have a store procedure that could help you with this. It is using sys.dm_db_index_physical_stats.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Ola Hallengren
August 6, 2008 at 9:23 am
hi Ola question about your stored procedure, for some reason is skipping one of my user databases which is in simple recovery model, is your sp only made for DBs which are in full recovery model? also does your Sp also update statistics? if so does it do it with full scan to the ones that were rebuilt?
August 6, 2008 at 10:47 am
I will definitely check your resources out. Thanks for the reply I will keep you up to date.
August 6, 2008 at 12:08 pm
Some answers to DBA's questions.
The logic in the DatabaseBackup stored procedure is like this.
IF DATABASEPROPERTYEX(@CurrentDatabase,'status') = 'ONLINE'
AND NOT (@BackupType = 'LOG' AND DATABASEPROPERTYEX(@CurrentDatabase,'recovery') = 'SIMPLE')
BEGIN
END
So only databases that are Online are backed up. For log backups only databases that are in Full or Bulk-Logged recovery model are backed up.
About updating statistics you can use the action 'INDEX_REORGANIZE_STATISTICS_UPDATE' in the IndexOptimize stored procedure.
EXECUTE dbo.IndexOptimize @databases = 'USER_DATABASES',
@FragmentationHigh_LOB = 'INDEX_REBUILD_OFFLINE',
@FragmentationHigh_NonLOB = 'INDEX_REBUILD_ONLINE',
@FragmentationMedium_LOB = 'INDEX_REORGANIZE_STATISTICS_UPDATE',
@FragmentationMedium_NonLOB = 'INDEX_REORGANIZE_STATISTICS_UPDATE',
@FragmentationLow_LOB = 'NOTHING',
@FragmentationLow_NonLOB = 'NOTHING',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PageCountLevel = 1000
Here indexes with a fragmentation over 30% will be rebuilt, online if possible (no LOBs), otherwise offline (LOBs). Indexes with a fragmentation between 5% and 30% will be reorganized and have their statistics updated (default sample). Indexes with a fragmentation under 5% or a size under a 1000 pages will not be touched.
Please let me know if you have any more questions.
Ola Hallengren
August 6, 2008 at 3:56 pm
thanks for your reply, for the dbo.IndexOptimize sp for some reason is skipping one of my databases, it is online.
August 6, 2008 at 4:03 pm
Maybe there are just no indexes that needs to be rebuilt or reorganized in this database.
Sometimes on small databases many indexes fall below the PageCountLevel. Try setting @PageCountLevel = 1 or something as a test.
Ola Hallengren
August 13, 2008 at 1:14 pm
Hi DBA,
I customized the script provided in the following link, it works very well for huges databases http://blogs.digineer.com/blogs/larar/archive/2006/08/16/smart-index-defrag-reindex-for-a-consolidated-sql-server-2005-environment.aspx
Regards,
Ahmed
August 28, 2008 at 12:28 am
How can I tell what is Table page Count
August 28, 2008 at 2:34 pm
You can check the page_count column in the DMV sys.dm_db_index_physical_stats.
If you use my index optimization stored procedure it is logged in the output file.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Ola Hallengren
August 28, 2008 at 5:02 pm
Ola Hallengren (8/28/2008)
You can check the page_count column in the DMV sys.dm_db_index_physical_stats.If you use my index optimization stored procedure it is logged in the output file.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
Ola Hallengren
August 28, 2008 at 5:10 pm
Adam Haines (2/27/2008)
hi i have a question, i just got told by the senior DBA that to consider that query i have to put at least the page count > 50 is that correct?
Edited: Today @ 4:41 PM by DBA
Well, this depends on what you want to see. The page count is the number of pages for a given index. If you say that you want > 50 you are saying that you only want to look at fragmentation on indexes that have a moderate number of data pages.
I would agree that 50is a fair number. You could go higher or lower, but the main point is you dont want to reorganize/rebuild indexes that have no data in them, do you?
Hi
From this DMV sys.dm_db_index_physical_stats --> how can I differetiate what is Index page count & what is Table page count?
Thanks
August 29, 2008 at 3:00 am
You can look at the index_id in sys.dm_db_index_physical_stats.
0 = Heap
1 = Clustered index
> 1 = Nonclustered index
The clustered index is the table.
If you use my solution you could set the @PageCountLevel = 50 to exclude small indexes.
Ola Hallengren
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply