Basic Introduction related to Performance

Use Normalization  - Speed updates, Slow in insertions , Fast Search , reports take time because of joining.

Denormalization –fast report (less joining).

Good database design use file groups (primary , secondary , log) .

FGs should be created on different physical disk for performance.

Those objects which compete for resources  should be on diff.  filegroups on diff. disks.

Place diff. tables (to be join) on diff. filegroup (table contain large amount of data).

Keep rows compact  i.e size of row should be small.

Avoid null compact  , use not null + default values.

Keep PK compact. They are Freq. referred in another table as FK.

Try to keep PK on integer value avoid character by character comparison.

Index on column that are used in where clause too much.

Make index on such column that eliminated max rows (not on sex but on name).

Avoid update on index keys instead use delete/insert .

Use fill factor judiciously (amt of space filled in a sql page).

Ordering of columns in index (leftmost should be one eliminates max rows).

Create / drop indexes as required.

Use execution plans to understand query performance.

Use SPs  almost  always.

Use parametrized queries.
Disable the sql messages . like n rows affected ( set no count on).

Avoid using cursors C or if as all required use forward only / read only.

Use transaction control judiciously. Start as late as possible, exit (commit / rollback) as early as possible.

Select only required columns . avoid using queries like “ “

Use table variables instead of temp tables.

Set auto compute statistics is true.

Performance

1.       DB Design

2.       Correct use of indexes

3.       File groups

4.       I/O from tables

5.       Calculation like summarizing (query advancement, direct use sum function in query using group by) )

6.       joins  (file on diff.  file groups)

7.       SPs (precompiled)

8.       Network traffic

Temp Database

1.       Set size of temp DB to a reasonable good value.

2.       Set its increment value not too small.

3.       Place temp DB  on fast I/O multiple disks (use file groups to place temp DB on disk other then user DB)

Security

1.       Create user with limited permissions  (use NTFS  FS)

2.       Keep DB on separate m/c rather  than on the one running Web Application

3.       Enable login auditing at OS+SQL level

4.       Drop guest user from SQLDB using SP_dropuser ‘guest’

5.       Use SPs (insert, update, delete via SPs)

6.       No direct query to base tables, use standard query views.

7.        Use role base security

8.       Secure erroelog by assigning by ntfs permission.