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: |
| 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
- 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:
537
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
