PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 08/01/2019 10:37 AM by  Alex Tsekhansky
SQL Re-indexing
 7 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Greg Moeller
Private
Private
Veteran Member
(3984 points)
Veteran Member
Posts:1418


Send Message:

--
07/19/2019 12:56 PM
    Kind of a strange one here:

    Our DBA reports he sees no re-indexing going on for our Lawson database.

    Looking for recommendations on 1.  Should we?  2.  If so, how frequently.

    Any input is appreciated.

    -Greg

    Alex Tsekhansky
    Private
    Private
    Veteran Member
    (267 points)
    Veteran Member
    Posts:89


    Send Message:

    --
    07/29/2019 1:20 PM
    Depends on a database really, as well as the data usage.

    Installations that use Payroll usually have rapid pace of changes. For Oracle and DB2 installations that have it I suggest evaluate fragmentation and row chaining, and if these are anywhere in a medium range, I usually recommend weekly or once-in-two-weeks reindexing. Same may apply to companies that have rapid changes related to GL, especially data coming over the interfaces. Db2 can actually be configured to auto-reindex things under certain circumstances (as well as Oracle via OEM, though it's a bit more complicated). Reindexing in MS SQL is generally less important (because of the way how it stores data and operates), but for adequate performance you would need more RAM with the same amount of data in a database. Also MS SQL is more affected by the network latency, and is much less tolerant to the momentary network interruptions (it's not related to indexes, but it's related to other items related to the SQL setup, such as enabling in-flight encryption, or VMWARE vmotion).

    In other words, if you provide more details about your environment, I could give you a better answer
    Greg Moeller
    Private
    Private
    Veteran Member
    (3984 points)
    Veteran Member
    Posts:1418


    Send Message:

    --
    07/29/2019 1:33 PM
    Surely: We are Infor/Lawson v10 on-premise with HR/Payroll/Finance/Materials
    We have API Healthcare as our timekeeping vendor with an interface to Lawson at least weekly for PR530.
    We have a lot of different HR/Materials related interfaces as well.
    We currently have 64 GB RAM dedicated to all of our Lawson databases... including Landmark, Lawson, LBI, MSCM, TF10 running in VMWare on Windows 2012 R2 servers.
    We only have IPA installed in Landmark so far.
    Alex Tsekhansky
    Private
    Private
    Veteran Member
    (267 points)
    Veteran Member
    Posts:89


    Send Message:

    --
    07/29/2019 1:34 PM
    All databases are SQL? If so, version, please?
    Greg Moeller
    Private
    Private
    Veteran Member
    (3984 points)
    Veteran Member
    Posts:1418


    Send Message:

    --
    07/29/2019 1:45 PM
    Yes, all databases are sql running on SQL Server 2014 Enterprise (for production)-- 12.0.4100.1
    2014 Standard on test.
    16 processors (on production), less on test
    Alex Tsekhansky
    Private
    Private
    Veteran Member
    (267 points)
    Veteran Member
    Posts:89


    Send Message:

    --
    07/29/2019 10:31 PM
    If your databases are below 100Gb each (just datafiles, not logs), it's VERY likely you do not need any reindexing. Are they bigger than that?
    Greg Moeller
    Private
    Private
    Veteran Member
    (3984 points)
    Veteran Member
    Posts:1418


    Send Message:

    --
    07/30/2019 10:05 AM
    Lawson database is 366gb w/data and index split almost 50/50.
    Alex Tsekhansky
    Private
    Private
    Veteran Member
    (267 points)
    Veteran Member
    Posts:89


    Send Message:

    --
    08/01/2019 10:37 AM
    That data size may indeed be enough to consider occasional reindexing. You can also consider regular (e.g. weekly) statistics update
    You are not authorized to post a reply.