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)...

      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?

2 Answers 2


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.

  • Add USE [DB Name] so you know it focuses on one DB each time you run it.
    – user507
    Commented Jul 15, 2011 at 16:40
  • FYI, if you haven't looked at it yet, I'd recommend the Index Defrag tool from SQL Fool, current version 4.1 at sqlfool.com/2011/06/index-defrag-script-v4-1
    – Jeff
    Commented Jul 15, 2011 at 22:30

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

  • 1
    Another good one is Ola Hallengren's DB maintenance script. It includes a snippet on managing index frag: ola.hallengren.com/Versions.html
    – user507
    Commented Jul 15, 2011 at 16:38
  • Yeah this worked ok. At least I get a result set now, just not what I had before. I still don't know what happened, but I guess I just need to move on.
    – David
    Commented Jul 15, 2011 at 18:35
  • 1
    Totally, that script was a good start, I have since added more columns so I don't have to work too hard to understand the context of the results. Commented Jul 15, 2011 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.