How to re-index data in SQL server 2008 enterprise full

2-what's the benefits from re-index

In the simple word, continous insert \update\ delete operation lead to index fragmentation which cause slow query and longer disk IO time while accessing the table data. Reindexing (reorganizing \rebuilding indexes) is the fix of the fragmentation.

Index reorgize when fragmentation level <= 30
Index renbuild when fragmentation level > 30


 3-should i run re-index schedule or while needed
You should schedule it as a weekly\fortnightly\ monthly maintenance activity.

4-is there any assistant tools can i use to verify SQL Data every X time

You can schedule it using SQL server agent job.

here is the very good script for index mentinance you can download and schedule this script in SQL agent JOB.

Referring > https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

You have SQL server Enterprise edition install so you can take the advantage of ONLINE rebuild

Thanks eng
i have downloaded this file form site above
IndexOptimize.sql

it's a massive command :)
where should i enter my SQL Data name

You have to download two more scripts from there. 1. CommandExecute.sql  and 2. CommandLog.sql

Open these scripts in SSMS and execute these script will create a table for log and a SP to execute the log command.

You have downloaded the "IndexOptimize.sql" script now open this script in SSMS and execute.
A stored procedure named IndexOptimize will be created in Master database. Now question is how to use this stored procedure. I have set the code for you and "@databases=' '............" are the few options you can change accordingly if require.

---@Databases = 'USER_DATABASES', (index maintenance for all user database on the server)
---@Databases = 'POSData', (index maintenance only for one database eg POSData)
---@Databases = 'POSData, Database2, Dataabse3', (index maintenance only for selected databases)

Use master
Go
EXECUTE dbo.IndexOptimize
 @Databases = 'POSData',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @UpdateStatistics = 'ALL',
 @OnlyModifiedStatistics = 'Y',
 @LogToTable = 'Y'

Select allOpen in new window

Date and time: 2015-06-08 21:20:29
Server: SQL
Version: 10.0.1600.22
Edition: Enterprise Edition (64-bit)
Procedure: [master].[dbo].[IndexOptimize]
Parameters: @Databases = 'POSData', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @PageCountLevel = 1000, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = NULL, @TimeLimit = NULL, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @LockTimeout = NULL, @LogToTable = 'Y', @Execute = 'Y'
Source: https://ola.hallengren.com
 
Msg 50000, Level 16, State 1, Procedure IndexOptimize, Line 227
The stored procedure CommandExecute is missing. Download https://ola.hallengren.com/scripts/CommandExecute.sql.
 
Msg 50000, Level 16, State 1, Procedure IndexOptimize, Line 241
The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.
 
Date and time: 2015-06-08 21:20:29

My Dear, I suggested you above that you have to sownload two more script from that link and run the script in same way like you have created IndexOptimize stored procedure in Master database.

You have to download two more scripts from there. 1. CommandExecute.sql  and 2. CommandLog.sql

 Open these scripts in SSMS and execute these script will create a table for log and a SP to execute the log command.

Then execute the dbo.IndexOptimize stored procedure

Use master
Go
EXECUTE dbo.IndexOptimize
 @Databases = 'POSData',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @UpdateStatistics = 'ALL',
 @OnlyModifiedStatistics = 'Y',
 @LogToTable = 'Y'

Select allOpen in new window

dear Eng deepakChauhan

thanks i already download the 2 script you mentioned above
but i run those on posdata so it doesn't work
so i run the 2 script in master data it's work fine Thanks ;-)
i got now this result ( but it's run and finish so quickly is that normal because  i think it will take a while )

the result
Date and time: 2015-06-09 10:54:34
Server: SQL
Version: 10.0.1600.22
Edition: Enterprise Edition (64-bit)
Procedure: [master].[dbo].[IndexOptimize]
Parameters: @Databases = 'POSData', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @PageCountLevel = 1000, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = NULL, @TimeLimit = NULL, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @LockTimeout = NULL, @LogToTable = 'Y', @Execute = 'Y'
Source: https://ola.hallengren.com
 
Date and time: 2015-06-09 10:54:34
Database: [POSdata]
Status: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: FULL
 
Date and time: 2015-06-09 10:54:34

It means there should be no fragmentation in "posdata" . You can check the log in commandlog table at master database.

and check by executing this query

use POSdata
GO
SELECT
      object_id, index_id, avg_fragmentation_in_percent
      FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'detailed')
      WHERE index_id >4

wow it's work thanks ;)
but the database size extended from 505.43 MB TO 1283.25 MB is that normal
another question could i shrink the data to reduce that extend size or leave it as it is
the size not my problem all i need is the performance

Thanks eng