Usage of #TempTables and an Index in Stored Procedure

Question

Usage of #TempTables and an Index in Stored Procedure

Viewing 8 posts - 31 through 37 (of 37 total)

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

ScottPletcher SSC Guru Points: 101099 More actions · Answer 1

Jeff Moden wrote:

ScottPletcher wrote:
Jeff Moden wrote:
ScottPletcher wrote:
I've seen issues repeatedly with "SELECT ... INTO <new_table>" causing blocking (on SQL 2016). When we adjust the code to split the table creation and the table load the problems go away. So you can say "it doesn't exist" but, since I've seen it many times, I disagree, even though I can't give you an appeal-to-authority on it that you would accept.
My point is that you said "The DBA's right about the concurrency issue." My point is that neither you nor he are correct on this issue. The correct answer is "It Depends". 😉
Actually your point seemed to be that their DBA was wrong. Basically you said, "I've never seen it, it really doesn't happen like that all, unless you prove to me by some know authority that it can."
For me, seeing it was believing it. I'm not sure why we had some of an issue with it. Maybe because the data volume was high and/or came from remote sources, and thus could often take 30 minutes or more (sometimes much more) to load?
Yes... their DBA is wrong. He didn't say "It Depends".

Neither did you (initially). What, specifically, would you have him do to verify that the specific SELECT ... INTO in q will never cause blocking in the future??

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".

ScottPletcher SSC Guru Points: 101099 More actions · Answer 2

/* dummy post to allow me to see my previous post */

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".

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

ScottPletcher wrote:

Jeff Moden wrote:
ScottPletcher wrote:
Jeff Moden wrote:
ScottPletcher wrote:
I've seen issues repeatedly with "SELECT ... INTO <new_table>" causing blocking (on SQL 2016). When we adjust the code to split the table creation and the table load the problems go away. So you can say "it doesn't exist" but, since I've seen it many times, I disagree, even though I can't give you an appeal-to-authority on it that you would accept.
My point is that you said "The DBA's right about the concurrency issue." My point is that neither you nor he are correct on this issue. The correct answer is "It Depends". 😉
Actually your point seemed to be that their DBA was wrong. Basically you said, "I've never seen it, it really doesn't happen like that all, unless you prove to me by some know authority that it can."
For me, seeing it was believing it. I'm not sure why we had some of an issue with it. Maybe because the data volume was high and/or came from remote sources, and thus could often take 30 minutes or more (sometimes much more) to load?
Yes... their DBA is wrong. He didn't say "It Depends".
Neither did you (initially). What, specifically, would you have him do to verify that the specific SELECT ... INTO in q will never cause blocking in the future??

Their DBA rejected code because they said it would be contentious. You said their DBA was correct. I said I've never seen the issue. I never said that it couldn't happen and even asked to to provide an example of where it can happen.

No one can prove that it will never happen and but someone can prove that it has happened and that's why I asked you for your example, which you have not yet provided.

As for the DBA, I'd suggest to him the same thing that I've done in the past... either show them a better way or allow the code to go through with the understanding that if it does prove to be contentious in-situ, then it will need to be changed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101099 More actions · Answer 4

Jeff Moden wrote:

ScottPletcher wrote:
Jeff Moden wrote:
ScottPletcher wrote:
Jeff Moden wrote:
ScottPletcher wrote:
I've seen issues repeatedly with "SELECT ... INTO <new_table>" causing blocking (on SQL 2016). When we adjust the code to split the table creation and the table load the problems go away. So you can say "it doesn't exist" but, since I've seen it many times, I disagree, even though I can't give you an appeal-to-authority on it that you would accept.
My point is that you said "The DBA's right about the concurrency issue." My point is that neither you nor he are correct on this issue. The correct answer is "It Depends". 😉
Actually your point seemed to be that their DBA was wrong. Basically you said, "I've never seen it, it really doesn't happen like that all, unless you prove to me by some know authority that it can."
For me, seeing it was believing it. I'm not sure why we had some of an issue with it. Maybe because the data volume was high and/or came from remote sources, and thus could often take 30 minutes or more (sometimes much more) to load?
Yes... their DBA is wrong. He didn't say "It Depends".
Neither did you (initially). What, specifically, would you have him do to verify that the specific SELECT ... INTO in q will never cause blocking in the future??
Their DBA rejected code because they said it would be contentious. You said their DBA was correct. I said I've never seen the issue. I never said that it couldn't happen and even asked to to provide an example of where it can happen.
No one can prove that it will never happen and but someone can prove that it has happened and that's why I asked you for your example, which you have not yet provided.
As for the DBA, I'd suggest to him the same thing that I've done in the past... either show them a better way or allow the code to go through with the understanding that if it does prove to be contentious in-situ, then it will need to be changed.

I've already provided the better way: create the table first using TOP (0), create the clus index, and only then load the table WITH (TABLOCK).

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".

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Nothing like shifting gears but, whatever. It's also not always the "better" way. "It Depends".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 6

Why does creating and inserting data into a table with insert into cause blocking but inserting into an existing table not cause blocking?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Jonathan AC Roberts wrote:

Why does creating and inserting data into a table with insert into cause blocking but inserting into an existing table not cause blocking?

Someone like Paul White could answer that much better than I but I don't believe that's a true statement.

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/understand-resolve-blocking#what-is-blocking.

Also, SELECT/INTO first makes the table and then it does and insert.

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/understand-resolve-blocking#what-is-blocking

A long time ago (6.5 RTM pre-hotfix), they were done in the same transaction that would actually cause TempDB to be entirely blocked.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 8

Jeff Moden wrote:

Jonathan AC Roberts wrote:
Why does creating and inserting data into a table with insert into cause blocking but inserting into an existing table not cause blocking?
Someone like Paul White could answer that much better than I but I don't believe that's a true statement.
https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/understand-resolve-blocking#what-is-blocking.
Also, SELECT/INTO first makes the table and then it does and insert.
https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/understand-resolve-blocking#what-is-blocking
A long time ago (6.5 RTM pre-hotfix), they were done in the same transaction that would actually cause TempDB to be entirely blocked.

Thanks, In my case it wouldn't really matter anyway as the data warehouse is not in use by anything else during a full load.