DBAs use database partitioning for two main reasons: to enforce security by limiting access to data and to enhance performance. In "Horizontal and Vertical Partitioning," February 1997, http://www.winnetmag.com, InstantDoc ID 98, Robert Schneider explains how to combine replication with vertical (column) and horizontal (row) partitioning to deliver only relevant sections of a database to replication subscribers. DBAs can use partitioning to separate current data from historical data, which lets a DBA set up a transactional server for current data and an OLAP or reporting server for historical data. The greatest benefit of separating one database into two is that you can tune each database or server for its primary purpose instead of trying to make one server work well for two different activities.
Usually, you wouldn't consider partitioning a SQL Server 2000 database until it grew to hundreds of gigabytes or even terabytes because SQL Server 2000 has powerful built-in optimization capabilities that, combined with proper indexing, should let you keep all your data on one server. But recently, I worked with a client whose small database was performing poorly. In this situation, circumstances and software constraints led me and the company's IT staff to choose database partitioning to improve performance—with great success.
Analyzing the Problem
Tom is the head of IT for my client—a small company that does human-resources (HR) processing. Several years ago, the company purchased a semi-customizable client-server software solution that has become mission-critical. The software supports the company's main functions and is available 24 * 7. The company uses the software for managing accounts and customer information, processing HR data, reporting and statistical analysis, and invoicing. When Tom called me, his database was outgrowing his server's capacity. Tom's company had just spent a lot of money to upgrade the system's hardware and networking components, and the business was growing—but so was the data.
Tom was in a fix: He'd worked hard to streamline and optimize existing technology to keep costs down so that the company could thrive. Even so, Tom's boss wasn't inclined to spend more money to enhance the technology because this mission-critical system was now an orphan product—one whose vendor had gone out of business, leaving Tom with no product support for the software. Tom planned to eventually replace the entire system, but meanwhile, he had to keep the existing database server functioning while supporting the ever-increasing operational load.
The primary production database contained 5GB of data, and Tom's users were encountering slow response times for complex queries and data updates. For example, the job of printing customer invoices (which happened three times each month) took half a day and further bogged down server response time.
As part of the discovery and systems-analysis process, Tom and I defined several solutions that could relieve the overburdened production server. We could tweak indexes; optimize the embedded business logic, which was partly implemented as stored procedures on the production database; convert the program-enforced relationships to Declarative Referential Integrity (DRI); move data to separate filegroups; partition the data into current and aged data tables on one database; or partition the database itself. All but the last two options would have given incremental relief to the server and the users, and in a less time-critical situation, we might have implemented some or all of these techniques. But at the current growth rate, Tom realized that his server would exceed capacity in only about 6 weeks, so we needed to implement an immediate solution. Tom opted for partitioning the database, figuring that this would give maximum relief and extend the life of his servers for a year or more.
Before choosing partitioning, we considered some other, more obvious choices. With smaller databases, the most obvious choice for performance tuning is to tweak the database's indexes. Index tuning is typically the best thing you can do to improve performance and the least intrusive change you can make to a production application—as long as it works. But a few factors led us to reject this solution. First, we performed an index analysis, which told us that the tables were already adequately indexed. We experimented with alternative indexing schemes, but nothing improved performance as much as Tom wanted, and in some cases, the changes slowed down data entry. We strongly suspected that logic embedded in the client programs on the user desktop was forcing table scans, but without the source code, we couldn't confirm our theory. In a table scan, SQL Server reads all rows in the table until it finds the data needed to resolve the query instead of using indexes to directly access the data—so table scans could be part of the performance problem.
An option that might have helped more than additional indexing would have been judicious use of filegroups—separating system tables from user tables, clustered indexes from nonclustered indexes, memo and tabular data from text and image data, then spreading the filegroups across separate spindles. However, we deduced that the data location was hard-coded into the client programs because the client applications failed when we tried to change the filegroups. So we had to leave all the data in the primary filegroup on the production database.