LATCH_EX when creating a clustered index

Question

Post reply

LATCH_EX when creating a clustered index

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

DamianC SSCrazy Eights Points: 8023 More actions · Answer 1

Thanks Scott

Cost threshold is 75

There are no non-clustered, just the clustered

- Damian

ScottPletcher SSC Guru Points: 101109 More actions · Answer 2

Again, I'd say create the clustered index before loading the table. Use row compression, since that could save some I/O which would reduce the time needed.

Are other processes using "SELECT ... INTO new_table_name" in that db? That method causes locking on certain system tables and that can cause long delays.

Instead, use a "SELECT TOP (0) ... INTO new_table_name FROM ..." to create the table; create the clus index (only); and a final "INSERT INTO new_table_name WITH (TABLOCK) SELECT ... FROM" to actually load the table data.

Same would apply to this process if it's using the SELECT ... INTO method.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

DamianC SSCrazy Eights Points: 8023 More actions · Answer 3

I'm using SSIS data flow for insertion

From OLE DB to OLE DB

I'm finding that Page compression on a clustered index on the destination table i.e. leave in place, doubles the time taken

So, I thought I'd adopt this approach with all my tables (12). There's only 3 non-clustered indexes i.e. only 3 tables have 1, so I've lefter in the pre-drop and post-recreate (aware I could disable and enable). Now I have an approach where ALL destinati0n tables have a clustered index on them (not compressed). On my test server (only thing happening), I tested before (drop, populate, create) and it took 40 mins. I'm now testing after (leave, populate). It's been running for over 2 hours and is still going. Appreciate you are all saying leave the clustered index. I've also been internally advised that that's the best approach. Problem is, it's making the load times a lot worse

This reply was modified 3 years, 6 months ago by DamianC.

- Damian

ScottPletcher SSC Guru Points: 101109 More actions · Answer 4

You need to make sure that TABLOCK is specified on the load so that you get minimal logging. I'm not 100% sure how to do that via SSIS but look around and see if you can find and activate an option for that. You probably also need to load all the rows in a single batch to make sure you get minimal logging for all rows, not just the first batch of rows. I believe, if IIRC, that a batch size of 0 will give you one large batch, but verify that, to be sure.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".