Take the 2-minute tour ×
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. It's 100% free, no registration required.

I am new here and have a little question.

In a customer environment after checking his index fragmentation we see some "NULL" named index that we couldn't rebuilt or reorganize.

This was a SQL Server 2008 R2 Sp1. I have try looking on the net for this unnamed index but have no luck finding anything about it, can anyone please enlighten me on what are those.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

These "indexes" are actually not indexes at all. They're called heaps, which is an internal structure that holds the table data without using any ordering mechanism like an index does.

Since there's no order, there's nothing to do as far as reorganizing or rebuilding goes, so these can be safely ignored as far as consideration for index maintenance. I would recommend using a 3rd-party solution for index maintenance though.

If you're new to index structures in SQL Server, I have a video here that explains them.

share|improve this answer
    
Thanks Jon, my idea was not unfounded then, I was under the impression since this tables lacked a clustered index "this" heap (I didn't know the name at that point) maintain the actual data. –  Enrique Mar 12 at 17:33
    
@Enrique: Yes, exactly. You're welcome! –  Jon Seigel Mar 12 at 17:35

Following on from Jon's answer, the index type is noted in the sys.dm_db_index_physical_stats catalog view.

You can begin to obtain details about index fragmentation, along with the index type, from this view.

SELECT 
  o.name AS [table],
  i.name AS [index],
  ddips.index_type_desc,
  ddips.avg_fragmentation_in_percent,
  ddips.fragment_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ddips

LEFT OUTER JOIN sys.indexes AS i 
      ON i.index_id = ddips.index_id 
      AND i.object_id = ddips.object_id

INNER JOIN sys.objects AS o ON o.object_id = ddips.object_id
share|improve this answer

Your Answer

 
discard

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.