SQL Coding Best Practices and Design Considerations

As the business demand the applications to be more flexible and user friendly, the data access layers become more critical. For the applications to be flexible and quick responsive, the database reads and writes should be at optimum performance levels leaving the developers and DBA's an mandatory option to following the coding standards and best practices.

Superior coding techniques and programming practices are hallmarks of a professional programmer. The bulk of programming consists of making a large number of small choices while attempting to solve a larger set of problems. How wisely those choices are made depends largely upon the programmer's skill and expertise.

The readability of source code has a direct impact on how well a developer comprehends a software system. Code maintainability refers to how easily that software system can be changed to add new features, modify existing features, fix bugs, or improve performance. Although readability and maintainability are the result of many factors, one particular facet of software development upon which all developers have an influence is coding technique. The easiest method to ensure that a team of developers will yield quality code is to establish a coding standard, which is then enforced at routine code reviews.

This post and the underlying presentation aims at the fundamentals of SQL Coding Best Practices and Design Considerations. To read further, download the copy of presentation from here.

Backup job failed - "file manipulation operations"

On some occasions when scheduling maintenance jobs like, Full database backup, Shrink database, Differential backups, index maintenance jobs we oversee the schedules and end up with jobs running into each other.

This will definitely lead to bigger errors and failures at any point in time. One such error we come across in such situations is the following.

 

"Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed."

Note: This is just the part of job history.

 

Fixes:

• All we need to do is be alert and make sure we have the correct schedules for all the jobs without allowing any of the jobs running into each other.
• If error or failures, make sure to change the job schedules and confirm if it works fine.

Hope this helps!

Best Practices for tempdb

The tempdb is a system database which is available for all the users connected to that instance of SQL Server.
This database is used to hold

• Temporary User Objects
• Internal Objects created by the Database Engine
• Row Versions that are generated by data Modifications

Since tempdb is used by all users and the system by itself for all their temporary operations, it is very much important that we optimize the tempdb and follow best practices for getting better performance out of it.

Below are some best practices that can be followed for tempdb

• Create the number of data files for tempdb based on the number of CPU's present on that system. Example: if the system has 4 CPUs then create 4 data files for tempdb with one Log file.
• Place tempdb files on the fastest available Drive.
• Isolate tempdb on a separate disk from other databases.
• Make all the data files of tempdb the same size.
• Disable autogrow option for all tempdb files and make sure you have enough space in them.
• Make sure to Commit or Rollback the transactions and if not done, then any space allocated for that transaction may not be released.