Index Where Name = NULL

Question

Index Where Name = NULL

johnwolfe2008

Newbie

Points: 3
More actions
January 29, 2010 at 8:48 am

#216215

When I pull fragmentation stats from sys.dm_db_index_physical_stats, I find more than a dozen highly fragmented indexes with the name = "NULL". When I look at the related tables, I don't find any indexes. This is a legay database that I recently inherited. I'm using SQL 2005.
I will deeply appreciate any response.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281344 More actions · Answer 1

Looks like you have Heaps (which will show as indexes in the views). To defrag these, you will need to create a clustered index on the table.

However, it may not be necessary if the tables do not have 1000 pages (a typical starting point for defrag recommendations), or the tables are really small.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004696 More actions · Answer 2

Unless a table is nothing more than a temp table, I put clustered indexes on all tables as a matter of rote. That way, I don't have to worry about them if they ever need to grow. Clustered indexes just don't take up that much room.

I may also put a clustered index on a temp table depending on the need for speed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

Jeff Moden (1/29/2010)
Unless a table is nothing more than a temp table, I put clustered indexes on all tables as a matter of rote. That way, I don't have to worry about them if they ever need to grow. Clustered indexes just don't take up that much room.
I may also put a clustered index on a temp table depending on the need for speed.

hehehe. That is actually the method to my madness. Even on temp tables. I was trying to be PC in regards to design philosophies.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004696 More actions · Answer 4

Heh... sorry Jason... I don't know what you mean by "PC" in this case. I get the jist but not the meaning. 🙂

Sounds like your design philosophy and mine are pretty much the same. When I get to a new SQL Server site, one of the things I do in my server/DB health checks is to find all the tables that have no clustered index so I can "fix" them and for those that do have a clustered index, find out what the index is based on and why because they might need fixing, too. Of course, I do that check in order based on size of the table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281344 More actions · Answer 5

Jeff Moden (1/30/2010)
...
Sounds like your design philosophy and mine are pretty much the same. When I get to a new SQL Server site, one of the things I do in my server/DB health checks is to find all the tables that have no clustered index so I can "fix" them and for those that do have a clustered index, find out what the index is based on and why because they might need fixing, too. Of course, I do that check in order based on size of the table.

Yup, same process. I started at one place that had no clustered indexes on any tables. They believed it was unnecessary. With clustered indexes in place, they couldn't believe the performance gain.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004696 More actions · Answer 6

Not to mention the space savings on disk if any deletes occurred on the heaps.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)