December 22, 2021 at 5:08 pm
Michael L John wrote: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.
Preconceived biases? Cmon Scott, isn't that the proverbial pot calling the kettle black?
Mega-huge overhead? Again, some of us have actually tested and worked with these things, probably for longer than you have. Yet you keep making these blanket absolute statements as if they apply in every situation.
Your act is wearing thin.
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 5:14 pm
ScottPletcher wrote:Michael L John wrote: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.
Preconceived biases? Cmon Scott, isn't that the proverbial pot calling the kettle black?
Mega-huge overhead? Again, some of us have actually tested and worked with these things, probably for longer than you have. Yet you keep making these blanket absolute statements as if they apply in every situation.
Your act is wearing thin.
You have the only known dbms that has ever existed that has no overhead for taking locks and for implementing SI. Hopefully they don't let you out of the asylum too often.
You might want to read up on the overhead required by SI so you don't look quite so ignorant next time.
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 6:40 pm
Michael L John wrote:ScottPletcher wrote:Michael L John wrote: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.
Preconceived biases? Cmon Scott, isn't that the proverbial pot calling the kettle black?
Mega-huge overhead? Again, some of us have actually tested and worked with these things, probably for longer than you have. Yet you keep making these blanket absolute statements as if they apply in every situation.
Your act is wearing thin.
You have the only known dbms that has ever existed that has no overhead for taking locks and for implementing SI. Hopefully they don't let you out of the asylum too often.
You might want to read up on the overhead required by SI so you don't look quite so ignorant next time.
as you have been advised before I, and at least one other individual here on Forums (and not Michael), do have DB's with RCSI implemented where the performance improvement on the processes was very very significant - so as Michael stated please do refrain from making such blanket statements.
December 22, 2021 at 6:48 pm
ScottPletcher wrote:Michael L John wrote:ScottPletcher wrote:Michael L John wrote: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.
Preconceived biases? Cmon Scott, isn't that the proverbial pot calling the kettle black?
Mega-huge overhead? Again, some of us have actually tested and worked with these things, probably for longer than you have. Yet you keep making these blanket absolute statements as if they apply in every situation.
Your act is wearing thin.
You have the only known dbms that has ever existed that has no overhead for taking locks and for implementing SI. Hopefully they don't let you out of the asylum too often.
You might want to read up on the overhead required by SI so you don't look quite so ignorant next time.
as you have been advised before I, and at least one other individual here on Forums (and not Michael), do have DB's with RCSI implemented where the performance improvement on the processes was very very significant - so as Michael stated please do refrain from making such blanket statements.
I didn't say it couldn't help performance. I said that implementing RCSI requires big overhead. Sure, it could still improve overall performance (esp. if your indexes are not otherwise well tuned), perhaps significantly, but that does not mean it does not have big overhead of its own. And, yes, I still make that as a blanket statement because it's a "blanket fact".
RCSI requires: 14 additional bytes per row (which in most cases can cause massive page splits if enabled some time after table creation); plus space in tempdb for all changed rows; plus following a chain of changed rows when there are multiple changes to one row; and so on. You pay a lot for RCSI, you need to make sure it really does you good before implementing it. A blanket policy for (nearly) all dbs of "just turn on RCSI" is a very, very bad idea.
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 7:39 pm
frederico_fonseca wrote:ScottPletcher wrote:Michael L John wrote:ScottPletcher wrote:Michael L John wrote: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.
Preconceived biases? Cmon Scott, isn't that the proverbial pot calling the kettle black?
Mega-huge overhead? Again, some of us have actually tested and worked with these things, probably for longer than you have. Yet you keep making these blanket absolute statements as if they apply in every situation.
Your act is wearing thin.
You have the only known dbms that has ever existed that has no overhead for taking locks and for implementing SI. Hopefully they don't let you out of the asylum too often.
You might want to read up on the overhead required by SI so you don't look quite so ignorant next time.
as you have been advised before I, and at least one other individual here on Forums (and not Michael), do have DB's with RCSI implemented where the performance improvement on the processes was very very significant - so as Michael stated please do refrain from making such blanket statements.
I didn't say it couldn't help performance. I said that implementing RCSI requires big overhead. Sure, it could still improve overall performance (esp. if your indexes are not otherwise well tuned), perhaps significantly, but that does not mean it does not have big overhead of its own. And, yes, I still make that as a blanket statement because it's a "blanket fact".
RCSI requires: 14 additional bytes per row (which in most cases can cause massive page splits if enabled some time after table creation); plus space in tempdb for all changed rows; plus following a chain of changed rows when there are multiple changes to one row; and so on. You pay a lot for RCSI, you need to make sure it really does you good before implementing it. A blanket policy for (nearly) all dbs of "just turn on RCSI" is a very, very bad idea.
Your implication is that implementing RCSI is bad. The manner in which you state this would cause lesser experienced people to not bother when the benefit can be tremendous.
I have that "blanket policy" in place on all of my on-prem databases. In Azure, it's on by default.
As for resources, what are the systems that you work with that may be affected by this "Mega-overhead"? In our case, the "mega-overhead" barely registered in every metric for every test we performed. And no, I am not going to prove it to you Scott. It was 6 years ago, and we haven't looked back.
I can't figure out why there always seems to be this doom and gloom for everything from you. RCSI is a good thing. Find a way to take advantage of it instead of dismissing it entirely.
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 8:53 pm
ScottPletcher wrote:frederico_fonseca wrote:ScottPletcher wrote:Michael L John wrote:ScottPletcher wrote:Michael L John wrote: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.
Preconceived biases? Cmon Scott, isn't that the proverbial pot calling the kettle black?
Mega-huge overhead? Again, some of us have actually tested and worked with these things, probably for longer than you have. Yet you keep making these blanket absolute statements as if they apply in every situation.
Your act is wearing thin.
You have the only known dbms that has ever existed that has no overhead for taking locks and for implementing SI. Hopefully they don't let you out of the asylum too often.
You might want to read up on the overhead required by SI so you don't look quite so ignorant next time.
as you have been advised before I, and at least one other individual here on Forums (and not Michael), do have DB's with RCSI implemented where the performance improvement on the processes was very very significant - so as Michael stated please do refrain from making such blanket statements.
I didn't say it couldn't help performance. I said that implementing RCSI requires big overhead. Sure, it could still improve overall performance (esp. if your indexes are not otherwise well tuned), perhaps significantly, but that does not mean it does not have big overhead of its own. And, yes, I still make that as a blanket statement because it's a "blanket fact".
RCSI requires: 14 additional bytes per row (which in most cases can cause massive page splits if enabled some time after table creation); plus space in tempdb for all changed rows; plus following a chain of changed rows when there are multiple changes to one row; and so on. You pay a lot for RCSI, you need to make sure it really does you good before implementing it. A blanket policy for (nearly) all dbs of "just turn on RCSI" is a very, very bad idea.
Your implication is that implementing RCSI is bad. The manner in which you state this would cause lesser experienced people to not bother when the benefit can be tremendous.
I have that "blanket policy" in place on all of my on-prem databases. In Azure, it's on by default.
As for resources, what are the systems that you work with that may be affected by this "Mega-overhead"? In our case, the "mega-overhead" barely registered in every metric for every test we performed. And no, I am not going to prove it to you Scott. It was 6 years ago, and we haven't looked back.
I can't figure out why there always seems to be this doom and gloom for everything from you. RCSI is a good thing. Find a way to take advantage of it instead of dismissing it entirely.
I didn't dismiss it entirely. You're the one that entirely dismissed NOLOCK, always, in favor of RCSI, always.
RCSI is a great thing. I do use RCSI on some dbs, as I've stated in threads before. For when it's truly needed. NOLOCK is a great thing too. But each should be used when needed and not at other times. The jihadists against NOLOCK are the real doom-and-gloomers, and you seem to be one. For most companies, NOLOCK can work reasonably and with vastly less overhead than SI. Not on critical, company-changing queries of course, as some have apparently done. Any tool can be poorly used.
Azure is, of course, not on-prem. Considerations are different for on-prem. Many of us can't afford to go full Azure, and not just financially. Always RCSI has tremendous advantages for a hosting company: it reduces contention at the cost of more CPU, RAM and disk size ... all of which they get to charge for! What's not to like!
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply