November 5, 2021 at 4:53 pm
Does an estimated execution plan find any missing indexes?
To speed the query up you could try putting an index on the Customer table with the leading edge on columns Customer, Bank e.g.:
CREATE INDEX IX_Customer_Customer_Bank ON dbo.Customer(Customer, Bank);
November 8, 2021 at 2:24 pm
No missing index suggestion. It didn't use the your recommended one.
November 8, 2021 at 4:46 pm
Thanks for everyone's input. I presented my findings to the SR DEV, he also feels the Bank subquery is redundant. He also doesn't believe we need DISTINCT and NOLOCK. He will clean it up, beat it up, and test it out. Thank You!
November 20, 2021 at 11:27 am
It really depends on whether you understand the consequences of NOLOCK. It can result in a 'dirty read' if there are uncomitted transactions in the database that affect the data on the table, especially if the update can move the data in an index that the execution plan is relying on. It could be that a record get misssed, or that it gets duplicated because it moved from the top of the index to the bottom while the index was being read.
November 20, 2021 at 1:34 pm
This was removed by the editor as SPAM
November 23, 2021 at 6:32 am
This was removed by the editor as SPAM
December 21, 2021 at 6:41 pm
Does an estimated execution plan find any missing indexes?
To speed the query up you could try putting an index on the Customer table with the leading edge on columns Customer, Bank e.g.:
CREATE INDEX IX_Customer_Customer_Bank ON dbo.Customer(Customer, Bank);
Given the query has an OR condition on the customer column, I think it would still scan the index here (depending on statistics).
----------------------------------------------------
December 21, 2021 at 7:02 pm
It really depends on whether you understand the consequences of NOLOCK. It can result in a 'dirty read' if there are uncomitted transactions in the database that affect the data on the table, especially if the update can move the data in an index that the execution plan is relying on. It could be that a record get misssed, or that it gets duplicated because it moved from the top of the index to the bottom while the index was being read.
'Dirty reads' are of course unique to NOLOCK and the big danger of using it. But the part about "It could be that a record [sic] get misssed, or that it gets duplicated because it moved from the top of the index to the bottom while the index was being read." True, but (1) you can reduce the changes of that happening by changing the cursor threshold setting, and (2) those errors can also happen with the READ COMMITTED setting! Most people don't seem to realize that.
As Paul White put it:"
In particular, a statement running under locking read committed isolation [emphasis added]:
Can encounter the same row multiple times;
Can miss some rows completely; and"
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 22, 2021 at 3:45 am
aaron.reese wrote:It really depends on whether you understand the consequences of NOLOCK. It can result in a 'dirty read' if there are uncomitted transactions in the database that affect the data on the table, especially if the update can move the data in an index that the execution plan is relying on. It could be that a record get misssed, or that it gets duplicated because it moved from the top of the index to the bottom while the index was being read.
'Dirty reads' are of course unique to NOLOCK and the big danger of using it. But the part about "It could be that a record [sic] get misssed, or that it gets duplicated because it moved from the top of the index to the bottom while the index was being read." True, but (1) you can reduce the changes of that happening by changing the cursor threshold setting, and (2) those errors can also happen with the READ COMMITTED setting! Most people don't seem to realize that.
As Paul White put it:" In particular, a statement running under locking read committed isolation [emphasis added]: Can encounter the same row multiple times; Can miss some rows completely; and"
I think you refer to phantom reads that can happen under the read uncommitted condition. Do you happen to have a link? I am curious as to the probabilities on read committed versus read uncommitted.
----------------------------------------------------
December 22, 2021 at 12:51 pm
In addition to the responses above, the following part of the WHERE clause will always result in a table/index scan
AND (@Session = 0 OR @session = SessionID)
That is because SQL evaluates the [@Session = 0] against every record in the table/index
December 22, 2021 at 1:26 pm
aaron.reese wrote:It really depends on whether you understand the consequences of NOLOCK. It can result in a 'dirty read' if there are uncomitted transactions in the database that affect the data on the table, especially if the update can move the data in an index that the execution plan is relying on. It could be that a record get misssed, or that it gets duplicated because it moved from the top of the index to the bottom while the index was being read.
'Dirty reads' are of course unique to NOLOCK and the big danger of using it. But the part about "It could be that a record [sic] get misssed, or that it gets duplicated because it moved from the top of the index to the bottom while the index was being read." True, but (1) you can reduce the changes of that happening by changing the cursor threshold setting, and (2) those errors can also happen with the READ COMMITTED setting! Most people don't seem to realize that.
As Paul White put it:" In particular, a statement running under locking read committed isolation [emphasis added]: Can encounter the same row multiple times; Can miss some rows completely; and"
You have left out a considerable set of facts. You statement is very out of context. This is the entire article:
https://sqlperformance.com/2014/04/t-sql-queries/the-read-committed-isolation-level
This article further explains the behavior:
https://www.sql.kiwi/2010/11/read-committed-shared-locks-and-rollbacks.html
To generalize, and encourage, the behavior and use of NOLOCK is troubling. Few people understand what NOLOCK actually means, and likely even less people fully understand the various isolation levels.
But please keep doing it. I have made a ton of money fixing poorly designed code where the people writing it have little understanding of how things such as this work.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 22, 2021 at 3:49 pm
ScottPletcher wrote:aaron.reese wrote:It really depends on whether you understand the consequences of NOLOCK. It can result in a 'dirty read' if there are uncomitted transactions in the database that affect the data on the table, especially if the update can move the data in an index that the execution plan is relying on. It could be that a record get misssed, or that it gets duplicated because it moved from the top of the index to the bottom while the index was being read.
'Dirty reads' are of course unique to NOLOCK and the big danger of using it. But the part about "It could be that a record [sic] get misssed, or that it gets duplicated because it moved from the top of the index to the bottom while the index was being read." True, but (1) you can reduce the changes of that happening by changing the cursor threshold setting, and (2) those errors can also happen with the READ COMMITTED setting! Most people don't seem to realize that.
As Paul White put it:" In particular, a statement running under locking read committed isolation [emphasis added]: Can encounter the same row multiple times; Can miss some rows completely; and"
You have left out a considerable set of facts. You statement is very out of context. This is the entire article:
https://sqlperformance.com/2014/04/t-sql-queries/the-read-committed-isolation-level
This article further explains the behavior:
https://www.sql.kiwi/2010/11/read-committed-shared-locks-and-rollbacks.html
To generalize, and encourage, the behavior and use of NOLOCK is troubling. Few people understand what NOLOCK actually means, and likely even less people fully understand the various isolation levels.
But please keep doing it. I have made a ton of money fixing poorly designed code where the people writing it have little understanding of how things such as this work.
NOLOCK should only be used when appropriate. But, yes, I do think the jihad against it can be overdone at times, aka the "never use NOLOCK" crowd. Makes almost as little sense as "always use NOLOCK". My favorite lie is "it makes no different to performance anyway".
There are many times when it's perfectly safe to use NOLOCK, such as when reading historic data that will never change. Some people say "just turn on RCSI for all data" and you won't need NOLOCK. Well, yeah, and if I cut off my feet, I don't need shoes.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 22, 2021 at 4:08 pm
NOLOCK should only be used when appropriate. But, yes, I do think the jihad against it can be overdone at times, aka the "never use NOLOCK" crowd. Makes almost as little sense as "always use NOLOCK". My favorite lie is "it makes no different to performance anyway".
There are many times when it's perfectly safe to use NOLOCK, such as when reading historic data that will never change. Some people say "just turn on RCSI for all data" and you won't need NOLOCK. Well, yeah, and if I cut off my feet, I don't need shoes.
My favorite lie is that NOLOCK improves performance. Prove it. Because I can bet there are better ways to improve performance on almost every case. It's rarely a good choice. And, in never changing historical data, this is read only. Use a read only connection, and use snapshot isolation.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 22, 2021 at 4:22 pm
My favorite lie is that NOLOCK improves performance.
Of course it must vs taking locks.
"and use snapshot isolation." MEGA-HUGE overhead. SI should only be used when truly necessary.
You need to drop your preconceived biases and look at what's actually happening.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 22, 2021 at 4:39 pm
And, in never changing historical data, this is read only. Use a read only connection, and use snapshot isolation.
You can't use a "read-only connection" in/for a proc that does reads and modifications. And far too many people just slap SI on without realizing the huge overhead associated with using it.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply