snprotips.com Open in urlscan Pro
198.185.159.145  Public Scan

Form analysis
2 forms found in the DOM

POST

<form class="newsletter-form" data-form-id="5b32802003ce643f1a80c6ae" autocomplete="on" method="POST" novalidate="" onsubmit="return (function (form) {
    Y.use('squarespace-form-submit', 'node', function usingFormSubmit(Y) {
      (new Y.Squarespace.FormSubmit(form)).submit({
        formId: '5b32802003ce643f1a80c6ae',
        collectionId: '56788ca9d08d9279f2a3afb0',
        objectName: 'item-5b292603575d1f3de94c70af'
      });
    });
    return false;
  })(this);">
  <header class="newsletter-form-header">
    <h2 class="newsletter-form-header-title">Subscribe</h2>
    <div class="newsletter-form-header-description">
      <p>Sign up with your email address to receive news and updates.</p>
    </div>
  </header>
  <div class="newsletter-form-body">
    <div class="newsletter-form-fields-wrapper form-fields" style="vertical-align: middle;">
      <fieldset id="name-yui_3_17_2_1_1530036168171_9978" class="newsletter-form-name-fieldset form-item fields name required" style="vertical-align: bottom;">
        <div class="newsletter-form-field-wrapper field first-name">
          <label class="newsletter-form-field-label title">First Name</label>
          <input class="newsletter-form-field-element field-element field-control" name="fname" x-autocompletetype="given-name" type="text" spellcheck="false" placeholder="First Name" maxlength="30" data-title="First">
        </div>
        <div class="newsletter-form-field-wrapper field last-name">
          <label class="newsletter-form-field-label title">Last Name</label>
          <input class="newsletter-form-field-element field-element field-control" name="lname" x-autocompletetype="surname" type="text" spellcheck="false" placeholder="Last Name" maxlength="30" data-title="Last">
        </div>
      </fieldset>
      <div id="email-yui_3_17_2_1_1530036168171_9977" class="newsletter-form-field-wrapper form-item field email required" style="vertical-align: bottom;">
        <label class="newsletter-form-field-label title" for="email-yui_3_17_2_1_1530036168171_9977-field">Email Address</label>
        <input id="email-yui_3_17_2_1_1530036168171_9977-field" class="newsletter-form-field-element field-element" name="email" x-autocompletetype="email" autocomplete="email" type="email" spellcheck="false" placeholder="Email Address">
      </div>
    </div>
    <div data-animation-role="button" class="newsletter-form-button-wrapper submit-wrapper" style="vertical-align: middle;">
      <button class="
            newsletter-form-button
            sqs-system-button
            sqs-editable-button-layout
            sqs-editable-button-style
            sqs-editable-button-shape
            sqs-button-element--primary
          " type="submit" value="Sign Up">
        <span class="newsletter-form-spinner sqs-spin light large"></span>
        <span class="newsletter-form-button-label">Sign Up</span>
        <span class="newsletter-form-button-icon"></span>
      </button>
    </div>
    <div class="captcha-container sqs-form-rendered
          align-left
          
          
          " data-theme="light"></div>
  </div>
  <div class="newsletter-form-footnote">
    <p>We respect your privacy.</p>
  </div>
  <div class="hidden form-submission-text">Thank you!</div>
  <div class="hidden form-submission-html" data-submission-html=""></div>
</form>

POST #

<form action="#" method="post" class="comment-form">
  <textarea class="comment-input" name="comment" data-defaulttext="Leave a comment here." data-edited="false"></textarea>
</form>

Text Content

The ServiceNow Development Handbook 3rd Edition is out now! Click here to get
your copy, and level-up your ServiceNow development career!

 * Pro-Tips Blog
 * Blog Archive
 * Subscribe
 * Books
    * Learning ServiceNow
    * SN Developer Handbook
    * Building Powerful Workflows

 * Tools
    * A Better, One-Click Approval
    * Advanced Attachment Copy Util
    * Custom Chrome Engines
    * Detect Duplicates (Single or Multi-field)
    * EfficientGlideRecord (Client-side)
    * Enable Guided Setup Creation
    * Get Latest Docs Page Version
    * Get RITM Variables via SRAPI
    * Include in Update Set Tool
    * Journal Redactor
    * Login Link Generator
    * Paginated GlideRecord Utility
    * reCAPTCHA Integration
    * Service Portal: Attachments & DOM
    * Set Catalog Item Variables from URL
    * Smarter Update Sets
    * SN Framerizer Extension
    * SN JS Libraries
    * Temporary Permissions Utility
    * Time-Zone Utilities
    * Try Catalog Item in Portal
    * Update Relocator
    * Update Set Collision Avoidance
    * URL Shortener (go.snc.guru)
    * Was this Email Helpful?

 * Useful Scripts
 * Contact
    * Contact Us
    * Community Discord
    * Community Slack
    * Community Telegram

 * Search

