Monitoring Data File Fragmentation
Over time in a busy database as records are created, updated, or deleted, data can become fragmented, lengthening times for file access and transaction response. This fragmentation differs from file system fragmentation on a hard disk because it occurs within the data file itself. As a developer or DBA, you may know when a file is likely to fragment from heavy use, but in some systems, you may be guessing.
Defragmenter is a utility that helps you solve this problem by finding data fragmentation and enabling you to correct it. Defragmentation rearranges records and rebuilds indexes in data files and removes unused space so that data can be efficiently accessed again. Defragmenting a file does not alter its data in any way, and records can be created, read, updated, or deleted while their files are being defragmented. You can use Defragmenter features during database engine execution with no need for down time or disruption of business operations in most cases.
Opened as a graphical tool in PSQL Control Center, Defragmenter shows data files in use, including their number of reads and writes so that you can quickly find ones under heavy use. To add files or tables to the Watch List tab, select them and then drag-and-drop, click a button, or right-click and choose the command. If you know of Btrieve files in other locations, you can browse to them and add them to be watched as well.
Defragmenter also runs as the dbdefrag utility.
The following topics cover the use of Defragmenter:
•
Deciding When to Defragment
•
When Defragmenter Cannot Be Used
•
Accessing Defragmenter
•
Defragmenter GUI
•
Setting Defragmenter Preferences
•
Setting Automatic Refresh Interval
•
Arranging Tabs
•
Defragmenter Tasks
•
Command Line Interface Defragmenter
See Automatic Defragmentation for information about using Defragmenter in an unattended mode.
Deciding When to Defragment
Defragmenter helps you analyze your data files for statistics that may explain loss of performance. High statistics for file size and percentage fragmented, unused, and not in order can explain loss of database performance. By defragmenting a file or table, you can reduce all four of these numbers, which are explained in more detail under the Watch List topic. Transactions generally run more quickly against a newly compacted, reindexed file, restoring efficiency, capacity, and performance.
Every database is different, so it isn’t possible to make recommendations that apply to everyone. The decision to defragment depends on your knowledge and experience with your own database and its applications. However, some general considerations are worth making:
•
Performance remains constant in read-only databases, but as reads and writes occur over time, analysis of watched files will show rising values for statistics. Changes in database behavior may also become noticeable, such as queries and reports running more slowly.
•
Bulk delete actions often greatly increase unused space in files, which can be corrected by defragmenting them.
•
Reads and writes continue uninterrupted during defragmentation. However, for the following reasons you may want to consider low traffic times for your defragmentation strategy:
•
Defragmentation uses resources that normally are fully devoted to operations on data files, so the extra demands on the engine may cause small effects on performance in high traffic periods.
•
Defragmenting must very briefly lock each data file it runs against. In low traffic periods, this lock may not be noticeable, but in high traffic times, clients may wait slightly for responses.
If you do not see performance improvements after defragmenting, then the problem likely lies elsewhere and requires a different diagnosis and solution.
When Defragmenter Cannot Be Used
In some cases, Defragmenter cannot be used or will be canceled by database activities of higher priority.
•
Defragmentation of files on clients is not currently supported. Files can be defragmented only on the local server where the tool runs.
•
The disk space needed to defragment a file is displayed in the analysis results. Defragmenter requires free disk space equal to the size of the file, plus a small amount for defragmenting operations. Files undergoing high write activity may require more space.
•
If free disk space is too low, then the engine denies attempts to start defragmentation and returns error code 126. If space becomes low on a disk volume while defragmentation is executing there, the engine cancels defragmentation of files on that volume and returns 126.
•
If defragmentation starts but then the disk volume where defragmentation is executing becomes full, the engine cancels defragmentation activity on that volume and returns error code 18.
•
Use other PCC tools to make changes to indexes and metadata, including:
•
Creating, modifying, or deleting indexes
•
Modifying a schema, such as creating, modifying, or deleting a column
•
Deleting databases, tables, or data files
•
Changing database, table, or file names
•
Setting or clearing owner names
•
Creating, modifying, or deleting bound databases
•
Backup Agent operations cannot be performed on a file undergoing defragmentation. To perform defragmentation, you must first exit from Backup Agent.
•
Continuous operations for data backup cannot be performed on a file undergoing defragmentation because their higher priority cause defragmentation to be canceled. To perform defragmentation, you must wait for these operations to finish.
•
Defragmentation is not currently supported for a server engine in an environment where VSS has performed backup operations
•
If you need to alter data file properties, such as page size, compression, or file version, use Tools > Rebuild instead of Defragmenter.
•
Defragmenter stops and exits automatically if it finds corrupted or erroneous records. In these cases, use Rebuild to recover the data. Freshly rebuilt files do not need defragmenting.
•
For DataExchange users: Defragmentation does not change the system data and key used by DataExchange. After defragmenting, you do not need to run the table synchronization and check utility (dxsynctables).
Accessing Defragmenter
Defragmenter runs in three ways:
•
In PSQL Control Center, select Tools > Defragmenter. For details, see Defragmenter GUI.
•
At a command prompt, run dbdefrag. Note that this differs from defrag, the Microsoft utility for defragmenting Windows file systems. For details, see Command Line Interface Defragmenter.
•
The Automatic Defragmentation option can be turned on in the Performance Tuning configuration settings for a server engine. For details, see Automatic Defragmentation.
Defragmenter GUI
The following screen shot shows the Defragmenter GUI. The table after the image describes the GUI objects. Click an area of the image for which you want more information.
 
