Essential Trace Flags to enable at SQL Server Services startup

By SHIVAM KUMAR -
 List of trace flags to enable on SQL server service startup:
Server NameTrace FlagStatusGlobalSession
PRD-MSSQL01C1222110
PRD-MSSQL01C1224110
PRD-MSSQL01C4136110

Reason: 
  1. 4136 will help in preventing parameter sniffing on sql server and in turn will result in query optimizer to create optimized plans for same queries having different parameters which will result in better performance. Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.
    Scope: Global or session
  2. 1224 disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation. This helps avoid "out-of-locks" errors when many locks are being used.
    Scope: Global or session
  3. 1222 helps in detecting deadlocks returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.
    Note: Avoid using trace flag 1222 on workload-intensive systems causing deadlocks.
    Scope: Global only


Comments

Post a Comment

Note:Please be gentle while commenting and avoid spamming as comment are anyways moderated thank you.

Popular posts from this blog

Enabling fixed Sidebar for blogger Contempo template for screen sizes smaller than 1440px

By SHIVAM KUMAR -
Image
Here is how to do it : Add this code in your blogger template(where to add this code is mentioned before 2nd code snippet of this post) ,now the value 1300px is the screen size for which I want the sidebar to be always visible for, you can reduce this value as you like, but be aware that reducing this value to much smaller digit will result in showing the overlapped sidebar instead of content body for your blog readers who are using much smaller screen devices such as mobiles which you might not want to happen so be wise : @media screen and (max-width: 1300px) {   .sidebar-container {     bottom: 0;     position: fixed;     top: 0;   }   .sidebar-container {     left: 0;     right: auto;   }   .sidebar-container.sidebar-invisible {     -webkit-transition-timing-function: cubic-bezier(0.4,0.0,0.6,1);             tra...
Read more

Adding Home Older Newer Post Buttons in new Blogger templates such as Contempo

By SHIVAM KUMAR -
Image
Requirement: Add Home, Older, Newer Post Buttons in new Blogger templates such as Contempo. Solution: Hi for this jump to Blog widget in HTML editor of your template and find and replace code in includables with respective id's with the ones given below also change the order of buttons as shown below in postPagination includable as per your needs. Code given below shows button in this order   Older Posts Home Newer Posts Demo: Can be seen by clicking here and scrolling to end of page. Screenshot: Note: First page will display only Older posts button as there is nothing to go back when we are on landing or home page and the last page will display Previous posts button and the Home button as there is nothing left to go forward. Displays buttons in order provided here in frontend ,ID to search postPagination: <b:includable id='postPagination'> <div class='blog-pager container' id='blog-pager'> <b:include cond='data:new...
Read more

Adding copy to clipboard button to every google code prettify pre blocks in blogger

By SHIVAM KUMAR -
Image
It's a 2 part process: The script part to find all the pre blocks having code in it and append a copy button to it dynamically. The style part to place our copy button at the specified location(on top right of every code block in this case) The script part: <script> var pre = document.getElementsByTagName('pre'); for (var i = 0; i < pre.length; i++) {             var button = document.createElement('button');                     button.className = 'copy-button';                     button.textContent = 'Copy';                     var s = pre[i].innerText;             ...
Read more