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.
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.
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.
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