The SQL Server ODBC driver can log information useful for the DBA to performance tune SQL Server applications. First, it has the ability to log information in a file about any query that does not get any response from SQL Server within a set amount of time. Second, the ODBC driver has the ability to record performance statistics in a tab-delimited file that can be analyzed in a spreadsheet. Logging can be turned on programmatically or by configuring the DSN connection used to access SQL Server. [6.5, 7.0, 2000, 2005] Updated 2-20-2006
*****
The SQL Server ODBC drivers are automatically installed when SQL Server is installed. These drivers are periodically updated, often with little fanfare. Like SQL Server service pack updates, you will generally want to update to the latest drivers as they often include performance improvements.
To upgrade the SQL Server ODBC drivers, you will need to install what Microsoft calls MDAC (Microsoft Data Access Components). The latest MDAC can be downloaded from Microsoft at www.microsoft.com/data. [6.5, 7.0, 2000, 2005] Updated 2-20-2006
*****
When making your connection to SQL Server, choose DSN-less connections for the fastest connection. If you do need to use a DSN, select System DSNs over File DSNs, because they are faster when making connections. [6.5, 7.0, 2000, 2005] Updated 2-20-2006
*****
If you have a choice between using ODBC or OLE DB to access your SQL Server database, choose OLE DB, as it is generally faster. [7.0, 2000, 2005] Updated 2-20-2006
*****
When creating a DSN using the ODBC Database Wizard (found in the Control Panel as the ODBC Data Sources), you have the opportunity to “Change the default database to,” which allows you to select a default database that the connection using this DSN will use when it connects to SQL Server.
If you don’t select this option, the default database used for this connection will be the default database specified for the Login ID in SQL Server. For best performance, do not select the option “Change the default database to,” instead leave it blank. If you leave it blank, SQL Server will make the connection faster than if you do elect to change the default. [6.5, 7.0, 2000, 2005] Updated 2-20-2006
*****
Try to take advantage of “pooling” to reduce SQL Server connection overhead. Pooling is the term used to refer to the process where a client application can use a preexisting connection from a pool of available connections to connect to SQL Server without having to establish a new connection each time a new connection is required. This reduces SQL Server’s overhead and speeds up connections to SQL Server.
Microsoft offers two types of pooling. Connection pooling is available through ODBC and can be configured by using the ODBC Data Source Administrator, the registry, or the calling application. Resource pooling is available through OLE DB, and can be configured through the application’s connection string, the OLE DB API, or the registry.
Either connection pooling or resource pooling can be run for the same connection. Both pooling connections cannot be used for the same connection. For maximum performance, you will almost always want to take advantage of one or the other pooling methods when connecting to SQL Server. [6.5, 7.0, 2000, 2005] Updated 2-20-2006
*****
Don’t use ODBC connection pooling and temporary stored procedures at the same time, or your SQL Server will experience a performance hit. When you use a DSN to make a connection from your application to SQL Server, the MDAC driver, by default, converts any dynamic Transact-SQL from your application to temporary stored procedures in SQL Server. The theory behind this is that if the application resends the same Transact-SQL to SQL Server more than twice, that it will save the overhead of SQL Server having to re-parse and re-compile it each time. Assuming that the Transact-SQL is run more than twice from the client application, this is a good idea. This feature is configurable from the ODBC Database Wizard when creating or modifying a DSN.
Connection pooling is another option that can be configured using the ODBC Database Wizard when creating or modifying a DSN. It is also on by default, and what it does is to pool database connections from the application. Database connection pooling allows connections to be reused, which reduces the overhead of making and breaking database connections.
The problem is, if both of these options are on — which is often the case in DSNs — your SQL Server can take a performance hit. Here’s what can happen. When dynamic Transact-SQL is converted into a temporary stored procedure by the MDAC driver, the temporary stored procedure is stored in the Tempdb database. When connection pooling is not enabled, and when the connection between the client application and SQL Server is ended, any temporary stored procedures created during the connection are deleted. But, when connection pooling is enabled, things work differently. When a database connection is ended by the client application, it is not ended at SQL Server. SQL Server still thinks the connection is still open, even though the client application does not. This means the temporary stored procedures created during the connection are not deleted. With a busy client application that often starts and stops database connections, the Tempdb database can fill up with temporary stored procedures, putting unnecessary overhead on SQL Server.
How do you deal with this problem? You have two options when using the ODBC Database Wizard. One option is to leave database connection pooling enabled and turn off temporary stored procedures (pooling improves performance more than temporary stored procedures). Another option is to leave temporary stored procedures on, but also select this ODBC Database Wizard option, and that is “When you disconnect and as appropriate while you are connected.” If you select this option, the MDAC driver will drop the connection when the object that created the connection goes out of scope. This will drop the stored procedures in the Tempdb database on a timelier basis, but it will also somewhat reduce the full benefit of connection pooling. [6.5, 7.0, 2000, 2005] Updated 2-20-2006