4 Replies Latest reply on Mar 11, 2015 11:59 AM by contactjt

    NPM DB Index Fragmentation

    jonchill

      I've just implemented AppInsight on both of our NPM SQL DB's, one DB is fine but the other one is showing 10 indexes over 75% fragmentation of which 5 are over 83% and ones 0ver 90%.

       

      I thought Indexes were fragmented as part of the overnight NPM maintenance, if this isn't the case how do I go about fragmenting the indexes (I have little SQL knowledge and we don;t have a DBA)?

       

      Thanks

       

      Jon

        • Re: NPM DB Index Fragmentation
          Jan Pelousek

          I assume you don't have enabled the index defragmentation within th DB maintenance. To enable it, please follow this article: SolarWinds Knowledge Base :: Critical index fragmentation detected during database maintenance,

          I think the only tables excluded from index defragmentation are NetFlow statistical tables. After you enable it, the indexes are defragmented during DB maintenance.

            • Re: NPM DB Index Fragmentation
              jonchill

              Thanks for the info Jan, I've made the changes to the DB so hopefully that will sort the problems out.

              • Re: NPM DB Index Fragmentation
                jonchill

                Making a change to the DB maintenance and enabling index fragmentation on Friday hasn't made any difference to the fragmentation levels.

                  • Re: NPM DB Index Fragmentation
                    contactjt

                    To be honest I wouldn't worry too much if users are not complaining or you don't see a lot of web resources loading up slowly or timing out. The way the DB works is that the data is 'rolled up' from the detailed level with each poll (kept as detailed for 7 days I think by default). Nightly this rolls up this into hourly averages (kept for a month?) then into daily averages. This process causes a ton of DB changes and it happens every night as there is always from data to average and expire.

                     

                    There is this KB article on how to enable the Solarwinds defragment tool but as I said the Solarwinds one seems to choke on my data.

                    http://knowledgebase.solarwinds.com/kb/questions/3202/Critical+index+fragmentation+detected+during+database+maintenance

                     

                     

                     

                    Another thing is have you checked for fragmentation after your DB folks script runs? The SW maintenance happens around 3am so if your DB script runs after that then the morning shouldn't be too bad. Run this script on the Solarwinds DB:

                     

                    SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
                    ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
                    indexstats.avg_fragmentation_in_percent 
                    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
                    INNER JOIN sys.indexes ind  
                    ON ind.object_id = indexstats.object_id 
                    AND ind.index_id = indexstats.index_id 
                    WHERE indexstats.avg_fragmentation_in_percent > 30 
                    ORDER BY indexstats.avg_fragmentation_in_percent DESC
                    

                     

                    There are about 105 tables in a base NPM DB. This script show show you only ones with fragmentation over 30%