It should be commonly understood that any ERP system is built to appeal generally to a wide variety of companies. Epicor is no exception to the rule.  This is a fundamental principle of how you make money selling software.  You build a product with broad appeal, and then customize it where you have to here and there.  Out of the box, Prophet 21 is just that, a stock ERP system which you then have to adapt to your needs, or adapt your processes to the system.  It should not be a surprise then that the P21 database is no different.  It ships with a stock index set based on commonalities among a broad range of customers.

Adding an index to a P21 database is not super difficult to do.  However, it does need a little care and understanding to prevent unintended consequences from happening.  As a rule of thumb, you want to minimize the number of indexes on a given table.  Each index adds overhead and cost.  The result of having too many indexes on a table are wasted disk space, wasted caching and a performance hit when modifying data.  None of these are particularly good things.  An article at sqlmag.com suggest that 15 is about the maximum number of indexes that should be on a table.  Judging by the more than 30 indexes on the oe_hdr table, I think it is fair to say that the max gets exceeded from time to time.

When Does the P21 Database Need a New Index?

Before you go adding an index to P21, you need to know where your getting the biggest performance hits.  Then you can very selectively pick where you want to work.  Ola Hallengren develops a great set of SQL maintenance tools.  The best feature about the toolkit is the price…free.  Inside the kit, there is a query that looks for missing indexes and ranks them by a metric called “Index Advantage”.  This is a very good way to see which missing index are giving you the biggest hits in performance.  I have modified this slightly to look at the score over a 21 day average period.

Why 21 days? The reason for this is that, out of the box, the Index Advantage score gets bigger as time goes on.  Eventually every missing index will look bad if the server is up for long enough.  I once asked David Klee how long the server should run before you look at index advantage.  He said it needed to be 2 to 4 weeks.  So I split the difference and went with 21 days.

Index Advantage Query

-- Missing Indexes for current database by Index Advantage  (Query 58) (Missing Indexes with advantage > 10000)
WITH idx as (
	SELECT DISTINCT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage], 
	migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
	mid.equality_columns, mid.inequality_columns, mid.included_columns,
	migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact,
	OBJECT_NAME(mid.[object_id]) AS [Table Name], p.rows AS [Table Rows]
	FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
	INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
	ON migs.group_handle = mig.index_group_handle
	INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
	ON mig.index_handle = mid.index_handle
	INNER JOIN sys.partitions AS p WITH (NOLOCK)
	ON p.[object_id] = mid.[object_id]
	WHERE mid.database_id = DB_ID() 

)


SELECT	
	CONVERT(DECIMAL(19,1), i.index_advantage / DATEDIFF(DAY, s.[sqlserver_start_time], GETDATE()) * 21) as IndexAdvantage21d
	,i.[Table Name]
	,i.last_user_seek
	,CONVERT(DECIMAL(19,4), i.avg_total_user_cost) as avg_cost
	,i.avg_user_impact
	,i.user_seeks / DATEDIFF(DAY, s.[sqlserver_start_time], GETDATE()) as avg_seeks_per_day
	,i.equality_columns
	,i.inequality_columns
	,i.included_columns

FROM idx i 
	CROSS JOIN [sys].[dm_os_sys_info] s
WHERE
	CONVERT(DECIMAL(19,1), i.index_advantage / DATEDIFF(DAY, s.[sqlserver_start_time], GETDATE()) * 21) > 10000
	AND i.last_user_seek >= DATEADD(DAY, -30, GETDATE())
ORDER BY index_advantage DESC OPTION (RECOMPILE)

Reading the Results

Once the query has been run against the P21 database, the results will look something like the below:

When I look at the results, I am looking for things that will have a serious impact on user performance.  The first two entries I know are part of a SQL agent job that runs periodically through out the day and is not visible to a user.  I also know from testing that these queries run instantly and have very few records returned on each run.  So as bad as this looks, I am not too concerned.  The entries on the oe_hdr table look bad, but with 30+ indexes on that table already, I am hesitant to put more on it.

The third entry I am a little more interested in.  The gl table only has a few indexes on it, and it probably wouldn’t hurt to add this index.  I say probably because I know the the general ledger is a write intensive table, but it gets a lot of reads too.  There are two entries here on the gl table, so I will work with the more complicated of the two, which should handle both entries.

Create a P21 Database Index

Create the index using the statement formatted as shown below.  When putting in the column names, start with the equality columns, then put in the inequality columns.  Finally, use the include statement to add in the include columns.

CREATE NONCLUSTERED INDEX [CUST_IX_gl_journal_acct_yr]
ON [dbo].[gl] ([journal_id], [account_number], [year_for_period])
INCLUDE ([period], [amount], [source])
GO

You can continue working through the list to think through each entry.  I highly recommend that you keep a list of all indexes that you create.  If you start seeing performance issues after creating an index, it is much easier to diagnose when you have a reference point to work from.  It is not a good idea to just blindly add indexes without considering how often the data is accessed or how adding the index might impact performance.

How Often Should I Review Indexes?

Reviewing indexes on your database is an iterative process.  Your business changes, which means the way you use your ERP system will evolve.  This natural evolution with affect how the P21 database is accessed over time.  Personally, I try to review this about once a quarter just to make sure there is nothing glaring.  I don’t add every index that is suggested.  If the table already has a ton of indexes or the average seeks per day is low, I might wait and see what happens.  My P21 database is big enough without me trying to needlessly help it grow.

On an ad hoc basis, if we craft a new analytics package or report that looks at data in a new way, I try to review the execution plans.  Based on this review, I will optimize the query wherever possible.  If I cannot conform the query to an existing index, I might proactively add an index where it makes sense.  This doesn’t happen often, but it does happen.

Finally, I review after an upgrade.  There are new features and tables added all of the time, and I think it is a good idea to look at how these features hit the P21 database, just to make sure nothing got missed during the development process at Epicor.

Closing Thoughts on the P21 Database

Hopefully this wasn’t too much to absorb at once.  Database indexing is a complicated subject.  Unfortunately there is not an easy way to conquer it.  People spend careers on just SQL performance.  It is not something you can boil down to “just do this and you will be fine”.  That said, it is also not an impossible feat to dig in and and do some tune ups that can help you get more performance from the P21 database.

Read more from our Prophet 21 Knowledge Base.