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.