GUI Object
Description
File menu
Allows you to set the refresh rate for the Watch List tab, exit Defragmenter, or exit PCC.
Window menu
Allows you to return to the PCC window or to set preferences.
Help menu
Allows you to access documentation and logs and to check the PSQL version.
 
Table 48
Defragmenter Icons
Icon
Meaning
Cancel all defragmentation
Allows you to manually stop all analysis or defragmentation. Cancellation means that any file currently being analyzed will show no new statistics. Files being defragmented but not yet finished will be unchanged. Files awaiting analysis or defragmenting will not be done.
In the upper left corner of the window, click this icon to cancel all active analysis and defragmentation operations. You do not need to select any files to use this option.
Note: You can cancel all operations from the Defragmenter window even if they were started at a command prompt, and vice versa.
Set the automatic refresh rate
Opens a dialog to set how often the Watch List tab updates progress. The value is in seconds. The default is 5.
Refresh
Allows you to manually refresh the list of items in the Files In Use, Tables, or Btrieve Files tabs. Note that setting the automatic refresh rate affects only the Watch List tab.
Add to Watch List
Enters selected items in the Watch List tab. Use Ctrl-click to add one item at a time to the selection or shift-click to select a range. You can also use Ctrl-A to select all items.
Analyze file
Gathers and displays fragmentation statistics for items selected in the Watch List tab. Use Ctrl-click to add one item at a time to the selection or shift-click to select a range. You can also use Ctrl-A to select all items. Analysis does not change the file being analyzed.
Defragment file
Starts defragmentation for items selected in the Watch List tab. Use Ctrl-click to add an item to the selection or shift-click to select a range. You can also use Ctrl-A to select all items.
Cancel defragmentation
Allows you to selectively stop analysis or defragmentation. Canceling means that files being analyzed show no new statistics. Files being defragmented but not finished are unchanged.
In the icons to the right above the Watch List, this icon becomes available when files in the Watch List are undergoing analysis or defragmentation. Selecting one or more files and clicking this icon cancels the operation for those files. Use Ctrl-click to add one item at a time to the selection or shift-click to select a range. You can also use Ctrl-A to select all items.
Remove file
Removes an item from the Watch List tab. Use Ctrl-click to add one item at a time to the selection or shift-click to select a range. You can also use Ctrl-A to select all items.
Select columns to display
Opens a dialog of check boxes to customize the columns in the Watch List tab. The default is for all columns to be displayed.
Show or Hide details
Toggles the display of the Details pane.
Defragmenter Tab Views
Defragmenter presents database tables, files, and other objects in tabs in the Defragmenter window.
•
Files In Use
•
Tables
•
Btrieve Files
•
Watch List
The tabs in the window can be rearranged. See Arranging Tabs.
Files In Use
The Files In Use tab lists items currently or recently opened by the PSQL engine. You can add files shown in this tab to the Watch List tab to monitor, analyze, and defragment. It is best practice to select only files involved in routine database execution, since they are most likely to contribute to performance issues.
In the Files In Use tab, you can sort the list by clicking the Reads/Writes column heading. Sorting the list helps to identify which files are in heaviest use and possibly more fragmented.
Tables
The Tables tab resembles PSQL Explorer, showing nodes for the server where Defragmenter is running. You can add any file shown in this tab to the Watch List tab.
Btrieve Files
The Btrieve Files tab is a file explorer for drives and directories on the file system. In addition to data files, this tab also lists items not involved in routine operations, such as dbnames.cfg and .ddf files. It is possible to add these files to the Watch List tab to analyze and defragment, but unlike data files, attempting to change them while they are in active use returns errors. These metadata objects are at low risk of fragmentation because they generally do not change in a production environment. In the rare case of their needing defragmenting, it can be done during down-time maintenance.
Watch List
Items in the Watch List tab display statistics after an Analyze or Defragment action has been applied.
Statistic
Measures
Interpretation
File size (MB)
File size in megabytes
The longer a file has been in use, the greater the amount of unused space it may contain, increasing its size.
Smaller file sizes typically take less time to defragment, unless they have a large number of indexes.
For Vx users, the larger size of fragmented files may be a concern, so defragmentation may help to stay within your license limit.
% Fragmented
Percentage of data residing in blocks separated by unused space
Transactions over time can result in pages containing no data. The more these pages are intermixed with actual data pages, the greater the fragmentation.
A lower percentage indicates fewer, larger data blocks stored closer together, allowing more rapid reads and writes.
Note: This statistic is not supported for Btrieve 6.x and 7.x files. You can analyze and successfully defragment these earlier file versions, but no measurements can be shown for these statistics.
% Unused
Percentage of unused space
Unused space is often created when a file is modified by insert, update and delete operations.
The lower this percentage, the more compact the file, allowing more rapid reads and writes.
Note: This statistic is not supported for Btrieve 6.x and 7.x files. You can analyze and successfully defragment these earlier file versions, but no measurements can be shown for these statistics.
% Not in Order
Percentage of records not stored sequentially
Inserts over time result in a widening mismatch between the logical order of pages and their physical locations, increasing the time needed to locate data.
A lower percentage generally gives higher performance for actions such as table scans on large files.
Note: In some files, the lowest possible percentage not in order may be higher than zero. Further defragmentation does not decrease this statistic because records in the file are already stored as efficiently as possible.
Last Run
Date and time of last action
Action can be Analyze or Defragment.
Status
Report from last action
Blank for newly added item. Typically notice of analysis or defragmentation completed.
Table
Logical location
Database and table or file name
Path
Physical location
File system path
Details
Under the Watch List tab, a Details pane summarizes the statistics in the Watch List columns, but with some additional information:
•
Time taken to defragment the item
•
Before and after comparisons of file size and percentage fragmented, unused, and not in order
Selecting an item in the Watch List tab displays its details. If you select more than one item, details are shown for the first one highlighted.
Setting Defragmenter Preferences
You can set preferences for Defragment from either the utility itself or from PSQL Control Center. In either GUI, select Window > Preferences > PSQL > Defragmenter to open the Defragmenter tab of the Preferences dialog box.
The following table shows the types of preferences you can set and what they do.
Preference
When selected...
Remember window layout.
Saves the current arrangement of Defragmenter tabs when you exit the tool, as well as the overall window size, height, and position.
Don’t warn about incompatible actions during multi-select operations.
Stops the dialog box from showing when actions will be unevenly applied to items of different types.
Don’t warn about canceling defragmentations again.
Stops the dialog box from showing a warning when you cancel an analysis or defragmentation.
If you selected the check box in the dialog to confirm canceling, this box also becomes checked. To make the confirmation dialog appear again, clear this check box.
Don’t warn about canceling all defragmentations again.
Stops the dialog box from showing a warning when you cancel all analysis and defragmentation operations.
If you selected the check box in the dialog to confirm canceling, this box also becomes checked. To make the confirmation dialog appear again, clear this check box.
Setting Automatic Refresh Interval
Information in Defragmenter can be refreshed automatically at a configured interval. The default is to refresh every 5 seconds. Be aware that refreshing too many files too often may slow performance.
Arranging Tabs
The Defragmenter tabs can be rearranged and separated for your convenience. To move a tab, drag and drop the tab label where you want the tab to be. If the setting to remember window layout is selected in the Defragmenter preferences, your tab arrangement will be kept.
Defragmenter Tasks
This topic provides steps for using features for various tasks in the Defragmenter GUI.
►
To set automatic refresh interval
The automatic refresh rate applies to the statistics in the Watch List tab. Refreshing of lists in the other tabs is done manually by clicking the Refresh icon or right-clicking and selecting Refresh.
1
Click File > Set Automatic Refresh Rate or click the Set the Automatic Refresh Rate icon to open the refresh rate dialog box.
2
In the dialog box, enter the number of seconds between each refresh. The value must be an integer greater than 1.
3
Click OK.
►
To add a file or table to the Watch List tab
Adding a file or table to the Watch List tab can be done in a number of ways:
•
In Defragmenter, select one or more items in the Files In Use, Tables, or Btrieve Files tabs and then click the Add to Watch List icon at upper right in the tab. Use Ctrl-click to add one item at a time to the selection or shift-click to select a range. You can also use Ctrl-A to select all items.
•
In Defragmenter, right-click an item in the Files In Use, Tables, or Btrieve Files tabs and select Add to Watch List.
•
In Defragmenter, drag and drop an item from any tab to the Watch List tab.
Items added to the Watch List tab appear at the end, after the items already there.
►
To analyze a file or table
Analysis reports statistics that determine which items may need defragmenting.
1
In the Watch List tab, click items to select them. Use Ctrl-click to add one item at a time to the selection or shift-click to select a range. You can also use Ctrl-A to select all items.
2
Do one of the following:
•
In the Watch List tab at top right, click Analyze.
•
Right-click the selected items and click Analyze File.
The Watch List tab reports analysis results by updating its columns. For the first item selected, the Details tab provides individual status, including the free disk space needed to defragment the file.
►
To defragment a file or table
1
In the Watch List tab, click items to select them. Use Ctrl-click to add one item at a time to the selection or shift-click to select a range. You can also use Ctrl-A to select all items.
2
Do one of the following:
•
In the Watch List tab at top right, click Defragment.
•
Right-click the selected items and click Defragment File.
The Watch List tab reports defragmentation results by updating its columns. For each item selected, the Details tab below provides individual status. For multiple selected items, the first one selected is shown.
►
To cancel analysis or defragmentation
During analysis or defragmentation, select a file in the Watch List and click the Cancel Defragmentation icon on the right. In the dialog that appears, click Yes to confirm. The Defragmenter operation continues until you click Yes.
Alternatively, you can click the Cancel All Defragmentations icon on the left. This icon stops all analysis and defragmentation activity, while the one on the right is for selected files in the Watch List that are undergoing analysis or defragmentation.
After cancellation, status for some items in the Watch List tab may be Analysis: Completed or Defrag: Completed, with new statistics, while others display Analysis:Canceled or Defrag: Canceled and blank statistics. For a canceled item, the Details tab description gives the time that passed until the moment of cancellation.
Successful cancellation means that analysis was stopped or that fragmentation in the file or table is unchanged.
*Note: You can cancel all operations from the Defragmenter window even if they were started at a command prompt, and vice versa.
►
To remove a file or table from the Watch List tab
1
In the Watch List tab, click items to select them. Use Ctrl-click to add one item at a time to the selection or shift-click to select a range. You can also use Ctrl-A to select all items.
2
Do one of the following:
•
In the Watch List tab at top right, click the Remove File icon .
•
Right-click the selected items and click Remove File.
►
To select columns to display
You can set every column in the Watch List tab to be shown or hidden.
1
In the Watch List tab at top right, click the Select Columns to Display icon .
2
By default, all choices are selected. Make any needed changes and click OK or click Cancel.
►
To hide or show details of analysis or defragmentation
You can toggle to hide or show the Details tab by clicking the Click to Hide Details icon .
Command Line Interface Defragmenter
The CLI utility dbdefrag provides the same functions as its GUI counterpart, except that there is no watch list. Defragmentation actions can be started, checked for status, or canceled from either interface.
The following table gives command line options.
Command
Description
dbdefrag files
Start defragmentation on one or more files. You may use asterisks as wildcards. Displays the number of records and keys processed. You can use Ctrl-C to cancel. In addition to path names, files can also be specified as a URI using the syntax brtv://user@host/dbname?parameters, as described under Database URIs.
dbdefrag -background files
Start defragmentation on one or more files as a background process. You may use asterisks as wildcards.
dbdefrag -cancel files
Cancel the currently running analysis or defragmentation for one or more files. You may use asterisks as wildcards. Must be executed at a separate prompt unless the defragmentation is running in the background.
dbdefrag -cancelall
Cancel all active analysis and defragmentation operations. Must be executed at a separate prompt unless the defragmentation is running in the background.
Note: You can cancel all operations from the Defragmenter command prompt even if they were started in the Defragmenter GUI window, and vice versa.
dbdefrag -status files
Show defragmentation status for one or more files. You may use asterisks as wildcards.
Displays the following for the most recently completed defragmentation:
•
Status: Complete
•
Started: yyyy-mm-dd hh:mm:ss
•
Finished: yyyy-mm-dd hh:mm:ss
•
Time taken: nh:nnm:nns
Displays the following for a defragmentation currently running:
•
Defragmentation status: In Progress
•
n out of n records processed (n%)
•
n out of n keys processed (n%)
Displays the following for a canceled defragmentation:
•
Status: Defragmentation Canceled
•
Time taken: nh:nnm:nns
File status remains available until the engine is restarted. After an engine restart, you can find status information in the PSQL event log (pvsw.log).
dbdefrag -analyze files
Show fragmentation statistics for one or more files. You may use asterisks as wildcards. Returns the following:
•
% Fragmented
•
% Unused
•
% Not in Order
•
File Size (MB)
•
Estimated disk space needed for defragmenting (MB)
Analysis does not change the file being analyzed.
dbdefrag -ignore
Ignore errors during file processing. You can add this option to any command. It is useful when you are analyzing or defragmenting multiple files. Without the -ignore option, if one of the files being analyzed or defragmented returns an error, then the error is logged and analysis and defragmentation operations stop for remaining files. If you use the -ignore option, then the error is logged and analysis and defragmentation continue on the rest of the files.
dbdefrag -help
Display the list of command options.
Using dbdefrag
The following example shows a series of steps to demonstrate use of dbdefrag on a large .mkd file.
1
Analyzing
C:\ProgramData\Actian\PSQL\Examples>dbdefrag -analyze mybtrievefile.mkd
  Fragmented: 9%
      Unused: 4%
