Tuning Up SQL Server 2005 Databases
It's a familiar problem for most developers. The application is just about finished, the near-final bits are rolled out to the test servers...and things aren't quite fast enough to suit everyone. As you scramble around looking for places to squeeze out another few percentage points of performance, the database looks like a likely suspect. The problem is that no one on the team is a real expert in the esoterica of SQL Server indexing and physical architecture design. So what can you do to make sure you've got things set up for optimal database performance?
Fortunately, SQL Server 2005 comes with a built-in answer to this problem: the Database Engine Tuning Advisor. Combining a simple user interface with a deep knowledge of SQL Server, this utility can help you tune your databases for peak performance. In this article I'll walk you through using the Tuning Advisor and show you what it can do for you.
You Can't Tune in a Vacuum
The first thing you need to understand when you're attacking a database tuning problem is that there is seldom (if ever) a single best way to set up a database. To understand this principle, consider the very simple case of a table holding customer information: should you create an index on the LastName column or not? The answer is that it depends on whether and how often you search by last name, sort by last name, or join the Customer table to other tables by the LastName column. If you don't do any of those things, than an index on this column is pure overhead. On the other hand, if every other database operation involves looking up customers by last name, it would be extremely inefficient to not index that column.
The Tuning Advisor handles these issues by introducing the concept of a workload. A workload is simply a mix of SQL statements that indicates the "typical" uses of your database; it gives the Tuning Advisor something to consider when deciding what recommendations to make. You can supply a workload in several ways. If your database isn't in use at all yet, you may have to deliver the workload as a simple file of SQL statements, typed directly into SQL Server Management Studio and saved to disk. In this case, the workload is your best guess as to how you think the database will be used.
But if the database is in active use, you can do better than that. The other way to generate a workload is to use the SQL Server Profiler utility to capture a trace file, using the tuning template. A trace file records the actual activity in your database over a period of time. If you record a substantial trace file - say, five megabytes or more, captured over a period of days - then the Tuning Advisor can tell you what changes would have made your database more efficient with the use that the database really got. I recommend that you follow this path to tune with real-world data, rather than guesswork, whenever possible.
The Tuning Process
To launch the Database Engine Tuning Advisor, select Microsoft SQL Server 2005, Performance Tools, Database Engine Tuning Advisor from your All Programs menu. When the utility launches, you'll need to connect to the server where the database that you want to tune resides. The Tuning Advisor will then retrieve a list of all databases on the server and wait for you to tell it what to do.
Tuning Advisor is capable of evaluating workloads that cross database boundaries (it looks for USE DATABASE statements within the workload). After choosing your workload, you can select both the database where the tuning will be conducted (that is, the database that the Tuning Advisor connects to when it starts running SQL statements) and the databases to actually tune. As you can see in Figure 1, you can also choose to limit your tuning to individual tables within your target databases. This is a useful feature when you're trying to tune only parts of a very large database.
See full detail: http://www.developer.com/db/article.php/3607186
Fortunately, SQL Server 2005 comes with a built-in answer to this problem: the Database Engine Tuning Advisor. Combining a simple user interface with a deep knowledge of SQL Server, this utility can help you tune your databases for peak performance. In this article I'll walk you through using the Tuning Advisor and show you what it can do for you.
You Can't Tune in a Vacuum
The first thing you need to understand when you're attacking a database tuning problem is that there is seldom (if ever) a single best way to set up a database. To understand this principle, consider the very simple case of a table holding customer information: should you create an index on the LastName column or not? The answer is that it depends on whether and how often you search by last name, sort by last name, or join the Customer table to other tables by the LastName column. If you don't do any of those things, than an index on this column is pure overhead. On the other hand, if every other database operation involves looking up customers by last name, it would be extremely inefficient to not index that column.
The Tuning Advisor handles these issues by introducing the concept of a workload. A workload is simply a mix of SQL statements that indicates the "typical" uses of your database; it gives the Tuning Advisor something to consider when deciding what recommendations to make. You can supply a workload in several ways. If your database isn't in use at all yet, you may have to deliver the workload as a simple file of SQL statements, typed directly into SQL Server Management Studio and saved to disk. In this case, the workload is your best guess as to how you think the database will be used.
But if the database is in active use, you can do better than that. The other way to generate a workload is to use the SQL Server Profiler utility to capture a trace file, using the tuning template. A trace file records the actual activity in your database over a period of time. If you record a substantial trace file - say, five megabytes or more, captured over a period of days - then the Tuning Advisor can tell you what changes would have made your database more efficient with the use that the database really got. I recommend that you follow this path to tune with real-world data, rather than guesswork, whenever possible.
The Tuning Process
To launch the Database Engine Tuning Advisor, select Microsoft SQL Server 2005, Performance Tools, Database Engine Tuning Advisor from your All Programs menu. When the utility launches, you'll need to connect to the server where the database that you want to tune resides. The Tuning Advisor will then retrieve a list of all databases on the server and wait for you to tell it what to do.
Tuning Advisor is capable of evaluating workloads that cross database boundaries (it looks for USE DATABASE statements within the workload). After choosing your workload, you can select both the database where the tuning will be conducted (that is, the database that the Tuning Advisor connects to when it starts running SQL statements) and the databases to actually tune. As you can see in Figure 1, you can also choose to limit your tuning to individual tables within your target databases. This is a useful feature when you're trying to tune only parts of a very large database.
See full detail: http://www.developer.com/db/article.php/3607186
Comments