A common question amongst those who administrate the Epicor Prophet 21 ERP system revolves around Microsoft SQL Server and the setting that controls compatibility level on the Prophet 21 database. Compatibility levels in SQL Server are primarily used to control backward compatibility for legacy applications. Essentially, if you are running SQL Server 2016, but your database is set to the SQL 2008 compatibility level, then SQL Server will treat your database as if it were SQL 2008.
Epicor’s Advice on Compatibility Level
Epicor has published some information on the matter via the solutions database. In solution 53698 (8/2/2016), they state “The compatibility mode generally does not matter when working in P21”. They further goes on to explain that they keep the compatibility mode at 90, which is SQL 2005, as a means of protecting user designed extensions to P21 from breaking due to issues stemming from backwards compatibility. There are 3 stated reasons in the solutions database that Epicor might choose to deviate from their default advice on database compatibility level.
- An Epicor design change. This would mean that they introduce code into P21 that uses functions only found in a newer version of SQL Server.
- Higher versions of SQL Server that do not support compatibility level 90. This actually has happened as of SQL Server 2014, which only supports 100 (SQL 2008) and higher.
- A user design change: a user introduces custom functionality into P21 that is only available in a higher version of SQL Server.
This information is not exactly earth shattering. For the most part, it really doesn’t seem to have a major practical effect on users or administrators of P21 on a day to day basis. That said, there has been some concern amongst the user community over the last year or so about how Prophet 21 will perform with a database compatibility level above 110 (SQL Server 2012).
SQL 2014 and the Improved Query Optimizer
The cause for concern began with the release of SQL 2014. Version 2014 brought many changes to the SQL query engine. Early adopters of this version were reporting what was perceived as significant performance degradation when the Prophet 21 database compatibility level was set above 110. As a result, it seems like system administrators are steering clear of setting the compatibility level any higher.
Today we are going to do an empirical deep dive on the concept of breaking the compatibility level 110 barrier. The SQL Server query optimizer is updated nearly every time a patch for the software is released. It is a part of the software that is constantly being worked on, thus it is not fair to assume that if one release has issues, that all successive releases will suffer equally.
Having said that, I do not know very many Prophet 21 administrators who get excited about the concept of making a major change to the database and hoping for the best. In that spirit, we will do some testing to try and identify any potential red flags that would prevent us from making the change.
The SQL Server Test Environment
To set up for these tests, we first need a separate lab environment. Playing around like this with the production database is just asking for trouble. For this test, I built a new SQL box to test with. It is nothing special, just an old host we don’t use much, and it has the following specifications:
- CPU: 4 cores of an Intel Xeon E7540 CPU (2.0 GHz)
- RAM: 32Gb (SQL instance has 14Gb minimum, 28Gb Max RAM)
- Storage: 3 drives (OS/Instance, Database File, Log File) on 10,000RPM SAS drives
- Hypervisor: VMWare
- SQL Server 2016 SP1 Developer Edition (build: 13.0.4457)
The SQL instance was configured along the principles I have outlined in a previous post.
Test 1: Simulate Opening P21 and Order Entry
A trace was run on the Prophet 21 database while the user went through commonly known functions in P21, allowing any background activity to continue occuring. The trace actions included:
- Open and log in to P21
- Open Order Entry
- Load an order
- Sweep the order
- Load another order
- Close Order Entry
- Open Item Master Inquiry
- Load 5 items into Item Master Inquiry
- Close Item Master Inquiry
- Open Customer Master Inquiry
- Load 3 customers
- Close Customer Master Inquiry
- Log out of P21
The trace was saved to a table so it could be replayed under varying conditions on the Prophet 21 database.
The trace was replayed 4 times at compatibility level 110 and 4 times at compatibility level 130. Each time the compatibility level was changed, the query execution plan cache was cleared. To clear the cache, the command DBCC FREEPROCCACHE; was executed on the SQL instance.
Under compatibility level 110, the average execution time of the replay was 16.991 seconds. The result for compatibility level 130 was 17.107 seconds.
Test 2: High Volume Queries – Short Duration
The second test on the Prophet 21 database was performed with the assistance of a tool called SQLQueryStress. A list of commonly executed queries on the production instance of P21 was pulled using the query below. In the 2 weeks that the production instance had been running, approximately 3.4 million queries had been executed. Of those, over 51% of executions were contained in the top 7 queries.
These 7 queries were put into a stored procedure, and 150 combinations of parameters for the queries were developed to makes sure the exact same query was not being run over and over again. Using SQLQueryStress, the stored procedure was executed 25 times with 25 concurrent threads. This means the procedure was run 625 times, or a total of 4,375 queries. the query execution plan cache was cleared between runs.
The average execution time for these query batches was 8.8651 seconds for compatibility level 110 and 8.7027 seconds for compatibility level 130
Test 3: High Volume Queries – Long Duration
The third test used the same 7 commonly executed queries in P21, but the procedure was executed 100 times with 200 concurrent threads using SQLQueryStress. This gave us 20,000 executions of the stored procedure, resulting in 140,000 query executions.
For this test, Perfmon data was also collected to measure CPU load as well as execution time. Again, this is the average of 4 executions of the query. Each execution took at about 4 minutes to complete. The query execution plan cache was cleared between runs.
| Compatibility 110 | Compatibility 130 | |
| CPU Usage % | 6.437% | 5.582% |
| Execution Time (sec) | 270.1398 | 238.5049 |
Test 4: Read/Write Intensive Stored Procedure
The final test executed a very read & write intensive stored procedure that collects and analyzes two years of inventory sales & and usage data. It writes this information to a table, updating it a few times along the way. The table has approximately 250,000 rows and 30 columns. I estimate that there are a total of about 500,000 INSERT operations, 1,000,000 UPDATE operations, and 2,000,000 rows of data read.
Again, Perfmon data was collected to measure CPU usage as well as execution time. Additionally, the query execution plan cache was cleared between runs.
| Compatibility 110 | Compatibility 130 | |
| CPU Usage % | 35.715 % | 38.202 % |
| Execution Time (sec) | 136.7920 | 107.2419 |
Conclusions on Prophet 21 Database Compatibility Level
I do not know if you can ever truly predict exactly what will happen when you make a major change in your Prophet 21 database configuration. There are always edge cases where something is happening that will make performance suffer. However, based on the results of the testing above, I don’t see anything that would deter me from increasing the compatibility level on P21 up to 130.
Obviously, if you have backward compatibility issues with legacy code from customizations made in the past, this could cause an issue. These things need to be refactored, as SQL Server will not support older compatibility levels forever. As always, test these things in a play environment first.
If by chance you do come across queries that just will not run well in a higher compatibility mode, there are things you can do at application, query, and database level to work through it. Microsoft provides a very granular description of this kind of trouble shooting in this article about cardinality estimation. If you do need to apply a query level patch, it is likely this could be achieved using a pre-SQL hook in Prophet 21.
Finally, If you are going to make the change, I recommend that you clear the execution plan cache to let SQL Server rebuild all of the execution plans using the latest version of the query optimizer. This will prevent any hiccups from using old execution plans with a newer optimizer.
