Modifying and/or deleting indexes on various tables

Discussions relating to plugin development, and the Jiwa API.

Modifying and/or deleting indexes on various tables

Postby DannyC » Fri Jan 24, 2020 1:10 pm

Getting back to that squeaky wheel with slow Jiwa.
I've been doing some monitoring over the past few weeks on their SQL Server. One of the diagnostic tools I'm using has reported some tables as heavily indexed. This has a few issues particularly with INSERTs and UPDATEs. Without going into the nitty gritty of negative performance with too many indexes, as no doubt you're just as knowledgeable (!) about their pros and cons, I just wanted to touch base on the issue via this thread.

One of the slow areas of Jiwa is saving sales orders. Coincidentally, the tool I'm using reports SO_Main as being over-indexed.
I can see that this table has 16 indexes, yet the JiwaDemo database has 14. I think I'll drop those indexes seeing as they're not standard. They are non clustered on SO_Main.CreditNote. Another index on SO_Main.WholesaleInvoice.

But the other indexes...what would your feeling be if I dropped a few indexes and maybe just combined them into a single, wider index with a few columns?
Just as an example only, dropping the single indexes for InvoiceNo, OrderNo, SOReference and just having one index thus:
Code: Select all
CREATE NONCLUSTERED INDEX [IX_MyNewIndexName] ON dbo.SO_Main
(InvoiceID)  INCLUDE (InvoiceNo, OrderNo, SOReference)


Another table: GL_Ledger.
You have single indexes on each 6 segments. Seeing as this client only goes to 3 segments do you have any reservations about dropping the indexes on Seg4, Seg5, Seg6?
What about dropping the indexes on Seg1, Seg2, Seg3 and just combining into a wider index with all 3 segments to:
Code: Select all
CREATE NONCLUSTERED INDEX [IX_MyIndexName] ON dbo.GL_Ledger (GLLedgerID) INCLUDE (Seg1, Seg2, Seg3)

or
Code: Select all
CREATE NONCLUSTERED INDEX [IX_MyIndexName] ON dbo.GL_Ledger (GLLedgerID, Seg1, Seg2,Seg3)


Are those segments really used extensively (or at all) on your code to perform queries?

Have you done much research on performance gains over large databases with big transactional load (such as a file watcher with 500 files to import as sales orders, with a few other users already in the sales order module doing manual entry and saving of orders) using various indexes?

Cheers
User avatar
DannyC
Senpai
Senpai
 
Posts: 635
Joined: Fri Mar 22, 2013 12:23 pm
Topics Solved: 29

Re: Modifying and/or deleting indexes on various tables

Postby Mike.Sheen » Tue Jan 28, 2020 11:03 am

The indexes we currently have are the result of simple reactionary changes over time and really do need a good looking at.

For instance, just recently we added to SO_Main the index on the CreditNote column (one you noted was not standard and want to remove from your customer database) as part of DEV-7737.

In this case, the customer reported to us the Transaction History tab on Inventory Maintenance was taking up to a minute for certain parts. After running profile traces on their data, and examining the query plans being used for the slow queries we honed in on their cause - missing index on SO_Main.CreditNote, so without thinking too hard about it we added it to be part of the standard index schema for 7.2.2 onwards.

This is how many of our indexes came to be.

DannyC wrote:what would your feeling be if I dropped a few indexes and maybe just combined them into a single, wider index with a few columns?


DannyC wrote:do you have any reservations about dropping the indexes on Seg4, Seg5, Seg6?


DannyC wrote:What about dropping the indexes on Seg1, Seg2, Seg3 and just combining into a wider index with all 3 segments to:
Code: Select all
CREATE NONCLUSTERED INDEX [IX_MyIndexName] ON dbo.GL_Ledger (GLLedgerID) INCLUDE (Seg1, Seg2, Seg3)

or
Code: Select all
CREATE NONCLUSTERED INDEX [IX_MyIndexName] ON dbo.GL_Ledger (GLLedgerID, Seg1, Seg2,Seg3)


I cannot provide any sort of guarantee that making those changes won't impact performance somewhere or at sometime negatively - but I don't think you have need to be timid regarding index changes - it's pretty easy to put things back to how they were if it doesn't work out - and we don't ever depend on certain indexes being present, so it's not like it will break the application.

If you can tune the indexes to obtain greater performance for that database, then by all means do so.

DannyC wrote:Are those segments really used extensively (or at all) on your code to perform queries?


I'll go have a look and see if I can find anywhere which uses those columns in a query. EDIT: Reports, such as the P&L report (backed by the stored procedure usp_Jiwa_GL_ProfitAndLoss) use those table columns, and the GL Maintenance form used them (but only to read and save) - but nothing else uses them.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Modifying and/or deleting indexes on various tables

Postby pricerc » Tue Jan 28, 2020 11:40 am

There are many things to be said about performance in SQL. And indexes in particular.

