BLS keeps databases in tune
Like a string quartet, databases must stay in tune if they're going to put on a good performance.
By Patricia DaukantasGCN StaffLike a string quartet, databases must stay in tune if they're going to put on a good performance.A database administrator at the Bureau of Labor Statistics has learned that the fine-tuning goes more harmoniously if the utilities that come with the database are supplemented with extra tools.To keep databases humming in the bureau's Office of Technology and Survey Processing, Bakul Patel last fall began using ProActive DBA 4 Classic from White Sands Technology Inc. of Canoga Park, Calif.Patel, a BLS computer specialist, said he used ProActive DBA while he was working as a database administrator, before he moved into his current front-office job a few months ago.The databases that Patel supervised in his previous position hold raw data about U.S. employment and jobs that has not yet been analyzed.The Sybase SQL Server 11.5.1 databases reside on two Sun Enterprise 3500 servers and one Sun Enterprise 3000 server running SunSoft Solaris 2.6 and 7.Many database administration tasks are routine. They include verifying data integrity, checking disk usage, and updating index statistics on the databases and tables, Patel said.Other day-to-day administration chores depend on the type of applications and the type of use, he said. The more often users make inserts and updates to a database, the more often the administrator must update its index statistics.Also, the administrator must guard against data corruption, 'because if you back up a corrupt database, when you do a restore, it's kind of useless,' Patel said.The ProActive DBA software was still in Version 3.x when Patel saw it demonstrated at a Sybase Inc. users' conference in Washington last year and decided to buy a copy.When Version 4 came out shortly thereafter, BLS purchased two more copies for its other Sun servers.Patel said he still uses the regular backup and estore functions within the Sybase software, but before acquiring ProActive DBA he hadn't tried any other tools to optimize the databases.Database performance is gauged by how long it takes to execute a given query. 'You could have a fast machine, but if your query is not written optimally or the database hasn't been tuned properly, a faster machine is not going to help,' Patel said.When queries execute, the database uses the indexing information to get to the data quickly. If the indexes are not laid out properly and the hard disks are highly fragmented, database performance suffers. How often defragmentation is necessary depends on the database application.'If it's a read-only or decision support system application, you may not have to do that much,' he said. 'But if you've got an online transaction processing application where you're doing lots of inserts and updates, you might have to defragment more often.'Patel said his group's databases fell between those extremes, doing moderate numbers of inserts and updates, so it was adequate to run the defragmentation utility once a month. The defrag part of ProActive DBA works essentially like the defrag utility on a PC, he said, though it's a bit more complex.Along with the defragmentation utility, there is a task scheduler for creating maintenance scripts. For example, the database administrator can write a script to defragment the drives every Sunday at 1 a.m., Patel said. Users can choose to be notified when the task has been completed.Although Patel's group uses ProActive DBA to optimize a database running under Unix, the tool actually resides on a workstation running Microsoft Windows 9x or Windows NT elsewhere on the BLS network.Besides Sybase databases, ProActive DBA 4 supports Microsoft SQL Server databases up to Version 6.5.Patel said he has not compiled statistics to document how much the ProActive DBA tool speeds up database performance. 'I never had time to sit down and do a complete end-to-end analysis, but the users seemed to be satisfied that performance was much better,' he said. 'It does pretty much what I thought it should do.'Patel said the software cost the agency about $2,500 per copy. He would like to evaluate the next version of ProActive DBA when it comes out, he said, although there are no specific plans to upgrade the office to Version 5.
Extra tools supplement built-ins to keep the agency's databases humming
Try new tools
 | ProActive DBA software graphically models the internal storage structure of a Sybase SQL Server database with colored blocks that represent the pages that store data rows. |
Try new tools
 | Many database administration tasks are routine, says BLS computer specialist Bakul Patel. |
NEXT STORY: POWER USER