Vérification de la fragmentation de l'index dans SOLIDWORKS EPDM

Article by Joe Medeiros, CSWE updated August 6, 2015


This article was originally intended to introduce the Reorder and Reorganize tasks in SQL. These tasks will be discussed in other articles, but while researching for this article my mindset on the aforementioned tasks, has shifted somewhat. First let’s look at how to check for fragmentation.

Check Fragmentation Query

If you do a little research you will find a Microsoft Knowledge Base article that mentions the use of the function sys.dm_db_index_physical_stats to check fragmentation. You may also find queries similar to the one below, that will check for fragmentation in all indexes.

SELECTdbschemas.[name] as'Schema',
 dbtables.[name] as'Table',
 dbindexes.[name] as'Index',
 FROMsys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) ASindexstats
 INNERJOINsys.tables dbtables ondbtables.[object_id] = indexstats.[object_id]
 INNERJOINsys.schemas dbschemas ondbtables.[schema_id] = dbschemas.[schema_id]
 INNERJOINsys.indexes ASdbindexes ONdbindexes.[object_id] = indexstats.[object_id]
 ANDindexstats.index_id = dbindexes.index_id
 WHEREindexstats.database_id = DB_ID()
 ORDERBYindexstats.avg_fragmentation_in_percent desc

The SOLIDWORKS Knowledge Base article S-061702 offers a query to find average fragmentation and whether to use the Reorganize or Rebuild tasks. Typically Reorganize would be used for low fragmentation (SOLIDWORKS suggests 30% or lower) and Rebuild for larger fragmentation.

Why not to schedule the Rebuild tasks

These two tasks can be scheduled to run at specified periods of time and this may initially seem to be a smart way of controlling fragmentation, but the below articles make some points on why not to schedule the Rebuild tasks.

The importance of Index Fragmentation

The YouTube video below questions the importance of index fragmentation.

While I still believe that the tasks Reorganize and Rebuild are still valid tools to deal with fragmentation. If there is little or no index fragmentation, scheduling these tasks may not increase performance. An alternative to scheduling these tasks is to regularly check for fragmentation and then use an appropriate task to reduce fragmentation

Liens connexes

Vous voulez en faire plus avec SOLIDWORKS PDM ?

Nos experts certifiés SOLIDWORKS PDM peuvent vous aider :

Trouver du contenu connexe par TAG :

Joe Medeiros, CSWE

Joe Medeiros est un expert certifié SOLIDWORKS et PDM. Il aide les utilisateurs de SOLIDWORKS en matière de formation, de mentorat et de mise en œuvre depuis 1998. Il associe son expérience de l'industrie à une connaissance approfondie des produits SOLIDWORKS pour aider ses clients à réussir. Il partage son expérience et son expertise par le biais de blogs, dont l'un a été intégré au manuel de formation SOLIDWORKS Essentials.