Menu

The SN Guys
Vancouver, WA, 98686
(971) 410-8777
Grow with purpose! ServiceNow Pro-tips, tricks, consulting, and undocumented
information on the ServiceNow platform

Your Custom Text Here




 * Pro-Tips Blog
 * Blog Archive
 * Subscribe
 * Books
    * Learning ServiceNow
    * SN Developer Handbook
    * Building Powerful Workflows

 * Tools
    * A Better, One-Click Approval
    * Advanced Attachment Copy Util
    * Custom Chrome Engines
    * Detect Duplicates (Single or Multi-field)
    * EfficientGlideRecord (Client-side)
    * Enable Guided Setup Creation
    * Get Latest Docs Page Version
    * Get RITM Variables via SRAPI
    * Include in Update Set Tool
    * Journal Redactor
    * Login Link Generator
    * Paginated GlideRecord Utility
    * reCAPTCHA Integration
    * Service Portal: Attachments & DOM
    * Set Catalog Item Variables from URL
    * Smarter Update Sets
    * SN Framerizer Extension
    * SN JS Libraries
    * Temporary Permissions Utility
    * Time-Zone Utilities
    * Try Catalog Item in Portal
    * Update Relocator
    * Update Set Collision Avoidance
    * URL Shortener (go.snc.guru)
    * Was this Email Helpful?

 * Useful Scripts
 * Contact
    * Contact Us
    * Community Discord
    * Community Slack
    * Community Telegram

 * Search





IMPROVING PERFORMANCE ON OLDER INSTANCES WITH TABLE ROTATION

June 19, 2018 Tim Woodruff

If your instance has been around for a while, you've probably built up a few
tables that are quite extensive. The good news is - there are steps that you can
take to both mitigate, and even preemptively prevent this issue. 

"Table Rotations Groups" are a little-known feature that allows you to split
very large tables into manageable chunks. There are two types of table rotation
groups: Rotation, and Extension. What we're going to use today, is an
extension type. This means that every so often, the table you're rotating will
be extended, and all new data will go into the new extended table. 

There are specific use-cases for each table rotation group type. Extension is
best for when a table is queried by the sys_created_on field often, but you need
to retain all historical data. Rotation on the other hand, sets up a specified
number of tables to rotate through using, for a specified period of time each -
but once all tables have been used for the specified period of time, it goes
back to the first table and overwrites it. This means that data in these tables
is not permanent. There is an OOB "rotation"-type extension on the syslog table,
for example. 

The benefits of this may not be obvious, but consider the following scenario: 

Say you want to query the table in question for all records created in the last
60 days.
Since the Created [sys_created_on] field is not typically indexed (being a
date/time field), you essentially have to scan the entire table . If your
instance is a few years old, and the table has been around for a long time with
a lot of throughput, you might have a great number of records in this table! As
you might imagine, this could easily result in query hell. 

View fullsize


Learning ServiceNow - Second Edition
Available now!

By creating an "extension" type table rotation group, we are telling the
database: "Every N days, extend the table in question and put all new records in
the latest extension" (These extensions are called "shards"). The result of
this, is that if you query by the Created [sys_created_on] field, it only has to
scan the shards that contain the records from those dates. 

Imagine the above scenario again, except this time you've got an
"extension" table rotation enabled so that every 90 days, the table is extended.
If we imagine that the last shard was created 60 days ago, then a query for
records created within the last 90 days would only need to scan the two most
recent shards - far more efficient than scanning the entirety of all records for
all time!

That said, there is a performance hit if you ever do need to scan a large
portion of the table, or if you do a query without a sys_created_on query
parameter. More on that below.


SHOULD I DO THIS IN MY INSTANCE? 

Maybe. It's good to ask this question and think carefully about it. It should
certainly not be done on every table. Only a few are good candidates for table
rotation, and many already have rotation enabled! For example, the ecc_queue
table already has table rotation enabled! If you have a custom table that, for
example, has a high-throughput, is used for tacit reporting, but data retention
laws only require that you retain the records in it for two years... you might
consider optimizing that table a little bit, by adding a table rotation group
(Rotation type). 

