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.
|
