3

Using the following query I can get the size of the indexes for a table:

SELECT
    i.name              AS IndexName,
    SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(db_id(), object_id('schema.table'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name

If I execute sp_spaceused 'schema.table' does the size without indexes correspond to data column?

Any way to get the size of the table without indexes in sql server? (SELECT statement would be nice as I'd like to plug this query into a bigger query).

If I execute Get size of all tables in database, the sizes for tables are almost the same as the sizes for indexes (suggesting that something is wrong).

2 Answers 2

6

There are multiple ways of finding size of a table in the database. First and easiest way is to right click on Table and check the storage tab and this gives you Data space and Index Space. Data space is nothing but size of data in the table and similarly index space gives you size of index in the mentioned table.

Table Storage

The above information would exactly match with the command sp_spaceused 'schema_name.table_name' We can cross check the same for the Users Table(in dbo schema and StackOverFlow Database).

sp_spaceused

Here, if you add data, index size and unused(in KB), you will get reserved space by table.

You may also check the size of table and index by running command sp_blitzindex(from Mr. Brent Ozar). Command would be as below:

exec sp_BlitzIndex  @SchemaName = 'dbo', @TableName = 'Users'

Here, you may check the Size column. Clustered index size will give you Table size and you can also find size of each and every index as well as their usage(if interested).

Moreover you can also check table size of all of them by getting into Reports as below:

Standard Report

You may also run below select statement to get full size and index size of all the tables:

;with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
            WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
            ELSE lob_used_page_count + row_overflow_used_page_count
        END) as pages
FROM sys.dm_db_partition_stats  AS s 
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select
    cte.TableName, 
    cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB, 
    cast(((CASE WHEN cte.used_pages_count > cte.pages 
                THEN cte.used_pages_count - cte.pages
                ELSE 0 
          END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc

If you want only the table sizes then execute the following:

SELECT
t.name as TableName
, SUM (CASE WHEN (i.index_id < 2) 
          THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
          ELSE lob_used_page_count + row_overflow_used_page_count
     END) * 8 as table_size_kb
FROM sys.dm_db_partition_stats  AS s 
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
where t.object_id = OBJECT_ID('schema.table')
GROUP BY t.name

Hope this helps.

6
  • is it possible to get this from a sql query too? I'd like to connect this with another query I am running on the system. does data from sp_spaceused return the size of the table without indexes (from the post you do not explicitly say what data is)?
    – arthur
    Commented Jan 29, 2021 at 10:25
  • @arthur Edited my answer, please check if this helps. Commented Jan 29, 2021 at 10:38
  • I confirm that the query shows similar results to the ones of sp_spacedused. Can you discuss the query? In my case there's only index_id=1 and no index_id=0 (in the cte), and that index_id points to the primary index of the table. So from there you deduce the number of pages used overall? Pretty sophisticated stuff
    – arthur
    Commented Jan 29, 2021 at 11:38
  • Index id 1 means its Primary key(mostly clustered index). Size of Clusterd Indexis data size of Table itself. Commented Jan 29, 2021 at 11:53
  • @Learning_DBAdmin Don't add confusion. "Primary key" and "clustered index" are not synonyms.
    – SMor
    Commented Jan 29, 2021 at 13:53
2

The DMV returns the information you want, you just need to know that index_id = 0 is for Heaps, & that Index_id 1 is for the clustered index

It is these that are reported as data in the output from sp_spaceused

use tempdb

create table heap(id int identity(1,1), txt nvarchar(max) default 'hahaha')

insert heap (txt) values ('one')

select object_name(object_id) as objname, * FROM sys.dm_db_index_physical_stats(db_id(), object_id('schema.table'), NULL, NULL, 'DETAILED') AS s where index_id = 0

2
  • does the version of sql server play a role? My is 2014 if I do select * from sys.dm_db_index_physical_stats(db_id(), object_id('schema.table'), 0, NULL, 'DETAILED') sql server complains that there's no index 0 :-(
    – arthur
    Commented Jan 29, 2021 at 9:57
  • that works on my little test harness select object_name(object_id) as objname, * FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.heap'), 0, NULL, 'DETAILED') AS s where index_id = 0 but you can always pass null values to the DMV - get all the data out & then filter in the where clause Commented Jan 29, 2021 at 10:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.