October 12, 2018 at 7:50 am
Eirikur Eiriksson - Friday, October 12, 2018 7:45 AMBrandie Tarvin - Friday, October 12, 2018 6:16 AMChrisM@Work - Friday, October 12, 2018 6:03 AMjasona.work - Friday, October 12, 2018 5:53 AMBrandie Tarvin - Friday, October 12, 2018 5:40 AMjasona.work - Friday, October 12, 2018 5:36 AMBrandie Tarvin - Friday, October 12, 2018 5:34 AMInteresting that he hasn't responded since being told No.Aw, and I just decided to troll the troll and told him how to encrypt his Close Hold Classified view to get it to us...
:hehe:Do it. I thought about it, but that was after I did the one word answer.
Oh, I already did!
Who knows, we might actually get to see this view...I doubt it, he's even refusing to share it with Microsoft:
Boom!
I'm not going to tell the guy, but this is a simple lock escalation issue, add tablockx and the perm table method becomes the fastest 😉
😎
No, don't tell him. As someone else said, we're not responding to his question until he finally responds to ours. Plus, every time he chooses to repost that same question on Grant's query, I'm reporting him for bumping the topic.
October 12, 2018 at 7:55 am
Brandie Tarvin - Friday, October 12, 2018 7:49 AMSigh. Everyone keeps getting invited to royal weddings. Except for me. Why don't I ever get invited?Not that I could watch it. Just saw the news articles about it. But still, she should have at least invited some unknown American whom she's never heard of. @=)
Bah, nothing fun and exciting about a wedding, royal or otherwise...
Me, I'd like to get invited to something fun...
San Diego Comic Con, or the Brent Ozar PASS Summit after-party, or center ice tickets for a game 4 RedWings Stanley Cup sweep...
I guess I might have a chance at one of those...
(edit) BTW, I was at the royal wedding, it was boring up until the Queen Mother did a keg stand...
October 12, 2018 at 9:21 am
Brandie Tarvin - Friday, October 12, 2018 7:49 AMSigh. Everyone keeps getting invited to royal weddings. Except for me. Why don't I ever get invited?Not that I could watch it. Just saw the news articles about it. But still, she should have at least invited some unknown American whom she's never heard of. @=)
Consider your self lucky, why spend hours on stuffy "royal" pretentious, no benefit, tax payer squeezing, totally useless stuff?
😎
October 12, 2018 at 9:29 am
Eirikur Eiriksson - Friday, October 12, 2018 9:21 AMBrandie Tarvin - Friday, October 12, 2018 7:49 AMSigh. Everyone keeps getting invited to royal weddings. Except for me. Why don't I ever get invited?Not that I could watch it. Just saw the news articles about it. But still, she should have at least invited some unknown American whom she's never heard of. @=)
Consider your self lucky, why spend hours on stuffy "royal" pretentious, no benefit, tax payer squeezing, totally useless stuff?
😎
As a Brit, should I be ashamed that I heard about this first here, rather than in the news? :hehe:
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 12, 2018 at 9:32 am
Thom A - Friday, October 12, 2018 9:29 AMEirikur Eiriksson - Friday, October 12, 2018 9:21 AMBrandie Tarvin - Friday, October 12, 2018 7:49 AMSigh. Everyone keeps getting invited to royal weddings. Except for me. Why don't I ever get invited?Not that I could watch it. Just saw the news articles about it. But still, she should have at least invited some unknown American whom she's never heard of. @=)
Consider your self lucky, why spend hours on stuffy "royal" pretentious, no benefit, tax payer squeezing, totally useless stuff?
😎As a Brit, should I be ashamed that I heard about this first here, rather than in the news? :hehe:
HA! Probably. @=)
October 12, 2018 at 9:33 am
Thom A - Friday, October 12, 2018 9:29 AMEirikur Eiriksson - Friday, October 12, 2018 9:21 AMBrandie Tarvin - Friday, October 12, 2018 7:49 AMSigh. Everyone keeps getting invited to royal weddings. Except for me. Why don't I ever get invited?Not that I could watch it. Just saw the news articles about it. But still, she should have at least invited some unknown American whom she's never heard of. @=)
Consider your self lucky, why spend hours on stuffy "royal" pretentious, no benefit, tax payer squeezing, totally useless stuff?
😎As a Brit, should I be ashamed that I heard about this first here, rather than in the news? :hehe:
No, only seen it in the Lack of Standard
😎
l
October 12, 2018 at 9:39 am
ChrisM@Work - Friday, October 12, 2018 4:29 AMI think the secret view guy is just bored.
I gave up on that thread a long time ago. I thought about responding when he misapplied Mathematical Induction to "prove" that this problem existed on all machines, but I decided I didn't want to get dragged into it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 12, 2018 at 9:46 am
Post gone - maybe one of the admins wishes to revive it but I don't see any point on it.
October 12, 2018 at 9:59 am
frederico_fonseca - Friday, October 12, 2018 9:46 AMPost gone - maybe one of the admins wishes to revive it but I don't see any point on it.
Looks like the OP's account has been removed, as the post still exists, you just can't see it in the forum, and all his posts are missing. Might be that several members (not me) marked his repeated reply, the one he posted about 8 times, as spam and it hit the spam threshold. I recall Steve saying a while ago that the forum reports work on a "3 strikes and you're (temporarily) out" on new(ish) accounts when most of us do the reporting, due to the amount of points we have. Apparently points really do mean "prizes"; aka privileges to temporarily "ban".
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 12, 2018 at 10:00 am
frederico_fonseca - Friday, October 12, 2018 9:46 AMPost gone - maybe one of the admins wishes to revive it but I don't see any point on it.
That would explain the weird hiccup I saw in Chris's last post. The thread is technically still there, but all of his posts are gone. Every last one.
October 12, 2018 at 10:02 am
Thom A - Friday, October 12, 2018 9:59 AMfrederico_fonseca - Friday, October 12, 2018 9:46 AMPost gone - maybe one of the admins wishes to revive it but I don't see any point on it.Looks like the OP's account has been removed, as the post still exists, you just can't see it in the forum, and all his posts are missing. Might be that several members (not me) marked his repeated reply, the one he psoted about 8 times, as spam and it hit the spam threshold. I recall Steve saying a while ago that the forum reports work on a "3 strikes and you're (temporarily) out" on new(ish) accountd when most of us do the reporting, due to the amount of points we have. Apparently points really do mean "prizes"; aka privileges to temporarily "ban".
That was it - 3 spam reports of his last repeated post after I warned him I would do it.
I (and others I'm sure) use this a lot for the chinese spam that shows nearly every day
October 12, 2018 at 10:35 am
So looking through the news of Michael (and hearing it when it slammed SC), I heard / saw reports of flooding and trees down in my old neighborhood. In fact right on top of where I used to live. If I still lived there, the good news would have been that I was on a second floor and wouldn't have gotten flooded, but the bad news would have been that I couldn't go anywhere because trees and water.
The best news? I'm in Florida in a place that barely got any rain ("barely" meaning quite a bit of rain but more like regular storms and not like hurricane rains) and had one tornado spin off in the area far away from me. So I'm good. I hope everyone else is.
October 12, 2018 at 10:40 am
Brandie Tarvin - Friday, October 12, 2018 10:35 AMSo looking through the news of Michael (and hearing it when it slammed SC), I heard / saw reports of flooding and trees down in my old neighborhood. In fact right on top of where I used to live. If I still lived there, the good news would have been that I was on a second floor and wouldn't have gotten flooded, but the bad news would have been that I couldn't go anywhere because trees and water.
The best news? I'm in Florida in a place that barely got any rain ("barely" meaning quite a bit of rain but more like regular storms and not like hurricane rains) and had one tornado spin off in the area far away from me. So I'm good. I hope everyone else is.
Other than the weather folk talking about the west side of the state possibly getting some graupel mixed in with the drizzle over the evening / weekend, we're doing OK...
Of course, we're not sure what to wear from one day to the next right now though...
80s on Tuesday and Wednesday, 60-70s yesterday, and 40-50s most of the weekend into next week...
October 12, 2018 at 11:43 am
Brandie Tarvin - Friday, October 12, 2018 10:00 AMfrederico_fonseca - Friday, October 12, 2018 9:46 AMPost gone - maybe one of the admins wishes to revive it but I don't see any point on it.That would explain the weird hiccup I saw in Chris's last post. The thread is technically still there, but all of his posts are gone. Every last one.
I was able to do a quick check on that thread a few hours back, but unable to post. I wanted to put in at least one more reply, not for the sake of the OP but for the sake of others in the thread, to correct some incorrect information I saw posted from others there. I believe most who were in that thread are here as well so I'll just post it here.
There wa a question at one time of why writing to a permanent table took more time than writing to a temporary table, and I believe that someone said "basically yes" to the question whether this is because permanent tables start on disk and temporary tables start in memory. That is NOT correct. All tables (permanent, temporary, and even table variables) are stored in a database - tempdb in the case of the latter two. They are stored on 8K database pages. Writes to those pages, including the writes associating with creating the table, are done in memory (buffer pool) and the pages are marked dirty. A background checkpoint process occassionally wakes up, sweeps through memory to look for dirty pages and writes them to disk. The same happens when an explicit CHECKPOINT statement runs, or when the database is shut down.
So all tables effectively start in memory. All tables remain only there until a checkpoint runs and causes the table's data pages to be written to disk. If the table happens to have already been deleted before a checkpoint ever runs (and due to the nature of how tables are used, that is far more likely for temporary tables than for permanent tables), then they might be removed before ever having a checkpoint, in which case they indeed are never written to disk. This can happen to temp tables and table variables that are short-lived, but if you create a permanent table and drop it fast enough the same happens. And conversely, if you use temp tables or table variables and then hold your connection open for long enough, they will be checkpointed as normal.
As to the different execution times that the Mystery View Guy talked about, my suspicion is that the permanent table was made in a user database and hence subject to full logging (SQL Server needs info in the log file for doing a rollback but also for recovery if the DB goes down mid-transaction). The temporary table is by definition in tempdb which never uses recovery - so SQL Server all by itself reduces the amount of information it logs, keeping only what is needed for a rollback. And if you then switch from temp table to table variable, you get even less logging because those are not subject to transactions and hence never require a rollback either.
(I also suspect that the example Grant posted, which I didn't actually look at, used hundreds or thousands of micro-transactions instead of embedding all code within a single transaction - which explains why the logging overhead was as big as it was. The log file uses an unbuffered write-ahead system when a transaction is committed. If you do a thousand writes and wait for confirmation from the disk system every time, you will notice the time it takes. If you use a single transaction, the overhead will be far less).
October 12, 2018 at 11:47 am
Hugo Kornelis - Friday, October 12, 2018 11:43 AMBrandie Tarvin - Friday, October 12, 2018 10:00 AMfrederico_fonseca - Friday, October 12, 2018 9:46 AMPost gone - maybe one of the admins wishes to revive it but I don't see any point on it.That would explain the weird hiccup I saw in Chris's last post. The thread is technically still there, but all of his posts are gone. Every last one.
I was able to do a quick check on that thread a few hours back, but unable to post. I wanted to put in at least one more reply, not for the sake of the OP but for the sake of others in the thread, to correct some incorrect information I saw posted from others there. I believe most who were in that thread are here as well so I'll just post it here.
There wa a question at one time of why writing to a permanent table took more time than writing to a temporary table, and I believe that someone said "basically yes" to the question whether this is because permanent tables start on disk and temporary tables start in memory. That is NOT correct. All tables (permanent, temporary, and even table variables) are stored in a database - tempdb in the case of the latter two. They are stored on 8K database pages. Writes to those pages, including the writes associating with creating the table, are done in memory (buffer pool) and the pages are marked dirty. A background checkpoint process occassionally wakes up, sweeps through memory to look for dirty pages and writes them to disk. The same happens when an explicit CHECKPOINT statement runs, or when the database is shut down.
So all tables effectively start in memory. All tables remain only there until a checkpoint runs and causes the table's data pages to be written to disk. If the table happens to have already been deleted before a checkpoint ever runs (and due to the nature of how tables are used, that is far more likely for temporary tables than for permanent tables), then they might be removed before ever having a checkpoint, in which case they indeed are never written to disk. This can happen to temp tables and table variables that are short-lived, but if you create a permanent table and drop it fast enough the same happens. And conversely, if you use temp tables or table variables and then hold your connection open for long enough, they will be checkpointed as normal.
As to the different execution times that the Mystery View Guy talked about, my suspicion is that the permanent table was made in a user database and hence subject to full logging (SQL Server needs info in the log file for doing a rollback but also for recovery if the DB goes down mid-transaction). The temporary table is by definition in tempdb which never uses recovery - so SQL Server all by itself reduces the amount of information it logs, keeping only what is needed for a rollback. And if you then switch from temp table to table variable, you get even less logging because those are not subject to transactions and hence never require a rollback either.
(I also suspect that the example Grant posted, which I didn't actually look at, used hundreds or thousands of micro-transactions instead of embedding all code within a single transaction - which explains why the logging overhead was as big as it was. The log file uses an unbuffered write-ahead system when a transaction is committed. If you do a thousand writes and wait for confirmation from the disk system every time, you will notice the time it takes. If you use a single transaction, the overhead will be far less).
Good explanation - although on this case the insert was of a single record (output of count(*)) which is why most were not relating the issue to what you explained so well here.
Viewing 15 posts - 62,596 through 62,610 (of 66,000 total)
You must be logged in to reply to this topic. Login to reply