DBCC PINTABLE Google for it.. search results however may indicate that it might have been discontinued after SQL 2005. I do not have a ready test instance to try it on..
Joe From: Andrew C Goodall Sent: Thursday, September 08, 2011 3:12 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Does anybody know if there is a similar option for SQL Server 2008? Regards, Andrew Goodall Software Engineer 2 | Development Services | jcpenney . www.jcp.com -------------------------------------------------------------------------------- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of patrick zandi Sent: Thursday, September 08, 2011 1:44 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** I have done this in the past:: pin "user" (T30 for me) table, this will drop IO to database.. have see this alot.. On Thu, Sep 8, 2011 at 2:36 PM, Guillaume Rheault <guilla...@dcshq.com> wrote: ** Hi Joe, I got to disagree with you again... but I guess this is what makes this ARS list fun! Pinning a table into memory is not an overkill, it is quite simple to do, you can ask your Oracle DBA. Since the cost of physical memory is lower and lower every year, it is actually more cost-effective to add some more memory to your database server and pin look-up tables, than optimizing the searches to these look-up tables; optimizing the searches will involves one or more of the following: - Possible DBA time to analyze the performance of queries - Remedy Admin/Developer/Consultant time to figure where those sub-optimal searches are being issued from, and modify them - Possible customizations to the ITSM application (which is what everybody is trying to avoid) Pinning a table into memory involves: - Small amount of DBA time to alter the T table to pin it. - Small amount of sys admin to add memory in the database server (this cost is a one time cost) See, when you pin the table in memory, it does NOT matter if your queries are crappy or inefficient, since the table data is in memory; that's the beauty of it! While you are at it, you may as well pin the T table related to the User form. cheers, Guillaume -------------------------------------------------------------------------------- From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Joe Martin D'Souza [jdso...@shyle.net] Sent: Thursday, September 08, 2011 12:33 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Yes I agree you would want to avoid pinning a table to memory whose contents are changed continuously by way of modifications or additions.. This would result in frequent memory writes which would beat the purpose of why you choose to pin it to memory in the first place. While the CTM:People table is a good candidate as its contents change less frequently in most standard environments, unless it’s a B2C environment where you maintain your customer base in your CTM:People form, if the table size is as small as 140K, just optimizing searches on it is more than enough, and pinning it to memory is an overkill.. Optimizing searches on this table when records are about that much or even upto half a million, would return the search in less than a fraction of a second anyways.. Joe From: Guillaume Rheault Sent: Thursday, September 08, 2011 12:15 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** Joe, well, I disagree with your rationale... actually because it is not a large table, you can pin in it memory. Generally speaking, you only pin into memory look-up tables that are used heavily, and the people form/table is a good candidate. You definitely do not want to pin a transactional table (like the incident form). Guillaume -------------------------------------------------------------------------------- From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Joe Martin D'Souza [jdso...@shyle.net] Sent: Thursday, September 01, 2011 2:19 PM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** For only 140K records I don’t think you need to do anything out of the ordinary to boost up performance. If your statistics were not updated, it does make sense as Oracle didn’t know it had to use indexes and was perhaps attempting table scans assuming the table has no records if the statistics information it had for row count was 0 or thereabouts prior to updating it.. Personally I don’t really think you can consider CTM:People with around 140 K records to be a large object. Its big but not that big enough to be considered to pin to memory.. Joe From: John Sundberg Sent: Thursday, September 01, 2011 1:31 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ** True... good suggestion. Fundamentally - I was looking for what is "normal" -- what we were seeing was what we thought was slow. But - just cause you think something is slow - does not mean that it is slow. Sometimes -- you have to look to your neighbors and compare. So - thanks to all that shared their timings and system info. -John On Sep 1, 2011, at 8:30 AM, Guillaume Rheault wrote: ** One more way to make things even faster in Oracle is to "pin" the underlying T table into memory. Ask the DBA over there to do that -Guilalume -------------------------------------------------------------------------------- From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of John Sundberg [john.sundb...@kineticdata.com] Sent: Thursday, August 25, 2011 7:25 AM To: arslist@ARSLIST.ORG Subject: Re: Performance question CTM:People timing ARSlist: "Where the Answers Are"_ _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ -- Patrick Zandi _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer. _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"