3

I have downloaded Ola Hallengren's script and deployed to master database. I run it using the below...

EXECUTE dbo.IndexOptimize
 @Databases = 'USER_DATABASES',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30

I get this output in SSMS but the Indexes have not been rebuilt. Fragmentation is still very high. Am I missing something?

Date and time: 2015-03-01 14:07:24
Server: TestSvr
Version: 10.50.2500.0
Edition: Standard Edition (64-bit)
Procedure: [master].[dbo].[IndexOptimize]
Parameters: @Databases = 'USER_DATABASES', 
@FragmentationLow = NULL, 
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',     
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', 
@FragmentationLevel1 = 5, 
@FragmentationLevel2 = 30, 
@PageCountLevel = 1000, @SortInTempdb = 'N', 
@MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, 
@LOBCompaction = 'Y', @UpdateStatistics = NULL, 
@OnlyModifiedStatistics = 'N', @StatisticsSample = NULL, 
@StatisticsResample = 'N', @PartitionLevel = 'Y', 
@MSShippedObjects = 'N', 
@Indexes = NULL, @TimeLimit = NULL, @Delay = NULL, 
@WaitAtLowPriorityMaxDuration = NULL, 
@WaitAtLowPriorityAbortAfterWait = NULL, @LockTimeout = NULL, 
@LogToTable = 'N', @Execute = 'Y'
Source: https://ola.hallengren.com

Date and time: 2015-03-01 14:07:24
Database: [TestData]
Status: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: SIMPLE

enter image description here

closed as off-topic by Philᵀᴹ, ypercubeᵀᴹ, Paul White, Kin Shah, Michael Green Mar 2 '15 at 1:38

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Philᵀᴹ, ypercubeᵀᴹ, Paul White, Kin Shah, Michael Green
If this question can be reworded to fit the rules in the help center, please edit the question.

  • What indexes? How many pages are in the index? Is fragmentation actually causing a problem? (On small tables, almost certainly not; on large tables, only if your database doesn't fit into the buffer pool). – Aaron Bertrand Mar 1 '15 at 14:25
  • both clustered and non clustered indexes. added a screenshot to original post – K09 Mar 1 '15 at 14:28
  • 5
    Your index only has 679 pages. Ola's solution is set to ignore indexes with less than 1000 pages (see the @PageCountLevel parameter). I would stop worrying about small tables like this - let his solution do its job, and worry about fragmentation when you can actually prove it is causing a tangible performance problem. – Aaron Bertrand Mar 1 '15 at 14:35
  • Possible duplicate of Why not rebuild indexes with page count <1000? – Paul White Mar 2 '15 at 0:46
15

Your index only has 679 pages. Ola's solution is set to ignore indexes with less than 1000 pages (see the @PageCountLevel parameter). You can override that so that it cares about indexes with fewer than 1000 pages, but why? Wasted effort IMHO.

I would stop worrying about small tables like this - let Ola's solution do its job, and worry about fragmentation when you can actually prove it is causing a tangible performance problem for a specific index. "Fragmentation is high" is not a problem on its own.

  • This could really use a canonical answer as it seems to come up a lot. (Perhaps anecdotal and only related to questions I tend to read though, so insert salt before reading) – Reaces Mar 1 '15 at 21:38
  • To add a bit to this, depending on how big the rest of your tables/indexes and databases are, you may even want to consider upping the page threshold to 5000 or 10000. In larger environments those little indexes are likely sitting in memory anyway. The larger tables, where fragmented indexes result in reading many extra pages are where the problems usually are. Worry more about keeping your statistics up to date; they will have a much more profound effect on your queries. Reading extra (read: fragmented) pages is a lot less painful when the right plan is chosen. – Erik Darling Mar 2 '15 at 2:11

Not the answer you're looking for? Browse other questions tagged or ask your own question.