September 8, 2011 at 8:23 am
I have a database refresh process in SSIS where I need to drop indexes and truncate the tables. Which would be faster? Truncating first and then truncating or truncate first and then drop index? Or does it matter which you do first?
September 8, 2011 at 8:29 am
I don't think the difference would really be measurable, especially in terms of some process you do once a day. Having said that my vote would be to truncate then drop the index if you needed to. My guess is that the truncate does most of what the drop index would do anyway but probably more efficiently.
The probability of survival is inversely proportional to the angle of arrival.
September 8, 2011 at 8:38 am
Thanks, I pasted the truncate in front of the drop index statements and it sped the process up a good bit.
September 8, 2011 at 8:45 am
I would personnally disable the indexes. That way you don't have to bother about keeping the packages in sync with the index mods for tuning or whatknot.
September 8, 2011 at 8:59 am
It depends on the size of the load, number of indexes, etc. I've seen it be faster (significantly) on the drop indexes at times. However not always.
Test
September 8, 2011 at 9:12 am
Ninja's_RGR'us (9/8/2011)
I would personnally disable the indexes. That way you don't have to bother about keeping the packages in sync with the index mods for tuning or whatknot.
Only issue with that is that unless the distribution is consistent from day to day, the statistics would be out of date when you re-enable the indexes. Unlike if your dropped and re-created the index.
The probability of survival is inversely proportional to the angle of arrival.
September 8, 2011 at 9:22 am
sturner (9/8/2011)
Ninja's_RGR'us (9/8/2011)
I would personnally disable the indexes. That way you don't have to bother about keeping the packages in sync with the index mods for tuning or whatknot.Only issue with that is that unless the distribution is consistent from day to day, the statistics would be out of date when you re-enable the indexes.
The only way to re-enable an index is to rebuild it, and an index rebuild updates statistics with full scan
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
September 8, 2011 at 9:27 am
GilaMonster (9/8/2011)
The only way to re-enable an index is to rebuild it, and an index rebuild updates statistics with full scan
The defense rests.
The probability of survival is inversely proportional to the angle of arrival.
September 8, 2011 at 9:40 am
It would be better to leave the clustered index in place and load the table in sequence by the clustered index. This is usually faster than rebuilding the clustered index after the load.
So:
1. Truncate the table.
2. Drop or disable the non-clustered indexes.
3. Load the table in order by the clustered index.
4. Re-create or re-build the non-clustered indexes after the load.
September 8, 2011 at 9:58 am
sturner (9/8/2011)
GilaMonster (9/8/2011)
The only way to re-enable an index is to rebuild it, and an index rebuild updates statistics with full scanThe defense rests.
Just not to waste a newly minted demo script...
and to kill defense once and for all so it may RIP.
SET STATISTICS IO, TIME OFF
SET NOCOUNT ON
SET IMPLICIT_TRANSACTIONS OFF
BEGIN TRAN
DECLARE @stats TABLE (RANGE_HI_KEY INT, RANGE_ROWS INT, EQ_ROWS INT, DISTINCT_RANGE_ROWS INT, AVG_RANGE_ROWS INT)
CREATE TABLE dbo.a (ID INT IDENTITY(1,1), val VARCHAR(10) NOT NULL, n INT NOT NULL, CONSTRAINT PK_a PRIMARY KEY CLUSTERED (ID))
INSERT INTO dbo.a (
val,
n
)
--Min / max range to 100
SELECT TOP 10000 LEFT(NEWID(), 10), CHECKSUM(NEWID()) % 101 FROM sys.all_columns
CREATE NONCLUSTERED INDEX n ON dbo.a (n)
INSERT INTO @stats (
RANGE_HI_KEY,
RANGE_ROWS,
EQ_ROWS,
DISTINCT_RANGE_ROWS,
AVG_RANGE_ROWS
)
EXEC ('DBCC SHOW_STATISTICS (''dbo.a'', ''n'') WITH HISTOGRAM')
SELECT MIN(RANGE_HI_KEY) AS Mini, MAX(RANGE_HI_KEY) AS Maxi FROM @stats
-- -100 to 100
DELETE FROM @stats
ALTER INDEX n ON dbo.a DISABLE
DELETE FROM dbo.a
INSERT INTO dbo.a (
val,
n
)
--change min / max range to 10K
SELECT TOP 10000 LEFT(NEWID(), 10), CHECKSUM(NEWID()) % 10001 FROM sys.all_columns
--Re enable the NC index
ALTER INDEX n ON dbo.a REBUILD
INSERT INTO @stats (
RANGE_HI_KEY,
RANGE_ROWS,
EQ_ROWS,
DISTINCT_RANGE_ROWS,
AVG_RANGE_ROWS
)
EXEC ('DBCC SHOW_STATISTICS (''dbo.a'', ''n'') WITH HISTOGRAM')
SELECT MIN(RANGE_HI_KEY) AS Mini, MAX(RANGE_HI_KEY) AS Maxi FROM @stats
-- -9993 to 9989
ROLLBACK
September 8, 2011 at 12:21 pm
I know the OP is asking about Truncating, but I would Disable (or drop) indexes first, then Truncate. The reason is that when you link the tasks in SSIS, people tend to just edit them in place. And if you ever changed to a pattern where you deleted instead of truncate, you'll want to disable your indexes first, as deleting large amounts of data with indexes in place can be pretty slow. Granted re-arranging SISS tasks isn't a big deal, but just food for thought when establishing a pattern.
September 13, 2011 at 8:31 am
Truncate is not logged so i would assume it being faster!
September 13, 2011 at 8:35 am
THE-FHA (9/13/2011)
Truncate is not logged so i would assume it being faster!
Myth!
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
September 13, 2011 at 8:35 am
THE-FHA (9/13/2011)
Truncate is not logged so i would assume it being faster!
Wrong, minimally logged, but logged.
http://www.sqlservercentral.com/Forums/Topic908285-1198-1.aspx
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply