Parent, SQL Exec Stats, Copyright 2006-2018 Elemental Inc. All rights reserved.
Read Me, Intro, Main, General, Databases, File IO, Tables, Indexes, ExecStats, StoredProcs, DistStats, IxOpStats, Appendix

update in progress 2018-Oct

Databases

The Databases tab show database summary information. The columns start with the database name, the database id, the number of file groups, and the number of files. The next series of columns are calculated from other system views. The column "Size MB" is the total size of the data files. Reserved, Data and Index are the respective sizes computed for tables and indexes. Log is the size of the log files, and LUsed is the log used space. The columns Tables, Indexes, XmlIX and Stats are the number of tables, indexes, Xml Indexes, and statistics in each database.

Databases 1a

The cost of calculating database space can be non-trivial when there are tens of thousands of tables, partitions, indexes or allocations. The database space is calculated only for selected databases. During off-hours, it can be helpful to see the details for the entire set of databases.

The next set of Database columns are below. By default, the column widths are kept very narrow.

Databases 1b

The remaining Database columns below were added in 2013 Sep. These are the file IO stats rolled up by database. As of now, only data read and log write values are displayed. The last column is ms/Write - log.

Databases 1b

Below is the initial query used to populate the Database grid from the sys.databases view. The size, space and objects related columns are populated later using values from other system views. sys.master_files

--Databases SQL:

SELECT name [Database], database_id [dbid], 0 Groups, 0 Files
, 0.0 Size, null Reserved, null [Data], null [lob], null [Index], null Unallocated
, 0.0 [Log], 0.0 Lused, null [Tables], null [Indexes], null XmlIx, null [Stats]
, create_date, [compatibility_level], collation_name, user_access, [state]
, CONVERT(tinyint, is_in_standby) is_in_standby, recovery_model
, CONVERT(tinyint, is_auto_create_stats_on) is_auto_create_stats_on
, CONVERT(tinyint, is_auto_update_stats_on) is_auto_update_stats_on
, CONVERT(tinyint, is_fulltext_enabled) ftc
, CONVERT(tinyint, is_parameterization_forced) is_parameterization_forced
, CONVERT(tinyint, is_date_correlation_on) is_date_corr
, CONVERT(tinyint, is_encrypted) is_encrypted
FROM sys.databases WITH (NOLOCK)
ORDER BY name

 

DB Columns

Additional columns have been added sometime ago, partial documentation as follows.

-- Database Backups:
;WITH b1 AS (
SELECT backup_set_id bsid, media_set_id msid, database_name
, backup_start_date, backup_finish_date
, DATEDIFF(ss, backup_start_date, backup_finish_date) bu_sec, backup_size bsize
, compressed_backup_size csize, [type] btype
FROM msdb..backupset WHERE backup_start_date > '2018-07-24 15:25:42'
) SELECT database_name, btype, SUM(bsize) bsize, SUM(csize) csize
, SUM(bu_sec) bu_sec, MAX(bsize) msize, COUNT(*) Cnt
FROM b1 GROUP BY database_name, btype





DBU - database backup size
CDB - compressed size
IBU - Incremental?
LBU - Log backup size
DBUs - database backup secs

 

Prev        Next