The most important being that every implementation of JIWA will have different indexing requirements that will change over time.

Out of practical necessity, JIWA (and all other ISVs) put some 'reasonable default' indexes in the database that are typically satisfactory, but cannot be guaranteed to be correct for every implementation, or for all time during an implementation, since requirements can change over time. So index tuning is something that every active database will likely need at some point.

I've tuned a fair few in my time, so I feel compelled to share.

I'm going to only talk about clustered indexes, since that's where I've found some of the biggest gains when dealing with JIWA data.

I do things based on tips and tricks learnt on-the-job with a lot of help from SqlServerCentral.com (a fantastic resource for learning all things SQL).

My "Clustered index rules of thumb" are:
1) a clustered index should be on the field(or fields) that you are most likely to want to seek or sort data on and/or
2) a clustered index should use an ever-increasing key (not necessarily the primary key).

Often, they're the same thing, and that's a good thing. Because the clustered index determines the physical order of records in the database table, the right clustered index means that:
1) SQL needs to do less work when running a query,
2) you get less locking, and
3) you get better "add record" performance (because of reduced page splits).

My process:

Firstly. When I suspect index performance problems, I start with doing a "SCRIPT INDEX as DROP and CREATE" in SQL Management Studio. I save this script, which then gives me a backup of the indexes so that I can restore them later, and it also gives me a way of assessing all the indexes (easier if you put all of them in one script).

I find that most systems have two common problems:

1) poor choice of clustered index. This causes performance problems because the data isn't organised efficiently on the disk (even on SSDs, this matters).
2) overlapping indexes (multiple indexes sharing similar column sets). This causes performance problems because of volume of updates required, and 'challenges the SQL optimizer'.

Having saved my 'CREATE' script, I will often then modify and/or delete indexes based on:
a) an understanding of the data and how it is used in the user interface, and the reports that my users care about, and
b) the results of diagnostic tools (like SQL's own performance analyzer, a.k.a. DTA).

Overlapping indexes need to be dealt with using analysis tools, but clustered indexes usually have some basic things that can be addressed without a lot of analysis, which is where I'm going with this.

By default, most JIWA tables are clustered on a random RECID (char(n) or uniqueidentifier, depending on the vintage of the table). Once they get hit hard enough, or they're big enough, it can become a problem and it becomes worth reviewing them (while there are probably many tables that would benefit from tuning, the tuning work has to be weighed up against the return on the investment).

In my experience, the JIWA tables that cause problems usually have a good alternative field that can be used instead.

For example, SO_Main has an InvoiceNo that is (usually) increased for each new sales order. It also already has a unique index on the InvoiceNo column. So this will (typically) make a good clustered index. So I swap the 'clustered' attribute on the indexes for InvoiceID and InvoiceNo (keeping InvoiceID as the primary key):

Code: Select all
   DROP INDEX [SO_MainInvoiceNo] ON [dbo].[SO_Main];

   ALTER TABLE [dbo].[SO_Main] DROP CONSTRAINT [PK_SO_Main] WITH ( ONLINE = OFF );

   CREATE UNIQUE CLUSTERED INDEX [SO_MainInvoiceNo] ON [dbo].[SO_Main]([InvoiceNo] ASC);

   ALTER TABLE [dbo].[SO_Main] ADD  CONSTRAINT [PK_SO_Main] PRIMARY KEY NONCLUSTERED([InvoiceID] ASC)


Note that the new clustered index is created *before* the new primary key. This is because creating a new clustered index will physically re-arrange the table on disk (don't be doing this during a busy time on your database), and will then rebuild all the other indexes on the table.

Some tables are a bit trickier, having an absence of an obvious auto-increasing field like an order number. However, you can usually find an alternative which is at least better than completely random.

E.g. 1) SO_History. For SO_History, I would consider the combination of InvoiceID + HistoryNo (I suspect this should probably be unique as well, but that's not how it is in my latest JIWA demo DB).

E.g. 2) SO_Lines. I would go for SalesOrderHistoryID + LineNo (which is also a unique index by default - IX_SO_Lines_InvoiceHistoryID_LineNum):

Code: Select all
   DROP INDEX [SO_LinesPrimaryKey] ON [dbo].[SO_Lines];

   DROP INDEX [IX_SO_Lines_InvoiceHistoryID_LineNum] ON [dbo].[SO_Lines];

   CREATE UNIQUE CLUSTERED INDEX [IX_SO_Lines_InvoiceHistoryID_LineNum] ON [dbo].[SO_Lines] ([InvoiceHistoryID] ASC, [LineNum] ASC);

   CREATE UNIQUE NONCLUSTERED INDEX [SO_LinesPrimaryKey] ON [dbo].[SO_Lines] ([InvoiceLineID] ASC);

(note that the 'primary key' index isn't defined as a *primary key constraint*, but that's ok if it is at least a unique index)

For SO_Lines, clustering on InvoiceHistoryID also means that all the lines for your order are likely to be in the same SQL data block, making them more efficient to fetch and less likely to cause locking problems.


Notes:
If you're using SSDs, many index performance problems will be dramatically mitigated by the performance of an SSD, but you nonetheless will be generating unnecessary I/O against the database, which will increase the wear-and-tear on your device, and reduce the performance of your system. Locking problems will also still apply on SSDs when indexes are wrong.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Modifying and/or deleting indexes on various tables

Postby DannyC » Tue Jan 28, 2020 2:47 pm

That's awesome feedback Ryan. You have basically just reinforced my thoughts. I've just never had the guts to change the indexes in a live database but with the current complaints from the client (yes their db is on an SSD, and so is tempdb) I might just go ahead and make those changes. As you & Mike say, rolling back to what the indexes used to be is very easy.

cheers
User avatar
DannyC
Senpai
Senpai
 
Posts: 635
Joined: Fri Mar 22, 2013 12:23 pm
Topics Solved: 29

Re: Modifying and/or deleting indexes on various tables

Postby pricerc » Tue Jan 28, 2020 5:22 pm

DannyC wrote:That's awesome feedback Ryan. You have basically just reinforced my thoughts. I've just never had the guts to change the indexes in a live database but with the current complaints from the client (yes their db is on an SSD, and so is tempdb) I might just go ahead and make those changes. As you & Mike say, rolling back to what the indexes used to be is very easy.

cheers


You're welcome.

Of course there is a caveat that, while should arguably "go without saying", I'll say it anyway: The JIWA database upgrade scripts *may* try and do something with an index and/or primary key and/or constraint that you've modified, so make sure your changes are documented so that the necessary bits can be put back if the upgrade script stalls somewhere.

Another possibility, if you're not keen on DROPping indexes, is to just DISABLE them. This is no good for your clustered index and primary key, but for all other indexes will remove the performance impact without removing the index definition. (I think there's a gotcha with disabling indexes where they can get re-enabled when you don't expect it, but I've never had that problem).
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Modifying and/or deleting indexes on various tables

