We have critical performance issues with our production database (its around 300 gb size) We have run the following script for a few huge tables :

DECLARE @object_id INT;

SET @db_id = DB_ID(N'master');
SET @object_id = OBJECT_ID(N'master.dbo.tTable');

IF @db_id IS NULL
    PRINT N'Invalid database';
ELSE IF @object_id IS NULL
    PRINT N'Invalid object';
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');

and get the following relults :

First table:

index_type_desc : HEAP

alloc_unit_type_desc : IN_ROW_DATA

index_depth: 1

index_level: 0

avg_fragmentation_in_percent : 81.1555698682339

fragment_count : 163818

avg_fragment_size_in_pages : 8.4652052887961

page_count : 1386753

Second table :

index_type_desc : HEAP

alloc_unit_type_desc : IN_ROW_DATA

index_depth: 1

index_level: 0

avg_fragmentation_in_percent: 82.2484358706986

fragment_count : 76826

avg_fragment_size_in_pages: 6.87469085986515

page_count : 528155

For non-clustered indexes the avg_framgentation_values value is too low. Are we have a problem with clustered index ? And what can we do with it to improve our performance ?

  • You can't have a problem with clustered indexes because these tables don't have clustered indexes. Was that an intentional choice? Why? – Aaron Bertrand Jul 16 '15 at 14:04
  • We have a lot of non-clustered indexes for our queries includes non-clustered index for one Id field. It seems we haven't got a clustered indexes, also, in our queries we have the query whose execution plan contains RID Lookup (HEAP) 94% – Vladislav Furdak Jul 16 '15 at 14:20
  • What is advantage of using clustered indexes ? – Vladislav Furdak Jul 16 '15 at 14:21
  • You have a RID lookup because an index is used to locate rows, but it isn't covering - e.g. you have columns you want to output (or are used for additional filter or join criteria, though most commonly this is from SELECT *) that can't be found in the index, so need to be retrieved from the heap. – Aaron Bertrand Jul 16 '15 at 14:39
  • thanks, but is heap fragmentation can affects on performance ? – Vladislav Furdak Jul 16 '15 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.