Discover our Clipsal Smart Home packages as seen on the Block

in location

OR

I'm looking for

  • Wholesalers
    • Uncheck All Check All
  • EXPERTISE
    • Expand
    • Expand
    • Expand
    • Uncheck All Check All
  • Switchboard Manufacturers
    • Uncheck All Check All
OR

The best way to see what Clipsal electric products can do for your home is seeing them in action at a Clipsal Display Centre.

Product stock may vary according to location. If you're after a specific product range, please check with your chosen location before visiting.

10 Results
    Load More Load Complete
    Change Location

    How to Check Index Fragmentation on Indexes in a Database

    Issue
    SQL Queries taking longer than normal to complete.

    Product
    Struxureware Power Monitoring
    Power Monitoring Expert
    ION Enterprise
    ION EEM

    Environment
    SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016

    Cause
    When a database is frequently updated via INSERT, UPDATE, or DELETE statements we can expect it to become fragmented over the time.
    If database indexes are fragmented, the SQL Server query optimizer may chose a non-optimal execution plan when using an index to resolve a query.
    This will affect the overall query performance and you may notice a query behaving slower than normal.

    Resolution
    *Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

    The following is a simple query that will list every index on every table in your database, ordered by percentage of index fragmentation.
     
    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 query can be modified to focus on specific tables by append the table name to the 'where' clause:

    WHERE indexstats.database_id = DB_ID() AND dbtables.[name] like '%%'

    In order to reduce fragmentation we will have to reorganize or rebuild the indexes. Choosing between reorganizing or rebuilding depends on the query results.
    For heavily fragmented indexes a rebuild process is needed, otherwise index reorganization should be sufficient.

    The following table summarizes when to use each one:
    Reference Values (in %) Action  SQL statement
    avg_fragmentation_in_percent > 5 AND < 30  Reorganize Index  ALTER INDEX REORGANIZE
    avg_fragmentation_in_percent > 30  Rebuild Index  ALTER INDEX REBUILD

    Didn’t find what you were looking for?

    Try Searching Again View Our Categories

    Need further assistance?

    Our Customer Care department provides total customer service solutions for our residential, industrial and commercial applications.

    Get Assistance