Performance Tuning SQL Server ODBC

While SQL Server application roles offer many benefits, they also cause a performance problem. Application roles cannot take advantage of ODBC connection pooling. Because of this, you may want to reconsider using application roles in your applications. [7.0, 2000, 2005] Updated 2-20-2006

*****

When you create a DSN for a SQL Server connection using the DSN Wizard, there is an option called "Save long running queries to the log file." Along with this option is the setting, "Long query time (milliseconds)." Both of these options can be used together to help you identify slow running queries in SQL Server applications that use a DSN for connecting to SQL Server. When you select this option and specify what you want as the long query time, those queries that exceed the time you enter will be logged to a file that you specify by name.

While this feature can be handy in some occasions, long running queries can also be identified using the SQL Server Profiler. One advantage of this feature over the Profiler is that is will generally take up less overhead when it runs than Profiler will. On the other hand, Profiler provides more detailed data than this feature. [6.5, 7.0, 2000, 2005] Updated 2-20-2006