A “how to” document is generally brief and describes the steps involved in performing an action or procedure. Below are some samples.
How to Use DSX
DSX is a diagnostic utility tool that is bundled with your Crystal Reports software. When an error occurs, DSX creates a log that lists all of the files (DLLs, OCXx, etc.) that the computer was running at that point in time.
DSX comes in a 16-bit and a 32-bit version. The latest 16-bit version of DSX will run on Windows 95/98 and NT. The latest 32-bit version of DSX (DSX32.exe) will only run on Windows 95/98. Seagate distributes a similar utility for Windows NT called PSPY. Before attempting to run DSX, check your version to make sure it is compatible with your operating system.
For additional information on DSX or PSPY, please see Seagate Software’s web site.
How to generate a DSX file:
- Check to be sure DSX.EXE is located on the hard drive of the computer on which you are working. The executable may be located in the same directory where Crystal Reports is installed (i.e., C:\CRW). The executable must be present on the hard drive of the computer you are using to work properly.
- Close out of all applications except those relevant to reproducing the problem you are experiencing.
- To accurately list the files used by the application, DSX must be running at the same time that the error occurs. To create a complete list of files launch DSX then print/preview your report.
- A list of files will be generated. This list will include the following: filename, file size (in bytes), date/time modified, version number (if possible), path to the file location.
- Save a copy of the file with the extension *.lst (you can specify the path in the File > Save As dialog box) and send it for analysis. Do not choose the “Save to Text” option. If you do not save the file with the extension *.lst, the output cannot be compared to another file using DSX.
Database Index Tuning
Indexes should be monitored periodically by running DBCC ShowContig on each table in the database to determine the current health of the database. The following is an example of how to perform periodic monitoring of database tables/indexes.
- A script is run that cycles through all tables and returns the DBCC ShowContig results. These results are then analyzed, paying close attention to Scan Density, logical and extent fragmentation, average bytes free per page, and average page density (full). The following results should be used as a guideline for determining whether database tuning should be performed:
Scan Density = 90-100%: Values less than 90% are an indication that maintenance should be performed. Smaller tables may fall below 90%, but should not be a cause for performing maintenance.
Logical and Extent Fragmentation = 0-10%: Values greater than 10% are an indication that maintenance should be performed.
Average Bytes Free per Page and Average Page Density (full): This should show that there is sufficient empty space to accommodate inserts into each page of data (fill factor).
- Appropriate Fill factors should be applied to each table with clustered indexes based on how frequently records are inserted into the tables and at what points these records are added. Tables that have records that can be inserted at any point inside the table and not simply at the end of the table should provide a smaller fill factor to accommodate these inserts. The following fill factors can be used as a guideline for setting up appropriate fill factors for tables:
Tables performing sequential data inserts: fill factor 90 (10% free space)
Tables performing non-sequential data inserts: fill factor 80 (20% free space)
- Table/Index statistics should be kept up-to-date. This can be set at the database level in SQL Server by enabling Auto Create Statistics/Auto Update Statistics , or creating a job to run UPDATE STATISTICS, particularly at times when large amounts of data are being inserted into the tables.
- Once it has been determined that the database maintenance and tuning is necessary, DBCC DBREINDEX should be performed on each table in the database, while specifying the appropriate fill factors. This process will also automatically update the statistics as well. This process should only be performed during times when the database is not in use. In addition to re-indexing at times when the database showcontig results deem it appropriate, the re-indexing can also be performed periodically. A good time frame to use re-indexing would be to perform this task weekly, preferably over the weekend.
Avoid using the Database Maintenance Wizard tool that comes with SQL Server to perform the database maintenance and tuning. This method has shown inconsistencies in properly tuning and reporting problems that may occur during this process. The recommended approach would be to create a SQL Server job to do the re-indexing of each table at a specified interval.