Database View (SQL Server Performance Analyst)

From DBArtisan
Jump to: navigation, search

Go Up to Microsoft SQL Server Performance Analyst Statistics

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

Home

The Home page of the Database Performance Analyst page includes the following categories:

Database Analysis Pane

SQL Server is broken up into many different databases, all of which have their own levels of activity. The Database Analysis section displays a summary of activities that have occurred within each database. The table below describes the information available in the Database Analysis section:

Information Description

Database Name

The name of the database.

DBCC Logical Scans

The number of DBCC scans that have occurred within the database.

Transactions

The number of transactions that have occurred within the database.

Active Transactions

The number of transactions currently running in the database.

Bulk Copy Rows

The number of rows inserted per second into the database via BCP operations.

Bulk Copy Throughput

The amount of data bulk (KB) copied into the database per second.

Log Cache Reads

The number of reads per second that have gone through the log manager cache.

Log Flushes

The number of transaction log flushes per second. A log flush occurs when SQL Server writes all changes from the database’s log cache out to the database’s log files on disk.

Log Growths

The number of times the transaction log has grown in size.

Log Shrinks

The number of times the transaction log has reduced itself in size.

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 critical database might be offline or be marked suspect. 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 Database home page displays database-related bottlenecks that might be dragging down the overall performance of your system.

The following bottleneck indicators are used on the Performance Analyst Database home page to communicate the general overall conditions of databases on the monitored server:

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

Databases Requiring Backup

This metric represents the number of databases in SQL Server that have not been backed up for more than seven days. This statistic excludes the pubs, tempdb, Northwind, msdb, and model databases.

Location

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

Metrics

To ensure proper protection for most databases, it is recommended that even the most static databases be backed up at least once a week. You should frequently backup critical, dynamic databases and include transaction log backups to enable point-in-time recovery ability.

Correction

Any critical databases found with obsolete or no backups should immediately be backed up. Moreover, to ensure proper data protection for each database, you should institute a planned backup schedule. The timing and repetition of the backups depend on the critical recovery needs of each database.

Databases Without Auto-Create Stats

The Databases Without Auto-Create Stats statistic represents the total number of databases defined on SQL Server that do not have the AUTO_CREATE_STATISTICS option enabled.

Location

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

Metrics

When the AUTO_CREATE_STATISTICS option is enabled, statistics are automatically created on columns used in a SQL query predicate. Keeping object statistics fresh in the SQL Server data dictionary improves query performance because the optimizer can better determine how to evaluate a query and return the requested data. If the statistics are not used, SQL Server should automatically delete them.

Correction

If possible, a DBA should keep their databases in AUTO_CREATE_STATISTICS mode. If a database is found without this option set, you can easily change it by using the command: EXEC sp_dboption '<database name>','auto create statistics',true

Databases Without Auto-Update Stats

The Databases Without Auto-Update Statistics statistic represents the total number of databases defined on SQL Server that do not have the AUTO_UPDATE_STATISTICS option enabled.

Location

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

Metrics

When the AUTO_UPDATE_STATISTICS option is enabled, SQL Server automatically updates existing statistics when the statistics become out-of-date because data in the tables has changed enough to affect the optimizer's decision-making process.

Correction

If possible, a DBA should keep databases in AUTO_UPDATE_STATISTICS mode. If a database is found without this option set, you can easily change its auto-update statistics to true by using the command: EXEC sp_dboption '(database name)',

Extents Allocated

SQL Server groups eight 8KB pages together to form an extent of space (64KB). Space is allocated to database objects (tables, indexes) in the form of extents. The Extents Allocated statistic provides a count of the number of extents allocated to tables and indexes.

Location

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

Metrics

None. However, be aware that excessive counts of allocated extents might signal a large load job occurring in the database.

Freespace Scans

The Freespace Scans statistic provides a count of the number of scans SQL Server initiated to search for free space that will accommodate requests to insert new data rows.

Location

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

