Submit a ticketCall us

Don’t fall victim to a ransomware attack
Backups are helpful, but sometimes that’s not enough to protect your business against ransomware. At our live webcast we will discuss how to protect against ransomware attacks with SolarWinds® Patch Manager and how to leverage log data to detect ransomware. Register now for our live webcast.

Home > Success Center > Network Performance Monitor (NPM) > Query to reindex the entire Orion database

Query to reindex the entire Orion database

Table of contents
Created by Wendell Bazile, last modified by Chris Klinedinst on May 30, 2017

Views: 129 Votes: 2 Revisions: 10

Overview

This article provides an alternative to using the Fragmented Index clean up in our Database Maintenance process.

Environment

-Customers may want to run this several times in a day (instead of once with DBM)

-DBA's may want to run this as part of their own Maintenance routines

Steps

 

THESE SCRIPTS MAY CAUSE SQL PERFORMANCE ISSUES, PLEASE CONSULT A DBA BEFORE EXECUTING

 

The following script is supplied by Microsoft to rebuild or reorganize indexes based on fragmentation level. This is very similar to what we use in Database Maintenance. This script will not work in Database Manager:

 

----------------------------------Begin Script----------------------------------

SET NOCOUNT ON;  
DECLARE @objectid int;  
DECLARE @indexid int;  
DECLARE @partitioncount bigint;  
DECLARE @schemaname nvarchar(130);   
DECLARE @objectname nvarchar(130);   
DECLARE @indexname nvarchar(130);   
DECLARE @partitionnum bigint;  
DECLARE @partitions bigint;  
DECLARE @frag float;  
DECLARE @command nvarchar(4000);   
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function   
-- and convert object and index IDs to names.  
SELECT  
    object_id AS objectid,  
    index_id AS indexid,  
    partition_number AS partitionnum,  
    avg_fragmentation_in_percent AS frag  
INTO #work_to_do  
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')  
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;  

-- Declare the cursor for the list of partitions to be processed.  
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;  

-- Open the cursor.  
OPEN partitions;  

-- Loop through the partitions.  
WHILE (1=1)  
    BEGIN;  
        FETCH NEXT  
           FROM partitions  
           INTO @objectid, @indexid, @partitionnum, @frag;  
        IF @@FETCH_STATUS < 0 BREAK;  
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)  
        FROM sys.objects AS o  
        JOIN sys.schemas as s ON s.schema_id = o.schema_id  
        WHERE o.object_id = @objectid;  
        SELECT @indexname = QUOTENAME(name)  
        FROM sys.indexes  
        WHERE  object_id = @objectid AND index_id = @indexid;  
        SELECT @partitioncount = count (*)  
        FROM sys.partitions  
        WHERE object_id = @objectid AND index_id = @indexid;  

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.  
        IF @frag < 30.0  
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';  
        IF @frag >= 30.0  
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';  
        IF @partitioncount > 1  
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));  
        EXEC (@command);  
        PRINT N'Executed: ' + @command;  
    END;  

-- Close and deallocate the cursor.  
CLOSE partitions;  
DEALLOCATE partitions;  

-- Drop the temporary table.  
DROP TABLE #work_to_do;  
GO  

----------------------------------End Script -----------------------------------

 

The following query rebuilds index blindly (without checking their fragmentation level) but excludes large tables: 

 

----------------------------------Begin Script----------------------------------

DECLARE @CoreTable VARCHAR(255)
DECLARE CoreCursor
CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
AND table_name not like '%Trap%'
AND table_name not like '%syslog%'
AND table_name not like '%CustomPoller%'
AND table_name not like '%Wireless%'
AND table_name not like '%_detail%'
AND table_name not like '%_hourly%'
AND table_name not like '%_daily%'
AND table_name not like '%Netpath%'
OPEN CoreCursor
FETCH NEXT
FROM CoreCursor INTO @CoreTable
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing Table:  ' + @CoreTable
DBCC DBREINDEX(@CoreTable, '', 80)
FETCH NEXT
FROM CoreCursor INTO @CoreTable
END
CLOSE CoreCursor
DEALLOCATE CoreCursor

----------------------------------End Script -----------------------------------
 

 

 

Last modified
15:32, 30 May 2017

Tags

Classifications

Internal Use Only