SQL Server Worst Practices

Written By: Edgewood Solutions Engineers -- 3/13/2009 -- 1 comments

     Stay informed - get the MSSQLTips.com newsletter and win - click here    

Problem
Since today is Friday the thirteenth, what are some of the SQL Server worst practices and how can I prevent or fix them?  Can you provide some real world examples?  Can you focus on the technical aspects of SQL Server that DBAs and Developers have the ability to correct?

Solution
Worst practices can differ from organization to organization or team to team.  The first step is recognizing they are an issue and then working towards a solution.  Below outlines the thirteen worst practices across numerous projects that we feel can easily be addressed from a technical perspective.  Some items many resonate with you or be higher on your particular worst practices list, but the goal is to shed some light on these worst practices and how to begin to address them.

---

SQL Server Worst Practices

13 - No requirements

In many organizations application or business requirements still seem to be optional.  The business has a general idea of what they want, but getting to a sufficient level of detail to design, build and maintain a solution is very difficult.

Recommendation - Getting an organization to change over night to a formalized project process will be difficult, but is worth the long term investment even if you just start with your own projects.  At a minimum build your own template for requirements that can easily be updated then distributed to your management for that project's approval.  If this is not possible, consider an email template that you can quickly fill-out then send to the team for approval.  Remember the more time you spend up front will save hours over the life of the project and should reduce re-work considerably.

12 - No code reviews

Code reviews are another key step in the design and development phases that projects seem to never have time to conduct.  Typically there is never enough time for this type of meeting, but always enough time to correct an issue.

Recommendation - If your organization is not conducting code reviews at regular intervals during the week or at specific points in the project, see if you can start this process informally.  This meeting is a good way to prevent many of the issues listed in this list before they become production issues.  Catching issues like, no referential integrity, no indexes, incorrect data types, unnecessary hinting, not validating data input, etc. can improve the code and minimize the support needs in the long run.  So build your checklist and see what improvements you can make.  If you need a starting point for your checklist, reference the following:

• SQL Server Code Review Checklist
• SQL Server Code Deployment Best Practices

11 - No referential integrity

Here is the first item on the list that would most likely be caught during a code review, a database design without any referential integrity.  With that being said, be sure to have the proper primary keys and foreign keys established between your tables.

Recommendation - Consider starting with a design where all tables have a primary key and establish foreign keys.  As you work through that process, be sure to normalize your tables at least to third normal form and de-normalize as your application requires.

• MSSQLTips Category: Referential Integrity
• Foreign Key vs. Trigger Referential Integrity in SQL Server
• The Importance of SQL Server Foreign Keys
• Finding primary keys and missing primary keys in SQL Server
• Identify all of your foreign keys in a SQL Server database

10 - No indexes

Here is another item that would be uncovered during a code review.  That being, no indexes.  None whatsoever.  Since the tables do not even have a primary key, you do not benefit from SQL Server adding an index on this column by default.  So making indexes an after thought is typically not a good thought after all. 

Recommendation - As a portion of the code review or code deployment process be sure to review the indexes to validate they will meet your application needs.  Table scanning may not rear its ugly head until your user community grows or your data grows which maxes out the resources on your SQL Server.  As a simple check, review those indexes.  Too many or too few can both be significant issues.

• MSSQLTips Category: Indexing
• SQL Server Index Checklist

9 - Duplicate indexes

From one extreme to the other, having too many indexes or duplicate indexes is not a favorable situation.  This too should be a problem identified during your code reviews.  Duplicate or repeating indexes cause SQL Server to have to manage more processing than necessary when data is inserted, updated or deleted.  Duplicate indexes can also cause database bloat and require enormous amounts for time for database maintenance that is unnecessary.

Recommendation - Review your code carefully to understand the data access needs then determine if your indexes meet those needs by reviewing the query plans.  Having a covering index is typically a good idea to prevent bookmark lookups, but be sure to select the columns and associated order properly.  A good tip on that issue is Avoid Index Redundancy in SQL Server Tables which explains how similar indexes are unnecessary.

• Avoid Index Redundancy in SQL Server Tables
• MSSQLTips Category: Indexing
• SQL Server Index Checklist
• Improve Performance with SQL Server 2005 Covering Index Enhancements

8 - Throwing hardware at the problem

More often than not, hardware is the answer to performance problems.  In reality the problem could be hardware based, but code can typically be re-written to correct the problem.  Although the initial cost of correcting the code could be more than a new server, the performance problems will persist and the problems will continue unnoticed until one day when history repeats itself.

Recommendation - Baseline and review your systems on a regular basis.  Understand the norms and when processes are outside the norm.  Be sure to dig into the details to identify the issue, research and test alternatives then implement the most appropriate solution.

• MSSQLTips Category: Hardware
• MSSQLTips Category: Performance Tuning

7 - Unnecessary coding techniques

