SQL Server Development

Performance Tuning SQL Server ODBC

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 […]

Data Type Performance Tuning Tips for Microsoft SQL Server

Always specify the narrowest columns you can. The narrower the column, the less amount of data SQL Server has to store, and the faster SQL Server is able to read and write data. In addition, if any sorts need to be performed on the column, the narrower the column, the faster the sort will be. […]

Performance Tuning Tips for Using Microsoft Access and SQL Server Together

If you are interested in the fastest performance, don’t use Access as a front-end to a SQL Server database. While Access is relatively easy to learn and fast to develop in, its performance is poor when compared to other front-end options. But if you like to develop in Access, or don’t have any choice, then […]

Performance Tuning SQL Server OLE DB

The SQL Server OLE DB 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 OLE DB drivers, you will […]

SQL Server XML Performance Tips

When using the FOR XML clause in your Transact-SQL applications, don’t include the XMLDATA option. The XMLDATA option returns additional XML schema data that generally is not needed. Because of this, using this option adds extra overhead to your server and network connection, reducing performance. [2000, 2005, 2008] Updated 1-29-2009 ***** The OPENXML function in […]

Performance Tuning Tips for Using Microsoft Visual FoxPro and SQL Server Together

When using Visual FoxPro as a client to access SQL Server data, always do what you can to reduce network traffic between the client and the server. One of the easiest and most common ways to accomplish this is to have the client application call SQL Server stored procedures that run on SQL Server, and […]

Tips on Optimizing SQL Server Database Design

Bad logical database design results in bad physical database design, and generally results in poor database performance. So, if it is your responsibility to design a database from scratch, be sure you take the necessary time and effort to get the logical database design right. Once the logical design is right, then you also need […]

SQL Server Application Design Performance Tuning Tips

SQL Server Application Design Performance Tuning Tips When designing applications using SQL Server as the backend, consider designing the application to take advantage of the n-tier application model. By doing so, you will design applications that not only maximize performance, but help to boost scalability. Application design can be divided into two areas: logical design […]

Tips for Performance Tuning SQL Server OLTP Databases

Try to avoid performing both OLTP and OLAP activity within the same database. If you do, OLTP activities will slow down OLAP activities, and OLAP activities will slow down OLTP activities. OLTP and OLAP activities should be considered mutually exclusive, and ideally, should be relegated to their own databases. For example, OLTP activities should occur […]

Temp Table Performance Tuning Tips

In general, temp tables should be avoided, if possible. Because they are created in the tempdb database, they create additional overhead for SQL Server, slowing overall performance. As an alternative to temp tables, consider the following alternatives: Rewrite your code so that the action you need completed can be done using a standard query or […]