Designing Applications for Performance

As mentioned at the beginning of this chapter, poor application design is one of the top reasons applications run slowly and don't scale well. Application designers and architects should think about performance objectives early in the application life cycle. Designers should proactively make key design choices to maximize performance and scalability, while balancing other quality-of-service (QoS) considerations, such as availability, manageability, integrity, and security.

The following sections present a brief overview of new developer features introduced in SQL Server 2005 to create applications that perform and scale well.

Service Broker and Asynchronous Messaging

With just a few lines of T-SQL code, you can now build triggers that execute asynchronously, execute stored procedures in parallel, scale out with no or minimal change in an application, defer part of the processing to spread out the load evenly or for batch processing, increase the availability by distributing or splitting the server-side processing, and so on. The platform, or framework, that supports this inside SQL Server 2005 is Service Broker.

If you want to build scalable database applications, you should seriously consider using Service Broker in your applications. You can use T-SQL statements to create Service Broker queues, services, and routes; send and receive messages; and so on. Service Broker, which is built into the SQL Server 2005 database engine, provides the infrastructure required for queuing and communicating messages asynchronously, reliably, and securely. Refer to Chapter 14 for complete details on Service Broker.

.NET Runtime Integration

T-SQL is an interpreted language, and it is not well suited for complex computational and procedural tasks. The SQL Server 2005 database engine can host the .NET runtime to facilitate running compiled .NET code inside SQL Server. This feature is called the .NET Common Language Runtime (CLR) integration, or SQLCLR, and it can be used to write stored procedures, triggers, and user-defined functions, types, and aggregates, using any .NET programming language. You can use SQLCLR to implement computational and procedural tasks that may yield better performance than their T-SQL counterparts. SQLCLR is discussed in Chapter 11, "SQL Server 2005 and .NET Integration."

T-SQL Enhancements

The previous section describes that you can use .NET to extend T-SQL. If something is not possible using T-SQL, you can implement it by using .NET (as a stored procedure, a function, a trigger, a type, or an aggregate) and execute it inside SQL Server. For data access, TSQL is still the premier and most optimal language. SQL Server 2005 introduces enhancements to the T-SQL languages that database professionals can leverage to write efficient and manageable queries. Common table expressions, PIVOT/UNPIVOT operators, and ranking functions are some examples of T-SQL enhancements. Refer to Chapter 6, "Transact-SQL Enhancements," for a detailed description of new T-SQL features in SQL Server 2005.

Multiple Active Result Sets

Before SQL Server 2005, a connection supported only a single request at any time. If there was a pending request or an open result set (that is, a fire hose cursor), the connection could not execute another statement until the first one finished. SQL Server 2005, along with the new SQL Native Client data access API, or .NET 2.0 SqlClient provider, changes this by introducing a feature called Multiple Active Result Sets (MARS). As the name suggests, MARS provides the ability to have more than one pending request under a given SQL Server connection. MARS involves enabling the interleaved execution of multiple requests within a single connection. It allows a batch to run and, within the execution, allows other requests to execute. Note, however, that MARS is defined in terms of interleaving, not in terms of parallel execution. In many situations, MARS may provide an alternative to server-side cursors and provide performance improvements.

Query Notifications

Query notifications are a new functionality introduced in SQL Server 2005 that allow an application to request a notification from SQL Server when the results of a query change. The most common scenario where this can be used is while caching a result set. Instead of polling the database periodically or refreshing the cache periodically, an application can enlist with SQL Server 2005 to be notified whenever there is a change in the cached result set. This can aid in reducing the number of round trips to the database and improving the overall system throughput. The database engine uses Service Broker to deliver query notification messages. Therefore, Service Broker must be enabled in the database where the application requests the subscription.

HTTP SOAP Support

The SQLXML add-on to SQL Server 2000 allows you to invoke stored procedures over HTTP as web service methods. However, this requires the Microsoft Internet Information Services (IIS) web server and an ISAPI extension DLL. This layering can have some impact on performance when web service methods are invoked.

HTTP SOAP web services support is now built into the SQL Server 2005 database engine, eliminating the need for any web server or any ISAPI extension DLL, resulting in better performance. The HTTP endpoints can be created, and web service methods can be mapped to stored procedures, by using the CREATE ENDPOINT T-SQL statement. Refer to Chapter 10 for more details on this.