USEFUL SITES :
Write for Us
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. The "dictionary order, case-insensitive, accent-insensitive, uppercase preference" and the dictionary order, case-sensitive" are even slower yet. Choose the sort order that best meets your performance and application needs. [6.5, 7.0, 2000, 2005] Updated 12-20-2004
*****
The network libraries you choose when installing SQL Server can affect the speed of communications between the server and its clients. Of the three key network libraries, TCP/IP is the fastest and Multi-Protocol is the slowest. Because of the speed advantage, you will want to use TCP/IP on both your servers and clients. Also, don't install unused network libraries on the server, as they only contribute unnecessary overhead. [6.5, 7.0, 2000, 2005] Updated 12-20-2004
In most cases, you will want to install the latest SQL Server service packs. In virtually every service pack issued for SQL, bugs have been fixed that affect performance. As we all know, don't automatically install a new service pack when it is first released. Wait at least 2-4 week and monitor the SQL Server newsgroups to find out if the new service pack has any major problems before you install it. [6.5, 7.0, 2000, 2005] Updated 12-20-2004
Don't install SQL Server services you don't need, such as Microsoft Search, Notification Services or Analysis Services, as they only add additional overhead to your server if they are not used. [6.5, 7.0, 2000, 2005] Updated 12-20-2004
The physical location you choose for the master, msdb, and model databases is not as critical as your user files as they are not used excessively in production environments. [6.5, 7.0, 2000, 2005] Updated 12-20-2004
For best overall performance, locate the database files (.mdf) and log files (.ldf) on separate arrays in your server to isolate potentially conflicting reads and writes. [6.5, 7.0, 2000, 2005] Updated 12-20-2004
To store your database files (.mdf), the best performance is gained by storing them on RAID 10 arrays. If this is too expensive, then RAID 5 is most likely the next best bet. Each RAID array (5 or 10) should have as many physical disks in the array as the controller will support. This allows reads and writes to be performed simultaneously on each physical drive in the array, boosting disk I/O. [6.5, 7.0, 2000, 2005] Updated 12-20-2004
To store your database log files (.ldf), best I/O performance is often gained by storing them using a RAID 1 (mirrored or duplexed) array. This assumes that there is only a single log file on the RAID 1 array. If there is only a single log file on the RAID 1 array, the file can be written to sequentially, speeding up log writes.
But if there are multiple log files (from multiple databases) sharing the same RAID 1 array, then there less advantage of using a RAID 1 array. This is because although writing to a log is done sequentially, and multiple log files on the same array means that the array will no longer be able to write sequentially, but will have to write randomly, negating much of the benefits.
But note: a RAID 1 array might still be better than a RAID 5 array if it can process more random writes than the RAID 5 array. You will have to check your hardware to know for sure. Another option is to put each database log on its own separate RAID 1 array. One more option is to put the log on a RAID 10 array, which offers the best features of RAID 1 and RAID 5. While this is expensive, it will provide optimum I/O performance. [6.5, 7.0, 2000, 2005] Updated 12-20-2004
If your database is very large and very busy, multiple database data files can be used to increase performance. Here is one example of how you might use multiple data files. Let's say you have a single table with 10 million rows that is heavily queried. If the table is in a single file, such as a single database file, then SQL Server would only use one thread to perform a sequential read of the rows in the table. But if the table were divided into three physical files (all part of the same filegroup), then SQL Server would use three threads (one per physical file) to sequentially read the table, which potentially could be much faster. In addition, if each file were on its own separate disk or disk array, the performance would even be greater.
Essentially, the more separate physical files that a large table is divided into, the greater the potential performance. Of course there is a point where the additional threads aren't of much use when you max out the server's I/O. But up until you do max out the I/O, additional threads (and files) should increase performance. [7.0, 2000, 2005] Updated 12-20-2004
