SQL Server Configuration

SQL Server Service Pack

Because service packs include fixes that often affect performance (among other things) you should upgrade your SQL Servers with the most recent service pack when it becomes available. In some cases, service packs affect how the Query Optimizer performs. Because of this, some queries that were once slow may now be faster. On the other […]

How to Performance Tune the Microsoft SQL Server tempdb Database

If your SQL Server’s tempdb database is heavily used by your application(s), consider locating it on an array of its own (such as RAID 1 or RAID 10). This will allow disk I/O to be more evenly distributed, reducing disk I/O contention issues, and speeding up SQL Server’s overall performance. [6.5, 7.0, 2000, 2005] Updated […]

How to Performance Tune Microsoft SQL Server During Setup

The sort order you choose when installing SQL Server can affect its performance. The fastest sort order you can choose is the binary sort, although the results it produces may cause problems in your client applications. The default sort order of “dictionary order, case-insensitive, is the next fastest, and the one you should probably choose. […]

Link Servers

Distributed transactions, over linked servers, incur more overhead than transactions occurring on the same server. This is due, in part, to the fact that more than one server is involved in the transaction and more network traffic is generated. Because of the overhead involved in distributed transactions, they should be avoided when they can be […]

SQL Server Federated Database Performance Tuning Tips

For very large databases, consider using federated database servers to balance the processing load across multiple SQL 2000 or 2005 servers. This technique horizontally partitions SQL 2000 or 2005 data over one or more SQL Servers, allowing the client application to send SQL statements to the server in the federation having most of the data […]

Database Settings

If you set your SQL Server 7.0, SQL 2000, and SQL Server 2005 databases and transaction logs to grow automatically, keep in mind that every time this feature kicks in, it takes up a little extra CPU and I/O resources. Ideally, you want to minimize how often automatic growth occurs. One way to help do […]

Performance Tuning SQL Server’s Configuration Settings

Unless you know exactly what you are doing and have already performed impartial experiments that prove that making SQL Server configuration changes helps you in your particular environment, do not change any of the SQL Server configuration settings that affect server performance. SQL Server, in most cases, knows how to configure itself dynamically for optimum […]

SQL Server Filegroups

While filegroups provide an opportunity for fine-tuning performance by allowing you to move specific tables and indexes from one physical drive array to another, unless you have a lot of SQL Server experience and have a very large database, you may end up causing more performance problems than you fix. As a general rule of […]

AWE Memory

If you are using SQL Server 2000 Standard Edition under Windows NT 4.0, Windows 2000 (any version), or Windows 2003 (any version), or are running SQL Server 2000 Enterprise Edition under the Standard Edition Windows NT 4.0, Windows 2000, or Windows 2003, or if your server has 4GB or less of RAM, the “awe enabled” […]

VLDB Performance Tuning and Optimization

Network connections should be of the highest speed, such as 100Mbs Switched Ethernet, or even 1Gbs Switched Ethernet. [6.5, 7.0, 2000, 2005] Updated 1-3-2005 ***** Generally, DBCC commands are not run on a routine basis on VLDB because of the times involved, often greater than a day. Instead, only run them if you suspect possible […]