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

Hi every one
1-I need to re-index SQL 2008 Data named POSdata
2-what's the benefits from re-index
3-should i run re-index schedule or while needed
4-is there any assistant tools can i use to verify SQL Data every X time

With my sincere appreciation
LVL 8
TemodyPickalbatros, IT ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
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
TemodyPickalbatros, IT ManagerAuthor Commented:
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
Deepak ChauhanSQL Server DBACommented:
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'

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

TemodyPickalbatros, IT ManagerAuthor Commented:
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
Deepak ChauhanSQL Server DBACommented:
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.
Deepak ChauhanSQL Server DBACommented:
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'

Open in new window

TemodyPickalbatros, IT ManagerAuthor Commented:
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
Deepak ChauhanSQL Server DBACommented:
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
TemodyPickalbatros, IT ManagerAuthor Commented:
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
Deepak ChauhanSQL Server DBACommented:
You should not shrink the database, free space in database would not affect the performance.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TemodyPickalbatros, IT ManagerAuthor Commented:
Thanks eng
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.