Metrics

None. However, be aware that excessive counts of freespace scans might signal INSERT problems being performed on large tables.

Page Splits

When data is added or modified for a table that has indexes, the indexes must be updated as well. As index pages fill up, free space needed to keep index keys in their proper order can oftentimes run very low. If an index update needs to be made to a page, and no free space exists on that page, SQL Server will perform a page split where it moves approximately half the rows on a page to a new page. The Page Splits statistic provides a count of how many page splits are occurring in SQL Server.

Location

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

Metrics

Page splits cause additional overhead in the form of CPU usage and I/O. Observing large numbers of page splits can signal a resource bottleneck in your server.

Correction

To avoid page splits, you can look into tuning the FILLFACTOR property of an index, which controls the percentage of the index page that is filled during creation. The default, 100, tells SQL Server to completely fill each page, whereas lower numbers tell SQL Server to leave room for additional index rows or updates to existing rows.

SQL Analysis Pane

Certain databases on a SQL Server will experience more SQL activity than others. The SQL Analysis section displays a count of all statement executions for each database.

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.

Error Log Analysis pane

SQL Server records various system events in its system or error log. The majority of messages placed into the log are informational in nature, however since some contain critical messages, you should immediately review them. This section indicates the number of actual error messages in the most recent error log so you know if there are potential events that require your attention.

Note: Similar information is also available on the Bottleneck Analysis Pane of the Home View (SQL Server Performance Analyst) page.

Metrics

You should investigate any positive values for errors.

Correction

If non-zero values are found for this statistic, you should drill down into the current error log and examine the detail found that accompanies each error issued by SQL Server.

Workload Analysis Pane

SQL Server is broken up into many different databases, all of which have their own levels of activity. The Workload Analysis section displays a summary of resource consumption across all databases. The table below describes the information available in the Workload Analysis section:

Information Description

Database Name

The name of the database.

Connections

The number of processes that are connected to the database.

Blocked Users

The number of processes in the database experiencing blocks from lock activity.

Total Memory

The total number of memory pages in use by processes for each database.

Total I/O

The total amount of physical I/O being experienced by each database.

Total CPU

The total amount of cumulative CPU for all processes connected to the database.

Total Wait Time

The total wait time (in milliseconds) that all processes connected to the database have experienced.

DBCC Running Count

This indicates if any DBCC operations are currently running against the database.

BCP Running

This indicates if any BCP operations are currently running against the database.

Backup Restore Running

This indicates if any backup or restore operations are currently running against the database.

Overview Tab

The Overview tab of the Database Detail includes the following sections:

Database Size Analysis

The Database Size Analysis chart displays each database in terms of its total percentage of size to the total size of all databases on the SQL Server.

Location

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

Database Object Analysis

The Database Object Analysis chart displays each database in terms of its total percentage of object to the total objects for all databases on the SQL Server.

Location

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

Database User Analysis

The Database User Analysis chart displays each database in terms of its total percentage of user accounts to the total user accounts of all databases on the SQL Server.

Location

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

Database Summary

The Database Summary Analysis section displays summarized metadata for all defined databases on SQL Server. The table below describes the information available in the Database Summary section:

Information Description

Database

The name of the database.

Created

The date/time when the database was created.

Status

The status of the database (online, offline, suspect, etc.)

Database Size (MB)

The total size of the database in MB.

Log Size (MB)

The total size of the log in MB.

Tables

The number of all tables in the database.

Indexes

The number of all indexes in the database.

Users

The number of all defined user accounts in the database.

Last Backup

The date/time of the last full backup for the database.

Autoshrink

This indicates if the database will release space automatically back to the operating system when it does not need it.

DB Autogrow

This indicates if one or more of the database’s underlying files can automatically grow in size if needed.

Log Autogrow

This indicates if one or more of the log’s underlying files can automatically grow in size if needed.

Autocreate Stats

This indicates if object statistics are automatically created for objects in the database.

Autoupdate Stats

