Advanced SQL Server Performance Tuning
The roles of database professionals are always evolving, but their tuning skills should remain sharp. Read on for tips for troubleshooting bottlenecks with complex causes and how to remediate them for hardware, operating systems (OS) and the database itself.
On-premises SQL Server: Performance problems for a on-premises SQL Server are typically caused by disk latency.
Azure SQL Database: Improving performance for an Azure SQL Database requires frequent monitoring, especially for problems with complex causes. Azure SQL has storage limitations that can impact database performance. DBAs may be able to solve performance problems in Azure SQL by adding virtual memory.
SQL Server and Azure SQL
The following techniques apply to both SQL Server and Azure databases.
Viewing blocking with DMVs: The frequent use of DMVs is essential for improving the performance.
Queries: Query statistics can help DBAs identify better ways to structure queries.
SQL Server wait types: Operations that are waiting for resources are a common cause of poor database performance.
Summary: The most effective methods of tuning SQL Server database may depend on whether it is located on premises or on an Azure platform, but some methods are effective for both environments.
