SAP Database Performance: Database Allocation
The Database Performance: Database Allocation monitor gives you current information related to the allocation of space in the database. The initial screen presents the most important information for:
· The entire database
· Individual database files
· Tables, indexes and stored procedures
In addition to the information presented, the monitor offers a set of functions that help you to analyze and check various properties of the database, tables or indexes in greater detail.
To access the Database Allocation monitor, enter the transaction code DB02.
The information displayed in the initial screen of the monitor is grouped into three different categories: Database System, Database, and Tables, indexes, stored procedures.
The date and time when the information was generated is shown at the top of the screen. A Refresh button is provided to update the information.
Name of the database, the SAP system and the time when the information on the screen was generated.
● Space analysis of the SAP database and transaction logs showing space allocated, free space and used space.
● List of basic options and the recovery model that have been set for the database. By default, automatic creation and update of statistics is set for an SAP system.
● List of all files of the SAP database and the free space available on disk.
Specifies the space allocated for individual files, the amount of space used and other data.
Tables, indexes, stored procedures
● Specifies how many of these objects exist in the database for the SAP system and their size.
● Displays the schema name.
If the SAP system is installed in a schema other than dbo, you can toggle between the objects in this schema and the objects in all schemas.
In addition to summarizing significant information, the monitor also offers the following functions to analyze and check the database, tables or indexes.
On database level:
· DBCC checkdb
Executes the SQL server DBCC CHECKDB command to check the consistency of the database. It examines all tables in the database to find out whether index and data pages are correctly linked and indexes are in proper-sorted order. It also checks that all pointers are consistent and that the data information on each page, and page offsets are reasonable. This typically locks user tables, indexes and system tables when running and should therefore not be run during normal operation, but at times when the system load is low.
· DB Space history
Displays a history of space usage in the system. Click DB space history to get an overview of the space allocated and used in the database for a longer period of time. You can sort the data according to different time intervals: by days, weeks, or months. In addition, a Database or Files button enables you to toggle between a view that focuses on the entire database or on individual files.
· Schema overview
Collects the information on tables, indexes and stored procedures of the dbo schema and all SAP systems running in the database.
On SAP system level:
· Detailed analysis
Displays specific information for a table or view you specify. For the table, the tabs General overview, Indexes and Fields are available. You can run the following functions and checks:
¡ Show Statistics:
Displays statistics for a selected index or column statistics.
¡ Update Statistics:
Updates the statistics for the selected index or all indexes.
Determines how many rows are typically identified by a key value. Selectivity helps, for example, to assess how efficient an index is when retrieving data associated with a key value.
This is also a measure of the selectivity of a column or combination of columns. But while selectivity simply counts the number of different values, density reflects whether some values occur more often then others. The density is lowest when all values occur the same number of times.
¡ Show contig.:
Displays fragmentation information for a selected index.
¡ Check table:
Checks the integrity of the data, index and image pages of the table.
¡ Update usage:
Reports and corrects inaccuracies in the sysindexes table which can lead to incorrect space usage reports.
The Detailed analysis screen for a table can also be accessed from the lists generated by the options the largest tables, most modified or top growing tables. Simply select a table and then click Table detail.
● Missing Indexes:
Compares indexes in the ABAP Dictionary and on the database. If any indexes exist in the ABAP Dictionary, but not in the database, you must create them with Create in DB. If any indexes exist in the database, but not in the ABAP Dictionary, you must delete them from the database.
● Space statistics:
¡ Table statistics history
¡ Shows size and fragmentation of the tables.
¡ Top n largest tables
¡ Shows data of the 25 largest tables in the system.
¡ Top n modified tables
¡ Shows data of the most frequently changed tables.
¡ Top n growing tables
¡ Shows data of the fastest growing tables.
Offers a list of checks that can be executed for the SAP system.
¡ Missing unique Indexes:
Compares unique indexes in the ABAP Dictionary and on the database. If any unique indexes exist in the ABAP Dictionary, but not in the database, you must create them with Create in DB. If there are any unique indexes in the database, but not in the ABAP Dictionary, delete them from the database.
¡ Database <-> ABAP Dictionary:
Compares views, tables and indexes in the ABAP Dictionary and on the database. If any of these objects exist in the ABAP Dictionary, but not in the database you must create them with Create in DB. If there are any objects on the database, but not in the ABAP Dictionary, delete them from the database.
¡ SAP Kernel:
Executes a program that analyses the SAP Kernel to find out whether any inconsistencies that can result in problems exist. Alternatively you can use transaction code SICK.