SQL Server Worst Practices

SQL Server Worst Practices

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.

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

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

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

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

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

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

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

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

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

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

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?

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