Advantages

 * Completely transparent (for the most part) - once you set it, it's handled by
   the database and you don't need to think about it or do anything special to
   work with the shards.
 * Allows deletion of old data without affecting current data (using
   Rotation type).
 * Ensures tables only grow to a reasonable size.
 * Reduces working set of data when date is known for query (using either
   Rotation or Extension).


Disadvantages

Queries that do not use the table rotation date (Created [sys_created_on]),
require an inefficient "join" query to query time ranges that span multiple
tables and can be extremely slow if the number of sub-tables is large.
Which it will be.

For example, if you query by Updated [sys_updated] rather than Created
[sys_created_on], you will not be able to make use of the "sharding", and will
end up with a very inefficient union query that joins all the shards together
before performing the query, since the database doesn't know for sure that only
a few specific shards might contain the data you're looking for. 

To improve performance, it's therefore obviously best that the query includes a
window of created dates. Otherwise, your queries would actually perform
worse than if you didn't enable table rotation. For this reason, you should only
enable table rotation on tables that you commonly query (either with scripts, or
implicitly by loading something in the UI such as the history log) by created
date. For example, this would not make sense for the Incident [incident] table,
as most queries on that table do not contain a query parameter on the
sys_created_on field. 


HOW DO I DO IT? 

 1. Navigate to the Table Rotation Group [sys_table_rotation] table by entering
    "sys_table_rotation.list" into the Application Navigator filter bar, and
    pressing Enter. 
 2. To create a new Table Rotation, click New at the top of the Table Rotation
    [sys_table_rotation] list. 
 3. Set the Name field to the name of the table you'd like the rotation to apply
    to.. 
 4. Set the Duration field to an amount of time representing how long each table
    should live before another extension (AKA "shard") should be created. I
    recommend setting this field to somewhere between 90 days and 180 days,
    depending on how active the table is (more active = lower duration). I'm
    going to enter 120 days here. 
 5. Make sure that Type is set to Extension.
    1. If you're creating a Rotation type, set the Type field to that, and
       you'll have the option to enter a number of rotations before it begins
       overwriting old data. 

View fullsize


Once you've got that set up, simply save the record, and table extension will be
enabled! 

Pro-Tip: Table Rotation records are not captured in update sets, so be sure to
manually create them in production after testing in sub-prod environments. Also
consider using our Include in Update Set tool if you want to move them from dev
to higher instances!

