It’s a good thing the SQL Server Cardinality Estimation is better than the TPET Job Estimation SWAG (C). I thought 2 to 3 hours for the job based on my previous query results. I was a bit off. OK, I was way off.
JOB RUN: 'IndexOptimize' was run on 3/3/2015 at 2:00:00 AM
DURATION: 0 hours, 21 minutes, 12 seconds
MESSAGES: The job succeeded. The Job was invoked by Schedule 73 (Daily). The last step to run was step 1
This morning, after blinking a few times at the job duration and a few cups of coffee, I checked out the CommandLog and found my estimate of the number of indexes IndexOptimize would work on was pretty close, only 3 off. I went back to my index query and saw a similar result, DB1 has 186 and DB2 has 176 indexes with > 5% fragmentation. These aren’t going away. I also found some interesting differences between the two database and the sys.dm_db_index_physical_stats based query. DB1 generally returns the results much faster than DB2. Using sp_whoisactive I would see PAGEIOLATCH_SH possibly indicating a storage issue. DB1 and DB2 are on different disks, 1 is local and 2 is on the SAN. This is definitely something to look into.
I’m also curious about the wild swing in indexes I saw yesterday. How did I go from 700k to 1.6 million? Have I been overrun with Index Bunnies?
I’ve setup a table and job to log the counts from sys.tables and sys.indexes for the 2 databases. It runs every hour and so
far no change. The population is in control, for the moment, but I”ll be keeping my eye on this. Was yesterday a serious
misinterpretation of my query results or do are my indexes really created and dropped like that on a regular basis?