SolarWinds sell a great solution for monitoring your network and your servers, but regarding the maintenance of the server itself or its database, there is a leak. After serveral months of intensive utilization, indexes from all tables become fragmented and the responsiveness from the SolarWinds Orion decrease a lot. At the beginning, in less than 10 seconds everything was finished but now, you can have a break and take a coffee and be back to see slowly the result.
I decided to build a script (Basic MS SQL) to select the most fragmented table (and the most heavy) and to build the “Rebuild script”.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
SET NOCOUNT ON DECLARE @myTables TABLE( tab varchar(256) NOT NULL, ind varchar(256) NOT NULL ); Declare @tabt varchar(256); Declare @indt varchar(256); INSERT INTO @myTables SELECT dbtables.[name] as 'Table', dbindexes.[name] as 'Index' 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() and (indexstats.page_count * indexstats.avg_fragmentation_in_percent) > 10000 --ORDER BY indexstats.avg_fragmentation_in_percent desc ORDER BY indexstats.page_count * (indexstats.avg_fragmentation_in_percent) desc; WHILE (SELECT COUNT(*) FROM @myTables) > 0 BEGIN SELECT TOP 1 @indt = ind, @tabt = tab FROM @myTables; DELETE @myTables WHERE ind = @indt AND tab = @tabt; PRINT 'USE [SolarWindsOrion]' PRINT 'GO' PRINT 'ALTER INDEX '+@indt+' ON [dbo].['+@tabt+'] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )'; PRINT 'GO' PRINT 'PRINT ''The index '+@indt+' from '+@tabt+' has been defragmented'''; END; |
It’s not perfect (it’s crappy, I know) but it solved my problem.