Space View (SQL Server Performance Analyst)

Go Up to Microsoft SQL Server Performance Analyst Statistics

In addition to a Home page, the Space category of SQL Server Performance Analyst includes the following tabbed pages:

• File Groups Tab
• Files Tab
• Objects Tab
• Virtual Log Files Tab
• Server Space Tab

Home

The Home page of the Space category includes statistics in the following categories:

• Bottleneck Analysis Pane
• Disk Analysis Pane
• Space Analysis Pane
• SQL Analysis Pane

Space Analysis Pane

A SQL server contains many databases, some of which are devoted to system-level activities (the master and tempdb databases, for example) and others that hold user data. The Key Space Analysis section of the Space home page displays details about the space situation for each database in a SQL server, including their total, used, and free space. The percent used amount for each database is also shown. In addition, each database’s transaction log space is analyzed and presented.

Metrics

If a database or transaction log’s free space goes below the Performance Analyst recommended threshold, (and either the database or transaction log does not have its automatic growth property enabled or the files have reached their growth limit) then the DBA should take action to ensure that the database or transaction log does not run out of available free space.

Correction

If the percent used amount of a database is approaching problematic levels, there are three ways a DBA can rectify the situation:

1. The DBA can resize the current file(s) used by the database via an ALTER DATABASE … MODIFY FILE command.
2. The DBA can add a new file to the database via the ALTER DATABASE … ADD FILE command.
3. The DBA can modify the file(s) used by the database to automatically grow by using the ALTER DATABASE … MODIFY FILE … FILEGROWTH command. You should also ensure that the MAXSIZE setting of each file is set appropriately.

Of course, the DBA should also ensure that enough physical space exists on the server to accommodate additional database space. There are several things a DBA can do to ensure that a database’s log does not run out of available free space:

1. First, most transactional-oriented databases should have their logs assigned to separate physical drives other than the database. Reasons for doing this include:
   • It prevents competition for space between the log and the database itself.
   • It allows the log to be monitored for space more effectively.
   • It improves performance.
2. If the database is not critical in nature, you can set the truncate log on checkpoint option (trunc log on chkpt), which eliminates any non-active space in the log when a database checkpoint occurs.
3. Critical databases needing higher levels of recovery should have schedules established that regular perform transaction log dumps. Doing so ensures better recovery scenarios as well as a reduced risk of the transaction log running out of space.
4. If a critical transaction log becomes full, it might be impossible to use standard procedures to dump transactions and reclaim space. The dump operation will likely have to incorporate the no log or truncate only options.
5. If a transaction log continuously approaches dangerously low levels of free space, then the DBA should allow the underlying file(s) of the log to automatically grow to meet the demand. This can be accomplished by using the ALTER DATABASE … MODIFY FILE … FILEGROWTH command. You should also ensure that the MAXSIZE setting of each file is set appropriately.

The DBA should also be on the lookout for large load or data modification operations that do not make use of prudently timed commit points. A single, large transaction has the ability to overwhelm any transaction log since only non-active space in the transaction log is removed from log dumps or truncation operations.

Bottleneck Analysis Pane

When using bottleneck analysis to troubleshoot a database, a DBA cannot rely only on the information contained in the wait event views that SQL Server provides. For example, a database might attempt to allocate another segment of space and yet be denied if no such free space exists on the server or if the underlying file has reached its growth limit. Such a failure will not be reflected in any wait event, but still represents a very real bottleneck to the database. In the same way that a DBA cannot depend on only a few ratios to properly carry out ratio-based performance analysis, an administrator must include other statistical metrics in their overall bottleneck analysis framework to obtain an accurate performance risk assessment. Performance Analyst works to identify bottlenecks in your database that fall outside of pure wait events so you can get a total picture of all stoppages in your system. The Performance Analyst Space home page displays space-related bottlenecks that might be threatening the overall performance of your system.

The following bottleneck indicators are used on the Performance Analyst Space home page to succinctly communicate the general overall space situation of the monitored database:

• Databases Without Auto-Grow
• Databases With Auto-Shrink
• Database Files Near Max Size
• Databases on Same Disk as Log
• Logs Without Auto-Grow
• Log Files Near Max Size

The following statistics, available on this pane, duplicate statistics available on the Bottleneck Analysis Pane of the Home View (SQL Server Performance Analyst) page:

• DB’s Low on Space
• Logs Low on Space

Database Files Near Max Size

A DBA can specify that one or more files of a SQL Server database can automatically grow by enabling the file’s auto-growth property. A DBA can also specify that the file not be permitted to grow beyond a certain limit, and once that limit is reached, the file and/or the database might be permanently out of space. The Database Files Near Max Size statistic provides a count of how many database files are near their maximum file size.

Location	Microsoft SQL Server Performance Analyst Statistics > Space View (SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane
Metrics	A DBA should not see any positive indicator that any file has reached its growth limit. While it might be okay from the standpoint that a file cannot simply grow anymore (if the database has other files that can grow or that no more space will be added to the database), the DBA should disable the auto-growth property of the file.
Correction	If a file has reached its growth limit, a DBA can take a number of actions, including: Eliminate the growth limit for the file either by increasing the limit size or allowing to grow in an unlimited fashion. If the file is on a disk or file system that has no free space left, the DBA can add another file to the database to allow for space expansion and alter the problem file to have auto-growth property disabled.

Databases on Same Disk as Log

To minimize I/O contention, a database should have its database files located on different physical disks than its log files. The Databases on Same Disk as Log statistic provides a count of how many databases have files that are on the same physical disk as their transaction log.

Location	Microsoft SQL Server Performance Analyst Statistics > Space View (SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane
Metrics	For critical production systems, all databases should have their database and log files on different physical disks.
Correction	If a DBA has database and log files on the same physical disk, they can create new database filegroups on different disks, relocate objects to the new filegroups, and then delete the old files/filegroups.

Databases Without Auto-Grow

In Microsoft SQL Server, a DBA has the ability to tell SQL Server to automatically grow a database in size when more space is required. This feature can save a critical transaction or other database request from failing due to a lack of free space in the database. It is recommended that critical databases have this feature enabled. The Databases Without Auto-Grow statistic provides a count of databases that do not have their automatic growth property enabled.

Location	Microsoft SQL Server Performance Analyst Statistics > Space View (SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane
Metrics	Static databases (those not expected to grow in size) will likely not need their auto-growth property enabled. Growing, dynamic databases should almost always be allowed to automatically grow when needed.
Correction	If any critical, dynamic database is found to not have its auto-growth feature enabled, then the DBA can modify the file(s) used by the database to automatically grow by using the ALTER DATABASE … MODIFY FILE … FILEGROWTH command. You should also ensure that the MAXSIZE setting of each file is set appropriately.

Databases With Auto-Shrink

A database can be set up to automatically shrink in size if SQL Server finds the database with an abundance of unused free space. Setting a database up so that this occurs can help reduce wasted storage on a database server. The Databases With Auto-Shrink statistic provides a count of databases that are allowed to automatically shrink in size.

Location	Microsoft SQL Server Performance Analyst Statistics > Space View (SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane
Metrics	Dynamic databases that have the potential to balloon in size and then have large amounts of that same data removed should be considered candidates for auto-shrink.
Correction	If a DBA wants to configure a database to automatically shrink, they can issue a command similar to the following: EXEC sp_dboption 'mydatabase','autoshrink',true

Logs Without Auto-Grow

In Microsoft SQL Server, a DBA has the ability to tell SQL Server to automatically grow a database or transaction log in size when more space is required. This feature can save a critical transaction or other database request from failing due to a lack of free space in the database or transaction log. It is recommended that critical databases and their transaction logs have this feature enabled.

The Logs Without Auto-Grow statistic provides a count of transaction logs that do not have their automatic growth property enabled.

Location	Microsoft SQL Server Performance Analyst Statistics > Space View (SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane
Metrics	Static databases (those not expected to grow in size) will likely not need their transaction log’s auto-growth property enabled. Growing, dynamic databases should almost always have their transaction log be set to automatically grow when needed.
Correction	If any critical, dynamic database is found to not have their transaction log auto-growth feature enabled, then the DBA can modify the file(s) used by the database’s transaction log to automatically grow by using the ALTER DATABASE … MODIFY FILE … FILEGROWTH command. You should also ensure that the MAXSIZE setting for each file is set appropriately.

Log Files Near Max Size

A DBA can specify that one or more transaction log files of a SQL Server database can automatically grow by enabling the file’s auto-growth property. A DBA can also specify that the file not be permitted to grow beyond a certain limit, and once that limit is reached, the file and/or the transaction log might be permanently out of space. The Log Files Near Max Size statistic provides a count of how many transaction log files are near their maximum file size.

You should not see any positive indicator that any file has reached its growth limit. While it might be okay from the standpoint that a file cannot simply grow anymore (if the database transaction log has other files that can grow or that no more transactions are expected to occur within the database), the DBA should disable the auto-growth property of the file.

Location

Microsoft SQL Server Performance Analyst Statistics > Space View (SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane

Correction

If a file has reached its growth limit, a DBA can take a number of actions: Eliminate the growth limit for the file either by increasing the limit size or allowing to grow in an unlimited fashion. If the file is on a disk or file system that has no free space left, the DBA can add another file to the database’s transaction log to allow for space expansion and alter the problem file to have auto-growth property disabled.

SQL Analysis Pane

A lot of a database’s overall performance can be attributed to SQL statement execution. Poorly optimized SQL statements can drag an otherwise well-configured database down in terms of end-user response times.

The SQL Analysis section of the Space home page shows a count of all SQL statements that have executed INSERT, UPDATE, DELETE, and TRUNCATE commands. Seeing these statistics should help you determine how much space-related SQL activity is occurring on your system.

The SQL Analysis section provides you with a quick overview of the percentage of Reads, Writes, CPU, and Elapsed Time the most expensive SQL statements tracked by Performance Analyst have used. Depending on the page you are on, you might just see information regarding physical I/O activity or DML statements. For example, you might see that a SQL statement has caused 60% of all physical reads on the system, or that a procedure is responsible for 90% of all the CPU usage. To get information regarding the actual SQL text or stored procedure calls, drill down into the Top SQL details views.

Note: SQL Profiling is turned on by default in Performance Analyst so you can automatically collect SQL traffic. If you do not want to collect SQL execution information, use the options inside of Performance Analyst to disable SQL profiling.

Metrics

The statistics in this section can be used in many different ways. For example, large numbers of inserts could indicate a growing database and that a DBA should keep a watchful eye on free space. Heavy update numbers could mean trouble for objects containing many indexes (indexes typically undergo splits and fragmentation when large volumes of updates are thrown their way). Many deletes might signal potential wasted space problems.

Disk Analysis Pane

The Disk Analysis section of the Performance Analyst Space home page provides a summary of the total database and log space used per server disk drive, as well as a summary of free disk space for each server drive.

Metrics

If any database or transaction log file has been set up to automatically grow, the DBA should ensure there is enough server disk space to accommodate any new, additional requests for space.

Correction

If you see any drive that has reached zero free space (or is close), you might want to add new files, on other disks with abundant free space, to any databases or transaction logs so that no out-of-space errors result.

File Groups Tab

SQL Server manages physical storage space through files and file groups. The file groups tab displays detailed information regarding storage usage in each of the SQL Server file groups. The table below describes the information available in the File Groups Summary section of the File Groups tab:

Information	Description
Database Name	The name of the database.
File Group ID	The numeric ID of the file group.
File Group	The name of the file group.
Can Grow	This indicates if one of more of the files making up the file group is allowed to grow automatically.
File Count	The number of files that make up the file group.
Size (MB)	The total physical size of the file group.
Table Res	The amount of reserved space consumed by tables.
Index Res	The amount of reserved space consumed by indexes.
Free (MB)	The amount of free space for the file group.
Free Percent	The percentage of free space for the file group.

Metrics

Unless server space is tight, it is normally wise practice to allow your files to automatically grow to meet demand for more incoming data. You should also physically separate your database and log files onto separate physical drives.

Correction

To let your files automatically grow until out of space, set the file's growth option in IDERA’s Rapid SQL, or by using the ALTER DATABASE... MODIFY FILE command.

Files Tab

SQL Server manages physical storage space through files and file groups. The Files tab displays detailed information regarding storage usage in each of the SQL Server files. The table below describes the information available in the Files Summary section of the Files tab:

Information	Description
Database Name	The name of the database.
Logical Name	The nickname given to the file by the DBA.
File Group	The name of the file group.
File Name	The name and location of the file.
Size (MB)	The total physical size of the file.
Can Grow	This indicates if the file can automatically grow in size.
Growth Amount	This indicates how much the file will grow in size.
Max File Size	This indicates the maximum file size that the file can grow.

Metrics

Unless server space is tight, it is normally wise practice to allow your files to automatically grow to meet demand for more incoming data. It is also smart to physically separate your database and log files onto separate physical drives.

Correction

To let your files automatically grow until out of space, set the file's growth option in IDERA’s Rapid SQL, or by using the ALTER DATABASE... MODIFY FILE command.

Virtual Log Files Tab

The Virtual Log Files tab of the Space Detail displays an internal structure of sorts for a each database's log. The presented information is helpful when trying to shrink a database's log because you can see how much of the log is active and exactly where the active portion resides. The table below describes the information available on the Virtual Log Files tab of the Space Detail:

Information	Description
Database Name	The name of database.
File Name	The name of the log file.
Status	This indicates if this portion of the log is active or inactive (not being used).
Size	The size of this portion of the log in MBs.

Objects Tab

The Objects tab of the Space/Databases Detail includes the following sections:

• Database Object Detail
• Database Object Summary

Database Object Summary

Tables and indexes consume the storage in all databases. The Database Object Summary grid displays summary information regarding table and index storage for all databases. The table below describes the information available in the Database Object Summary section of the Objects tab of the Space Detail:

Information	Description
Database Name	The name of the database.
Table Count	The count of all tables in the database.
Index Count	The count of all indexes in the database.
Table Reserved (MB)	The amount of space (in MB) reserved by tables.
Index Reserved (MB)	The amount of space (in MB) reserved by indexes.

Location	Microsoft SQL Server Performance Analyst Statistics > Space View (SQL Server Performance Analyst) > Objects Tab Microsoft SQL Server Performance Analyst Statistics > Database View (SQL Server Performance Analyst) > Objects Tab
Metrics	Negative numbers viewed for index space information can be caused by inaccuracies contained in the SQL Server data dictionary. Frequently, running a DBCC UPDATEUSAGE command against the database resolves the problem. However, there are bugs in SQL Server that sometimes caused erroneous numbers to the reported for the reserved space amounts used by tables and indexes.

Database Object Detail

Tables and indexes consume the storage in all databases. The Database Object Detail grid displays object space details for the database selected in the Database Object Summary grid. The table below describes the information available in the Database Object Detail section of the Objects tab of the Space Detail:

Information	Description
Object Owner	The owner of the object.
Object Name	The name of the object.
Clustered	Indicates if the index is clustered.
File Group	The file group where the object resides.
Reserved (KB)	The amount of space reserved by the object in kilobytes.
Used (KB)	The amount of space used by the object in kilobytes.
Free (KB)	The amount of free space used by the object in kilobytes.
Percent Used	The percentage of space used by the object.

Location	Microsoft SQL Server Performance Analyst Statistics > Space View (SQL Server Performance Analyst) > Objects Tab Microsoft SQL Server Performance Analyst Statistics > Database View (SQL Server Performance Analyst) > Objects Tab
Metrics	Negative numbers viewed for index space information can be caused by inaccuracies contained in the SQL Server data dictionary. Frequently, running a DBCC UPDATEUSAGE command against the database resolves the problem. However, there are bugs in SQL Server that sometimes caused erroneous numbers to the reported for the reserved space amounts used by tables and indexes.

Server Space Tab

The Server Space tab of the Space Detail displays the amounts of space used by SQL Server per physical drive and by database per physical drive. The first section displays summary information and the second section contains space information per database.

The table below describes the information available in the Disk Summary By Space section on the Server Space tab of the Space Detail:

Column	Description
SQL Data (MB)	The amount of reserved database space on the drive.
SQL Log (MB)	The amount of reserved log space on the drive.
Free	The total amount of free space that remains on the drive.

The table below describes the information available in the Disk Summary By Database section on the Server Space tab of the Space Detail:

Column	Description
Disk Drive	The physical drive letter.
Database	The database name.
SQL Data (MB)	The amount of reserved database space on the drive.
SQL Log (MB)	The amount of reserved log space on the drive.

Metrics

If you allow your database and/or log files to automatically grow, and you see little or no free space left on their physical drives, an option is to add new files to the database or log on different drives to avoid any out of space errors. It is also smart to physically separate your database and log files onto separate physical drives.

Correction

If you need to add new files to your databases or logs, you can do so easily by using the ALTER DATABASE… ADD FILE and ALTER DATABASE… ADD LOG FILE commands.