X
818-435-4527 sales@rebsi.com

Latest News

ACES PIES Rebuild Reorganize Indexes SQL Server

ACES PIES Rebuild Reorganize Indexes SQL Server

SQL Server database table indexes just like your hard drive can use a bit of housekeeping from time to time.  The same way your hard drive becomes fragmented, your SQL Server table indexes become fragmented as well.

Most larger companies have a SQL DBA on staff to monitor their SQL databases and reorganize or rebuild table indexes from time to time.  The improvement in performance can be night and day.

There are two different procedures that can be performed.  One is a complete rebuilding of the indexes during which time the table in question can not be used.

The other is a reorganizing of the indexes during which time the tables can be used normally.

Link to YouTube Video Tutorial

Rebuild All Inventory Indexes

Open MS SQL Server Management Studio.  Right click the database named Auto Plus and select “New Query”.

Paste the following text in your new query and execute.  Close the query when it is done and do not save.  This will not only build the stored procedure, this query will run the stored procedure as well.  This will rebuild all inventory tables.

CREATE PROCEDURE [dbo].[sp_Rebuild_All_Inventory_Indexes]
AS
BEGIN
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+’.’+name AS TableName
FROM sys.tables WHERE name LIKE ‘Inventory%’
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘ALTER INDEX ALL ON ‘ + @TableName + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
END

Rebuild Inventory Vehicle Indexes

Open MS SQL Server Management Studio.  Right click the database named Auto Plus and select “New Query”.

Paste the following text in your new query and execute.  Close the query when it is done and do not save.  This will not only build the stored procedure, this query will run the stored procedure as well.  This will rebuild all inventory vehicle tables.

CREATE PROCEDURE [dbo].[sp_Rebuild_All_InventoryVc_Indexes]
AS
BEGIN
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+’.’+name AS TableName
FROM sys.tables WHERE name LIKE ‘InventoryVc%’
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘ALTER INDEX ALL ON ‘ + @TableName + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
END

Rebuild All ACES VCdb Indexes

Open MS SQL Server Management Studio.  Right click the database named Auto Plus and select “New Query”.

Paste the following text in your new query and execute.  Close the query when it is done and do not save.  This will not only build the stored procedure, this query will run the stored procedure as well.  This will rebuild all inventory vehicle tables.

CREATE PROCEDURE [dbo].[sp_Rebuild_All_vc_Indexes]
AS
BEGIN
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+’.’+name AS TableName
FROM sys.tables WHERE name LIKE ‘vc%’
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘ALTER INDEX ALL ON ‘ + @TableName + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
END

Reorganize All Inventory Indexes

Open MS SQL Server Management Studio.  Right click the database named Auto Plus and select “New Query”.

Paste the following text in your new query and execute.  Close the query when it is done and do not save.  This will create a stored procedure that will reorganize all of the inventory table indexes.

CREATE PROCEDURE [dbo].[sp_ReOrganize_All_Inventory_Indexes]
AS
BEGIN
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+’.’+name AS TableName
FROM sys.tables WHERE name LIKE ‘Inventory%’
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘ALTER INDEX ALL ON ‘ + @TableName + ‘ REORGANIZE’
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
END

Reorganize All Inventory Vehicle Indexes

Open MS SQL Server Management Studio.  Right click the database named Auto Plus and select “New Query”.

Paste the following text in your new query and execute.  Close the query when it is done and do not save.  This will create a stored procedure that will reorganize all of the inventory vehicle table indexes.

CREATE PROCEDURE [dbo].[sp_ReOrganize_All_InventoryVc_Indexes]
AS
BEGIN
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+’.’+name AS TableName
FROM sys.tables WHERE name LIKE ‘InventoryVc%’
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘ALTER INDEX ALL ON ‘ + @TableName + ‘ REORGANIZE’
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
END

Reorganize All ACES VCdb Indexes

Open MS SQL Server Management Studio.  Right click the database named Auto Plus and select “New Query”.

Paste the following text in your new query and execute.  Close the query when it is done and do not save.  This will create a stored procedure that will reorganize all of the ACES VCdb table indexes.

CREATE PROCEDURE [dbo].[sp_ReOrganize_All_vc_Indexes]
AS
BEGIN
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+’.’+name AS TableName
FROM sys.tables WHERE name LIKE ‘vc%’
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘ALTER INDEX ALL ON ‘ + @TableName + ‘ REORGANIZE’
EXEC (@sql)
–Print @sql
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
END

Tags:

About the Author

ACES and PIES XML import and export guru.
[extraWatchAgent]