I'm completely stumped. I'm a relatively new SQL Server DBA so I'm at a loss with the following situation; hoping maybe someone had an idea.
I have a new production SQL Server (Windows Server 2003/SQL Server 2005 SP3) that I am going to be responsible for. Wanted to get some basic stats so I got some scripts off of SQLServerPedia to help with that. (Great stuff btw).
Did a check on index fragmentation and ran the following query (took it about 30 min to run)...
SELECT db.name AS databaseName , SCHEMA_NAME(obj.schema_id) AS schemaName , OBJECT_NAME(ps.OBJECT_ID) AS tableName , ps.OBJECT_ID AS objectID , ps.index_id AS indexID , ps.partition_number AS partitionNumber , ps.avg_fragmentation_in_percent AS fragmentation , ps.page_count FROM sys.databases db INNER JOIN sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, N'Limited') ps ON db.database_id = ps.database_id INNER JOIN sys.objects obj ON ps.object_id = obj.object_id WHERE ps.index_id > 0 AND ps.page_count > 100 AND ps.avg_fragmentation_in_percent > 30 ORDER BY databaseName, schemaName, tableName OPTION (MaxDop 1);
This came back with several rows of data with several indexes for several databases. (I didn't save the result set of the query -- wish I had now). Long story short I re-ran the query about ten minutes later and it ran in 2 seconds and returned no rows. Nothing. I have run 0 queries to manipulate indexes in that timeframe. No jobs had run. There is nothing in the SQL Server Log for that time period to indicate anything fishy. And I double checked and confirmed the query was run both times on the same server. How could this be? How could approx. 20 indexes that were over 30% fragmentation with more than 100 page count suddenly be taken care of (or disappear from this result set) without me doing anything (or from what I can tell -- no one else or no other application) doing anything either?