0

I am currently working on migrating sqlserver 2008r2 to SQL server 2016.The DBA before me used "IndexOptimize" from https://ola.hallengren.com/downloads.html to create the iIndex solution. I can see the job is schedlued and run on a monthly basis and logic apply is: if Fragmentation < 20 then reorganize index if fragmentation > 40 rebuild index I am planning on using the Maintenance plans via SSMS in order to reorganize or Rebuild indexes. How do i find which one has run "reorganize/rebuild", I can see fragmentation for some table are above 66. Am right in thinking that rebuild Index is not taking place?

Improve this question
1
  • 1
    There are other qualifiers too, like number of pages. If the index is under a certain size, Ola doesn't bother with fragmentation, because it's not worth the effort. IMHO: stop worrying about fragmentation for every single index in your system. Focus on indexes where performance is actually an issue you can quantify. Fragmentation on its own is like PLE: it's just a number.
    – Aaron Bertrand
    Commented Jul 5, 2019 at 14:47

1 Answer 1

Reset to default
1

As Aaron mentioned in the comment, the indexes with a lot of fragmentation you are seeing probably got skipped due to the index being too small or too big (default for this is no index is too big).

You can search for the below parameters in the sp definition, and check the job to make sure a different value is not being passed through:

@MinNumberOfPages INT = 1000,
@MaxNumberOfPages INT = NULL,

Another possibly is that the index is being rebuilt and then re-fragmenting again. This to me would be a sign that constant rebuilds of that index actually aren't a good help.

If you're looking for when\if the index was ever rebuilt, the Ola sp does have the ability to log to a table and the previous DBA hopefully utilized this feature. If so, you can use the below to figure out when the last time an index was rebuilt.

SELECT 
'[' + DatabaseName + '].[' + SchemaName + '].[' + ObjectName + ']' AS ObjectName,
MAX(StartTime)

FROM 
dbo.CommandLog

WHERE
CommandType = 'ALTER_INDEX'

GROUP BY 
'[' + DatabaseName + '].[' + SchemaName + '].[' + ObjectName + ']'
Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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