Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Best Practices in an Adhoc Environment

By Sharad Nandwani,

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.


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.


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.

Total article views: 7911 | Views in the last 30 days: 2
Related Articles

5 Things a developer should know about databases

  Database Normalization Theoretical versus real-world There is not a strong need for develop...


Every DBA Should Know Which Databases are be Backed Up

Do you know which of your databases are being backed up? Everyone answers "Yes", but I'm sure that t...


Development and Production Database

Insert Into Development and Production Database at the same time


A severe error occurred on the current command.The results, if any, should be discarded.

A severe error occurred on the current command.The results, if any, should be discarded.


Developers, developers, developers

A survey of developers on Stack Overflow has some interesting results that Steve Jones notes. Some o...