Session 4: Pinpointing SQL server Performance Inefficiencies

From DBArtisan
Jump to: navigation, search

Go Up to Space Analyst for IBM DB2 for Linux, Unix, and Windows Tutorial

Space Analyst can greatly accelerate the process of locating objects in your database that are degrading overall response times because of their current inefficient storage usage.

Finding Space-related Table Problems

You can quickly tell if any tables in a tablespace that may be contributing to performance slowdowns by following these steps:

  1. Open the Space Analyst and select the Tables tab.
  2. Select the tablespace you would like to interrogate in the Tablespace list.
  3. Click the Refresh button.

You with see a graphical count of the number of tables that are experiencing specific types of storage inefficiencies in the upper area of the tab. The table reorganization candidate details grid in the lower area of the tab can be used to view detailed storage information for every table in the selected tablespace. Space Analyst uses predefined thresholds (which you can customize by selecting the Threshold Options toolbar button) to determine how many and what tables are suffering from storage inefficiencies.

The types of table-related space problems shown in the bar graph found on the objects tab include:

  • Overflow Problems -Overflows occur in tables when overflows occur when rows are updated with more bytes than the original row (VARCHAR fields etc.). Overflows also occurs when the new columns are added to existing tables. Since the overflow data will not be stored contiguously with the other data, it causes access inefficiencies.
  • Space Utilization Problems - Normally, DB2 reclaims any space freed up by deletes, in subsequent insert/update operations. However, if a DB2 table has the 'APPEND' mode set to on and a large amount of data is deleted from the table DB2 will not reclaim freed space. Also when some types of large load operations are performed, the table may be left with a large amount of unused space.
  • Page Utilization Problems - As the number of empty pages increases, the need for table reorganization also increases. Empty pages waste space and reduce the table scan efficiency, since empty pages are read into the buffer pool for a table scan wasting bufferpool memory.
  • Index Contention - Unless the user explicitly separates the index and table tablespaces, DB2 tables and indexes are placed on the same tablespace. This can become a bottleneck with heavily accessed tables using index scans since data and index accesses will compete for the IO device resources.

The graph at the top of the Tables tab shows the overall count of problem tables, but you can also view the details for each type of problem by using the Details grid at the bottom of the tab. For example, if Space Analyst identifies tables that have overflow records, then to find the actual tables and view details regarding the identified space problem, you would perform the following:

  1. Select All from the Tablespace list in the Details grid.
  2. Select All from the Table Schema list in the Details grid.
  3. Select Tables with Overflow Problems from the Display list in the Details grid.
  4. Click Refresh.

You would then see the actual tables that suffer from overflowed records along with their demographic details. If you wanted to take corrective action on any object, you could highlight one or more tables and click the right mouse button to bring up a menu of possible actions.

Proceed to Finding Space-related Index Problems

Retrieved from "http://docwiki.embarcadero.com/DBArtisan/171/e/index.php?title=Session_4:_Pinpointing_SQL_server_Performance_Inefficiencies&oldid=4953"