Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
maharjb
Starting Member
2 Posts |
 Posted - 2009-04-05 : 18:39:19
|
| When running the following query against my database to detect the percentage of fragmentation.SELECT a.index_id, name, avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'<mydatabase>'),NULL, NULL, NULL) AS aJOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;I get a result set showing numerous istances of highly fragmented indexes(see attached) with the name NULL. I assume these were created automatically by SQL2005 as a result of not having a clustered index on the table being updated.My question is this. How can I drop these these NULL indexes when I don't know the specific table that they are associated with? If I add an index to a table that currently has no index but I know has been updated multiple times will the NULL instance take on the new name so that I can run a rebuild on that indexed table? If not what is my course of action? |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2009-04-05 : 21:18:54
|
I think what you want to do is just rebuild your index's. If you are using SSMS, just right click the index and it'll be a option.
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
 |
|
|
maharjb
Starting Member
2 Posts |
Posted - 2009-04-06 : 07:49:25
|
| Rebuilding known indexes is no problem in SSMS. The problem is that these NUL indexes don't appear as indexes for any tables in my database. How can I rebuild an index that is not associated with a specific table. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-04-06 : 08:10:18
|
| Can you check if they're hypothetical indexes (INDEXPROPERTY(id, name, 'IsHypothetical')=1)? If they are, you can't rebuild them, they're used by the performance tuning wizard. If the name is null you can't do anything with them anyway, short of dropping the table. |
|
|
|
|
|
|
|
|
Removing NULL Indexes