August 28, 2012 at 5:12 pm
GilaMonster (8/28/2012)
Sergiy (8/28/2012)
GilaMonster (8/28/2012)
http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/There's a good coverage of locking modes in chapter 6, including why SQL needs IX locks at higher levels.
So, what would be your explanation?
Well I wrote that portion of that chapter, so I guess what's in there would be my explanation.
Ahhh, if ever there was a time when I need a 'Like' button....... 😀
August 28, 2012 at 5:52 pm
GilaMonster (8/28/2012)
Well I wrote that portion of that chapter, so I guess what's in there would be my explanation.
So, it should be really easy for you to post a relevant quote here.
_____________
Code for TallyGenerator
August 28, 2012 at 5:56 pm
Sergiy (8/28/2012)
GilaMonster (8/28/2012)
Well I wrote that portion of that chapter, so I guess what's in there would be my explanation.So, it should be really easy for you to post a relevant quote here.
Or you could, you know, go to the link, get the free ebook and read it yourself maybe.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 28, 2012 at 5:59 pm
Evil Kraig F (8/28/2012)
*scratches his head* Out of curiousity, you realize these are other people you're talking to, not your drinking buddies who don't take offense to your constantly aggressive attitude?
Aggressive?
What are you talking about?
They have patiently answered all of your questions that you've made,
Really?
Do you take "read the book" as an answer?
How would YOU take such answer being in a discussion face to face?
_____________
Code for TallyGenerator
August 28, 2012 at 6:00 pm
GilaMonster (8/28/2012)
Or you could, you know, go to the link, get the free ebook and read it yourself maybe.
No answer, I see.
_____________
Code for TallyGenerator
August 28, 2012 at 6:14 pm
Sergiy (8/28/2012)
Evil Kraig F (8/28/2012)
*scratches his head* Out of curiousity, you realize these are other people you're talking to, not your drinking buddies who don't take offense to your constantly aggressive attitude?Aggressive?
What are you talking about?
Your constant badgering and attack of the person instead of the issue in question. Your phrasing is aggressive and challenging. In general, you're being trollish. So, to your question, how do YOU see your phrasing to be? It's possible I'm simply reading it poorly, so please, explain to me what your intended tone is behind the words you're using and the approach you're taking, such as saying that Gail gave you no answer when you're just too lazy to open up another window with a provided link.
Really?
Do you take "read the book" as an answer?
How would YOU take such answer being in a discussion face to face?
There's a point where you stop talking to a stump. Paul, Gail, and many others have linked me to MANY articles (and book chapters) instead of trying to cut/paste that information out of context. And I've read them, funny that. It doesn't burn like hygiene. So, I take it pretty well. A conversation is about surface information. They're not here to give you a free seminar, particularly on readily available information you can JUST AS EASILY read about in the other browser window with the link as you can if they regurgitate it for you here.
Sometimes a link IS an answer. As is silence and incredibly short answers. Don't worry, you'll get used to the fact that adults in the real world do their own research on occassion once you're out of college.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 28, 2012 at 6:22 pm
Evil Kraig F (8/28/2012)
Your constant badgering and attack of the person instead of the issue in question.
That's exactly what YOU are doing now.
I'd appreciate any example of a personal attack from my side.
It's not my fault that some people take being mistaken too personally.
_____________
Code for TallyGenerator
August 28, 2012 at 6:23 pm
GilaMonster (8/28/2012)
Or you could, you know, go to the link, get the free ebook and read it yourself maybe.
Read the Bible, my son, it has all the answers.
_____________
Code for TallyGenerator
August 28, 2012 at 6:52 pm
Sergiy (8/28/2012)
Evil Kraig F (8/28/2012)
Your constant badgering and attack of the person instead of the issue in question.That's exactly what YOU are doing now.
Quite correct, this conversation took a left turn from being about a discussion regarding internal mechanics awhile ago.
I'd appreciate any example of a personal attack from my side.
As you wish.
Sergiy (8/16/2012)
GilaMonster (8/16/2012)
I'm not going to be stupid enough to claim each and every case.Yeah, but you're trying to play stupid by not understanding that we have only 2 cases in this particular example.
:rolleyes:
Sergiy (8/26/2012)
SQL Kiwi (8/24/2012)
The storage engine sets the is_dirty bit when it changes a page.
If you'd only understand your own words...
:hehe:
Sergiy (8/27/2012)
SQL Kiwi (8/26/2012)
Sergiy (8/26/2012)
Was not so clear to me, as it turns out. Learned couple of things here, thanks to the good samaritans.Good. Well the last (important) thing to be learned is that the storage engine does not maintain an 'old version' of the page to compare with the 'new version' after the update. The engine knows whether it had to make any changes or not, and simply sets the is_dirty bit if so. As Gail mentioned, the way you have been thinking it works would be horribly inefficient. If the engine knows that it didn't have to update any bytes on the page, it doesn't set the dirty bit.
Fail.
There is no smallest proof for this statement.
The proof was in the link you decided not to read in its entirety.
Sergiy (8/28/2012)
GilaMonster (8/28/2012)
Or you could, you know, go to the link, get the free ebook and read it yourself maybe.No answer, I see.
So, a few examples. Are they extreme? No, not really, no one of those alone would have solicited the reaction from me. Taken in combination with the rest of your approach and attitude, I see them as aggressive statements of personal impact.
You seem to enjoy trying to decontextualize people or make cases against them which they didn't state. IE: Gail provided an example of a heap not doing a memory update (and thus, no disk update). Your speed to 'jump' on her for not writing a white paper in response to a question instead of simply providing examples of your own that DO write the memory page (heck, or just linked up Paul's post) as more information is another example of personal attack vs. discussion of the issue.
You haven't answered my question of the tone I should be taking your posts in. I'm trying to understand why you're confused. If it's simply text getting in the way then this misunderstanding could be simply repaired. Combined with your approach grammatically and in a debate style of 'winning' instead of 'knowledge gaining', I don't believe that's the case.
It's not my fault that some people take being mistaken too personally.
Agreed.
Sergiy (8/16/2012)
GilaMonster (8/16/2012)
They however are different binary values when stored in the data page.Yep.
Exactly my point.
To find out the values are different SQL Server first needs to write a new value to the page.
Actually write.
Sergiy (8/16/2012)
GilaMonster (8/16/2012)
The page was not marked as dirty, the update log record was not written when the update does not change the value of the row (as in the exact binary representation of the row on the page).
You agreed again.
"the exact binary representation of the row on the page" can be find out only AFTER the page is updated with a new value.
Otherwise - it just does not exist.
So, the new value is written to the page.
Same row may be allocated over differen pages.
Same page can contain many rows.
Figuring out which pages have become dirty as a result of the UPDATE is a totally different matter. The check can be carried on only after UPDATE is finished and updated versions of affected pages are present.
Unless we're discussing LOBs, this is untrue, and since LOBs are the exception they're usually specifically stated to be part of the discussion if they're brought in.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 28, 2012 at 6:53 pm
Sergiy (8/28/2012)
Please explain why:- X locks applied to RID
- IX locks applied to pages.
What makes then different?
SQL Server can acquire locks at different levels, for example it can lock rows, pages, a partition, or a whole table. When an exclusive (X) lock is taken on a row, SQL Server takes intent-exclusive (IX) locks at the page and table levels first. This is primarily a performance optimization. If I have an X lock on a single row, and you want an X lock on the table, it is much faster for the engine to test for an existing IX lock at the table level (indicating someone holds an X lock somewhere further down) than it would be to check every page and row for incompatible locks individually.
IX locks are compatible with each other, so two people can hold X locks on different rows in the same page at the same time. Both parties will have IX locks on the same page and table. The IX locks prevent anyone else taking an X lock on the page or table.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 28, 2012 at 7:55 pm
I wish I had gotten to this thread sooner. I could have sold popcorn to the spectators and made a small fortune. 🙂
August 28, 2012 at 9:52 pm
SQL Kiwi (8/28/2012)
Sergiy (8/28/2012)
Please explain why:- X locks applied to RID
- IX locks applied to pages.
What makes then different?
SQL Server can acquire locks at different levels, for example it can lock rows, pages, a partition, or a whole table. When an exclusive (X) lock is taken on a row, SQL Server takes intent-exclusive (IX) locks at the page and table levels first. This is primarily a performance optimization. If I have an X lock on a single row, and you want an X lock on the table, it is much faster for the engine to test for an existing IX lock at the table level (indicating someone holds an X lock somewhere further down) than it would be to check every page and row for incompatible locks individually.
IX locks are compatible with each other, so two people can hold X locks on different rows in the same page at the same time. Both parties will have IX locks on the same page and table. The IX locks prevent anyone else taking an X lock on the page or table.
It's OK, have no questions about this.
But still no answer.
When an exclusive (X) lock is taken on a row
So, when exactly an exclusive (X) lock is taken on a row?
Why X lock are taken on the rows when IX would be perfectly enough in a situation when no actual writing is happening?
Why Server uses IX on page level (pages are possibly changed by the update, but we don't know for sure yet) but refuses to do the same on row level?
_____________
Code for TallyGenerator
August 28, 2012 at 10:27 pm
Evil Kraig F (8/28/2012)
Sergiy (8/28/2012)
Evil Kraig F (8/28/2012)
Your constant badgering and attack of the person instead of the issue in question.That's exactly what YOU are doing now.
Quite correct, this conversation took a left turn from being about a discussion regarding internal mechanics awhile ago.
Should I say - you turned it that way?
I'd appreciate any example of a personal attack from my side.
As you wish.
Sergiy (8/16/2012)
GilaMonster (8/16/2012)
I'm not going to be stupid enough to claim each and every case.Yeah, but you're trying to play stupid by not understanding that we have only 2 cases in this particular example.
:rolleyes:
What did you find wrong?
Was not the opponent actually playing stupid in attempt to avoid answerring the question?
Don't you find THAT kind behaviour offensive in a discussion?
Or do you consider it allowed for the ones you personally admire?
Sergiy (8/26/2012)
SQL Kiwi (8/24/2012)
The storage engine sets the is_dirty bit when it changes a page.
If you'd only understand your own words...
:hehe:
Paul, were you offended with that comment?
Sorry, if yes.
I put the smile on purpose there, so it would be taken not too seriously.
Sergiy (8/27/2012)
SQL Kiwi (8/26/2012)
Sergiy (8/26/2012)
Was not so clear to me, as it turns out. Learned couple of things here, thanks to the good samaritans.Good. Well the last (important) thing to be learned is that the storage engine does not maintain an 'old version' of the page to compare with the 'new version' after the update. The engine knows whether it had to make any changes or not, and simply sets the is_dirty bit if so. As Gail mentioned, the way you have been thinking it works would be horribly inefficient. If the engine knows that it didn't have to update any bytes on the page, it doesn't set the dirty bit.
Fail.
There is no smallest proof for this statement.
The proof was in the link you decided not to read in its entirety.
What did you find personal here?
"Fail" was obviously about the statement, not any person.
And the link you posted does not have any proof for what's stated.
"The engine knows whether it had to make any changes or not" - can you explain how?
Sergiy (8/28/2012)
GilaMonster (8/28/2012)
Or you could, you know, go to the link, get the free ebook and read it yourself maybe.No answer, I see.
Yes, no answer.
It's no my fault Gila cannot give any answer.
If you could find the answer in her reply please help me to see it.
So, a few examples. Are they extreme? No, not really, no one of those alone would have solicited the reaction from me. Taken in combination with the rest of your approach and attitude, I see them as aggressive statements of personal impact.
And I see you came here with a single purpose - to turn the professional discussion into a personal fight.
Till now you did not bring any valid point into the discussuion, only some personal rant.
Please stop it.
.....
Unless we're discussing LOBs, this is untrue, and since LOBs are the exception they're usually specifically stated to be part of the discussion if they're brought in.
OK. Back to the discussion.
It's true.
You forget about fragmentation cases.
Which happen more often then many people anticipate.
Try to change the original test script.
Change the name column definition to varchar(8000).
Add 2 more columns. I used VARCHAR(8000) and VARBINARY (8000) and populate it with REVERSE(NaME) AND convert(VARBINARY(8000), Name)
Then make the queries in the loop to update names with LEFT(name + REPLICATE(' ', 2000), 8000).
After completing several updates you'll find out that a single row update locks 2 or 3 pages with an IX lock.
_____________
Code for TallyGenerator
August 29, 2012 at 6:21 am
Sergiy (8/28/2012)
Evil Kraig F (8/28/2012)
Sergiy (8/28/2012)
Evil Kraig F (8/28/2012)
Your constant badgering and attack of the person instead of the issue in question.That's exactly what YOU are doing now.
Quite correct, this conversation took a left turn from being about a discussion regarding internal mechanics awhile ago.
Should I say - you turned it that way?
I'd appreciate any example of a personal attack from my side.
As you wish.
Sergiy (8/16/2012)
GilaMonster (8/16/2012)
I'm not going to be stupid enough to claim each and every case.Yeah, but you're trying to play stupid by not understanding that we have only 2 cases in this particular example.
:rolleyes:
What did you find wrong?
Was not the opponent actually playing stupid in attempt to avoid answerring the question?
Don't you find THAT kind behaviour offensive in a discussion?
Or do you consider it allowed for the ones you personally admire?
Sergiy (8/26/2012)
SQL Kiwi (8/24/2012)
The storage engine sets the is_dirty bit when it changes a page.
If you'd only understand your own words...
:hehe:
Paul, were you offended with that comment?
Sorry, if yes.
I put the smile on purpose there, so it would be taken not too seriously.
Sergiy (8/27/2012)
SQL Kiwi (8/26/2012)
Sergiy (8/26/2012)
Was not so clear to me, as it turns out. Learned couple of things here, thanks to the good samaritans.Good. Well the last (important) thing to be learned is that the storage engine does not maintain an 'old version' of the page to compare with the 'new version' after the update. The engine knows whether it had to make any changes or not, and simply sets the is_dirty bit if so. As Gail mentioned, the way you have been thinking it works would be horribly inefficient. If the engine knows that it didn't have to update any bytes on the page, it doesn't set the dirty bit.
Fail.
There is no smallest proof for this statement.
The proof was in the link you decided not to read in its entirety.
What did you find personal here?
"Fail" was obviously about the statement, not any person.
And the link you posted does not have any proof for what's stated.
"The engine knows whether it had to make any changes or not" - can you explain how?
Sergiy (8/28/2012)
GilaMonster (8/28/2012)
Or you could, you know, go to the link, get the free ebook and read it yourself maybe.No answer, I see.
Yes, no answer.
It's no my fault Gila cannot give any answer.
If you could find the answer in her reply please help me to see it.
So, a few examples. Are they extreme? No, not really, no one of those alone would have solicited the reaction from me. Taken in combination with the rest of your approach and attitude, I see them as aggressive statements of personal impact.
And I see you came here with a single purpose - to turn the professional discussion into a personal fight.
Till now you did not bring any valid point into the discussuion, only some personal rant.
Please stop it.
.....
Unless we're discussing LOBs, this is untrue, and since LOBs are the exception they're usually specifically stated to be part of the discussion if they're brought in.
OK. Back to the discussion.
It's true.
You forget about fragmentation cases.
Which happen more often then many people anticipate.
Try to change the original test script.
Change the name column definition to varchar(8000).
Add 2 more columns. I used VARCHAR(8000) and VARBINARY (8000) and populate it with REVERSE(NaME) AND convert(VARBINARY(8000), Name)
Then make the queries in the loop to update names with LEFT(name + REPLICATE(' ', 2000), 8000).
After completing several updates you'll find out that a single row update locks 2 or 3 pages with an IX lock.
Hey everyone,
Obviously he is smarter than everyone on the planet and knows more about SQL Server internals than you could ever possibly know. I bet that he even knows that changing the table definition to include other columns and then changing the Name column to a VARCHAR(8000) and then doing "several updates" to Name with LEFT(name + REPLICATE(' ', 2000), 8000) would cause it to overflow a single page and explain the multiple page locks that occur depending on the fillfactor specification, but you all wouldn't get such a simple concept, he is way above you here.
IF YOU DON'T FEED A TROLL EVENTUALLY IT WILL STARVE TO DEATH AND GO AWAY! LET THIS GUY BE WRONG AND MOVE ON! ALL HE IS DOING IS WASTING YOUR TIME!
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
August 29, 2012 at 6:42 am
Edit: dog removed from pile.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 76 through 90 (of 159 total)
You must be logged in to reply to this topic. Login to reply