The Prophet 21 database, which runs on Microsoft SQL Server, is large and highly complex.  To get the best performance from the system, administrators should make sure that they have at least some of the basic database properties configured for better performance.  People dedicate entire careers to SQL performance tuning, and this post is not designed to be the end all be all of Prophet 21 database performance.  That said, we will try to provide you with a basic framework of what you can do to get started on the path to better performance.

Once you have the basics down, you may want to start taking the next steps.  I recommend this article by David Klee of Heraflux Technologies, which will help you understand how to establish a performance baseline on your SQL server.

Prophet 21 Database Server Settings

There are settings at the hardware and the operating system level that can help you improve performance on the database.  These should be checked and updated as needed to assure that there are no unintended bottlenecks on the server (credit: David Klee, Heraflux Technologies):

1. Disable any power saving settings at the hardware’s BIOS level
2. Set CPUs to High Performance Mode
3. Enable Hyper Threading
4. Make sure multiple paths are enabled wherever available
5. Windows power options are set to High Performance

In a virtualized server environment, there are additional settings you will also want to check.  This article is a good starting point if you are using VMWare.

SQL Server Instance Properties

There are a few properties on the database instance itself that you want to look at:

Advanced Settings

• Optimize for Ad hoc Workloads:  This setting should be set to true.  What this does is tell the server instance to only store a portion of the execution plan the first time it sees a query, and then store the whole plan if it sees the query again.  This helps conserve a little bit of processing and memory.  This may not seem significant, but consider how many thousands of queries execute against the P21 database, and a little savings here and there will really add up.
• Cost Threshold for Parallelism: Every query that executes has a cost.  SQL Server calculates the cost of the query when it executes.  That cost is then compared to this setting to see if query execution should be spread across multiple cores.  The default setting is 5.  David Klee recommends starting at a setting of 25 and working up from there.  The reason is that small queries with a low cost will not usually see any benefit from being spread across multiple cores.  This setting forces small queries to stay on a single core, thereby avoiding extra processing associated with multi-threading when it is not going to help anything.
• Max Degree of Parallelism:  Related to cost threshold, this setting caps the number of cores that can be used in a single query execution.  This setting is also often called MAXDOP, in case you want to do some more reading about it.  In the instance shown above, there are 4 cores made available to the SQL server, and hyper-threading as been enabled.  As per these recommendations, MAXDOP is set to 4, which is half of the number of visible schedulers.

Memory Settings

• Maximum Memory for SQL Server: On the memory settings in the instance properties window, it is also a good idea to make sure the server’s Windows operating system has some guaranteed headroom to operate.  Typically, you will want to leave 4Gb or 10% of the total server memory, whichever is greater.

With these settings, our 200 user Prophet 21 database runs over 99.5% of queries on a single thread with what we feel is very good performance, as shown below:

SQL Server Storage Configuration

Regardless of your hardware or whether your SQL box is virtualized, setting your logical disk drives up for performance is something every administrator should do.  The last thing you want is for SQL server running the Prophet 21 database to compete for storage resources, which increases wait time and slows down performance.  Separating your drives allows I/O operations for the database and the log to take place at the same time.  For this reason, it is best to create storage volumes as follows on your SQL box (credit: David Klee, Heraflux Technologies):

• Operating System
• SQL Instance
• System Databases
• User Databases
• User Transaction Logs
• TempDB
• Backups

Key Takeaways for the Prophet 21 Database

Finally, make sure your Prophet 21 database is not being hamstrung by settings that are going to hinder performance.  The basic settings you want to check are:

• Power and Performance settings on the BIOS.
• Power settings on the operating system.
• SQL instance settings (memory cap, ad hoc workloads, cost threshold, and MAXDOPS).
• Separate your storage volumes.

Hopefully this article will help you make sure your Prophet 21 database server is performing as well as it can.  Once you have this in place there many other things you can do at a more intricate level. An example of this is index tuning, which can help you even more!

For more articles from our Prophet 21 knowledge base, click here.

[UPDATED 12/28/17]:  We have tested the P21 database extensively at compatibility level 130 and we see no reason for concern about increasing the compatibility level above 110.  A deep dive on this subject will be published in a forthcoming blog post.