Don’t think that performance tuning your SQL Server applications is regulated to the end of the development process. If you want your SQL Server-based applications to scale and run at their full potential, you must begin considering scalability and performance issues during the early stages of your application’s development.
If you have been a DBA or SQL developer for very long, then you have probably run across some slow SQL Server-based applications. And often when this happens, everybody begins blaming everybody else for the problem. It’s the network. It’s the server hardware. It’s SQL Server. It’s the users. It’s the database. And this goes on and on, but unfortunately, blame doesn’t fix slow applications. The cure to most slow SQL Server-based applications is prevention, which includes careful up front analysis of the user’s needs, thoughtful design, optimal coding, and appropriate implementation.
For any application, SQL Server or otherwise, scalability and performance have to be built in from the very beginning. Once the application is rolled out, it is very difficult and expensive to resolve most scalability and performance issues.
In this article you are going to learn the fundamentals of how to design, code, and implement scalable and performance optimized SQL Server applications. You won’t learn everything, as that would take an entire book. The focus of this article is on learning the very minimum you must know in order to produce scalable and performance tuned SQL Server-based applications. Here’s what you will learn:
-
What Every Developer and DBA Must Know About SQL Server Performance Tuning
-
How to Optimize Your Server’s Hardware
-
How to Optimize SQL Server’s Configuration Settings
-
How to Optimize Your Application’s Design
-
How to Optimize Your Database’s Design
-
How to Optimize Your Application Code for SQL Server
-
How to Optimize Your Transact-SQL Code
-
How to Select Indexes for Optimal Database Performance
-
How to Take Advantage of SQL Server Performance Tuning Tools
At the very least, if you take advantage of the advice and information in this article, you will find that performance tuning your SQL Server-related applications is not a big as mystery as you might think. So let’s get to work.
What Every Developer Must Know About SQL Server Performance Tuning
As a developer, there are some overriding principals on which to proceed. This section introduces these principals. Keep these in mind as you read about specific performance tuning details discussed later in this article, and whenever performance tuning your SQL Server applications.
Performance Tuning is Not a Science
SQL Server performance tuning is more art than science. I am sure you didn’t want to hear that, but this is a fact of life. I wish I could tell you exactly, step-by-step, what you need to do to make your applications scale and perform well. The problem, as you probably already know, is that any modern software application is a combination of many complex variables. Unfortunately, no matter how hard you try, you don’t have full control of your application and the environment it runs under. For example, here are some (not all) of the factors that affect an application’s performance:
-
SQL Server (the program itself)
-
SQL Server’s Configuration Settings
-
The Application’s Transact-SQL Code
-
The Application’s non-Transact-SQL Code
-
The Database’s Design
-
The Operating System (server and client)
-
The Middleware (Microsoft Transaction Server, Microsoft Messaging Server)
-
The Hardware (server and client)
-
The Network Hardware and Bandwidth (LAN and WAN)
-
The Number of Clients
-
The Client Usage Patterns
-
The Type and Quantity of Data Stored in SQL Server
-
Whether the Application is OLTP- or OLAP-based
While it is virtually impossible to control every factor that influences SQL Servers’ scalability and performance, what you can do is make the most of what you can control.
Test During All Stages of Your Application’s Development
Scalability and performance testing should not only be done after the application is written and ready to be rolled out. Testing should be an integral part of the development process, beginning with the earliest stages of the application and database design, and continuing as appropriate throughout the entire process. Most scalability and performance issues are a result of poor initial design, and can only be prevented early in the game. If you wait until after the application is complete, you will either have to live with performance problems, or rewrite the application.
When performing tests, always proceed scientifically, only testing one dependant variable at a time. For example, if you suspect that you need to add an index to a table to boost performance, but you are not sure which one, or of what type is best, experiment with only one change at a time, testing each change individually to see if it produces the results you expect. If you change more than one thing at a time, you won’t know which change you made worked or didn’t work. This goes for all testing, whether it is adding indexes, making SQL Server configuration changes, or testing various hardware configurations.
Always try to test under realistic conditions. This means use “real” data, testing against the largest expected data sets, and using hardware similar to the hardware that will be used when the application goes into production. If you don’t, you may be surprised that while your application works well for 10 simultaneous users during testing, that it fails miserably when 500 users are online.
Not All Performance Issues are Obvious
If you do much performance tuning, you will soon realize that many performance tuning and scalability issues are not so obvious. This is because many performance-related problems are caused by two or more problems, not a single obvious problem. This makes it difficult to isolate and fix the problem. While there are no easy solutions, one of the best approaches to take is to isolate and correct one problem at a time, until you have found and fixed them all.
Not All Performance Tuning Suggestions Work In All Cases
In this article, and from other performance tuning sources, you will find dozens of ideas and tips on performance tuning. Keep in mind that in many cases a performance tuning suggestion will work great in one particular situation, but could actually reduce performance under a different situation. This is because many performance tuning suggestions are situation specific. As the person responsible for performance tuning, you will need to evaluate each tip or suggestion you run across and decide whether it is applicable to your particular situation. In other words, don’t blindly proceed with a performance tuning tip. Be sure you understand its implications before you use it.
SQL Server Performance Tuning is a Learned Skill
Learning how to master SQL Sever 2000 performance tuning cannot be learned overnight. In fact, experience, more than book learning, is how you will master this skill. But in order to take advantage of the experience you gain over time, it is also important to be conversant in the fundamentals of the technologies that affects your application’s performance.
For example, you need to fully understand the programming language used to write your application, database design, application design, Transact-SQL, how SQL Server stores and indexes data, and how networks and server hardware really work. The better understanding you have of the basics of the applicable technologies used to develop and roll out your application, the better position you will be in to understand what is causing performance and scalability problems and how to resolve them. Learn all you can.
How to Optimize Your Server’s Hardware
When it comes time to blame poor application performance on something, server hardware gets a disproportionate amount of blame. What is ironic, is that in most cases the hardware is not the main cause of the problem. In fact, server hardware plays a much smaller role than most people think when it comes to SQL Server-based application performance and scalability.
The reason for this is that most slow applications are slow because of poor up front design, not because of slow hardware. The reason hardware is often blamed is because performance problems often don’t show themselves until after the application is rolled out. And since the application’s design can’t be changed at this time, about the only thing you can try to help boost performance is to throw hardware at it. While hardware can help, it usually doesn’t fully resolve the problem, and this is why hardware is often blamed for slow performance. While hardware can sometimes be an issue, most likely it is not.
In order to prevent your server hardware from being a drag on your SQL Server-based application (which it can if it is inappropriately selected or configured), let’s take a brief look at some of the most common hardware selection and tuning issues.
Selecting Hardware
Selecting the optimum hardware for your SQL Server-based application depends on a variety of factors, such as the size of the database, the number of users, how the database is used (OLTP or OLAP), and others. While there is no sure-fire formula for sizing server hardware, the best way to get a feel for sizing is to test your application early in the development stage. Ah, testing is mentioned again. That’s right. While many experienced DBAs can probably give you a good estimate on the optimum hardware you need, only through realistic testing will you know for sure what hardware is required to meet your application’s needs.
When is comes to server hardware, here are some things to keep in mind:
-
CPU: Always purchase a server with the ability to expand its number of CPUs. For example, if testing indicates that a single CPU server will be adequate, purchase a server with at least room for two CPUs, even if you only use one of the slots. The same goes for larger servers with four or more CPUs. Always leave room for growth.
-
Memory: This is probably the most significant piece of hardware that affects SQL Server’s performance. Ideally, your entire database should fit into RAM. Unfortunately, this is not often possible. At the very minimum, try to get enough RAM to hold the largest table you expect to have, and if you can afford it, get all the RAM your server can handle, which is often 2GB or more. There is no such thing as too much RAM.
-
I/O Subsystem: After RAM, the I/O subsystem is the most important piece of hardware to affect SQL Server’s performance. At the very minimum, purchase hardware-based RAID for your databases. As a rule of thumb, you will to purchase more, smaller drives, not fewer, larger drives in your array. The more disks that are in an array, the faster I/O will be.
- Network Connection: At the server, have at least one 100Mbs network card, and it should be connected to a switch. Ideally, you should have two network cards in the server connected to a switch in full-duplex mode.
Tuning the Server
Even the most expensive server hardware won’t perform well if it is not configured and tuned correctly. I have seen many hardware-related performance problems caused as the result of not using Microsoft NT Server approved hardware and drivers. Some of these types of hardware performance-related issues are very difficult to trace and resolve. Ideally, ensure that you hardware, including NT, is correctly installed and configured by a competent technician. Then test your application under controlled conditions to test for potential performance issues before it is used in production.
Your operating system must also be configured correctly. This includes many things, too many to describe here. Just as with the hardware, ensure that the operating system is properly configured and tested before it is put into production.
For best performance on a server, SQL Server should be the only application running on the server, other than management utilities. Don’t try to save a few bucks by putting your IIS or MTS server on the same server as SQL Server. Not only does this hurt SQL Server’s performance, but it also makes it more difficult to performance tune and troubleshoot SQL Server.