October 23, 2018 at 8:10 pm
If you partition the table on the @@SPID value, then you don't need any of that lock-checking mess; everything gets its own partition. (You'll need to align your indexes for this to work)
Each partition can then be cleared with: TRUNCATE TABLE dbo.TTemp_MaintainActuals
WITH (PARTITIONS ($partition.MySPIDPartitionFunction(@@spid)));
Eddie Wuerch
MCM: SQL
October 23, 2018 at 8:29 pm
Eddie Wuerch - Tuesday, October 23, 2018 8:10 PMIf you partition the table on the @@SPID value, then you don't need any of that lock-checking mess; everything gets its own partition. (You'll need to align your indexes for this to work)Each partition can then be cleared with:
TRUNCATE TABLE dbo.TTemp_MaintainActuals
WITH (PARTITIONS ($partition.MySPIDPartitionFunction(@@spid)));
The only question that remains is.... how many partitions? We typically have a range of about 600 SPIDs at any given time.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 25, 2018 at 2:18 am
Jeff Moden - Monday, October 22, 2018 11:52 AMScottPletcher - Monday, October 22, 2018 11:21 AMJust delete the rows. If it's not that many rows, it's more work than it's worth to try to TRUNCATE.I suspect (hope) the temp table is clustered first on SPID. If it's not, do that. But other than that, you should be OK.
Ah, be careful now... clustering on the SPID will create silos that are inserted into "out of order" which will cause massive page splits and huge amounts of log file usage even in the SIMPLE recovery model unless you can convert it to SPID followed by some form of ever-increasing column. Even if you have that, if you also have "Expansive" updates, insert and update performance is going to tank compared to what could be and log file activity will rise to about 43 times as much as it needs to be. You'll also end up with incredibly low page density within the silos, which is where most of the work will be done which also wastes a ton of memory on mostly emptied pages.
I can't upload an image from where I'm at right now. I'll do it when I get home but you'll be shocked at what such an index looks like under such conditions.
Don't worry - there is definitely not a clustered index on the SPID column for the very reasons you've mentioned.
October 25, 2018 at 2:24 am
ScottPletcher - Monday, October 22, 2018 2:05 PMLynn Pettis - Monday, October 22, 2018 1:51 PMWell, with a permanent table already created they aren't using SELECT ... INTO #<table>. Why not create the #<table> then insert the data into it?When I said "perhaps because" I meant "maybe" they are using SELECT ... INTO, I don't know for sure. But I know that can cause delays, sometimes severe delays, in using metadata, including table creation and deletion, and even in tempdb. It's convenient to code, and a lot of people don't realize the potential issues they can see from it.
In our "would # tables be better than permanent ones?" test, we had to refactor quite a lot of code. To make this easier / simpler, we changed "INSERT INTO dbo.TTemp_MaintainActuals" to "INSERT INTO #TTemp_MaintainActuals". The #TTemp_MaintainActuals table would have been made earlier in the process.
To be honest, the results were so much worse we didn't spend a great deal of time investigating why. With hindsight, this was probably a mistake.
October 25, 2018 at 2:25 am
Eddie Wuerch - Tuesday, October 23, 2018 8:10 PMIf you partition the table on the @@SPID value, then you don't need any of that lock-checking mess; everything gets its own partition. (You'll need to align your indexes for this to work)Each partition can then be cleared with:
TRUNCATE TABLE dbo.TTemp_MaintainActuals
WITH (PARTITIONS ($partition.MySPIDPartitionFunction(@@spid)));
Thanks very much - that looks like a very promising line of investigation. I did wonder whether partitioning might be an option but wasn't aware of this functionality.
October 26, 2018 at 12:25 pm
julian.fletcher - Thursday, October 25, 2018 2:18 AMJeff Moden - Monday, October 22, 2018 11:52 AMScottPletcher - Monday, October 22, 2018 11:21 AMJust delete the rows. If it's not that many rows, it's more work than it's worth to try to TRUNCATE.I suspect (hope) the temp table is clustered first on SPID. If it's not, do that. But other than that, you should be OK.
Ah, be careful now... clustering on the SPID will create silos that are inserted into "out of order" which will cause massive page splits and huge amounts of log file usage even in the SIMPLE recovery model unless you can convert it to SPID followed by some form of ever-increasing column. Even if you have that, if you also have "Expansive" updates, insert and update performance is going to tank compared to what could be and log file activity will rise to about 43 times as much as it needs to be. You'll also end up with incredibly low page density within the silos, which is where most of the work will be done which also wastes a ton of memory on mostly emptied pages.
I can't upload an image from where I'm at right now. I'll do it when I get home but you'll be shocked at what such an index looks like under such conditions.
Don't worry - there is definitely not a clustered index on the SPID column for the very reasons you've mentioned.
I meant clustering on the SPID first, not necessarily only. I really thought the SPID here would be unqiue, but add identity if needed. I thought I stated that before but maybe not.
And, pad out the row if really needed to reduce the number of rows per page, if necessary down to 1. I've just never seen the type of things you're referring to on a table, esp. one that couldn't split rows. Maybe I just overlooked it.
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".
October 26, 2018 at 3:11 pm
Jeff Moden - Monday, October 22, 2018 3:48 PMScottPletcher - Monday, October 22, 2018 2:05 PMLynn Pettis - Monday, October 22, 2018 1:51 PMWell, with a permanent table already created they aren't using SELECT ... INTO #<table>. Why not create the #<table> then insert the data into it?When I said "perhaps because" I meant "maybe" they are using SELECT ... INTO, I don't know for sure. But I know that can cause delays, sometimes severe delays, in using metadata, including table creation and deletion, and even in tempdb. It's convenient to code, and a lot of people don't realize the potential issues they can see from it.
Do you have any demonstrable/repeatable examples? I've previously tested overlapping SELECT/INTOs and other ways of creating tables that started after the SELECT/INTO and finished before the SELECT/INTO. The only thing I've seen it be a PITA on is things like the Explorer window in SSMS. And, no... not saying it doesn't happen. I've just not seen it (interference/delays with table creation... not stuff in EM or SSMS) happen since SQL Server 6.5 RTM before the hot fix that kept it from crippling TempDB.
Just asking the same question again... do you have any demonstrable/repeatable examples? Not a challenge... I've just not seen such problems since 6.5 RTM except for within the old EM and its replacement, SSMS. I've yet to see table creation through T-SQL get stuffed up because of SELECT/INTO even in TempDB.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply