In late March 2005, the first installment of SQL Server Performance-Tuning worst practices was released, and people in the community responded to the article with other worst practices they have observed. In this installment, we will explore some new observations and provide recommendations as well as share some additional experiences from members in the community.
Worst practice #5: No managerial support for high performance
Observation -- One common theme I have heard from many managers and users is that systems should just work and have high performance, period. Things should run forever. My new roof will last 15 to 25 years and I do not need to do anything to it. SQL Server should be the same way. The response I have heard from frustrated IT professionals is that the organization expects things to happen "automagically" i.e., automatic and magically.
Recommendation -- In reality, nothing happens automatically, even with some sort of magic; it takes a great deal of planning, testing, integrating and maintenance by a dedicated IT team. It is imperative to gain managerial support concerning the time, resources and budget issues in order for IT to meet the business needs.
Worst practice #6: It's so complicated it turns my brain to mush
Observation -- Some solutions are far more complicated than need to be, and team members believe that the more difficult, the better. Some people even brag about building technologies that others do not understand or have never seen before.
Recommendation -- Strive for simplicity in all of your high-performance solutions. I think an IT professional has a gift if he or she is able to dissect a complex problem into a simplified solution. The icing on the cake is when it can be easily documented and communicated, and it delivers the needed performance. Sure, you can write a function that gets called by a procedure that loops through 60,000 records, creates and drops four subsequent temporary tables with 2 million rows each, then changes eight different flags that another Job will query every minute. But that doesn't mean that the same answer can't be resolved via a single set-based statement in one stored procedure. Just because you can do it, doesn't mean you should.
Worst practice #7: Lack of security
Observation -- On some systems, a number of IT team members know the System Administrator's password, have administrative rights explicitly or through Built-inAdministrators group to SQL Server. This gives them the ability to make system, DML (data manipulation language) or DDL (data definition language) changes. If a problem occurs, sometimes it is corrected without anyone knowing who made the change or what the change was that corrected the problem. This leaves many unanswered questions without a way to determine the final answer.
Recommendation -- For more reasons than just performance, it ii imperative that SQL Server security is properly administered and privileged users are accountable for their actions. If changes are made to correct an issue, they should be handled systematically and documented in response to correcting the original issue.
Worst practice #8: No performance monitoring and management tool
Observation -- Organizations know that particular systems are experiencing performance problems, but they cannot answer these simple questions:
- What is the cause of the performance problem?
- When is the issue occurring?
- Who are the users that are causing the issue?
- What tier in the application is causing the issue?
- What application is most resource intensive?
- Which statements require the most resources?
- Are the systems properly configured?
- Are the systems properly designed?
- Has the code optimized?
Recommendation -- Organizations should invest the time, team and budget on a performance monitoring and management tool that will run on a 24/7 basis to determine a baseline, gather the necessary statistics, alert when performance thresholds are exceeded and report on performance over time.
From my first article in these worst-practices installments, I received some feedback that I would like to share with everyone in this community:
Rudy in Indiana writes that the number one worst practice in his opinion is the absence of a production DBA. Two is a developer handling DBA responsibilities. Three is no initial or continuing education. Four is no time to test DBMS software and tools.
Kyle from Michigan shares a worst practice related to putting all mission-critical databases accessed on one server and wondering why all applications are performing poorly. He also shares an experience related to custom applications with the referential integrity enforced exclusively by the application. Kyle offers this recommendation: Spend time in the design phase of the project on the database structure to build the database referential integrity to avoid orphaned data and performance degradation.
Although not directly performance-related, I want to include Joyce's worst practice of no regular database or log backups, which, in her opinion is a crime punishable by lost data and full disk drives.
Thank you to everyone in the community who shared worst practices they have observed in the field. I encourage you to continue to identify worst practices and work to correct them and to, ultimately, achieve higher performance.
Anne R. from Delaware writes: One worst practice is to put multiple databases on one 500 GB drive -- only to wonder why applications are running slowly. In a similar vein, I've seen developers create large databases (200+ GB) with tables that are 40 and 50 GB large. Putting the databases on physically separate (not partitioned) drives is far more effective than changing the RAID of the drive. We've also placed (large) single tables that are heavily used in their own filegroup on separate drives for increased performance. Someone has wrongly convinced the database-using world that as long as disks are configured RAID 10, there's no problem.
Jeremy Kadlec is the Principal Database Engineer at a href="http://www.edgewoodsolutions.com/">Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. Jeremy is also the SearchSQLServer.com Performance Tuning expert. Ask him a question here.
Do you have comments on this tip? Let us know.
