Post reply

Double join with with sys.dm_db_index_physical_stats and sys.schemas, taking a long time

  • May 14, 2009 at 8:04 am

    #132274

    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

    #994570

    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

    #994580

    Samuel Vella (5/14/2009)

    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

    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

    #994588

    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