Wednesday, January 20, 2016

Determine space used by each SQL Server table

A customer asked me today, 'how much space are all my tables taking?'.  Great question! It is very helpful to know the size of your tables;  not just the rowcount, but the physical space each table is using.  This is a quick query that you can use for just that.  I am JOINing sys.tables, sys.indexes and a couple more catalog views, and returning back the record count and the size of the data and index pages for each of your tables.

WITH spaceUsed
AS (
        o.object_id, [SchemaName], [TableName],
        CASE MAX(i.index_id) WHEN 1 THEN 'Clustered' ELSE 'Heap' END [IndexType],
        SUM(p.rows) [RecordCount],
        SUM(a.total_pages) [DataPages]
        sys.tables o INNER JOIN sys.indexes i
          ON i.object_id = o.object_id INNER JOIN sys.partitions p
            ON p.object_id = o.object_id
            AND p.index_id = i.index_id INNER JOIN sys.allocation_units a
              ON a.container_id = p.partition_id INNER JOIN sys.schemas s
               ON s.schema_id = o.schema_id
        o.type = 'U'
        AND i.index_id IN(0,1)
    GROUP BY,,
AS (
         o.object_id, [TableName],
         SUM(a.total_pages) [IndexPages]
         sys.objects o INNER JOIN sys.indexes i
           ON i.object_id = o.object_id INNER JOIN sys.partitions p
              ON p.object_id = o.object_id
AND p.index_id = i.index_id INNER JOIN sys.allocation_units a
                ON a.container_id = p.partition_id
         i.index_id <> 0
      GROUP BY,

     -- return details
         (s.DataPages * 8) [DataPageSizeKB],
         ISNULL(i.IndexPages, 0) IndexPages,
         (ISNULL(i.IndexPages, 0) * 8) [IndexPageSizeKB]
         spaceUsed s LEFT JOIN IndexPages i
           ON s.object_id = i.object_id

This is the output from AdventureWorks2012 on my own instance:

Nothing too complex. There's probably several other details you could include as well. Take a look at this reference to the each of the catalog views I used, as well as many more:

Wednesday, January 6, 2016

Can I get the index creation date from SQL Server?

um.... No.  Well, not for ALL of them, anyway.  The sys.indexes catalog view does not include a creation date for the indexes, and sys.objects only stores data for indexes associated with primary key and unique constraints.  So... we do not have a create date for indexes that are not associated with primary or unique constraints.

Take a look at the two queries below;  the 1st one joins sys.objects and sys.indexes, but the date returned is of the table creation, not the index. It may be close, but it's not guaranteed to be the date the index was created.  The 2nd query, however, just takes a look into sys.objects for the create date (crdate) of the index that SQL Server creates for us automatically when any PRIMARY KEY or UNIQUE constraint is created. This one is definitely IS the create date for the indexes associated with each constraint.

-- #1. Not the index create date
USE AdventureWorks2012;
SELECT [IndexName], [TableName],
    o.create_date [Created] -- this is the table, not the index
    sys.indexes i INNER JOIN sys.objects o
      ON i.object_id = o.object_id
WHERE IN ('BusinessEntityContact','UnitMeasure')



-- #2. crdate for primary/unique constraint indexes
    name [IndexName],
    crdate [Created]
    xtype IN('pk','uq'-- all you need to find pk/uq constraints
    AND name IN


The AND portion of the WHERE clause in query #2 is only there because I wanted to give you back the same constraints returned in query #1.  If you take a look at the create dates for both, you will see they are definitely different than the date the tables were created.

So... can we get the created date for ALL indexes?  Sure, but not with out-of-the-box SQL Server. You could use a DDL event trigger to audit object creation/modifications. Possibly the topic of a later post....

Until then, please take a look at this reference to sys.indexes.