indexing question

[simian336]

When I run...

ALTER INDEX ALL ON dbo.[Yadkin Base Form]
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = On);
go

EXEC sp_updatestats

and I look at the Total Fragmentation in the properties of the index it shows 0.00%. But if I run

SELECT D.name AS 'Database', O.name AS 'Table', SP.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(null, null, null, null, null) SP
join sys.databases D
on SP.database_id = D.database_id
join sys.all_objects O
on SP.object_id = bject_id
where avg_fragmentation_in_percent > 5
order by SP.avg_fragmentation_in_percent desc

then I get
avg_fragmentation_in_percent=98.961937716263

Is there a problem with the query or shouldn't they match up?

Thanks

Simi

 

[simian336]

ps and I have tried running

EXEC sp_updatestats

with the same results

Simi

 

[gmmastros]

How many rows are in the table?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

[tt]sys.dm_db_index_physical_stats(null, null, null, null, [!]null[/!]) [/tt]

The last parameter is for mode. Valid values are DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. DEFAULT, NULL and LIMITED all do the same thing. To get a more accurate result, use SAMPLED or DETAILED. You should understand that it will likely take longer to run the query this way.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

Hey George,

There are only 60k rows in the table that I am testing on.

I reran the query with 'DETAILED' and the results were basically the same.

Total Fragmentation in the properties of the index it shows 0.00% and the query still shows avg_fragmentation_in_percent=98.961937716263 for that table.

Simi

 

[simian336]

hummm... my testing was on a ss 2005 and it does not match. On ss 2012 it does.

Simi