--------------------------------------------------------------------------------

 * March 2024
   * Mar 28, 2024 How to Identify Duplicate Records by Multiple Fields in
     ServiceNow Mar 28, 2024
   * Mar 7, 2024 How to Merge Personal & Company ServiceNow Accounts Mar 7, 2024
 * February 2024
   * Feb 12, 2024 5 Lessons About Programming From Richard Feynman Feb 12, 2024
 * July 2023
   * Jul 5, 2023 Managing Instance-Specific System Properties for Dev/Test/Prod
     in ServiceNow Jul 5, 2023
 * May 2023
   * May 11, 2023 5 Ways to Check your ServiceNow Instance for DANGEROUS CODE in
     Less Than 5 minutes May 11, 2023
 * April 2023
   * Apr 28, 2023 Your ACLs and Business Rules are Broken (Here's How to Fix
     Them) Apr 28, 2023
 * December 2022
   * Dec 13, 2022 ServiceNow Developers: BE THE GUIDE! Dec 13, 2022
 * October 2022
   * Oct 19, 2022 A Faster, More Efficient Client-side GlideRecord (Free tool!)
     Oct 19, 2022
   * Oct 9, 2022 Animated Loading Message & Collapsible Details on ServiceNow
     Form or Field (Client-side) Oct 9, 2022
 * August 2022
   * Aug 23, 2022 Using .addJoinQuery() & How to Query Records with Attachments
     in ServiceNow Aug 23, 2022
   * Aug 18, 2022 Free, Simple URL Shortener for ServiceNow Nerds (snc.guru) Aug
     18, 2022
   * Aug 16, 2022 How to Get and Parse ServiceNow Journal Entries as
     Strings/HTML Aug 16, 2022
   * Aug 14, 2022 New tool: Get Latest Version of ServiceNow Docs Page Aug 14,
     2022
 * March 2022
   * Mar 4, 2022 How to Set or Change ServiceNow Application's Repository URL,
     Credentials, or SSH Key Mar 4, 2022
 * February 2022
   * Feb 7, 2022 How to return a CSV file from a Scripted REST API (SRAPI) in
     ServiceNow Feb 7, 2022
 * May 2021
   * May 3, 2021 Adding a Guided Setup to Your ServiceNow Application May 3,
     2021
 * April 2021
   * Apr 27, 2021 Use Automated Tests to Validate "Guided Setup" Completion &
     Functionality. Apr 27, 2021
 * February 2021
   * Feb 11, 2021 "Processors", SRAPIs, and How to Run a Script and Redirect a
     User From a URL in ServiceNow Feb 11, 2021
 * November 2020
   * Nov 17, 2020 SN Guys is now part of Jahnel Group! Nov 17, 2020
 * September 2020
   * Sep 14, 2020 Better ServiceNow Notifications (& Another FREE Tool!) Sep 14,
     2020
 * July 2020
   * Jul 31, 2020 Debugging Client & Catalog Client Scripts in ServiceNow Jul
     31, 2020
 * January 2020
   * Jan 20, 2020 Getting Help from the ServiceNow Community Jan 20, 2020
 * December 2019
   * Dec 18, 2019 Can ServiceNow Script Includes Use the "current" Variable? Dec
     18, 2019
 * November 2019
   * Nov 18, 2019 Handling 'text/plain' and Other Unsupported Content Types in
     ServiceNow Scripted REST APIs Nov 18, 2019
 * April 2019
   * Apr 21, 2019 Understanding Attachments in ServiceNow Apr 21, 2019
   * Apr 10, 2019 Using Custom Search Engines in Chrome to Quickly Navigate
     ServiceNow Apr 10, 2019
   * Apr 4, 2019 Set Catalog Variables from URL Params (Free tool) Apr 4, 2019
   * Apr 1, 2019 Outlook for Android Breaks Email Approvals (+Solution) Apr 1,
     2019
 * March 2019
   * Mar 11, 2019 GlideFilter is Broken - Free Tool: “BetterGlideFilter” Mar 11,
     2019
 * February 2019
   * Feb 27, 2019 Making Update Sets Smarter - Free Tool Feb 27, 2019
 * November 2018
   * Nov 29, 2018 How to Learn ServiceNow Nov 29, 2018
   * Nov 6, 2018 ServiceNow & ITSM as a Career? Nov 6, 2018
 * October 2018
   * Oct 19, 2018 Asynchronous onSubmit Catalog/Client Scripts in ServiceNow Oct
     19, 2018
   * Oct 11, 2018 How to do Massive, Slow Database Operations Efficiently With
     Event-Driven Recursion Oct 11, 2018
 * September 2018
   * Sep 18, 2018 Broken Queries & Query Business Rules in ServiceNow Sep 18,
     2018
   * Sep 7, 2018 JournalRedactor - Easily Redact or Delete Journal Entries in
     ServiceNow! Sep 7, 2018
 * July 2018
   * Jul 23, 2018 Admin Duty Separation with a Single Account Jul 23, 2018
 * June 2018
   * Jun 19, 2018 Improving Performance on Older Instances with Table Rotation
     Jun 19, 2018
   * Jun 4, 2018 New Free Tool: Login Link Generator Jun 4, 2018
 * May 2018
   * May 29, 2018 Learning ServiceNow: Second Edition! May 29, 2018
 * April 2018
   * Apr 17, 2018 Upgrading From Express to Enterprise: What's Missing Apr 17,
     2018
   * Apr 12, 2018 If a Genie Gave Me Three Wishes, I'd Use Them All to "Fix"
     Scope Apr 12, 2018
 * March 2018
   * Mar 19, 2018 Service Catalog "Try in Portal" button Mar 19, 2018
   * Mar 15, 2018 Video: Custom Output Transition Conditions From a Single
     Workflow (Script) Activity Mar 15, 2018
 * February 2018
   * Feb 11, 2018 We have a new book! Feb 11, 2018
 * November 2017
   * Nov 6, 2017 Requiring Attachments (& Other Miracles) in Service Portal Nov
     6, 2017
 * September 2017
   * Sep 12, 2017 Handling TimeZones in ServiceNow (TimeZoneUtil) Sep 12, 2017
 * July 2017
   * Jul 27, 2017 How to Enable DOM Manipulation in ServiceNow Service Portal
     Catalog Client Scripts Jul 27, 2017
 * June 2017
   * Jun 25, 2017 What's New in ServiceNow: Jakarta (Pt. 1) Jun 25, 2017
   * Jun 4, 2017 Powerful Scripted Text Search in ServiceNow Jun 4, 2017
 * May 2017
   * May 9, 2017 Work at Lightspeed: ServiceNow's Plan for World Domination May
     9, 2017
 * April 2017
   * Apr 9, 2017 Avoiding Pass-By-Reference Using getValue() & setValue() Apr 9,
     2017
   * Apr 4, 2017 "Learning ServiceNow" is Now Available for Purchase! Apr 4,
     2017
 * March 2017
   * Mar 12, 2017 reCAPTCHA in ServiceNow CMS/Service Portal Mar 12, 2017
 * December 2016
   * Dec 20, 2016 Pro Tip: Use updateMultiple() for Maximum Efficiency! Dec 20,
     2016
   * Dec 2, 2016 We're Writing a Book! Dec 2, 2016
 * November 2016
   * Nov 10, 2016 Chrome Extension: Load in ServiceNow Frame Nov 10, 2016
 * September 2016
   * Sep 7, 2016 Force-Include Any Record Into an Update Set Sep 7, 2016
   * Sep 1, 2016 GlideRecord Pagination - Page through your GlideRecord query
     Sep 1, 2016
 * July 2016
   * Jul 17, 2016 Granting Temporary Roles/Groups in ServiceNow Jul 17, 2016
   * Jul 15, 2016 Scripted REST APIs & Retrieving RITM Variables via SRAPI Jul
     15, 2016
 * May 2016
   * May 17, 2016 What's New in Helsinki? May 17, 2016
 * April 2016
   * Apr 27, 2016 Customizing UI16 Through CSS and System Properties Apr 27,
     2016
   * Apr 5, 2016 ServiceNow Versions: Express Vs. Enterprise Apr 5, 2016
 * March 2016
   * Mar 28, 2016 Update Set Collision Avoidance Tool: V2 Mar 28, 2016
   * Mar 18, 2016 ServiceNow: What's New in Geneva & UI16 (Pt. 2) Mar 18, 2016
 * February 2016
   * Feb 22, 2016 Reference Field Auto-Complete Attributes Feb 22, 2016
   * Feb 6, 2016 GlideRecord & GlideAjax: Client-Side Vs. Server-Side Feb 6,
     2016
   * Feb 1, 2016 Make Your Log Entries Easier to Find Feb 1, 2016
 * January 2016
   * Jan 29, 2016 A Better, One-Click Approval Jan 29, 2016
   * Jan 25, 2016 Quickly Move Changes Between Update Sets Jan 25, 2016
   * Jan 20, 2016 Customize the Reference Icon Pop-up Jan 20, 2016
   * Jan 7, 2016 ServiceNow: Geneva & UI16 - What's new Jan 7, 2016
   * Jan 4, 2016 Detect/Prevent Update Set Conflicts Before They Happen Jan 4,
     2016
 * December 2015
   * Dec 28, 2015 SN101: Boolean logic and ServiceNow's Condition Builder Dec
     28, 2015
   * Dec 17, 2015 Locate any record in any table, by sys_id in ServiceNow Dec
     17, 2015
   * Dec 16, 2015 Detecting Duplicate Records with GlideAggregate Dec 16, 2015
   * Dec 11, 2015 Array.indexOf() not working in ServiceNow - Solution! Dec 11,
     2015
   * Dec 2, 2015 Understanding Dynamic Filters & Checking a Record Against a
     Filter Using GlideFilter Dec 2, 2015
 * October 2015
   * Oct 20, 2015 Bookmarklet: Load the current page in the ServiceNow frame Oct
     20, 2015
 * August 2015
   * Aug 27, 2015 Easily Clone One User's Access to Another User Aug 27, 2015


SUBSCRIBE

Sign up with your email address to receive news and updates.

First Name
Last Name
Email Address
Sign Up


We respect your privacy.

Thank you!

Share

3 Likes
← Admin Duty Separation with a Single AccountNew Free Tool: Login Link Generator
→


COMMENTS (0)

Newest First Oldest First Newest First Most Liked Least Liked
Preview Post Comment…





©SN Pro Tips, 2023

Mastodon