Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««4,0764,0774,0784,0794,080»»»

Are the posted questions getting worse? Expand / Collapse
Author
Message
Posted Monday, August 5, 2013 10:08 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:18 AM
Points: 843, Visits: 7,381
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 ...

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


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
Post #1480987
Posted Monday, August 5, 2013 12:33 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
Steve Jones - SSC Editor (8/5/2013)
GilaMonster (8/5/2013)
andrew gothard (8/5/2013)
... Remember being able to PIN TABLE ...

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


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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1481039
Posted Monday, August 5, 2013 1:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 13,295, Visits: 11,086
GilaMonster (8/5/2013)
andrew gothard (8/5/2013)
... Remember being able to PIN TABLE ...

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!




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1481069
Posted Monday, August 5, 2013 1:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:02 PM
Points: 35,369, Visits: 31,909
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1481070
Posted Monday, August 5, 2013 2:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:02 PM
Points: 7,801, Visits: 9,553
Steve Jones - SSC Editor (8/5/2013)
GilaMonster (8/5/2013)
andrew gothard (8/5/2013)
... Remember being able to PIN TABLE ...

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

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
Post #1481074
Posted Monday, August 5, 2013 2:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:02 PM
Points: 35,369, Visits: 31,909
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1481076
Posted Monday, August 5, 2013 2:29 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1481082
Posted Monday, August 5, 2013 2:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:02 PM
Points: 7,801, Visits: 9,553
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 ...

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


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
Post #1481098
Posted Monday, August 5, 2013 3:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:02 PM
Points: 35,369, Visits: 31,909
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1481100
Posted Monday, August 5, 2013 3:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:02 PM
Points: 35,369, Visits: 31,909
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 ...

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


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?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1481102
« Prev Topic | Next Topic »

Add to briefcase «««4,0764,0774,0784,0794,080»»»

Permissions Expand / Collapse