If you missed GPUG Amplify 2017 in Anaheim, don’t worry. We have got some of the great tips and tricks from the event including SQL Server for Dynamics GP Tips from Microsoft MVP and GPUG All Start John Lowther.
SQL Server for Dynamics GP Tip #1 – SQL Views vs. SQL Stored Procedures
When trying to decide to create a view or a stored procedure think about this; a friend once said that the difference between a view and a stored procedure is like the difference between a car and a boat, the car belongs on the ground, the boat belongs in the water. A little over simplified, but the analogy works quite well. While there are somethings you can do in one that you cannot do in the other for the most part think about how you want to use it and follow this simple rule of thumb. If you want to select things from it, then use a view. If you want to pass it some parameters and just have it return results, then use a stored procedure.
SQL Server for Dynamics GP Tip #2 – Using SQL Profiler
When using SQL Profiler, please keep in mind the following things. SQL Profiler can tell you absolutely everything that the SQL Server is doing, at a price! Keep the list of events and columns to the smallest set possible that gives you only the information you need. Start with a very small set and then add events as you need to get more information. Also, once you have the trace setup, but before you start it, get to the just before the point in your application to start the trace and then stop the trace as quickly as you can once you get past the point you are investigating. Running Profiler for a long time or selecting all events and all columns will not only make the results harder to understand, but can and will bring your SQL Server to a major slowdown. Also, when setting up the trace send the results to a table. Then you can select subsets of the results to help narrow down exactly what you are researching.
SQL Server for Dynamics GP Tip #3 – Turn Off Auto Shrink
If I had to pick just one Database settings to watch out for when it comes to performance, it would have to be Auto Shrink. Which should always be set to False. The problem with Auto Shrink is that every time SQL gets a chance, and sees some free space in the database files (MDF or LDF) it will shrink them. But as soon as something happens, a new record is inserted, updated, or deleted, SQL must first grow the files, but then there is some free space so now it shrinks the file again, this gets the server into a never-ending cycle of growing and shrinking the files, not only causing performance problems, but on none SSD drives also causes major fragmentation of the files on the disk.
Want more SQL Server for Dynamics GP tips and more tricks? Click Here
