Session 4: Pinpointing Object Performance Inefficiencies Using Space Analyst for Microsoft

Go Up to Space Analyst for Microsoft SQL Server Tutorial

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

Finding Space-related Table Problems with Space Analyst for Microsoft

To quickly determine if any tables in your database may be contributing to performance slowdowns, do the following:

1. Open Space Analyst.
2. Select the Objects tab.
3. In Object Details, select the target object.

By default, the General Space button is selected. This view will show you a graphical layout of table used and free space for the database. Note that the graph is scrollable so you can scroll to see all the tables for your database.

The Object details grid in the lower area of the tab can be used to view detailed storage information for every table in the selected database.

To troubleshoot table storage issues in your database, select the Space Diagnostics radio button. This option will interrogate your database and will provide you with a graphical count of the number of tables that are experiencing specific types of storage inefficiencies. Space Analyst uses predefined thresholds (which you can customize by selecting the Threshold Options toolbar button) to determine how many and what objects are suffering from storage inefficiencies.

The table below describes the types of table-related space problems shown in the bar graph found on the Objects Tab:

Problem	Description
Scan Density	This is computed by dividing the optimum number of extent switches by the actual number of extent switches. This percentage can be misleading, however, if the object spans more than one database file and should not be considered in such an event. Values close to 100% are best.
Logical Fragmentation	This metric represents the percentage of pages that are out of physical order. High values (greater than 25%) for this metric may mean that index scan performance is not what it could be. Note that this statistic is meaningless for tables without clustered indexes (heap tables).
Extent Fragmentation	This highlights any gaps (lack of contiguous order) in extents and indicates the presence of external fragmentation. Percentages of 25-50% or more are not a favorable reading.
Average Page Density	This metric represents the fullness of the data or index pages, and is an indicator of internal fragmentation. Remember that the more full a data or index page is, the less I/O needed by SQL Server when performing scans of tables or indexes. High values are good here, with anything below 50% being suspect. Low values for this metric often indicate the presence of page splitting. Keep in mind, however, that internal fragmentation isn't necessarily bad in OLTP environments where large FILLFACTORs are often specified. Also understand that small objects (those will little or no data) will likely show low readings because they simply don't consume enough space in a database page. You can ignore these objects.
Forwarded Records	SQL Server will move rows in a table under certain conditions. One situation might arise when you update a row in a table that has a variable-length column to a larger size that will no longer fit on its original page. Another situation would be if SQL Server moves a row when the clustered index column changes. Forwarded records can reduce performance at times because additional I/O is involved to first obtain the record pointer to the relocated row, and then the row itself. But when does row forwarding become a problem? For example, just because a table has one hundred forwarded rows, does that mean a performance problem exists? The answer is: it depends. If the table has one hundred rows in it, then yes, a problem exists - 100% of the table suffers from forwarded rows. If the table has three million rows, then the forwarded row situation is likely not causing much fuss in the database.

The graph at the top of the Objects 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 forwarded records, then to find the actual tables and view details regarding the identified space problem, do the following:

1. In the File Group list, select All.
2. In the Owner list, select All.
3. In the Display list, select Tables with forwarded records problems.
4. Click Refresh.

You would then see the actual tables that suffer from forwarded 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 Session 5: Correcting Space-related Problems Using Space Analyst for Microsoft