Not in Order: 100%
   File Size: 713 MB
2
Defragmenting
C:\ProgramData\Actian\PSQL\Examples>dbdefrag mybtrievefile.mkd
1125 out of 1195242 records processed (0%)
87021 out of 1195242 records processed (7%)
170910 out of 1195242 records processed (14%)
255393 out of 1195242 records processed (21%)
339805 out of 1195242 records processed (28%)
404202 out of 1195242 records processed (33%)
487928 out of 1195242 records processed (40%)
572585 out of 1195242 records processed (47%)
655802 out of 1195242 records processed (54%)
716472 out of 1195242 records processed (59%)
800406 out of 1195242 records processed (66%)
883729 out of 1195242 records processed (73%)
947475 out of 1195242 records processed (79%)
1032061 out of 1195242 records processed (86%)
1116015 out of 1195242 records processed (93%)
1185831 out of 1195242 records processed (99%)
1195242 out of 1195242 records processed (100%)
1 out of 2 keys processed (50%)
2 out of 2 keys processed (100%)
Defragmentation complete.
Time taken: 0h:24m:57s
3
Checking Status from Another Prompt or When Running in Background
C:\ProgramData\Actian\PSQL\Examples>dbdefrag -status mybtrievefile.mkd
Defragmentation status: In Progress
1053373 out of 1195242 records processed (88%)
0 out of 2 keys processed (0%)
4
Checking Status After Defragmenting
C:\ProgramData\Actian\PSQL\Examples>dbdefrag -status mybtrievefile.mkd
    Status: Complete
   Started: 2014-08-08 11:08:28
  Finished: 2014-08-08 11:33:25
Time taken: 0h:24m:57s
5
Analyzing Again
C:\ProgramData\Actian\PSQL\Examples>dbdefrag -analyze mybtrievefile.mkd
  Fragmented: 0%
      Unused: 0%
Not in Order: 27%
   File Size: 682 MB