Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

MS SQL 2008 Fragmented Indexes

Hi,

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 b.name,* 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
GO
SELECT object_name(IPS.object_id) AS [TableName], 
   SI.name AS [IndexName], 
   IPS.Index_type_desc, 
   IPS.avg_fragmentation_in_percent, 
   IPS.avg_fragment_size_in_pages, 
   IPS.avg_page_space_used_in_percent, 
   IPS.record_count, 
   IPS.ghost_record_count,
   IPS.fragment_count, 
   IPS.avg_fragment_size_in_pages
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
ORDER BY 5
GO

Open in new window

0
thomasm1948
Asked:
thomasm1948
  • 2
  • 2
1 Solution
 
tigin44Commented:
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%.  
0
 
thomasm1948Author Commented:
Thank you for your response.  The following is what I see for one table from both queries:

First Query

Planned_Services
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
0
 
tigin44Commented:
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...
0
 
thomasm1948Author Commented:
Ok i see

thank you for all of your help
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now