Third-party (Application) Access to Database

Be wary of allowing users to directly access the databases (especially OLTP databases) with third-party database access tools, such as Microsoft Excel or Access. Many of these tools can wreak havoc with database performance. Here are some reasons why:

• Often these users aren't experienced with these tools, and create overly complex queries that eat up server resources. At the other extreme, their queries may not be complex enough (such as lacking effective WHERE clauses) and return thousands, if not millions, of unnecessary rows of data.

• This reporting activity can often lock rows, pages, or tables, creating user contention for data and reducing database performance.

• These tools are often file-based. This means that even if an effective query is written, the query is not performed at the server. Instead, the entire table (or multiple tables in the case of joins) must be returned to the client software where the query is actually performed. This leads to excessive server activity, and can play havoc on your network.

If users must be allowed access to the data, limit hits on the production OLTP databases by pointing them to a reporting server that is replicated or in the form of a datamart or data warehouse.

For tools to help performance tune the operating system, see Sysinternals. The site has tools to defrag the server's swap file, among many others. And best of all, most are free.