Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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.

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.

Total article views: 7907 | Views in the last 30 days: 3
 
Related Articles
BLOG

5 Things a developer should know about databases

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

ARTICLE

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

FORUM

Development and Production Database

Insert Into Development and Production Database at the same time

FORUM

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.

ARTICLE

Developers, developers, developers

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

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones