Checking for Index Fragmentation in SOLIDWORKS EPDM

Article by Joe Medeiros, CSWE created/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',
 indexstats.avg_fragmentation_in_percent,
 indexstats.page_count
 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

Posts related to 'Checking for Index Fragmentation in SOLIDWORKS EPDM'

Find Related Content by TAG:

Joe Medeiros, CSWE

Joe Medeiros is a SOLIDWORKS and PDM Certified Expert. He has been helping SOLIDWORKS users with training, mentoring and implementations since 1998. He combines industry experience with a thorough understanding of SOLIDWORKS products to assist customers in being successful. He shares his experience and expertise through blogs; one of which has been incorporated into the SOLIDWORKS Essentials training manual.

Want to learn SOLIDWORKS?

Take a training course from our team of Certified SOLIDWORKS Experts