SQL Server – Get Last Reorganized or Rebuild Index Date

sql-server-2008sql-server-2016

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?

Best Answer

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 + ']'

Related Solutions

SQL Server Maintenance – Comprehensive Checklists

Backups
- Check for backup emails
- How long did the backup take to run (database backup duration)
- Verify that all databases are being backed up according to a maintenance plan
Disk free space. Note significant variations from previous check. Log files may be affected dramatically by monthly jobs
Job failures. Filter job activity for failures
System checks. Look in sql logs for any critical errors.
- Application logs
Performance
- Check performance statistics on all servers
- Check that counters are in normal range on all production servers
Connectivity
- Verify the customer application can get data from the database
- Verify acceptable speed of access data
Replication. Verify that the each publication and distributor is running for each subscription

SQL Server DBA Checklist

Brad's Sure DBA Checklist

Oracle DBA Checklist (maybe useful)

SQL Server DBA database management checklist

DBA Morning Check List

MS SQL Server DBA Checklist (many checklists)

SQL Server DBA Checklist

Sql-server – how to get last date of today in datetime datatype

Why on earth do you need the last possible value of today? Stop using BETWEEN; it is much easier (and much more reliable, and consistent across various date/time data types) to use an open ended range:

DECLARE @today SMALLDATETIME;

SET @today = DATEADD(DAY, DATEDIFF(DAY, '20000101', GETDATE()), '20000101');

SELECT ... FROM ...
WHERE date_column >= @today
AND date_column < DATEADD(DAY, 1, @today);

The problem with what you're doing is that the data type can affect what happens. If the underlying data type is SMALLDATETIME, it rounds up; if the underlying data type is DATETIME or DATETIME2, subtracting a second is not going to capture all of the data from that day. This is actually one of my favorite slides in my T-SQL : Bad Habits and Best Practices talk. For a lot more details, please read these blog posts:

Best Answer

Related Solutions

SQL Server Maintenance – Comprehensive Checklists

Sql-server – how to get last date of today in datetime datatype

Related Question