RSMUS.comDon’t let the caution headline lead you to believe SQL Server 2016 and Microsoft Dynamics GP 2016 are not compatible; they are fully compatible as stated by Microsoft in the System Requirements. Log into Customer source or Partner Source for the System Requirements document for your version of Dynamics GP. To understand the caution headline, please continue reading…
The new Query Store feature in SQL Server 2016 is a great new addition that DBA’s and SQL enthusiasts have been anticipating for many years. There are many articles available (http://bfy.tw/B2l2), that explain in great detail exactly what this feature is and what it allows you to do, so I won’t go into detail here. Basically, this feature allows the forcing of specific query plans. For example, 2 queries run and 2 query plans are created. Plan #2 runs faster than Plan #1, so the query optimizer uses Plan #2 for subsequent runs of the query. Since the parameters of subsequent queries are often different based on indexing, statistics, etc., the query performance may actually execute much slower than expected. This is why the caution headline is relevant.
For certain applications, forcing a very specific query plan may be desired. But the usage of this feature for Dynamics GP, for at least one of my clients, has created a poor performing system and failing transactions. By simply deactivating the feature we were able to return to normal operation with good response times, successful transactions, and clean batch postings.
Please don’t think I am stating that SQL Server 2016 is outpacing Dynamics GP. It absolutely is not, in my opinion. However, reckless activation of new features in any system without thorough testing is never recommended for any software deployment, big or small.
Plan Forcing Limitations
Plan forcing removes the ability of the query optimizer to adapt to changing data sizes and distributions, new indexes, and other variables, and really limits it to a static execution plan. Therefore, plan forcing, if misused, can cause performance problems. Plan forcing should be used only after fully exploring other options for improving query performance, such as using fresh, accurate statistics and optimized indexes. Plan forcing should be used sparingly and only by experienced database administrators and developers who are knowledgeable about performance issues and the changing environment of their database and applications.
For more insights for working with Dynamics GP, subscribe to our Dynamics Community News publication, or contact our Dynamics experts 855-437-7201.
by Joe Erickson for RSM
