August 5, 2013 at 10:08 am
Chad Crawford (8/5/2013)
Steve Jones - SSC Editor (8/5/2013)
GilaMonster (8/5/2013)
andrew gothard (8/5/2013)
... Remember being able to PIN TABLE ...
:exclamation: you have no idea how happy I am that vendors do not have access to that
And clients....
I remember finding that in the bank's main DB when I first started there. The overall performance gain I got from unpinning all the tables was quite large.
As a consultant, I'd think you'd want every company to have this ability, and every DBA/developer to have access. Very good for business :w00t:
That type of thinking has led me to change my view on cursors as well. Telling people, "I love cursors" usually gets me a strange look... until I finish the sentence.
We have a number of jobs that take > 30 minutes. One of them contains cursor loops. We recently moved from local drives to a SAN. All the jobs taking 30+ minutes saw 25% improvement in running time except the one with the cursor loops. That saw no improvement at all.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 5, 2013 at 12:33 pm
Steve Jones - SSC Editor (8/5/2013)
GilaMonster (8/5/2013)
andrew gothard (8/5/2013)
... Remember being able to PIN TABLE ...
:exclamation: you have no idea how happy I am that vendors do not have access to that
And clients....
I remember finding that in the bank's main DB when I first started there. The overall performance gain I got from unpinning all the tables was quite large.
As a consultant, I'd think you'd want every company to have this ability, and every DBA/developer to have access. Very good for business :w00t:
Boring business and very short-lived projects. Besides, devs mess up SQL's performance just fine by themselves and that's usually a longer-term project to fix.
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 5, 2013 at 1:50 pm
GilaMonster (8/5/2013)
andrew gothard (8/5/2013)
... Remember being able to PIN TABLE ...
:exclamation: you have no idea how happy I am that vendors do not have access to that
And clients....
I remember finding that in the bank's main DB when I first started there. The overall performance gain I got from unpinning all the tables was quite large.
Instant DBA magic!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 5, 2013 at 1:51 pm
GilaMonster (8/5/2013)
Jeff Moden (8/4/2013)
Remember being able to PIN TABLE and then took that wonderful feature away? Now they're bringing a form of it back with Hekaton but with more restrictions on use.Pin Table != Hekaton. Not even close.
All pin table did was ensure that a table remained in cache, just as if it were a hot table that's constantly used. No other optimisations, no reductions in writes to log or data file and the problem that depending what you try to pin you could absolutely cripple performance (imagine pinning a 2GB table on a server that only had 3 GB memory just because the DBA had been told that it was a wonderful feature that should be used on busy tables)
It wasn't that great a feature. Used on a table that was heavily used it would have little effect as a heavily used table tends to stay in cache (the heavily used portions anyway), used on a seldom used table it's taking memory that's not needed.
Won't Hekaton allow you to use more memory than you should?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2013 at 2:10 pm
Steve Jones - SSC Editor (8/5/2013)
GilaMonster (8/5/2013)
andrew gothard (8/5/2013)
... Remember being able to PIN TABLE ...
:exclamation: you have no idea how happy I am that vendors do not have access to that
And clients....
I remember finding that in the bank's main DB when I first started there. The overall performance gain I got from unpinning all the tables was quite large.
As a consultant, I'd think you'd want every company to have this ability, and every DBA/developer to have access. Very good for business :w00t:
Even better for business if you are selling RAM, perhaps - at least it would have been good in the old days when we used expensive core store instead of semiconductors with more bits on each chip than we used to have in a supercomputer's main store.
Tom
August 5, 2013 at 2:14 pm
I agree that, like anything else, pinned tables could be abused for all the wrong reasons. We used it as a temporary measure to get out of the woods because of all the crap batch code that would drive things out of cache in the middle of the day which brought the front end to it's knees. It took us a while but we fixed all of that and unpinned almost all of the tables but it sure was handy to have the option.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2013 at 2:29 pm
Jeff Moden (8/5/2013)
Won't Hekaton allow you to use more memory than you should?
I don't know what the memory management's going to be, there's no info available on it yet.
Hekaton is not just memory-resident tables, there's a whole lot more to it than just that (including tables that don't have any persistent data storage at all). All pintable did was force a table to stay in cache once read, essentially a 'hint' for the resource manager with all the caveats that hints have normally, except it was too often used as a silver bullet which it most certainly wasn't.
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 5, 2013 at 2:53 pm
GilaMonster (8/5/2013)
Steve Jones - SSC Editor (8/5/2013)
GilaMonster (8/5/2013)
andrew gothard (8/5/2013)
... Remember being able to PIN TABLE ...
:exclamation: you have no idea how happy I am that vendors do not have access to that
And clients....
I remember finding that in the bank's main DB when I first started there. The overall performance gain I got from unpinning all the tables was quite large.
As a consultant, I'd think you'd want every company to have this ability, and every DBA/developer to have access. Very good for business :w00t:
Boring business and very short-lived projects. Besides, devs mess up SQL's performance just fine by themselves and that's usually a longer-term project to fix.
My experience is that DBAs mess it up more.
That's partly because
(a) they like cursors, because they are the best way to handle large tables,
(b) they hate normalisation, even normalisation to 1NF, because it complicates the code and increases that database size,
(c) they think pinning tables is even better for performance than "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED", because it
saves all that IO while "READ UNCOMMITTED" merely saves pointless locking overhead (although it is of course best to do both), and
(d) they think multi-phase transactions (transactions that remain open waiting for end user response) are OK as long as you change "READ UNCOMMITTED"
to "SERIALIZABLE", because although slow and highly variable response time is inevitable with those things it's necessary to do them if you want to give
end-users an enjoyable experience.
But mostly it's because
(e) they've gone on DBA training courses on which they were taught all those things by idiots who shouldn't be allowed to teach, and thus they
are extremely difficult to educate.
Developers don't usually have any DBA training, so even though they mostly think they know it all they can usually (not always) be educated despite that.
Tom
August 5, 2013 at 3:28 pm
GilaMonster (8/5/2013)
Jeff Moden (8/5/2013)
Won't Hekaton allow you to use more memory than you should?I don't know what the memory management's going to be, there's no info available on it yet.
Hekaton is not just memory-resident tables, there's a whole lot more to it than just that (including tables that don't have any persistent data storage at all). All pintable did was force a table to stay in cache once read, essentially a 'hint' for the resource manager with all the caveats that hints have normally, except it was too often used as a silver bullet which it most certainly wasn't.
Yep. I aware of that. That's why I said it had more restrictions. Although it's not nearly a parallel to PIN TABLE, I just think it's funny that they brought something like Hekaton on because of the basic idea.
Do you think Hekaton will provide any additional performance over using something like SSDs?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2013 at 3:30 pm
L' Eomot Inversé (8/5/2013)
GilaMonster (8/5/2013)
Steve Jones - SSC Editor (8/5/2013)
GilaMonster (8/5/2013)
andrew gothard (8/5/2013)
... Remember being able to PIN TABLE ...
:exclamation: you have no idea how happy I am that vendors do not have access to that
And clients....
I remember finding that in the bank's main DB when I first started there. The overall performance gain I got from unpinning all the tables was quite large.
As a consultant, I'd think you'd want every company to have this ability, and every DBA/developer to have access. Very good for business :w00t:
Boring business and very short-lived projects. Besides, devs mess up SQL's performance just fine by themselves and that's usually a longer-term project to fix.
My experience is that DBAs mess it up more.
That's partly because
(a) they like cursors, because they are the best way to handle large tables,
(b) they hate normalisation, even normalisation to 1NF, because it complicates the code and increases that database size,
(c) they think pinning tables is even better for performance than "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED", because it
saves all that IO while "READ UNCOMMITTED" merely saves pointless locking overhead (although it is of course best to do both), and
(d) they think multi-phase transactions (transactions that remain open waiting for end user response) are OK as long as you change "READ UNCOMMITTED"
to "SERIALIZABLE", because although slow and highly variable response time is inevitable with those things it's necessary to do them if you want to give
end-users an enjoyable experience.
But mostly it's because
(e) they've gone on DBA training courses on which they were taught all those things by idiots who shouldn't be allowed to teach, and thus they
are extremely difficult to educate.
Developers don't usually have any DBA training, so even though they mostly think they know it all they can usually (not always) be educated despite that.
BWAAAA-HAAAA!!! That's funny. Every knows that if you pin a table that you want to use WITH (TABLOCKX), right? :-):-D:-P;-):hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2013 at 3:46 pm
Jeff Moden (8/5/2013)
Do you think Hekaton will provide any additional performance over using something like SSDs?
Yes, potentially large ones. Because it is not just tables in memory.
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 6, 2013 at 5:59 am
So, like an Id10T, I went to the gym last night and for the first time in almost 2 years lifted weights. Well, OK, used weight machines, but still...
Now, I can't bend my arm much past 90deg without the elbow end of my tricep hurting like a m-fer...
Teach me to try to dive back into the pool without first making sure I remembered how to swim...
On a completely unrelated note, I was perusing the "suggested contacts" on LinkedIn last night, and saw a familiar name pop up:
Brandie Tarvin...
Who I don't believe I've ever had the pleasure of actually meeting in person...
Gotta wonder just where LI is finding such a linkage...
/tinfoil hat time!
:hehe:
August 6, 2013 at 6:05 am
jasona.work (8/6/2013)
On a completely unrelated note, I was perusing the "suggested contacts" on LinkedIn last night, and saw a familiar name pop up:Brandie Tarvin...
...
Gotta wonder just where LI is finding such a linkage...
/tinfoil hat time!
:hehe:
I dunno. Sounds a bit suspicious to me. I wouldn't trust this Brandie Tarvin person if I were you. she's one of those techies that pretends to know what she's doing then kills her website by updating a bad WordPress plugin. 😀
LinkedIn tends to refer contacts based on who you know in your network. I'm betting I'm directly connected to one of your contacts (2nd connection to you), so it suggested we hook up. If you send me a LI message reminding me you're from the Thread, I'd be happy to connect up with you.
EDIT: If anyone here has tried to connect to me on Twitter or LI and hasn't gotten a response or follow-back, it's probably because I don't recognize your name. Please feel free to send me a message that you and I talk on SSC as a reminder and I will correct the oversight. Lately, I've gotten a lot of connection requests from people just trying to bump up their counts (who I don't know from boo), so I've gotten a little pickier about who I follow back / connect to.
August 6, 2013 at 6:16 am
Brandie Tarvin (8/6/2013)
Lately, I've gotten a lot of connection requests from people just trying to bump up their counts (who I don't know from boo), so I've gotten a little pickier about who I follow back / connect to.
I'm deleting more than 90% of connection requests now, especially ones from people who say they've worked with me at companies I've never done any work for or who tell me they're a friend when I don't recognise their name.
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 6, 2013 at 6:20 am
Something I found amusing on LI is that everyone at my old employer, doesn't actually work there...
The company hasn't / doesn't have an LI profile, but there's another company with the same name based out of Montreal. Well, anyone at my old job when they put in the company name, would just automatically select this company...
:hehe:
I think I was the only one who avoided this, by making sure I keyed in the company name, and not accepting any of the suggestions.
As for bumping up connection counts, I've actually got a fairly low profile on LI, at least for now...
Jason
(PS, Brandie, I believe I will take you up on your offer tonight! Thank you!)
Viewing 15 posts - 40,771 through 40,785 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply