Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SQL Server Worst Practices


By:   |   Read Comments (7)   |   Related Tips: More > DBA Best Practices

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:

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Next Steps



Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Tuesday, March 06, 2012 - 9:47:21 AM - Jeremy Kadlec Back To Top

James,

Thanks so much for the positive feedback.

Thank you,
Jeremy Kadlec


Tuesday, March 06, 2012 - 9:30:40 AM - James Lawrence Back To Top

A great article that I just forwarded to our group. Seems we're always focused on what we SHOULD do, not what to avoid. This is a much needed reminder.


Monday, March 05, 2012 - 2:20:51 PM - Jeremy Kadlec Back To Top

WiseOldMan,

Thank you for the feedback.

Thank you,
Jeremy Kadlec


Monday, March 05, 2012 - 2:04:54 PM - WiseOldMan Back To Top

I'm glad you mentioned "views on top of views."  I've seen this practice far too often.  Nothing bugs me more than opening a view and seeing that it joins 5 views together each of which also join 5 views together, and so on and so on.  By the time the query is finished running, every table in the database is being joined somewhere multiple times.  Laziness is no excuse!


Friday, August 27, 2010 - 10:47:14 AM - Admin Back To Top
Shelley,

Thank you so much.  We hope all of the tips are helpful.

Thank you,
The MSSQLTips Team


Thursday, August 26, 2010 - 2:25:08 PM - Shelley Back To Top
Thanks for this article, Jeremy. I wish I had found it sooner. It is very comprehensive (and YES, RICK. Documentation is KEY - I have to do it for myself, I can't remember what I did 10 minutes ago, much less what some other DBA/Developer did 2 years ago!).

I'm in a consulting job, just about to wrap up the implementation and documentation of SQL Server Best Practices for this organization. I have sent your article to the team.

Thanks again.

 


Friday, March 13, 2009 - 10:16:00 AM - Rick M. Back To Top

Hey All,

Another tip that obviously did not make the top 13, is documentation.  It can be a daunting task or nightmare when you have to follow behind someone else, especially those that are no longer associated with the company, and try to interpret their code.

I've been known to do the same thing, thinking that I will come back and insert the documentation only to find that I did not have the time later to do it...or I simply forgot!  I've learned that you do it as you go, and then you won't forget...and it has saved my hide a few times too!

Rick 

 


Learn more about SQL Server tools