First we will configure an SQL Server instance according to common best-practice, to establish a solid basis for good performance.
Then I will walk you through just 7 generic* TSQL scripts which will help you to easily identify bottlenecks and to fix several crucial problems.
*) these scripts work (more or less) from SQL version 2005 to 2017 independent of any application (no matter if NAV, BC, AX, CRM, … you name it)!
Bring your own Laptop! You need to have a locally pre-installed up-running SQL Server installation – any SQL Version (2005 to 2017) with an application of your choice (preferably NAV version 4.0 SP3 to BC).
The SQL “Edition” should be at least STANDARD, DEVELOPER or ENTERPRISE (not EXPRESS); incl. SQL Agent, Management Studio; maybe also SQL Profiler and “Books Online”.
You probably will not be able to access any remote installation through internet etc. but you could give it a try.
Today – 1st August 2019 – I decided to proudly publish the “Magic 7” scripts to the public!
7 Generic TSQL Scripts to Troubleshoot SQL Performance Problems
The M7 bunch of scripts should help the EXPERIENCED Troubleshooter to quickly identify bottlenecks and to develop/implement appropriate solutions.
The M7 scripts are published under GNU General Public License (V3, 29 June 2007)
It’s a collection of some basic but essential stuff I use daily (among other tools) for many years to get my job – troubleshooting & performance optimization – properly done.
Admittedly, these thingies are just doing the “grunt work”, of course there’s much more to do in terms of database tuning …
These scripts are more or less permanently “under construction” and constantly expanded and enhanced, e.g. targeting new issues or dealing with new releases of SQL Server.
I want to encourage you to share your experiences with the “Magic 7” and maybe contributing to it, making it better with every mission!