SQL Re-indexing

 7 Replies
 1 Subscribed to this topic
 27 Subscribed to this forum
Sort:
Author
Messages
Greg Moeller
Veteran Member Send Private Message
Posts: 1498
Veteran Member
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 Send Private Message
Posts: 92
Veteran Member
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 Send Private Message
Posts: 1498
Veteran Member
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 Send Private Message
Posts: 92
Veteran Member
All databases are SQL? If so, version, please?
Greg Moeller
Veteran Member Send Private Message
Posts: 1498
Veteran Member
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 Send Private Message
Posts: 92
Veteran Member
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 Send Private Message
Posts: 1498
Veteran Member
Lawson database is 366gb w/data and index split almost 50/50.
Alex Tsekhansky
Veteran Member Send Private Message
Posts: 92
Veteran Member
That data size may indeed be enough to consider occasional reindexing. You can also consider regular (e.g. weekly) statistics update