When it comes to assigning value to different components in today’s IT environment, it would be a serious mistake to underestimate the value of data and databases. Whether you are looking to reduce downtime and speed up your database-centric application or to save money and time on optimizing your database servers, the following best practices can help you improve the performance of your SQL Server environment and, ultimately, your business service delivery process.
Invest in and maintain your infrastructure
Organizations with high performing SQL Server environments give great consideration to the infrastructure underlying and surrounding their databases. This means more than just investing in newer and faster hardware (though that undoubtedly makes a difference). It also means maintaining development and test environments so that potential performance problems can be identified before reaching production. It means architecting solutions for high availability and disaster recovery such that preventative maintenance and updates can be performed regularly without impacting user workloads. And it means reaching across functional boundaries to ensure that the network, storage, virtualization, and application layers which communicate with your databases are properly configured and kept up to date.
Use the features of the engine
SQL Server is a robust and mature product and the number of features available to database administrators and developers continues to mount. Do not let fear of upgrading – to a new version or to a new edition – prevent you from testing and then utilizing the features which will improve the performance of your environment. Availability groups, columnstore indexes, memory optimized tables, snapshot isolation: the list goes on and on of features which can drastically speed up certain patterns of work.
Eliminate the low hanging fruit
With the wealth of information available online and through user groups, database administrators (DBAs) should be able to easily correct the most common performance-sapping misconfigurations and anti-practices that dog their environment. Overlapping maintenance windows, single-file tempdb topology, outdated server configuration options, excessive log file VLFs, over- and under- indexed tables, and many basic issues of blocking and deadlocking influenced by query hints and isolation levels may all be examined and corrected with minimal interaction with application code. Find these issues, correct them, and periodically check to make sure they do not recur.
Understand your workload
It is difficult to improve the performance of workloads which are not well understood. Knowing the business cycles, user patterns, and maintenance windows of the primary applications that are consuming database resources will help any DBA to make the right choices before a problem ever arises; for example, by not locating two applications with very heavy early morning login loads on the same server. A skilled administrator will further become familiar with the query plans and data access patterns of the most critical parts of an application and thus be able to anticipate possible consequences at the data layer when there is a change in procedure.
Monitor and maintain a baseline
It is a poor practice to wait until a user complains before investigating an issue. Many issues can be detected and corrected before they rise to a human-perceptible level of concern, and the users’ reports are often nonspecific and so subjective that they can be impossible to troubleshoot. SQL Server environments should be monitored and their performance baselined so that deviations can be isolated and investigated. Without this kind of data at hand, the DBA is often forced into a perennially reactive mode where no real progress can be made. With a baseline report, however, not only can improvements be achieved, but they can be documented and demonstrated to other team members.
The Bottom Line
There is a wide range of opinions as to which best practices are the most important to optimizing database server performance. Regardless of the factors that may affect your decision, keeping your database servers operating efficiently is vital to meeting business goals.
Image courtesy of Shutterstock.