September 23, 2017 at 6:48 pm
Hi folks... I'm hoping for some guidance on TRUNCATE and NONCLUSTERED INDEXES. I've been told conflicting information from a couple different DBAs. Here's the scenario:
I have a table with ~250M records. Right now it's a daily flush/fill. That will be changing once I have a good handle on the project. In the meantime, does the order of TRUNCATE and DISABLE of NONCLUSTERED INDEXES make a difference? I know the CLUSTERED makes a difference, but I'm hear conflicting info on the NONCLUSTERED INDEXES. What do you folks have to say? Is it faster to TRUNCATE the table and then DISABLE the NONCLUSTERED INDEXES, vice versa, or no difference really? Also, will it make a difference if I DISABLE or DROP the INDEXES?
September 24, 2017 at 4:38 am
Utrolig - Saturday, September 23, 2017 6:48 PMHi folks... I'm hoping for some guidance on TRUNCATE and NONCLUSTERED INDEXES. I've been told conflicting information from a couple different DBAs. Here's the scenario:I have a table with ~250M records. Right now it's a daily flush/fill. That will be changing once I have a good handle on the project. In the meantime, does the order of TRUNCATE and DISABLE of NONCLUSTERED INDEXES make a difference? I know the CLUSTERED makes a difference, but I'm hear conflicting info on the NONCLUSTERED INDEXES. What do you folks have to say? Is it faster to TRUNCATE the table and then DISABLE the NONCLUSTERED INDEXES, vice versa, or no difference really? Also, will it make a difference if I DISABLE or DROP the INDEXES?
In this case I would first drop any foreign key constraints, then truncate the table, then drop the indices, reload the table and finally create the indices and constraints again.
😎
September 24, 2017 at 6:23 am
It'll make no difference whether you drop the indexes first then truncate the table or vis versa.
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 25, 2017 at 8:39 am
Is there any gain to be had at all by disabling the nonclustered Indexes?
I certainly wouldn't disable/drop any foreign key constraints because it is an integral part of your data integrity plan between two tables.
Re-enabling the nonclustered index still requires the index to be rebuilt after your data load so will you have achieved anything?
I would be more inclined to simply truncate and load......
September 25, 2017 at 8:45 am
kevaburg - Monday, September 25, 2017 8:39 AMIs there any gain to be had at all by disabling the nonclustered Indexes?
I certainly wouldn't disable/drop any foreign key constraints because it is an integral part of your data integrity plan between two tables.
Re-enabling the nonclustered index still requires the index to be rebuilt after your data load so will you have achieved anything?
I would be more inclined to simply truncate and load......
Important to distinguish which kind of FK relationship one is dealing with. Only ones that need disabling are those that are NOT on the table in question, but RELY on said table. As this table is intended to be truncated daily, that may not be likely, but if it is reloaded together with other tables that rely on it, then those other FKs would need disabling before this table could be truncated, whereas FKs that go FROM this table to reference other tables would not need to be disabled.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 25, 2017 at 9:00 am
kevaburg - Monday, September 25, 2017 8:39 AMIs there any gain to be had at all by disabling the nonclustered Indexes?
.
There can be. It depends what the load does. A single insert, probably not. Multiple inserts, some updates and some more inserts, probably yes.
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 25, 2017 at 9:15 am
sgmunson - Monday, September 25, 2017 8:45 AMkevaburg - Monday, September 25, 2017 8:39 AMIs there any gain to be had at all by disabling the nonclustered Indexes?
I certainly wouldn't disable/drop any foreign key constraints because it is an integral part of your data integrity plan between two tables.
Re-enabling the nonclustered index still requires the index to be rebuilt after your data load so will you have achieved anything?
I would be more inclined to simply truncate and load......
Important to distinguish which kind of FK relationship one is dealing with. Only ones that need disabling are those that are NOT on the table in question, but RELY on said table. As this table is intended to be truncated daily, that may not be likely, but if it is reloaded together with other tables that rely on it, then those other FKs would need disabling before this table could be truncated, whereas FKs that go FROM this table to reference other tables would not need to be disabled.
To be honest if other tables have dependencies on the table to be truncated then we are risking data integrity for the sake of a fast load. It would interest me to know whether or not these dependencies exist. It sounds to me like the table being spoken about is more of a staging table within which no primary key constraint is defined.
If it really is the case that this table is reloaded together with other tables than the constellation is far from ideal I think even for a DWH. And thankfully I don't have to write the ETL for it.... 🙂
September 25, 2017 at 9:17 am
GilaMonster - Monday, September 25, 2017 9:00 AMkevaburg - Monday, September 25, 2017 8:39 AMIs there any gain to be had at all by disabling the nonclustered Indexes?
.There can be. It depends what the load does. A single insert, probably not. Multiple inserts, some updates and some more inserts, probably yes.
Sounds like a case for extensive testing. I wouldn't like to plan this sort of data load without it.
September 25, 2017 at 9:47 am
I'd say you get a slight gain by disabling the non-clus indexes first. That's because disabling the index requires removing the rows, which requires some minor logging (of page deallocations, IIRC), as does truncating the table. By disabling first, the non-clus index logging takes place in a separate transaction from the clus index truncation/logging,
I wouldn't drop the indexes unless you have no choice, because insuring you recreate them exactly as they were before requires very thorough scripting that not everyone has in place. For example, did you make sure to script the CASCADE options? The FILLFACTOR? Etc. Not to mention the potential loss of any extended properties.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply