Data Space Analyzer

DBA xPress ›› Features ››
Parent Previous Next

Data Space Analyzer


The Data Space Analyzer will assist DBA’s with managing their SQL Server instances and display graphs showing information on disk space usage.

Feature Highlights

  Displays data about disk usage of databases


Using the Data Space Analyzer

1.   Click the Data Space analyzer icon in the Pragmatic Workbench.


2.   This brings up the 'Choose Server or Database' dialog.

 

 

3.   Here we can select a SQL Server from the drop-down list, or click on the "Find Local Servers" button to find any SQL Servers on the local network.

 

Important Note

Data Space Analyzer automatically starts a search for local servers when it starts, so it is not necessary to use the "Find Local Servers" button unless you know a new server has appeared since the application started, or the dialog is opened before the initial search completes.


4.   If you click on "SQL Server Security" then you are given the option to enter a username and password. You can select a server and click 'OK' in order to profile space usage on the server as a whole, or you can select a database in order to profile space usage on that single database.


5.   After specifying your database, a graph will appear showing the size of the database.



6.   Clicking on the graph will step you into the file. You will be able to see a further breakdown of the project.




In the reports there are a number of characteristics displayed. These are shown either in the list of objects at the bottom of the display box or by moving the cursor over the item in the chart display.

 

Characteristic

Description

File Size

This is the total size of files that make up a file group in a database.

Allocated Space

This is the space within a database that is reserved for usage by a table or an index.

Unallocated Space

This is the space within a database file that has not been reserved.

Reserved

This is the total amount of space that has been reserved for a table or an index.

Used

This is the total amount of space which is being actively used by a table or an index.

In-Row Data

This is the space which is being used for the main data storage of table rows.

Overflow/LOB Data

This is the space which is used for LOB types (text, ntext & image) or for overflow data associated with overflow types (varchar(MAX), nvarchar(MAX), varbinary(MAX) & xml).

Index Data

This is the space which is used for storing indexes on a table or view.

Free

This is the space that has been reserved for table usage but is not currently in use.

Rows

This is the total row count associated with an entity. Note that the total row count for SQL Server 2000 servers is estimated.

Average Page Fragmentation

This shows the amount of fragmentation that an index or a table has. Note that This information is not available on SQL Server 2000 servers, or when using the faster data loading mode on SQL Server 2005+ servers.

 

The following is a list of the charts that are available in Data Space Analyzer, along with the destinations for clicks both on chart elements and list elements below:

 

Chart Name

Chart Click Destination

List Click Destination

Databases by total allocated space

Data spaces by total allocated space

Data spaces by total allocated space

Databases by total file group size

Data spaces by total file size

Data spaces by total file size

Databases by total page type size

Data spaces by selected page type total size

Data spaces by total page type size

Databases by row count

Data spaces by row count

Data spaces by row count

Databases by total unallocated space

Data spaces by total unallocated space

Data spaces by total unallocated space

Page type summary

Data spaces by selected page type total size

Data spaces by selected page type total size

Data spaces by selected page type total size

Storage objects by total allocated space

Storage objects by total allocated space

Data spaces by total allocated space

Storage objects by total allocated space

Storage objects by total allocated space

Data spaces by total file size

Storage objects by total allocated space

Storage objects by total allocated space

Data spaces by total page type size

Storage objects by selected page type total size

Storage objects by total allocated space

Data spaces by row count

Storage objects by row count

Storage objects by row count

Data spaces by total unallocated space

Storage objects by total allocated space

Storage objects by total allocated space

Partitions for index by row count

N/A

N/A

Partitions for index

N/A

N/A

Indexes on storage object by row count

Partitions for index by row count

Partitions for index by row count

Indexes on storage object

Partitions for index

Partitions for index

Page type summary for storage object

Indexes on storage object

Indexes on storage object

Storage objects by selected page type total size

Page type summary for storage object

Page type summary for storage object

Storage objects by total allocated space

Indexes on storage object

Indexes on storage object

Storage objects by row count

Indexes on storage object by row count

Indexes on storage object by row count

 


There are four chart types. Three single series charts: Torus, Pie, Bar; and one single series chart: Manhattan. Chart types can be selected from the View section of the toolbar.

 

Torus Chart


Pie Chart


Bar Chart


Manhattan Chart