Tier II Broadband Company Database Performance Case Study

How to handle 5,840,000,000,000,000 transactions and the associated 373,760,000,000,000,000 detail records yearly.

Challenge:

Could not process approximate 124,000,000 transactions and 7,936,000,000 associated detail records during monthly billing cycle. The bill cycle would run in excess of 40+ hours before failing.

Customer Hardware:
Current install of SQL Server 2000 on a 32-bit Intel based system with 4 SCSI II disks in a non-striped array. 2 hyper-threaded Pentium processors and 2 Gig of RAM.

Identified potential constraints:

• Memory limitations
• Disk I/O
• Poor indexing strategies causing table scans
• Poorly written TSQL with inappropriate joins.
• Too much data – no archiving of old data from production OLTP environment
• Known data model issue with vendor software. 2 tables contained the majority of the data required for billing cycles. All other supporting tables represented less than 3% of all data within the database.

Test environment:

Hardware: Install SQL Server 2005 Enterprise Edition on a 64-bit AMD system with 2 dual-core processors. 4 SATA disk drives in a non-striped array. 4 gig of RAM.

Database was loaded and upgraded from SQL Server 2000 to SQL Server 2005.
The tables containing transaction and transaction detail records were analyzed for query performance and partitioning. This was facilitated by running the profiler tool during the Billing cycle to capture the suspect transactions for further analysis.

4 key queries were captured and identified for further analysis and potential optimization.

During initial analysis of these queries it was determined the existing non-clustered index did not cover the specific clauses of the queries and needed to be expanded to include another column. It was also determined the added column did not conform to the standard index limitations of 8k and needed to use the new “include” function in SQL 2005. Upon rebuilding the index with the included column, the 4 queries were individually tested numerous times with a significant increase in performance. The chart below only shows a random sample of improved times, but the overall query performance indicated is for the entire test set.

This was an average improved query performance of 94.4%.

It was noted that billing and reporting was performed on a monthly basis, with no more than 1 year of data required for historical OLTP reporting. The next step for performance improvement was partitioning of the data based upon a monthly date range. The below function and scheme was created to facilitate the partition.

The partition function was defined as:
CREATE PARTITION FUNCTION [pf_date](datetime) AS RANGE RIGHT FOR VALUES (N'2006-01-01 00:00:00', N'2006-02-01 00:00:00', N'2006-03-01 00:00:00', N'2006-04-01 00:00:00', N'2006-05-01 00:00:00', N'2006-06-01 00:00:00', N'2006-07-01 00:00:00', N'2006-08-01 00:00:00', N'2006-09-01 00:00:00', N'2006-10-01 00:00:00', N'2006-11-01 00:00:00', N'2006-12-01 00:00:00')

The partition scheme was defined as:
CREATE PARTITION SCHEME [ps_date] AS PARTITION [pf_date] TO ([data1], [data2], [data3], [data4], [data5], [data6], [data1], [data2], [data3], [data4], [data5], [data6], [data1])

Due to disk configuration (and limitation) in the test environment it was determined to have the first month of each quarter on the same disk as outlined below. In a production environment it is assumed a true RAID system would be utilized with proper striping and configuration.

Since the table already existed in the database it was determined the best approach to partition the table would be to create a clustered index on the table across the partition scheme. This would efficiently move the data to its aligned partition.

The existing non-clustered index was dropped before the clustered index was created. It was later recreated to be aligned with the clustered index partition.

Several billing cycles were run against the new partitioning architecture and a significant increase in performance was observed. The chart below only shows a random sample of improved times, but the overall billing cycle performance indicated is for the entire test set.

The average billing cycle run on the test server increased 72.5%

Conclusions:

While the increased processing power, 64-bit architecture, and added memory played a major role in the optimization of the billing process, the index optimization and table partitioning based upon the month/quarter data sets was a significant contributor to overall performance increase.

Through this small testing process the company was able to verify the much needed upgrade to newer hardware, database versions, and data storage architecture. They were also able to complete their billing cycle in a timely fashion, bringing in much needed revenue. The ROI on the improved process significantly out-weighed the investment in new hardware/software technology.

This may appear to be a simple diagnosis, but it is not so apparent when you’re not familiar with the available features of your database environment. Many times it is the simplest solution to an issue that brings the greatest improvement in performance. We are often tempted to perform a ‘forklift’ upgrade to our hardware environment, but many times that only masks the underlying problem and will only compound the issue in the future. With a little bit of analysis on the processing elements of your applications, it can often expose the simple changes required to get the performance sought.

So long as the application being used supports the newer deployment options, every application should consider taking advantage of new features – with proper education on the consequences of choosing the wrong feature.

Contact iGneiTe directly for further questions and analysis of your database environment.

Send Page To a Friend