SQL Server Performance Tips
SQL Server performance tuning can consume a considerable amount of time and effort. The following list is a quick guideline that you should keep in mind when designing and developing SQL Server database applications:
User Defined Functions (UDF)
Refrain from using user defined functions (UDF) in a select statement that may potentially return many records. UDFs are executed as many times as there are rows in a returned result. A query that returns 100,000 rows calls the UDF 100,000 times.
SQL Server table indexes
Create SQL statements that utilize defined table indexes. Using indexes minimizes the amount of table scan which in most cases will be much slower than an index scan.
Multiple disks
The single best performance increase on a SQL Server computer comes from spreading I/O among multiple drives. Adding memory is a close second. Having many smaller drives is better than having one large drive for SQL Server machines. Even though the seek time is faster in larger drives, you will still get a tremendous performance improvement by spreading files, tables, and logs among more than one drive.
Disk controllers
Different disk controllers and drivers use different amounts of CPU time to perform disk I/O. Efficient controllers and drivers use less time, leaving more processing time available for user applications and increasing overall throughput.
SQL Server foreign keys
Ensure that all your tables are linked with foreign keys. foreign keys enhance the performance of queries with joins. Database tables inside each application are naturally related. Islands of tables are rarely needed if your application's business logic is well defined.
SQL Server primary keys
Ensure that every table has a primary key. if you can't find a natural set of columns to serve as a primary key, create a new column and make it a primary key on the table.
Processor (CPU)
When you examine processor usage, consider the type of work the instance of SQL Server is performing. If SQL Server is performing a lot of calculations, such as queries involving aggregates or memory-bound queries that require no disk I/O, 100 percent of the processor's time can be used. If this causes the performance of other applications to suffer, try changing the workload of the queries with aggregates.
This article has been taken from teratrax.com.