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.