Heavy use of cursors, unnecessary temporary tables, no naming conventions, views on top of views, etc. you name it and it happens.  But are these techniques really necessary?  What seems to happen is one developer finds a technique that works and everyone follows the technique with small modifications. 

Recommendation - Take the time to understand what the code is trying to accomplish.  Seek out a few different options then test them for performance and security then consider the code maintenance aspects.  Once you have worked through this cycle you should be able to leverage a coding standard that is best for your applications and environment.

• MSSQLTips Category: Scripts
• MSSQLTips Category: T-SQL

6 - No service packs

Windows and SQL Server service packs are coded for a reason.  They are to correct significant issues with the originally deployed code.  I agree that just letting Windows Updates apply new service packs is not the right way to handle production software updates in all circumstances, so figure out how your environment should test and deploy the code on a regular basis.

Recommendation - Setting up a schedule for your service pack updates is the first step in the right direction.  Once you have a schedule in place work through the testing process to ensure the new versions of Windows, SQL Server, etc will not break any functionality.  You might even be surprised to find new opportunities to improve your applications with some of the software updates.

• MSSQLTips Category: Service Packs / Patches

5 - No maintenance

In some environments database maintenance is very difficult due to uptime requirements.  However this is not the case in all environments.  Make sure you perform database maintenance at least as often as you change the oil in your car.  If not both your car and database costs will be high.

Recommendation - Just running maintenance is easier said than done, so be sure to understand the constraints of your environment and figure out the best plan possible.  Hopefully you can automate the entire process and be notified when all is finished.

• MSSQLTips Category: Maintenance
• Performing SQL Server Maintenance with No Maintenance Window
• Performing Maintenance with SQL Server Databases in Full Recovery model
• Performing maintenance tasks in SQL Server

4 - No security

As a DBA, one reasonable way to think about security is to think about who you would feel if someone stole your personal data and all of the hours it would take you to clear your good name.  If you could put it in those terms, it could be a great deal of motivation to approach security in a whole new light. 

Recommendation - Look at security from a few different angles to determine how you can prevent data misuse and theft.  Consider how to protect internal data versus publicly facing data.  Think about what processes are in place and who has access to what data.  Consider working with all of the members of your team to secure your systems from every angle.

• MSSQLTips Category: Security
• MSSQLTips Category: Auditing and Compliance

3 - No testing

Testing and fixing code in production is a scary proposition.  Period.

Recommendation - Make sure you have plans in place with sufficient time to test your changes appropriately.  Rushing through testing will only lead to a situation where you have to frantically correct an issue in production which could lead to more issues.  Before you know it you are fire fighting numerous issues that should have been flushed out in a test environment.

• MSSQLTips Category: Testing
• Automate SQL Server Testing with Profiler, SQLCMD and SQL Server Integration Services
• Testing Options with SQL Server 2000 and 2005

2 - Not verifying SQL Server backups, SQL Server Agent Jobs, business processes, disk space, performance, etc.

A little bit of prevention goes a long way.  That is absolutely the case when you need to manage a large or complex SQL Server environment.  One minor issue can quickly become a day long fire fighting session. 

Recommendation - Be sure to setup SQL Server Alerts, SQL Server 2008 policies (Using Policy-Based Management in SQL Server 2008 and Evaluating and Implementing Policies in Multiple Instances in SQL Server 2008) and/or use a third party product monitor and notify you based on a preventive threshold or an error.  Taking the time to setup these tools to prevent fire fighting when an problem occurs.

• SQL Server DBA Checklist
• Do you really know if your SQL Server database backups are successful?

1 - Developing and deploying code susceptible to SQL Injection

SQL Injection is still a real problem and many web sites have been impacted by not following some simple steps to validate the data being passed into the application whether from a form or URL.  Learn from the many hard lessons learned and protect your code.

Recommendation - Check out the URLs below or many others with tips and tricks to prevent and correct code susceptible to SQL Injection.  Build your own test cases to validate the code is not susceptible to SQL Injection before the code gets deployed to the production environment.

• Recover from a SQL Injection Attack on SQL Server
• Running a Dynamic Query against SQL Server without using Dynamic SQL
• Execute Dynamic SQL commands in SQL Server
• Using the CASE expression instead of dynamic SQL in SQL Server

Next Steps

• Whatever the worst practices may be in your environment, the first step in resolving them is to recognize them.  So take some time to identify the worst practices and begin to correct them.  You might have to address the issues one at a time and it might take years to completely resolve, but it is typically worth the investment and will improve current and future systems.
• Based on the tip above, hopefully you can see how a code review can prevent many of these problems from entering production systems.  That single practice may yield numerous benefits, so it might be the best place to start then have the remaining items follow behind.
• We want to hear from you.  Please add your worst practices to the forums by clicking on the link below.
• Check out these related MSSQLTips:
  • SQL Server Disasters with Preventive Measures
  • Best practices for taking on the SQL Server DBA role as a developer
  • Checklist to Re-Architect a SQL Server Database
  • SQL Server DBA database management checklist
  • SQL Server DBA Checklist