Optimize Application Design
The design and functionality of the application code is traditionally dependent upon the application developer. However, the role of the SQL Server developer/administrator should also include communication of key concepts to ensure the optimization and functionality of the application. During the entire application development and testing process, specific goals for application design and interface with SQL Server should attempted.
Eliminate Unnecessary Network Traffic
Through the use of stored procedures, network traffic is inherently reduced. By eliminating the number of times that an application or business object communicates with SQL Server in one transaction or a series of transactions, application performance will be noticeably improved.
Stored procedures that contain multiple statements send messages back to the calling application by default. As a result, each time that a successful SQL Statement is executed by a stored procedure, SQL Server must communicate with the application to inform it of the SQL statement success. In cases where the application does not need to evaluate the success of each statement, using the SET NOCOUNT option to ‘on’ in the declaration section of a stored procedure will disable this feature.
Use Small Result Sets
In applications where the retrieval of data to the client requires searches through large result sets, it is important for the developer to understand the ramifications of returning large recordsets from SQL Server. An application that requires large result sets from SQL Server will inherently limit user and presentation layer scalability. In addition, remote use of the application will be severely limited. Server I/O and resources as well as network traffic will also increase because of the amount of data being transmitted by SQL Server to the client. Developers should enable the presentation layer to prompt users for sufficient information to decrease the size of results set transmitted back to the client.
Set Query Timeouts and Cancellation Methods
Setting a timeout on long running queries will also increase the level of efficiency for the larger user community. Long running queries or application requests from clients can degrade performance of a SQL Server by utilizing resources and memory. Identifying long running queries (using SQL Server Profiler) and allowing the client API to cancel long running queries, server performance consistency is maintained.
Allowing the client to utilize a cancellation method for long running queries also allows for server performance consistency. However, the cancellation of a query in the middle of a business transaction does not release table locks. In addition, if a transaction is cancelled, appropriate procedures should be in place to rollback the initial portions of the transaction already committed.
Avoid Cursors
Appropriate parallels can be established between a client side ADO recordset and a SQL Server cursor. Both objects involve row level processing. SQL Server cursors pose performance related issues when employed by a distributed application. If row level processing is required, it should occur on the client. SQL Server tasks should be limited to set-oriented processing and return of data. Because SQL Server maintains row level locks on a cursor data, concurrency as well as application performance will suffer. SQL Server cursors should always be avoided, especially when a transaction requires a set update, delete or insert of data. By executing any of these statements using a cursor, each statement is executed on a row level basis, thereby increasing locks obtained, network traffic and client connection time.
Avoid Deadlocking and Blocking
Whenever a client application must execute series database operations that are inherently complex in nature, it is important to evaluate the length of a SQL Server transaction that attempts to encompass these operations. SQL Server transactions defined within a stored procedure should exist as logical groups of operations. Long running transactions that obtain locks on database objects increase the chance that blocking will occur. One instance of the client application that is executing a long running transaction can have locks on objects that another instance of the client requires. The second instance (depending on the operation) can be blocked.
Smaller more efficient transactions will decrease the occurrence of server blocking and deadlocking as well as network traffic. Application designers should enable the client to terminate a desired.
Strive for a Stateless Application
Application efficiency can also be determined by its degree of state. An application’s degree of state can be determined by the frequency and length of connections to the data tier. Applications that maintain state maintain a constant connection to the data tier. Similarly, these applications also decrease efficiency by increasing network traffic, locks on database objects and server overhead. Conversely, an application with a low degree of state connects to the database only to initiate stored procedures and receive data from them. After each database operation, a stateless application will disconnect from the data tier and only reconnect when necessary.
When application solution strategy employs a middle tier such as MTS, the middle tier handles all connectivity to the database. It is important to ensure that the middle tier strives for statelessness as well.
Use Application Roles
SQL Server 7.0 application roles allow for permissions to be defined based on the accessing application and not the user. Typically, in a Windows NT environment, an application that directly contacts the data tier through a middle tier such as IIS authenticates users based on their NT credentials. The user’s NT credentials are examined when they authenticate with IIS and again when they authenticate with SQL Server (If the application and IIS are designed not to use anonymous access). SQL Server, through the use of application roles will examine the user’s NT credentials to determine if the user can access the SQL Server. The application calls the sp_setapprole system stored procedure to activate the SQL Server application role. The user’s NT permissions based on their NT credentials are rescinded and the database permissions assigned to the application role are activated.
Application roles directly control the users actions in a database while the application role is activated. However, in an environment that employs a different middle tier that controls load balancing and database connectivity such as MTS, application roles may not be necessary. MTS controls all database connectivity and the application makes calls to MTS objects (i.e., COM, COM+ or DCOM). These MTS objects then obtain state in conjunction with the database. MTS also uses roles to determine which NT users can call certain MTS objects. As a result, application roles are unnecessary because the middle tier controls all actions in the data tier.
Eric Charran is currently working as a staff consultant for Innovative Consulting in Malvern, PA. Eric holds Microsoft Certifications in SQL Server and plans to attain the Microsoft Certified Database Professional certification, specializing in SQL Server 7.0. Eric has significant experience in planning, designing, modeling n-tier applications using SQL Server 7.0. Eric’s other professional skills encompass Database Administration, Data Warehousing Architecture, Modeling and Design, as well as Data Warehouse Transformation and Population using DTS. Contact Eric at: eric_charran@hotmail.com.
Article copyright ©2000 Eric Charran. All rights reserved. Printed with permiss
]]>