In evaluating general areas that affect performance, the most commonlyconsidered aspects are processor speed, disk I/O, and memory on the server.Although the performance of these parts of the server are crucial to properperformance, you must also consider network latency and client processingtime as factors that can also have a major impact on the overallperformance of the system.
This article discusses the latter areas and provides guidelines forevaluating what impact they may have on the server.
The following example will be used throughout the document. The steps forthe two connections perform the same update with just a small difference inthe Transact-SQL syntax.
use pubsgoselect convert(char(30), GetDate(), 9) "Start Time"go Begin transaction Go ==> Send to SQL Server and process results Update authors set au_lname = au_lname Go ==> Send to SQL Server and process resultsCommit / Rollback transaction Go ==> Send to SQL Server and process resultsselect convert(char(30), GetDate(), 9) "End Time"go
use pubsgoselect convert(char(30), GetDate(), 9) "Start Time"gobegin transactionif(0 = @@ERROR)begin update authors set au_lname = au_lname if(0 = @@ERROR) begin commit transaction end else begin rollback transaction endendgo ==> Send to SQL Server and process resultsselect convert(char(30), GetDate(), 9) "End Time"go
Network Round Trips
Connection 1 requires three trips to the SQL Server computer:
- Begin Transaction
- Commit / Rollback Transaction
Connection 2 requires a single trip to complete the update.
Both the DB-Library and the ODBC APIs support asynchronous queryprocessing. For example, DB-Library uses the dbdataready function to allowthe client to poll the completion status of the query.
In DB-Library, the dbdataready function is controlled by the DataReadySleepvalue. For additional information about the DataReadySleep registry key,please see the following article in the Microsoft Knowledge Base:
: INF: How to Change the Sleep Value Used by Dbdataready
How Sleep Times Affect the Timings
By default, the sleep value is 250 milliseconds.
Connection 1 makes three round trips to the SQL Server. By default, theclient encounters a minimum of 750 milliseconds of wait time, not countingthe time for the actual network transfer. The wait time is calculated from(250 milliseconds * 3) = 750 milliseconds.
Connection 2 makes a single trip and encounters a minimum of 250milliseconds of wait time, not counting the time for actual networktransfer.
You can change the speed of this example by a factor of three, simply bytaking advantage of the Transact-SQL syntax and removing two network roundtrips.
How Network Roundtrips Affect Other Users
Connection 1 holds a transaction open for a minimum of 500 milliseconds.After the transaction is open, it takes 500 milliseconds to complete theupdate and then commit or rollback the transaction. Database concurrencyprevents other users from accessing the records you are modifying.
Connection 2 keeps the transaction open only as long as necessary tocomplete the operation. On a 133-MHz Pentium single processor computerrunning both SQL Server and ISQL/w, the following timings are seen.
NOTE: The final Network I/O is not shown in either of the followingexamples. After the commit or rollback has completed the locks are releasedbut the final I/O is not tallied.
Begin transaction 5 milliseconds Update 20 milliseconds Commit/Rollback transaction 7 milliseconds TOTAL 32 milliseconds
Connection 2 will complete in approximately 32 milliseconds, whereasConnection 1 requires a much larger processing window and greatly extendsthe transaction latency time.
Begin transaction 5 milliseconds Network I/O 250 milliseconds Update 20 milliseconds Network I/O 250 milliseconds Commit/Rollback transaction 7 milliseconds TOTAL 532 milliseconds
As shown earlier, the network time is a simple factor of three. However,the locking impact that the example imposes on other database users is afactor of 16 (532/32 = ~16).
Now let's say that this simple example is from a remote portable computerconnecting with a 28.8 modem. In addition to the 250 milliseconds delayimposed by the dbdatareadysleep parameter, the time taken to actuallytransmit the information over the slow link is appreciable. Connection 1would affect other database users by an even larger factor, whileConnection 2 would primarily affected by the speed of the client computer.The command is sent once, processed at the SQL Server in 32 milliseconds.The only user of the system that experiences a slowdown is the remote user,which is as expected, due to slow modem.
Client Lag Time
Client lag time is the period of time that elapses while the clientprocesses the results which it received. If you again look at Connection 1,you can quickly see how this can affect the process. If an extra 10milliseconds are needed for the client to handle a result set, you can addanother 30 milliseconds to the overall transaction time and yet another 20milliseconds to the transaction latency time.
Let's switch examples again. In this case there is an inventory table froman online system. You have spent months developing and installing whatshould be the fastest online order processing system in history. The userscan search, buy, and keep a shopping cart, among other options. This is thetbllnventory table:
tblInventory iProductID int strTitle varchar(50) strDescription varchar(255) iSize int iInStock int iOnOrder int iType int
I want to purchase some cereal. However, I would like to see what isavailable. We can define cereal as type 2, so that the application issuesthe following query. In this example, the database contains 750 cereal-related items.
Select strTitle, strDescription, iSize, iInStock from tblInventory where iType = 2
SQL Server will compile and parse the query and then begin to return theresults. Shared locks are acquired on the appropriate pages. Remember thatshared locks block update, insert, and delete operations.
At the same time, because your application is used nationwide, six otherpeople are trying to place cereal orders.
SQL Server fills the first tabular data stream (TDS) packet, sends it tothe client, and then waits for the client to process the results. Duringthe time that the client is processing the results (client latency time),SQL Server continues to hold a shared page lock on the page where it wasprocessing. This shared lock can block a user who is attempting to completean order.
It seems like a simple action. Select a result set from the SQL Server andinsert the values into a list box. A 133-MHz Pentium computer can add 750items to a list box in just over one second. Disabling the list box whilefiling it takes only one-third of a second. You can significantly decreasethe client latency time by simply disabling the list box.
You might even be inclined to change the select operation to further reducethe locking. Limit the shared lock exposure by changing the query to thefollowing.
Insert * into #tblSelect from Select strTitle, strDescription, iSize, iInStock from tblInventory
The query is isolated on the SQL Server and will not start returningresults until they have been moved to the temporary table and all sharedlocks are released from the inventory table. This limits the time thatshared locks are held on the inventory table to the time required for SQLServer to move the results to tempdb. The control is again with thedatabase and not the client.
Another way to accomplish similar behavior is to make a "smart" client.Instead of filling a list box, it may be faster to load an array. However,you still have concerns about being bound by network throughput. Thetemporary table is a better solution in these situations.
As you can see, the client can play a pivotal roll in the databasethroughput. You should be especially careful when working with remote andreporting systems. The amount of time that the client takes to processresults while holding locks has the potential to impact the databasethroughput. These types of issues may be hard to see as the latency periodsmay be timings of 100 milliseconds and difficult to see with the sp_whostored procedure. Use a slow link to quickly see the behavior. Run theapplication from a RAS link and see what the overall behavior is like. Youcan also take full advantage of the SQL Trace utility to carefully profilethe application.
For additional information, please see the following articles in theMicrosoft Knowledge Base:
: INF: Result Processing for SQL Server 172117
: INF: How to Profile Transact-SQL Code in Stored Procedures and Triggers 162361
: INF: Understanding and Resolving SQL Server Blocking Problems 167610
: INF: Assessing Query Performance Degradation 48712
: INF: Handling Timeouts Correctly in DB-Library 117143
: INF: When and How to Use dbcancel() or sqlcancel()