Troubleshooting
Problem
Customer/consultant would like to find out how large each of their Controller database tables (for example 'xdb18') are. How can they do this?
Symptom
Customer is suffering from a problem (for example slow performance) where it is possible that the issue is being caused by a large size of a specific table.
Environment
The script (inside this Technote) has been tested on all modern versions of Microsoft SQL (including 2000, 2005, 2008, 2014, 2016 & SQL 2019).
- TIP: For similar instructions when using DB2, see separate IBM Technote #788433.
Resolving The Problem
Run the attached SQL script ("Tablesize.sql") on your database. This provides:
- Number of records
- Table Size
- Data Space Used
- Index Space Used
- Unused Space
Steps:
1. Logon to the SQL server as an administrator
2. Launch SQL Server Management Studio
3. Right-click on the Controller database, and choose 'New Query'
4. Paste the script (below) into the screen on the right-hand side
SET NOCOUNT ON
/* 0 = Alphabetically BY TABLE name
1 = Sorted BY total space used by TABLE
*/
DECLARE @strSQL varchar(100)
DECLARE @bitSort Bit
DECLARE @vchSortString varchar(50)
-- Edit this value FOR sorting options 0=Alphabetically and 1 = Table Size Descending
SELECT @bitSort = 1
-- Create Temporary Table
CREATE TABLE #TempTable (
vchTableName varchar(100),
biRowCount bigInt,
vchTableSize varchar(50),
vchDataSpaceUsed varchar(50),
vchIndexSpaceUsed varchar(50),
vchUnusedSpace varchar(50))
-- Create SQL Statement to run Stored Procedure String
SELECT @strSQL = 'sp_msforeachtable ''sp_spaceused "?"'''
-- Populate Temporary Table
INSERT INTO #TempTable EXEC(@strSQL)
-- Run different queries depending on sorting methed selected
IF @bitSort = 0 -- Sort by vchTableName Ascending
SELECT vchTableName, biRowCount,
CASE WHEN CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchTableSize END AS vchTableSize,
CASE WHEN CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchDatASpaceUsed END AS vchDatASpaceUsed,
CASE WHEN CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchIndexSpaceUsed END AS vchIndexSpaceUsed,
CASE WHEN CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchUnusedSpace END AS vchUnusedSpace
FROM #TempTable
ORDER BY vchTableName
ELSE -- Sort by Total Table Size Descending
SELECT vchTableName, biRowCount,
CASE WHEN CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchTableSize END AS vchTableSize,
CASE WHEN CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchDatASpaceUsed END AS vchDatASpaceUsed,
CASE WHEN CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchIndexSpaceUsed END AS vchIndexSpaceUsed,
CASE WHEN CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchUnusedSpace END AS vchUnusedSpace
FROM #TempTable
ORDER BY cast(left(vchTableSize,charindex(' ',vchTableSize)) AS bigInt) DESC
-- Delete Temp Table
DROP TABLE #TempTable
/* 0 = Alphabetically BY TABLE name
1 = Sorted BY total space used by TABLE
*/
DECLARE @strSQL varchar(100)
DECLARE @bitSort Bit
DECLARE @vchSortString varchar(50)
-- Edit this value FOR sorting options 0=Alphabetically and 1 = Table Size Descending
SELECT @bitSort = 1
-- Create Temporary Table
CREATE TABLE #TempTable (
vchTableName varchar(100),
biRowCount bigInt,
vchTableSize varchar(50),
vchDataSpaceUsed varchar(50),
vchIndexSpaceUsed varchar(50),
vchUnusedSpace varchar(50))
-- Create SQL Statement to run Stored Procedure String
SELECT @strSQL = 'sp_msforeachtable ''sp_spaceused "?"'''
-- Populate Temporary Table
INSERT INTO #TempTable EXEC(@strSQL)
-- Run different queries depending on sorting methed selected
IF @bitSort = 0 -- Sort by vchTableName Ascending
SELECT vchTableName, biRowCount,
CASE WHEN CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchTableSize END AS vchTableSize,
CASE WHEN CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchDatASpaceUsed END AS vchDatASpaceUsed,
CASE WHEN CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchIndexSpaceUsed END AS vchIndexSpaceUsed,
CASE WHEN CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchUnusedSpace END AS vchUnusedSpace
FROM #TempTable
ORDER BY vchTableName
ELSE -- Sort by Total Table Size Descending
SELECT vchTableName, biRowCount,
CASE WHEN CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchTableSize END AS vchTableSize,
CASE WHEN CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchDatASpaceUsed END AS vchDatASpaceUsed,
CASE WHEN CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchIndexSpaceUsed END AS vchIndexSpaceUsed,
CASE WHEN CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchUnusedSpace END AS vchUnusedSpace
FROM #TempTable
ORDER BY cast(left(vchTableSize,charindex(' ',vchTableSize)) AS bigInt) DESC
-- Delete Temp Table
DROP TABLE #TempTable
6. Press 'Execute'.
7. The output will look similar to:
8. Optionally, you can now export this information into either Excel or Notepad:
- Left mouse-click on the top-left (empty/blank) cell in the 'results' (which will make all the cells highlight in blue):
- Right-click on any of the blue (highlighted) cells, and choose "Copy" (or "Copy with Headers" if you prefer):
- Launch Excel (or Notepad) and paste the contents of the clipboard (to create an XLSX or TXT file).
Related Information
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"ARM Category":[{"code":"a8m0z000000GmxvAAC","label":"Database Tables"}],"ARM Case Number":"TS003997135","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Version(s)","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Historical Number
1021995
Was this topic helpful?
Document Information
Modified date:
06 August 2020
UID
swg21345780