Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 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?

share|improve this question

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.

share|improve this answer
Add USE [DB Name] so you know it focuses on one DB each time you run it. – Shawn Melton Jul 15 '11 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 – Jeff Jul 15 '11 at 22:30

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

share|improve this answer
Another good one is Ola Hallengren's DB maintenance script. It includes a snippet on managing index frag: – Shawn Melton Jul 15 '11 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 Jul 15 '11 at 18:35
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. – RateControl Jul 15 '11 at 18:54

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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