Whitepaper : Advanced SQL Server Performance Tuning
The roles of database professionals are always evolving. However, their tuning skills should remain sharp. Tuning proficiency is still one of the critical requirements for both database administrators and developers, whether the database is on-premises or delivered via a platform such as infrastructure-as-a-service or platform-as-a-service.
Fixing a slow query is often a simple matter of adding an index. However, solving this problem may require other methods. A database administrator may also need other tools such as indexed views, plan guides, and the query store to solve unusual performance issues. For example, a large number of tenants with wildly different amounts of data impacts performance in a way that needs a specialized approach to solve. Database administrators cannot always fix these problems. However, they can often find temporary solutions that will last until a long-term fix can be implemented. You may not use these tips every day. However, you should find opportunities to improve SQL Server performance with them.
This whitepaper discusses advanced database performance tuning for both on-premises SQL Server and Azure SQL Database. It also covers tips and techniques for troubleshooting bottlenecks with complex causes and how to remediate them for hardware, operating systems, and the database itself. This includes disk latency, storage limitations, memory considerations, blocking, problematic queries, and wait times.