This indicates if object statistics are automatically updated for objects in the database.


Location

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

Metrics

The following are things to take note of: Any critical database that shows a last full backup date that is older than the database’s required backup needs. Any database that shows a status of offline or suspect. Any growing database that does not have its database or log files set to automatically grow in size. Any dynamic database that does not have its object statistics set to automatically update.

Correction

Depending on the situation, you should take the following actions: For databases that require a full backup, perform a full backup of the database when appropriate. For suspect databases, the steps to handling will vary from one installation to another. However, here are some general guidelines you can use to troubleshoot a suspect database: Begin by examining the SQL Server error log for clues as to what caused the database to be marked as suspect. It is not unusual for a server to run out of physical disk space on drives used by SQL Server. When this happens, recovery for databases can sometimes fail with the end result being SQL Server marking a database as suspect. To remedy this situation, you should free up space on the identified drives or add files to the newly marked suspect database. This can be accomplished by utilizing the following stored procedures: sp_add_data_file_recover_suspect_db and sp_add_log_file_recover_suspect_db. For SQL Server version 7.0, you will need to use the sp_resetstatus stored procedure to reset the suspect status flag for the database in question, use the alter database command to add new datafiles to the database, and then stop/start SQL Server. Many times, suspect databases are caused by SQL Server not being able to access a database or log file. This happens if a particular physical hard drive has become unavailable, but also can occur if another operating system process has obtained exclusive access to a file. If this scenario proves to be true, once you have ensured that the file(s) are available once again to the operating system, you can use the sp_resetstatus stored procedure to reset the suspect status flag for the database and then stop/start SQL Server. Should an offline database be found by Performance Analyst, you can easily place it back online by utilizing either the sp_dboption stored procedure or the alter database command. If any critical, dynamic database or log is found to not have their 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 for each file is set appropriately. If possible, a DBA should keep their databases in AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS mode. If a database is found without this option set, you can easily change it by using the following commands: EXEC sp_dboption '<database name>','auto create statistics',true EXEC sp_dboption '<database name>','auto update statistics',true

Error Log Tab

The Error Log tab of the Database Detail includes the following sections:

SQL Server Error Log

SQL Server logs events pertinent to its operations to the SQL Server error log and the operating system application log. Errors encountered by SQL Server are clearly visible in the log, however large logs can be difficult to navigate. The SQL Server Error Log section displays all events for a selected log or just errors.

Location

Microsoft SQL Server Performance Analyst Statistics > Database View (SQL Server Performance Analyst) > Error Log Tab

Metrics

You should investigate any positive values reported for errors.

Error Log Volume Analysis

SQL Server logs events pertinent to its operations to the SQL Server error log and the operating system application log. Errors encountered by SQL Server as well as informational message are recorded in the log. The Error Log Volume Analysis section displays a graph that shows the volume amount of messages/errors over the last five days.

Location

Microsoft SQL Server Performance Analyst Statistics > Database View (SQL Server Performance Analyst) > Error Log Tab

Metrics

Seeing a disproportionate percentage of messages for one day might indicate that something has, or is occurring that needs investigation.

Error Log Content Analysis

SQL Server logs events pertinent to its operations to the error log and the operating system application log. Errors encountered by SQL Server as well as informational message are recorded in the log. The Error Log Content Analysis section displays a graph that shows percentage of informational messages to errors in the current log.

Location

Microsoft SQL Server Performance Analyst Statistics > Database View (SQL Server Performance Analyst) > Error Log Tab

Metrics

You should investigate any indicated errors.

Objects Tab

The following statistics, available on this tab, are duplicates of statistics available on the Objects Tab of the Space View (SQL Server Performance Analyst) page:

SQL Agent Tab

The SQL Agent tab of the Database Detail includes the following sections:

Job/Alert Detail

SQL Server provides the ability to submit and run jobs as well as be notified about certain SQL Server-related events. The Job/Alert Detail section displays information regarding the status of the server’s SQL Agent as well as details for all jobs and alerts that are defined to the system.

