March 10, 2014 at 10:06 am
Hello all,
I've written a little .net program that loops all my servers/ instances/ databases and uses this script to show me fragmented indexes...
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc
This has worked great on most of my servers, but on one server, there are a few indexes that doesn't have any names..
Its a table / field that an ex employee had created, and the fragmentation is 81%.
I've tried to use SQL's own rebuild all indexes, and TSQL to rebuild all indexes, but nothing touches it.
When viewing the indexes by expanding the table / indexes its not listed...
Any idea how I can rebuild an index that I can't see ( apart from the above script anyway )
Thanks in advance..
Dave
March 10, 2014 at 10:16 am
what is the index id of these nameless indexes? I am betting it is an id of 0. But let's confirm for certain before jumping down any assumptions.
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
March 10, 2014 at 10:23 am
Thanks for the reply Jason,
yeah, they're all Zero.
I've got 12 of these little monkeys.. all on the same Database.
March 10, 2014 at 10:29 am
Those are your heaps. You may want to investigate the creation of a clustered index on those tables.
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
March 10, 2014 at 10:48 am
I see..
To create a heap, create a table without a clustered index. If a table already has a clustered index, drop the clustered index to return the table to a heap.
To remove a heap, create a clustered index on the heap.
To rebuild a heap to reclaim wasted space, create a clustered index on the heap, and then drop that clustered index.
Thank you for your assistance Jason.
Dave
March 10, 2014 at 10:49 am
hb21l6 (3/10/2014)
I see..To create a heap, create a table without a clustered index. If a table already has a clustered index, drop the clustered index to return the table to a heap.
To remove a heap, create a clustered index on the heap.
To rebuild a heap to reclaim wasted space, create a clustered index on the heap, and then drop that clustered index.
Thank you for your assistance Jason.
Dave
FWIW - do not drop that Clustered Index after you create it. Just leave it in place.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply