SQL Server Magazine offers on its Web site a Database Performance Portal (at http://csaperform.sqlmag.com ) that lets you conduct ad-hoc performance testing of client/server database connections. With this tool, you can simulate a variety of two-tier runtime scenarios that span the range of available connection types and driver implementations. You can use the results of these simulations to qualify new client, server, and network hardware and software purchases; perform systems health monitoring; conduct off-site performance analysis; and identify infrastructure bottlenecks.
For example, in a recent test project CSA Research technicians used the Database Performance Portal Workload Simulator to evaluate the performance overhead associated with various connection protocols as it applied to both transactional and non-transactional OLE-DB and ODBC connections. We developed a standardized methodology--with a common duration and data set size--then varied the connection-string parameters through the Data Links dialog box. Using the results that the Workload Simulator returned, we were able to accurately measure the effect of switching from OLE-DB to ODBC over TCP and Named Pipes connections when running in a 2-tier application environment. To our surprise, we discovered that ODBC is as much as 33 percent faster than OLE-DB in transactional, ADO-driven environments in which SQL Server 2000 is the database target.
We used a similar method to compare network infrastructure components as part of a research project for Intel Corporation. First we established the methodology. In this case, we varied the data-set size and number of concurrent workloads. Then we executed the scenarios across both our target network segments. Using results we compiled from the Workload Simulator, we were able to develop a first-of-its-kind white paper about the benefits of a pervasive Gigabit Ethernet environment. The white paper showed that when we deployed Gigabit Ethernet to the desktop, two-tier application performance improved as much as 47 percent over traditional 10/100 "Fast" Ethernet.
What makes both of the above examples work is the flexibility and configurability of the Database Performance Portal's Workload Simulator object. Because the Portal lets you read from and write to almost any ADO-accessible data device, you can easily contrast subtle changes in client, server, and network infrastructure. The Portal also gives you the ability to store session data online and compare results across multiple sessions by using the Portal site's built-in charting functions. Because of these features, many SQL Server DBAs have made the Database Performance Portal an integral part of their administrative toolkits.