Location

Microsoft SQL Server Performance Analyst Statistics > Database View (SQL Server Performance Analyst) > SQL Agent Tab

SQL Server Job Summary

The SQL Server Job Summary section graphically displays the outcome of all jobs for the last two days, as well as current activity.

Location

Microsoft SQL Server Performance Analyst Statistics > Database View (SQL Server Performance Analyst) > SQL Agent Tab

Metrics

You should investigate any counts noted for failed jobs.

SQL Server Alert Summary

The SQL Server alert summary section graphically displays the number of times that performance and event alerts have fired.

Location

Microsoft SQL Server Performance Analyst Statistics > Database View (SQL Server Performance Analyst) > SQL Agent Tab

Metrics

You should investigate any counts noted for any event.

Backups Tab

The Backups tab of the Database Detail includes the following:

Backup Detail

The Backup section displays the most recent 25 backups for a selected database. The table below describes the information available on the Backups tab of the Database Detail:

Information Description

Database

The name of the database.

Backup Start

The time stamp when the backup began.

Backup Finish

The time stamp when the backup finished.

Backup Type

The type of backup (FULL, INCREMENTAL, etc.)

Backup Size (KB)

The size of the backup, in kilobytes.

Expiration Date

The expiration date for the backup, if any.


Location

Microsoft SQL Server Performance Analyst Statistics > Database View (SQL Server Performance Analyst) > Backups Tab

Metrics

Although the needs of an application determine the frequency and type of backup, it is generally recommended that most dynamic databases have a solid plan in place for full and differential backups. For databases requiring point-in-time recovery, a backup plan should also include log backups.

Database Backup Summary

The Database Backup Summary section graphically displays the backup successes and failures for database backups over the last week.

Location

Microsoft SQL Server Performance Analyst Statistics > Database View (SQL Server Performance Analyst) > Backups Tab

Metrics

You should investigate any backup failures.

Log Backup Summary

The Log Backup Summary section graphically displays the backup successes and failures for database log backups over the last week.

Location

Microsoft SQL Server Performance Analyst Statistics > Database View (SQL Server Performance Analyst) > Backups Tab

Metrics

You should investigate any backup failures.

Configuration Tab

The Configuration tab of the Database Detail displays metadata regarding the hardware characteristics of the SQL Server as well as the SQL Server configuration parameters. The table below describes the information available on the Configuration tab:

Information Description

Parameter

The parameter name.

Value

The value set for the parameter.

Run

The value currently running for the parameter.

Minimum

The minimum value allowed for the parameter.

Maximum

The maximum value allowed for the parameter.

Dynamic

Whether the parameter is dynamic (can be changed without requiring SQL Server to be stopped/started) or not.

Replication Tab

The Replication tab of the Database Detail provides basic information regarding replication activities that are occurring on the monitored SQL Server.

Replication Agent Details

The Replication Agent Details section shows information regarding replication. The table below describes the information available on the Replication tab:

Information Description

Agent

The agent performing the replication work.

Publication

The object being published to a subscribing database.

Publisher

The SQL Server providing the source material.

Publisher Database

The database of the SQL Server providing the source material.

Status

The current status of the replication agent's task.

Subscriber

The SQL Server requesting the source material.

Subscriber Database

The database of the SQL Server requesting the source material.

Type

The type of replication activity (push, pull, etc.)

Start Time

The start time for the replication task.

Duration

The duration of the replication task.

Last Action

The output message for the replication task.


Location

Microsoft SQL Server Performance Analyst Statistics > Database View (SQL Server Performance Analyst) > Replication Tab

Replication Throughput Details

The Replication Throughput Details section of the Replication tab displays:

  • Replication Type
  • Measure
  • Instance Name
  • Value

Location

Microsoft SQL Server Performance Analyst Statistics > Database View (SQL Server Performance Analyst) > Replication Tab