Check Index Fragementation Query

Question

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?

Answer 1

I think you ran the query from 2 different databases (same server, but different databases) and are getting unexpected results due to the INNER JOIN condition.

My suggestion would be to just focus on one database at a time when analyzing indexes.

Answer 2

I personally use Pinal Dave's script to find bloated indexes. Most of the time it runs in under 13 seconds.