Common Bottlenecks

The performance group in the COM team has identified several common bottlenecks seen in MTS-based applications. These bottlenecks and some of the experiments done to identify them are described in the MTS Performance Toolkit. In this section, we will look at some of the more common bottlenecks and how to work around them.

SQL Server Bottlenecks

Both the communication protocol and the login credentials used for SQL Server connections can be tuned to provide better performance.

Client protocols

The named pipe communication protocol is the default client protocol for SQL Server. However, you can achieve better performance and higher scalability using TCP/IP as the client protocol. To use TCP/IP, first enable TCP/IP Sockets in SQL Server using the SQL Server Setup program. Then for each system that runs components that access SQL Server, use the SQL Client Configuration Utility to specify TCP/IP Sockets as the default network on the Net Library tab. You must stop and restart SQL Server in order to make the changes take effect.

Using the system administrator login

When you use the system administrator login to access SQL Server, the master database will be written to in every transaction. However, your application probably does not use the master database. To avoid the overhead of accessing the master database, create a specific login for the application, make the default database for the new login the database the application accesses, and use this login for all data source names (DSNs) or connection strings in the application.

Data Access Bottlenecks

Accessing data is an expensive process—you can almost always find ways of improving data access performance. This section describes a few of the data access gotchas to look out for.

File DSNs

As we saw in Chapter 8, File DSNs provide an easy way for developers to define the database they need to access. However, File DSNs have very poor performance because the system must continually open and read the .DSN file to determine the database connection parameters. You can achieve substantially better performance using User DSNs, System DSNs, or inline connection strings. You can create a User DSN or a System DSN using the ODBC Data Source Administrator. These types of DSNs might make system administration a little more complex, since you can no longer simply copy a .DSN file from one place to another. However, the performance gains typically outweigh the increased administration costs. You might also choose to modify your components to directly specify the connection string rather than using a DSN when opening ADO Connection or Recordset objects. This technique reduces the administrative impact, but you might need to rebuild your components if there are any changes to the database configuration.

ADO and OLE DB performance

Early versions of ADO and OLE DB scale poorly in multi-threaded clients that use connection pooling, particularly on multiple-processor machines. This shortcoming can greatly affect the performance of MTS components and ASP pages. Microsoft Data Access Components (MDAC) 2.0 and later contain fixes for this problem—if you have the option to use MDAC 2.0 or later, you should do so. Otherwise, you can work around this problem by scaling out the application, hosting your MTS components on multiple server machines. Another workaround is to modify components that are bottlenecks to use ODBC directly.

Late-binding to data access components

As we've seen in earlier chapters, late-binding to COM components is inherently slower than vtable-binding or early-binding because late-binding must make multiple calls through the IDispatch interface for each method call in the client. In addition, there is the cost of packaging method call parameters into the data structures required by the IDispatch Invoke method. If your development tool provides support, using early-binding or vtable-binding will improve your application's performance.

Transaction Bottlenecks

Transactions can also be a source of bottlenecks. In this section, we'll look at two methods for tuning the MS DTC to enhance the performance of your application.

MS DTC log device

The MS DTC writes log records for every transaction. If the log is stored on a slow hard drive, it can create a bottleneck. You can easily improve the performance of an application that uses transactions by configuring the MS DTC to store its log on a dedicated, fast drive with a high-speed controller.

Multiple MS DTCs

By default, each machine running SQL Server and MTS will use a local MS DTC. The overhead of communicating between all the MS DTCs can impact your application's performance. You can reduce this bottleneck by configuring your system to use a single MS DTC. You can set up a remote MS DTC by stopping the MS DTC service, removing the MS DTC service from the local machine, and then using the MS DTC Control Panel applet to point to the machine on which the MS DTC is running.

Other Bottlenecks

The MTS Performance Toolkit provides a more complete list of potential bottlenecks. In this section, we'll look at just two more examples: accessing the system registry and dynamic memory allocation.

Accessing the system registry

Reading the system registry is an expensive process. If you are using the registry to store configuration information for your application, try to design your components and applications to read the information only once. Applications can then store the relevant information in local variables. Components can store the information in the Shared Property Manager (SPM) so that it is readily accessible to all objects in the process.

Dynamic memory allocation

Dynamic memory allocation is another expensive process that should be eliminated if possible. Allocation is especially expensive when heaps are created and destroyed. Microsoft Visual Basic 5.0 is particularly troublesome—it releases and re-creates project heap space on your behalf even when the heap space can be reused. To work around this problem, create a Main function in each project that takes out a reference to itself, as shown in the following code snippet:

 Dim MyServerLock as ServerLock Sub Main() if MyServerLock is Nothing then Set MyServerLock = New ServerLock End Sub