Prev
Next
Forums S3 Systems Administration

SQL Re-indexing

Sort:
You are not authorized to post a reply.
Author
Messages
Greg Moeller
Veteran Member
Posts: 1498
Veteran Member
7/19/2019 4: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
    Veteran Member
    Posts: 92
    Veteran Member
    7/29/2019 5: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
      Veteran Member
      Posts: 1498
      Veteran Member
      7/29/2019 5: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
        Veteran Member
        Posts: 92
        Veteran Member
        7/29/2019 5:34 PM
          All databases are SQL? If so, version, please?
          Greg Moeller
          Veteran Member
          Posts: 1498
          Veteran Member
          7/29/2019 5: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
            Veteran Member
            Posts: 92
            Veteran Member
            7/30/2019 2:31 AM
              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
              Veteran Member
              Posts: 1498
              Veteran Member
              7/30/2019 2:05 PM
                Lawson database is 366gb w/data and index split almost 50/50.
                Alex Tsekhansky
                Veteran Member
                Posts: 92
                Veteran Member
                8/1/2019 2:37 PM
                  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.