During a performance optimization workshop, among others, a variety of useful utilities is installed - the "SSI Performance Toolbox" (formerly "NAV/SQL Performance Toolbox")!
The PTB is a collection of useful SQL Server stored procedures, jobs, alerts etc. and MS Excel frontend to optimize all performance-relevant aspects of the system.
Each single feature could be adjusted to fit the specific requirements!
Content and Functions:
Optimization of SQL Server Instance- and Database- Configuration
Deletion of dispensable records from SIFT tables
Analysis and Optimization of SIFT and VSIFT structures
Investigation and deletion of needless indexes
Removal of needless fields from non-clustered indexes
Re-organisation and defragmentation of indexes using optimal fillfactors
Design of indexes with optimizedselectivity
Clean-up and creation of index-statistics
Analysis of locks and blocks on resource- and process-level
SQL query optimization and Query Hinting
and much more!
Here all features could be easily configured and executed, "ad hoc" or automatic - as required.
Basically the PTB is "generic", means independent from any application, but has some special features for Dynamics NAV.
The result is an improvement of read- and write performance of the NAV/SQL Server system between 50% and 100% (maybe more!)!