CODEWITHSUNDEEP
sqlsql-serversql-server-2005indexing
Murtaza Badshah
Murtaza Badshah

Reputation: 221

Rebuilding of the Indexes

Index rebuilding is taking too much time. what could be the possible factors affecting the rebuilding of indexes. usually when i do the rebuilding it doesn't take more than a min.

Syntax :

ALTER INDEX Pk_customer ON Customer REBUILD WITH (FILLFACTOR=90, SORT_IN_TEMPDB =    ON,STATISTICS_NORECOMPUTE = OFF) Go

Table structure : 

CREATE TABLE [dbo].[customer](
[pkey] [bigint] IDENTITY(1,1) NOT NULL,
[cid] [char](15) 
[name] [char](100) 
[cust_group] [char](20) 
[language] [char](2) 
[start] [datetime] NOT NULL,
[stop] [datetime] NOT NULL,
[country] [char](2) 
[_INSERTED] [datetime] NULL 
[_INSERTEDBY] [varchar](max) 
[_UPDATED] [datetime] NULL,
[_UPDATEDBY] [char](20) 
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
[pkey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [FG_BASE]) ON [FG_BASE]

Upvotes: 0

Views: 106

Answers (1)

user275683
user275683

Reputation:

You said:

what could be the possible factors affecting the rebuilding of indexes. usually when i do the rebuilding it doesn't take more than a min.

So here are possible factors, unless you specify details we can't say for sure:

  • Server utilization during the time of rebuild
    • Other jobs, backups, exports, reports, expensive queries and etc.
  • Index fragmentation percentage at the time of rebuild
  • Size of the table
  • Overall server performance (CPU, RAM, Disk utilization) outside of SQL Server.

Upvotes: 3

Related Questions