In an environment, where the developer has free access to the production

servers, he can make unintentional mistakes which can result in server

degradation and performance dipping down drastically over a period of time. The

DBA needs to be aware of these common mistakes and take every precaution to

monitor these mistakes, rectify the same and convey it back to the developer so

that going forward the developers do not make such mistakes.

DATABASE CREATION

The developer may choose to create a database with default options as

provided by SQL Server. The default options have the initial size that of a

model database which may be very small and may result in creation of database

file which needs to be expanded every few transaction while in production. The

DBA should make sure that the developers who have admin access to the SQL Server

are aware of the implications it can have on the production environment. The

developer should be able to estimate the initial size of the database to be

keeping it free from overloading the server soon.

The default creation of database also results in the file having unrestricted

growth which leaves lot of scope for fragmentation. Always ask your developers

to have a maximum size for the database file, this will help in avoiding

fragmentation. Keep a maximum size and have a small percentage set for increment

of size.

The recovery model is by default full which may result in very large

transaction logs over a period, if the backups are not scheduled on regular

basis or through SQL Server. The transaction log settings should be kept to

simple or as appropriate to your environment.

TSQL, DATABASE DESIGN

Developers have a tendency to write "Select * from tblName" when they need to

query just one or two columns resulting in more processor time, memory

requirement and network traffic. The result can have a huge impact on the

performance of Server as well as application.

The developer or designer should make sure that the column data type should

be varchar rather then character. This results in saving lot of memory and

traffic across the network.

Although it sounds very basic but one does come across many tables and

database structures which do not have a primary key associated with it. Make sue

that the Primary Keys always exist.

The database designer has to strike a balance in between normalization and

denormalized form of a design. At times the Database has to have a performance

of RDBMS and flexibility of a warehouse.

Once the database is in use, it will be good if a trace on Profiler can be

used and the events be recorded order to fine tune the indexes using Index

Tuning Wizard. Make sure that the trace is done during the peak time and the

Index Tuning Wizard is used in non Peak time.

Developers often write stored procedures which have dynamic SQL. The

developers should always try and avoid using dynamic SQL.

The DROP command in a stored Procedure should be avoided for dropping a table

and should either be replaced by a truncate command or an inline table operator

for same. Another alternative can be a temporary table.

The foreign key relation should exist for data accuracy and also to ensure

that the attributes share the same data type across tables. Query which runs on

separate data types can kill the system.

The Developer should be aware of code that can result in dead lock. The

objects should be accessed in the same order in different stored procedures or

triggers. The transaction isolation level should be set to low wherever

possible.