Postby Mike.Sheen » Sun Feb 02, 2020 1:29 pm

pricerc wrote:Out of practical necessity, JIWA (and all other ISVs) put some 'reasonable default' indexes in the database that are typically satisfactory, but cannot be guaranteed to be correct for every implementation, or for all time during an implementation, since requirements can change over time. So index tuning is something that every active database will likely need at some point.


Very well put!

On this, Friday last week I helped a customer get their P&L report generation from 10 minutes to 11 seconds just by adding 4 covering indexes on the GL_Ledger and GL_Budget* tables.

How I went about identifying and rectifying the issue was pretty simple - first profile traced to capture the query the report was issuing. As this was a live database, profiler surfaces a lot of noise from other users, so in order to eliminate that noise and have it only capture the queries I was interested in, I filtered the profiler trace on ClientProcessID. Discovering the ClientProcessID was a simple matter of running profiler, then in Jiwa doing something fairly distinct (like opening a form) and pausing the trace and then finding in profiler queries I know were only related to my activity and that allowed me to isolate my ClientProcessID. Then, start a new profile trace filtering on only my ClientProcessID.

Note to self: Add to the Jiwa About dialog the SQL Client Process ID to make this easier! Added as DEV-8040

Having now isolated profiler to show only the queries I was interested in, when running the report profiler only showed the queries I was interested in. In this case, it was a single stored procedure executed. I copy-pasted that query into Management Studio and ran it to confirm the same performance issue - indeed it performed poorly.

Next I generate the stored procedure code from Management studio and refactor it to be just a query, changing the parameters to be DECLARE statements and setting their values to the respective values captured by the profiler trace.

Then I turned on the Show Execution Plan and ran the query. Once finished it shows a breakdown of the various queries and their contribution toward the total execution time. Starting with the slowest query, I see that the Execution Plan has recommended an index for me already - after examining the recommended index to make sure it was sane, created the index, then repeated for the other occurrences of slow queries with index recommendations.

And now their P&L report generates in 11 seconds.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Modifying and/or deleting indexes on various tables

Postby Mike.Sheen » Sun Feb 02, 2020 4:33 pm

Whilst this is not related to indexes, it is related to SQL Server performance.

You might see this SQL Server setting "Boost SQL Server Priority" and the uninitiated might be tempted to turn it on - DON'T.
SQLPriorityBoost.png
SQLPriorityBoost.png (38.22 KiB) Viewed 500 times


Use your preferred search engine with the term "Boost SQL Server Priority" to find out why.
SQLPriorityBoost-Expectations_vs_reality.png
SQLPriorityBoost-Expectations_vs_reality.png (405.83 KiB) Viewed 500 times

Credit: https://www.brentozar.com/blitz/priority-boost/
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755


Return to Technical and or Programming

Who is online

Users browsing this forum: No registered users and 5 guests

cron