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.
srinivas_ganamurAsked:
Who is Participating?
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.

RiteshShahCommented:
second option will be performed on table base not partition wise.
0

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
srinivas_ganamurAuthor Commented:
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
0
RiteshShahCommented:
>> 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.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

srinivas_ganamurAuthor Commented:
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?
0
RiteshShahCommented:
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.
0
RiteshShahCommented:
and yes, one it is fully rebuild, I don't think next time it will take that much large space.
0
srinivas_ganamurAuthor Commented:
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
0
RiteshShahCommented:
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.
0
srinivas_ganamurAuthor Commented:
size of the table 366 GB, fragmentation level i have to check....
0
RiteshShahCommented:
:) well this is bit big size so obviously going to take space for sure. we can't control it at all.
0
srinivas_ganamurAuthor Commented:
Hi Ritesh,


I have posted replication query in EE. Can you give me solution ASAP.
0
St3veMaxCommented:
Table size maybe 366GB, but how big is the index? The required size is about 2 x it's current size (so Index size of 10GB = 22GB required inc a little room to grow).

Try the below to tell you how big things are.
begin try 
	SELECT TOP 1000
		(row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1,
		a3.name AS [schemaname],
		a2.name AS [tablename],
		a1.rows as row_count,
		(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
		a1.data * 8 AS data,
		(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
		(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
	FROM
		(SELECT
			ps.object_id,
			SUM (
				CASE
				WHEN (ps.index_id < 2) THEN row_count
				ELSE 0
			END
			) AS [rows],
			SUM (ps.reserved_page_count) AS reserved,
			SUM (
				CASE
				WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
				ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
			END
			) AS data,
			SUM (ps.used_page_count) AS used
		FROM 
			sys.dm_db_partition_stats ps
		GROUP BY 
			ps.object_id
		) AS a1
	LEFT OUTER JOIN (
		SELECT
			it.parent_id,
			SUM(ps.reserved_page_count) AS reserved,
			SUM(ps.used_page_count) AS used
		FROM 
			sys.dm_db_partition_stats ps
		INNER JOIN 
			sys.internal_tables it ON (it.object_id = ps.object_id)
		WHERE 
			it.internal_type IN (202,204)
		GROUP BY 
		it.parent_id
	) AS a4 ON (a4.parent_id = a1.object_id)
	INNER JOIN 
		sys.all_objects a2  ON ( a1.object_id = a2.object_id )
	INNER JOIN 
		sys.schemas a3 ON (a2.schema_id = a3.schema_id)
	WHERE 
		a2.type <> 'S' and a2.type <> 'IT'
end try
begin catch
	select
	-100 as l1
	,	1 as schemaname
	,       ERROR_NUMBER() as tablename
	,       ERROR_SEVERITY() as row_count
	,       ERROR_STATE() as reserved
	,       ERROR_MESSAGE() as data
	,       1 as index_size
	, 		1 as unused
end catch

Open in new window

0
srinivas_ganamurAuthor Commented:
Hi St3veMax,

the output:

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



0
St3veMaxCommented:
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

0
St3veMaxCommented:
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

0
srinivas_ganamurAuthor Commented:
--
0
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 2005

From novice to tech pro — start learning today.