asked on

Index Reoragnise/rebuild - Partioned Table

I have huge database around 2 tb all most of the tables are partioned.

1. Rebuilding an index/reoragnise at partition level is better or the whole table.

2. If I have to rebuild/reoragnise all partition

is it better by giving partition # like below

ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5;
GO

or

ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
PARTITION = ALL

Is the second option is same as rebuild index on the whole table or it goes one partition at a time.

ASKER CERTIFIED SOLUTION

RiteshShah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

srinivas_ganamur

ASKER

Hi Riteshshah,

i am executing second option, the log size is growing very high (user db log and tempdb log).

what could be the reason?

the output of sp_space used :

sp_spaceused 'Production.TransactionHistory'

name rows reseved data index_size unused
Production.TransactionHistory 438316722 184314936 KB 159387920 KB 24903992 KB 23024 KB

RiteshShah

>> i am executing second option, the log size is growing very high (user db log and tempdb log).

what could be the reason?<<

because this operation is logged operation and suppose to make an entry in log file so obviously it will grow big, depends on the size of table. Moreover, rearrange performs in tempdb so it will be increased for sure.

srinivas_ganamur

ASKER

But some other tables (large portioned tables) not taking that much of log space as compared this table.

Particularly this table only taking large log space.

by running dbcc checkalloc (tblname)...any possible solutions my problem?

RiteshShah

well this is not something anybody can handle, only SQL Server can decide how much space will be consumed in process. it is depends on table size, fragmentation level etc.

RiteshShah

and yes, one it is fully rebuild, I don't think next time it will take that much large space.

srinivas_ganamur

ASKER

No Ritesh, every time when i am rebuiliding... it was taking more space only for this object, no other objects are taking space)...after shrinking log file. i wont get original space

RiteshShah

what is the size of that table and fragmentation level? if table is big and heavily manipulated than obviously it is going to take space.

srinivas_ganamur

ASKER

size of the table 366 GB, fragmentation level i have to check....

RiteshShah

:) well this is bit big size so obviously going to take space for sure. we can't control it at all.

srinivas_ganamur

ASKER

Hi Ritesh,

I have posted replication query in EE. Can you give me solution ASAP.

SOLUTION

St3veMax

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

srinivas_ganamur

ASKER

Hi St3veMax,

the output:

schemename dbo
tablename X
rowcount 438316722
reserved 184314936
data 159387920
indexsize 24903992
unused 23024

St3veMax

rowcount 438,316,722
reserved 184,314,936
data 159,387,920
indexsize 24,903,992
unused 2302

OK, From that I can see there's 438m rows occupying 160GB on disk with 25GB Free.

The index is approx 25GB, so would estimate that you need another 30GB at least on disk as available for the database to grow into. If your logs are on a seperate drive, I would also ensure there's at least the same amount free.

HTH

St3veMax

Index's are often a factor which people dont account for when sizing a database. Index's typically comsume upto 20-30% of the data size.

Use the attached query to determine the MAX row size for each row and multiply by the projected number of rows. You need to /1024 ^3 to get a GB figure and then take 30% of that to add on for index's.

HTH

SELECT
	SUM(max_length)
FROM
	sys.columns
WHERE
	object_id = object_id('lmt_logs_new')

Open in new window

srinivas_ganamur

ASKER