Blocking query

Question

Blocking query

April 10, 2023 at 8:17 am

This was removed by the editor as SPAM

Viewing 6 posts - 16 through 20 (of 20 total)

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

vsamantha35 wrote:

Jeff Moden wrote:
From the nature of some of the code, it looks like all parameters are being passed as NVARCHAR(4000).
It doesn't matter how well code may be written, that generally means that index seeks will be impossible because not all columns those parameters are being compared against will have a datatype of NVARCHAR(4000). It does mean that the entire index will need to be to be fully scanned because all the columns involved in the criteria will need to be scanned and converted to NVARCHAR(4000) before it can be compared and it will hold lot'so locks while doing so.
So, because of the scans and datatype conversions no index will be used and it has to read all the data and that's it has to read all the data and takes long time to finish the query and release locks. Also, this is an SELECT statement, why it is taking 'X' lock on "lead_match_request_batch" table or is it somewhere at the top of the stored proc / batch, it has already done DML operation and haven't committed yet?
Regards,
Sam

It was a high level look-see on my part so I have to say, possibly. If all they're doing is being used to call a proc then, like Scott suggests, it depends on what's inside the proc especially for parameter datatypes, the datatypes of the columns being played against, and more.

--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)

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

Be real, real cautious about using ChatGPT for any tuning advice. I was testing it out asking questions and it gets a lot of stuff badly wrong. I mean, very badly. It's even been known to simply make things up. While it can be a somewhat useful tool, it requires quite a lot of judgement on your part to ensure you know that it's giving you good information. And, just like any of us making suggestions on how to fix something, testing is your buddy. Make sure you take advantage of that.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLWizard Grasshopper Points: 21 More actions · Answer 3

Still, there are a lot of opportunities to use ChatGPT, not here, of course. In terms of asking this AI language model some questions, i wouldn't say it's too bad at it, at least now (GPT-4). I do not promote using this, though I am having fun.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

SQLWizard wrote:

Still, there are a lot of opportunities to use ChatGPT, not here, of course. In terms of asking this AI language model some questions, i wouldn't say it's too bad at it, at least now (GPT-4). I do not promote using this, though I am having fun.

That sounds like a promotion. 🙁

I'll also say that it has failed miserably in producing correct answers for the simple questions that I've asked. I've even asked it "How would Jeff Moden do it?" and it gave me an answer that I actually and consistently rail against because of the extreme hidden RBAR nature of it. It gave a similar answer when I asked how Itzik Ben-Gan would do it.

Rather than any kind of promotion, I'll say that it's a heuristic bullshit grinder that responds in a deceptively over confident manner even though it's usually incorrect. I won't even say "use with caution"... if you need to ask a question, you simply don't have enough knowledge to use it with caution.

The only good part about some of the code solutions it produces is that they won't even "compile".