MS SQL 2008 Fragmented Indexes


I am trying to figure out which of these view fragmented statements are correct.  I get different results for the same tables.  I am trying to resolve some performance issues in our database that prevents patient lookups
--first statement
use dental

select,* from sys.dm_db_index_physical_stats (null, null, null, null,'detailed') as a
join sys.all_objects b on a.object_id = b.object_id
order by avg_fragmentation_in_percent desc

--second statement
use dental
USE Dental
SELECT object_name(IPS.object_id) AS [TableName], AS [IndexName], 
FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL , 'DETAILED') IPS
   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0 --AND ips.avg_fragmentation_in_percent >= 20

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

in either cases you should care about the "avg_fragmentation_in_percent" and "avg_fragment_size_in_pages". The value for avg_fragmentation_in_percent should be as close to zero as possible  for maximum performance. Also the value for the avg_fragment_size_in_pages should be close to 100%.  
thomasm1948Author Commented:
Thank you for your response.  The following is what I see for one table from both queries:

First Query

avg_fragmentation_in_percent = 100
avg_fragment_size_in_pages  = 1.5

Second Query
avg_fragmentation_in_percent = 0.7%
avg_fragment_size_in_pages  = 1

This is what I am confused about

Thank you for all of your help
second query gives the results for each index by the index name... while the first one gives with the index id... Did you check that were you comparing the same index values? Both queries should produce the same results for the same index. In my case they are the same...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thomasm1948Author Commented:
Ok i see

thank you for all of your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.