Procedure timing

IndexOptimize has been running in production in its current state for about a week now. Time to look at some results and use the following against CommandLog


SELECT DATEPART(YYYY,StartTime) AS 'Year'
,DATEPART(MM,StartTime) AS 'Month'
,DATEPART(d,StartTime) AS 'Day'
,DATENAME(dw,StartTime) AS 'Day of Week'
,COUNT(ID) AS 'Index Count'
,DATEDIFF(mi,MIN(StartTime),MAX(EndTime)) AS 'Job Time - minutes'
,DATEDIFF(ss,MIN(StartTime),MAX(EndTime)) AS 'Job Time - seconds'
FROM CommandLog
GROUP BY DATEPART(YYYY,StartTime)
,DATEPART(MM,StartTime)
,DATEPART(d,StartTime)
,DATENAME(dw,StartTime)
ORDER BY DATEPART(YYYY,StartTime)
,DATEPART(MM,StartTime)
,DATEPART(d,StartTime)

20150318-RunTime

First off, what in the name of all that is normalized is going on with Friday? That will need some investigation. Next, run time (outside of Friday) is looking really good. IndexOptimize is powering through several hundred indexes in a few minutes. I’m pretty happy with these results. Looking back on my old indexing job, it was regularly running 30 to 45 minutes. I don’t know how many indexes it was working on as I didn’t have the detailed logging that IndexOptimize does.

The problem table is still sitting there, waiting, lurking and possibly fragmenting. It is being skipped by IndexOptimze for the time being. I’m still keeping an eye on it and logging stats with sys.dm_db_index_physical_stats on a daily basis. Well, almost daily basis. Turns out there can be quite a few decimal places in the avg_fragmentation_in_percent column. I didn’t set that right the first time, but switching over to FLOAT got it back up and running. Yes FLOAT is an approximate data type, but I’m not concerned about accuracy to 7 or even 15 digits out. The fragmentation of the indexes on the problem table has increased a bit, but not too much. There is some work on Fridays that happens on this table that I have a feeling may change the index stats. This Friday work also has something to do with the IndexOptimize run times. This is a good opportunity to better understand the workload on this database.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s