May 14, 2009 at 8:04 am
I wrote the following query to list all tables with their schemas that have a fragmentation greater than 20% and are not system tables:
select quotename(sch.name)+'.'+quotename(object_name(idx.object_id)) "Object_name",
idx.index_type_desc, idx.avg_fragmentation_in_percent, idx.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) idx
inner join sys.objects so on (so.object_id = idx.object_id)
inner join sys.schemas sch on (sch.schema_id = so.schema_id)
where so.type = 'U' and idx.avg_fragmentation_in_percent > 20.00
order by idx.avg_fragmentation_in_percent desc
Is this the most efficient way to write this query or is there a more streamlined way to do so?
We have a rather large database used by a third party monitoring tool in the neighborhood of about 60GB that has no clustered indexes or primary keys on any of it's tables, and only has nonclustered indexes defined. Needless to say, we're interested in rebuilding the indexes and creating some clustered ones in the process.
However, the above query takes more than 10 minutes to run, and counting. Anything I'm doing wrong, or is this the price with having a heavily defragmented database with many (and I mean MANY) tables, so we'll just have to be patient with the run time?
Edit Clarification: changed query run time from 8 to 10 minutes. Decided to stop the execution for now.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
May 14, 2009 at 8:23 am
Nothing wrong with your query
the problem is with
sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
Which is always going to take a long time to run
May 14, 2009 at 8:31 am
Samuel Vella (5/14/2009)
Nothing wrong with your querythe problem is with
sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
Which is always going to take a long time to run
I guess that's the nature of the beast...well, I'll have to wait for it to run all the way then.
Thanks.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
May 14, 2009 at 8:39 am
On large databases I would advise to use sampled mode. That will speed up things quite a lot.
sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'SAMPLED')
[font="Verdana"]Markus Bohse[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply