There
are probably a hundred tips that I could give you on how to speed up E1, and
choosing just ten was kind of difficult. Should I list tips only for MSSQL or
Oracle? What about generic tips? I decided to include a variety of tips that I
recommend the most often overall. They generally offer the greatest benefit
with the least amount of complexity. Now, you're going to look at a few items
on this list and say, "Wait a second, this one right here is really
difficult!" You'd be right to think that, but here's a secret: It's tough
to do any performance tuning on a database that isn't somewhat complex. That's
just databases for you.
Before
you get in to the list, I have some disclaimers. This is a list of what I think are the ten best tips. Other DBAs may
pick a different list or even say that what I've got listed here is a bunch of
crazy talk. I also have to caution you that making some of the changes in here
might be beyond your capabilities, budget, or both. Please don't do anything
without doing research and determining if it's actually right for your system.
Also, I know you understand every system is different. On one installation a
particular tip could improve performance by 50%. There might be one IT shop
that wouldn't see any improvement at all.
Finally,
here are my ten most recommended tips for improving JDE database performance.
I've included some links for reference, but I encourage you to spend some time
reading as much about each topic as possible. Your database is the only one
like it in the world, make sure your solution is the best one for you.
- General: SSDs
- Nothing speeds up a production system faster than moving to solid-state drives.
- You could just put redo/transaction logs on SSD or move all your production and system data.
- It may be expensive to move everything to SSD, but the benefit is enormous.
- Reference:
- General: Remove unused indexes
- Every index adds an extra read or write to every transaction.
- If an index isn't used very often or not at all, it's stealing performance.
- You can run a test before dropping them
- Oracle
- Make the index invisible - ALTER INDEX index_name INVISIBLE;
- Index is still maintained like any other index, but optimizer does not use it.
- Useful for making sure nothing gets worse when it's gone
- MSSQL
- Disable the index - ALTER INDEX IX_IndexName ON Schema.TableName DISABLE;
- Index is not maintained and not used by the optimizer.
- You can measure the performance benefit and be sure it doesn't break anything.
- Make sure you remove the indexes from JDE E1 specs as well, otherwise if you regenerate indexes using OMW they will come back.
- General: Don't generate indexes from OMW
- By default OMW drops and recreates all indexes on a table. You can't change this behavior.
- Index generation is an OFFLINE operation by default, meaning the table is not usable until ALL the indexes are regenerated.
- On a large, busy table this will cause your system to grind to a halt.
- Better idea: Create the indexes directly on the database. If they work for you, then create them inside JDE.
- DO NOT generate them. Simply promote them using OMW so the specs are saved.
- MSSQL: Tempdb optimization
- Many MSSQL shops have only one file, or several files of different sizes and growth rates.
- Ideally you should have (or start with) eight identical, fixed size tempdb files then adjust as necessary.
- Make sure you allocate more space/files when enabling RCSI.
- Reference:
- MSSQL: Adjust max server memory
- A common setting that installers overlook is the max server memory.
- By default it is set to its maximum value of 2147483647 MB - 2.15 Petabytes!
- MSSQL will use all of your physical memory if you don't change this value.
- This can starve the operating system and other processes of memory and bring your system to a crawl.
- A good starting rule of thumb is to set the max server memory to 90% of the total available RAM.
- Reference:
- MSSQL: Read-Committed Snapshot Isolation (RCSI)
- Are you seeing a lot of blocking on your database?
- By default, Microsoft SQL Server uses "pessimistic locking".
- This means that read operations block writes and vice-versa.
- RCSI changes it to "optimistic locking".
- The database is "optimistic" that the results of your read operation will not change.
- If it does change, the database simply re-reads the data after the write is finished.
- Since most write blocks do not change the data you're reading, implementing RCSI is almost always a huge performance improvement.
- This requires increasing the amount of Tempdb space available.
- Reference:
- Oracle: Create data files at their maximum size
- If you're using a smallfile tablespace (the default), you will have one or more data files that are 32GB or less.
- Avoid the overhead of file growth operations by creating them at full size right away.
- It also covers you when you make a mistake on file growth settings.
- When the file has to grow, it causes a performance impact.
- Reference
- Oracle: Redo log tuning
- Your redo logs should switch about five times per hour, and have enough redo log groups that each one can write out to the archive logs before the logs switches get back around.
- Longer than 15 minutes between switches can will slow down your system.
- Less than that puts you at greater risk for data loss in the event of disaster.
- When you figure out the right size you need, simply add them all to your database and force log switches until all your old logs are inactive. Once you've got that, delete the old redo logs.
- Reference
- Oracle: AIX/Linux - Huge/large pages
- There are differences in opinion about the overall performance benefit of this setting, however it does provide protection against SGA swapping on memory-dependent systems.
- The memory page size on these operating systems is quite small out of the box.
- When dealing with large Oracle SGA (System Global Area), you can improve performance by reducing the number of OS memory pages the database has to deal with.
- In AIX it's called large pages and on Linux it's huge pages.
- The size of your pages depend on the size of your SGA, the operating system, and the capability of the hardware.
- The general advice is to implement 16MB on AIX and up to 1GB on Linux.
- Create enough pages at the larger size to accommodate your entire SGA
- Reference:
- Oracle: Pin the SGA
- This goes hand-in-hand with huge/large pages.
- "Pinning" means that the entire SGA will be loaded in to the memory pages you set aside and it will never be swapped out to disk.
- If you don't pin, the SGA can get swapped out to disk due to memory exhaustion. Your database will run much more slowly if that happens.
- Swapped memory pages won't get moved back to disk even when the memory pressure subsides, so your database will continue to run poorly.
- Reference:
- See above links
Hi,
ReplyDeleteDoes JDE have a list of things to do to keep their sql server database size from growing so much? It seems like this is a common theme with many clients.
Thx,
B