When troubleshooting disk I/O bottlenecks you may want to see what the largest tables are. Rather than looking at rows, it is more important to look at table size in bytes - after all disk I/O is all about bytes, not rows.
This script will give you bytes and rows for all tables in all databases in this instance. The heart of this is to call sp_spaceused for each table and store the results into a table.
Table size in rows and bytes for all tables in all databases on this SQL instance.
No dependencies in this script.
/* DATE AUTHOR REMARKS
5/16/18 PPaiva Initial creation.
NAME
Table
size by rows and bytes
DESCRIPTION
Gathers
table size by rows and bytes for all databases in this instance.
Results
are stored in table diagSpaceUsed which is not dropped at the end,
but
is dropped if pre-existing prior to running again. This way you can
query
this table differently to meet your needs.
*/
DECLARE @ID int,
@MaxID
int,
@Name
nvarchar(128),
@MaxDB
nvarchar(300),
@SchemaName
nvarchar(50),
@s
nvarchar(2000),
@ServerName
varchar(50) = Convert(varchar(50), ServerProperty('ServerName')),
@sNow
varchar(30) = Convert(varchar, GetDate(), 120), -- Allows the population date to be consistent
@DBNameForSpaceusedTable
varchar(30) = db_name(),
@ShowSql
bit = 0,
@DB
varchar(300) = 'All'
IF Object_ID('dbo.diagSpaceUsed') Is NOT Null
DROP TABLE dbo.diagSpaceUsed
-- Create table only once
IF Object_ID('dbo.diagSpaceUsed') Is Null
BEGIN
-- Left indented columns are inserted and can be NOT NULL
-- The rest must be NULL unless calculated or a default
exists
SET
@s = '
CREATE TABLE dbo.diagSpaceUsed(
ID int IDENTITY(1,1) NOT NULL CONSTRAINT pk_diagSpaceUsed PRIMARY KEY
CLUSTERED,
Instance varchar(100) NOT NULL CONSTRAINT def_diagSpaceUsed_Instance
DEFAULT ''' + @ServerName + ''',
DB varchar(100) NULL,
SchemaName varchar(50) NULL,
TableName nvarchar(128) NOT NULL,
Rows bigint NOT NULL,
SizeMB decimal(22, 3) NULL,
TotalMB AS
(CONVERT(int,replace(index_size,''KB'',''''),0)/(1000.0)+CONVERT(int,replace(data,''KB'',''''),0)/(1000.0)),
IndexMB AS
(CONVERT(int,replace(index_size,''KB'',''''),0)/(1000.0)),
DataMB AS
(CONVERT(int,replace(data,''KB'',''''),0)/(1000.0)),
ReservedMB AS
(CONVERT(int,replace(Reserved,''KB'',''''),0)/(1000.0)),
object_id int NULL,
Reserved varchar(18) NOT NULL,
data varchar(18) NOT NULL,
index_size varchar(18) NOT NULL,
unused varchar(18) NULL,
PopDate datetime NULL
)
'
IF
@ShowSql = 1
Print @s
Exec(@s)
CREATE UNIQUE INDEX
ix_diagSpaceUsed_InstanceDBSchemaTable
ON dbo.diagSpaceUsed (Instance, DB, SchemaName, TableName)
CREATE INDEX ix_diagSpaceUsed_Rows
ON dbo.diagSpaceUsed (Rows)
END
IF @DB = 'All'
TRUNCATE TABLE dbo.diagSpaceUsed
ELSE
DELETE
FROM dbo.diagSpaceUsed
WHERE DB = @DB
OR DB Is Null
IF Object_ID('tempdb.dbo.#diagSpaceUsedDBList') Is Not Null
DROP TABLE #diagSpaceUsedDBList
IF Object_ID('tempdb.dbo.#PopSpaceUsedTableList') Is Not Null
DROP TABLE #PopSpaceUsedTableList
IF @DB <> 'All'
IF Not Exists( SELECT *
FROM sys.databases
WHERE name = @DB
AND state_desc = 'online')
BEGIN
SET @s = 'Database <' + @DB + '> was not found on this
server.'
RaisError(@s, 16, 1)
RETURN
END
-- Since sp_spaceused only works for the
current database context,
-- we have to execute it as embedded
SQL. This table will contain
-- the names of all tables with database
names, then we are ready
-- to call sp_spaceused.
CREATE TABLE #PopSpaceUsedTableList(
ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
DB nvarchar(300),
object_id int,
SchemaName nvarchar(50),
TableName nvarchar(128)
)
IF Object_ID('tempdb.dbo.#SpaceUsedDBList') Is Not Null
DROP TABLE #SpaceUsedDBList
SELECT name
INTO #SpaceUsedDBList
FROM sys.databases
WHERE name Not In ('tempdb')
--WHERE name Not In ('master', 'model',
'msdb', 'tempdb')
AND state_desc = 'online'
AND (name = @DB OR @DB = 'All')
SELECT @MaxDB = Max(name),
@DB = ''
FROM #SpaceUsedDBList
-- For all designated databases
WHILE @DB < @MaxDB
BEGIN
-- Get next DB
SELECT @DB = Min(name)
FROM
#SpaceUsedDBList
WHERE
name > @DB
-- Get table list for this DB
SET
@s = 'INSERT INTO
#PopSpaceUsedTableList (DB, object_id, SchemaName, TableName)
SELECT ''' + @DB + ''' , t.object_id, s.name
SchemaName, t.Name
FROM [' + @DB + '].sys.tables t
JOIN [' + @DB + '].sys.schemas s
ON t.schema_id = s.schema_id
WHERE t.name Not In (''dtproperties'')
ORDER BY t.name, s.name'
Exec(@s)
END
IF @ShowSql = 1
SELECT *
FROM
#PopSpaceUsedTableList
SELECT @MaxID =
Max(ID),
@ID = 0
FROM #PopSpaceUsedTableList
-- Ready to call out sp_spaceused for
each table in designated DBs
WHILE @ID < @MaxID
BEGIN
-- Get next table
SELECT @ID = Min(ID)
FROM
#PopSpaceUsedTableList
WHERE
ID > @ID
SELECT @DB = DB,
@Name = TableName,
@SchemaName = SchemaName
FROM
#PopSpaceUsedTableList
WHERE
ID = @ID
SET
@s = 'use [' + @DB + '] INSERT INTO ' + @DBNameForSpaceusedTable + '.dbo.diagSpaceUsed (TableName,
Rows, Reserved, data, index_size, unused)
Exec
sp_spaceused ''['
+ @SchemaName + '].[' + @Name + ']'''
IF
@ShowSql = 1
Print @s
Exec(@s)
UPDATE diagSpaceUsed
SET
SchemaName = @SchemaName,
DB = @DB,
PopDate = @sNow
WHERE
ID = @ID
END
UPDATE diagSpaceUsed
SET Instance = Convert(varchar, ServerProperty('ServerName')),
SizeMB = Convert(decimal(22, 3), Replace(data, ' KB', '')) / 1000.
-- Strip out [schema]. from TableName
UPDATE diagSpaceUsed
SET TableName = Replace(Replace(
Replace(TableName, '[', '' ),
']', ''),
SchemaName
+ '.', '')
UPDATE diagSpaceUsed
SET object_id = t.object_id
FROM diagSpaceUsed s
JOIN #PopSpaceUsedTableList t
ON s.DB
= t.DB COLLATE SQL_Latin1_General_CP1_CI_AS
AND s.SchemaName = t.SchemaName COLLATE SQL_Latin1_General_CP1_CI_AS
AND s.TableName = t.TableName COLLATE SQL_Latin1_General_CP1_CI_AS
SELECT '5 TableSize' Query,
Instance,
DB,
SchemaName,
TableName,
Rows,
TotalMB,
SizeMB,
IndexMB,
DataMB,
ReservedMB,
Object_id,
PopDate,
ID
FROM diagSpaceUsed
--ORDER BY Instance, Rows desc
--ORDER BY Instance, DB, Rows desc
ORDER BY Instance, TotalMB desc
--ORDER BY Instance, DB, TotalMB
desc
