October 13, 2008 at 9:58 am
I have a table thats about 400mb and it has a index thats over a 1gb. I plan on truncating the table and my question is do I need to rebuild the index after truncating the table to get the index size under control? Also is it normal to have an index three times the size of the table?
October 13, 2008 at 10:48 am
Check the FillFactor on the index. It is also possible to have a badly fragmented index bloating it's size.
October 13, 2008 at 10:55 am
I have checked that and the index is just big
Page Fullness = 99.35%
Total fragmentation = 0.01%
Leaf-level rows = 131652200
Maximum row size = 187
Minimum row size = 125
Pages = 2399128
October 13, 2008 at 12:45 pm
Can you post the table and index definitions please? Also the output of sp_spaceused for that table
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2008 at 1:28 pm
sp_spaceused
name = performance_sample
rows = 11479644
reserved = 1543736 KB
data = 499120 KB
index_size = 1044464 KB
unused = 152 KB
USE [UpTime]
GO
/****** Object: Table [dbo].[performance_sample] Script Date: 10/13/2008 14:23:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[performance_sample](
[id] [numeric](19, 0) IDENTITY(1,1) NOT NULL,
[erdc_id] [numeric](19, 0) NOT NULL,
[uptimehost_id] [numeric](19, 0) NOT NULL,
[sample_time] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
October 13, 2008 at 2:45 pm
Are there any other indexes?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2008 at 4:35 am
... do I need to rebuild the index after truncating the table to get the index size under control?...
If you truncate the table, SQL marks the table as empty/reusable. All correspondending indexspace is also marked as empty/reusable. Actually, you can't rebuild the index, because there's no data (for an index).
However, the space is still occupied in the datafile.
Wilfred
The best things in life are the simple things
October 14, 2008 at 4:52 am
Wilfred van Dijk (10/14/2008)
... do I need to rebuild the index after truncating the table to get the index size under control?...
If you truncate the table, SQL marks the table as empty/reusable. All correspondending indexspace is also marked as empty/reusable. Actually, you can't rebuild the index, because there's no data (for an index).
However, the space is still occupied in the datafile.
That's why I love Oracle's syntax/functionality on TRUNCATE
By default Oracle's TRUNCATE moves the HWM -High Water Mark of the affected table and indexes to Zero.
In regards to the actual storage Oracle's TRUNCATE has a STORAGE clause. DROP STORAGE would return all data and index previously used storage space to the related tablespaces; The opposite option KEEPS the storage allocated to affected table and indexes.
You have to admit, that's cool 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 14, 2008 at 5:45 am
You have to admit, that's cool
"It depends"... 😛
Yeah, I know. Being an Oracle DBA for 8 years, I have to say some (many?) functionalities are more sophisticated, compared to MSSQL.
Wilfred
The best things in life are the simple things
October 14, 2008 at 7:19 am
I should'nt have said Truncate, I don't want to remove all the data just older records. I will be deleteing around a 700,000 rows. I'm not sure how the index will respond. If I need to rebuild it or what to do with it after i delete these records.
October 14, 2008 at 10:32 am
A rebuild won't hurt. Whether or not it's necessary depends on where the rows you're deleting are in that index. If scattered all over the place, then a rebuild's not a bad idea.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply