Tuning database performance comprises a major part of a DBA’s responsibilities. The usefulness of the most informative database filled with the secrets of the universe will be seriously hampered by slow response time. Users will soon get tired of waiting for the promised wealth of information and start complaining. In the case of a critical eCommerce application, customers will search for alternatives if they have to wait too long for your system to respond.
Many tuning techniques apply to all of the SQL Server instances that an organization supports. The tricks a DBA has accumulated over time can often be used to obtain the same results regardless of where the system is located. But there are instances where the differences between on-premises SQL Server implementations and SQL Azure databases may require a more refined approach to performance tuning.
Where Your Database Lives Might Matter
Microsoft’s SQL Server is one of the most popular relational databases in the world. Building on that popularity, it was an easy decision for Microsoft to unveil SQL Azure as their major cloud database offering. It is a marriage between the company’s Azure cloud environment and the technology behind SQL Server. Database professionals familiar with the terrestrial form of SQL Server will easily get up to speed with SQL Azure instances.
That being said, there are some variations in the way the databases operate that impact performance tuning. Differences in its architecture and the lack of access to an underlying hardware platform impact the ability of Azure SQL to support certain features of native SQL Server. Here are some of the differences DBAs need to keep in mind when optimizing the performance of their systems.
Communication architecture
In a traditional SQL Server implementation, communication is conducted over a LAN using the Tabular Data Stream (TDS) protocol carried over TCP/IP or HTTP. Transport security is not required and any configurable port can be used for data transmission. Contrast this with the explicit calls made through your firewall required by SQL Azure. The fact that Azure SQL accepts connects through the Internet and is handling multiple clients results in a more complex architecture.
Common language runtime (CLR)
Another difference that derives from Azure’s multi-tenant nature is the lack of support for CLR. To avoid instances of misuse by other tenants, system views, stored procedures, triggers, and user-defined functions are not available in Azure.
Data types
SQL Azure supports all the same data types as SQL Server with the exception of XML indexing and typed XML.
Table partitioning
SQL Server supports table partitioning that spreads table or index data across multiple filegroups to improve performance. SQL Azure does not support this functionality.
Clustered indexes
Clustered indexes are optional in SQL Server. When using SQL Azure, INSERT operations will fail on tables without a clustered index. This is another instance of Azure’s architecture impacting functionality. The lack of support for heap tables necessitates the use of clustered indexes.
Data manipulation language (DML)
Basic create, update, and delete (CRUD) functions are supported by DML. SQL Azure only supports a subset of the CRUD commands available in SQL Server.
These differences may impact the tuning methodology you use with your SQL Server or SQL Azure instances. Some methods will be more effective with a specific type of database.
A Versatile Tuning Tool
From the perspective of database team productivity, versatile tools that can address multiple issues are preferred over one-dimensional solutions. They allow users to develop a level of familiarity with the interface and functionality that streamlines their daily activities.
IDERA’s SQL Doctor enables your DBAs to tune SQL Server in physical, virtual, and cloud environments. Run health checks on a scheduled or as-needed basis to identify performance bottlenecks. Analyze database aspects like query plan statistics, custom queries, and wait states to find exactly where the issues are occurring so they can be resolved. Expert recommendations are provided for both on-premises and cloud SQL Server instances. It’s a valuable application for optimizing your SQL Server environment.
More information regarding the differences between advanced tuning techniques for SQL Servers based on their location can be found in this IDERA whitepaper. It’s worth checking out if you are trying to achieve optimal performance from your SQL Server environment. And what DBA doesn’t want that?