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 (
       SELECT
        o.object_id,
        s.name [SchemaName],
        o.name [TableName],
        CASE MAX(i.index_id) WHEN 1 THEN 'Clustered' ELSE 'Heap' END [IndexType],
        SUM(p.rows) [RecordCount],
        SUM(a.total_pages) [DataPages]
    FROM
        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
    WHERE
        o.type = 'U'
        AND i.index_id IN(0,1)
    GROUP BY
        s.name,
        o.name,
        o.object_id
    ),
IndexPages 
AS (
      SELECT
         o.object_id,
         o.name [TableName],
         SUM(a.total_pages) [IndexPages]
      FROM
         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
      WHERE
         i.index_id <> 0
      GROUP BY
         o.name,
         o.object_id
   )

     -- return details
     SELECT
         s.SchemaName,
         s.TableName,
         s.IndexType,
         s.RecordCount,
         s.DataPages,
         (s.DataPages * 8) [DataPageSizeKB],
         ISNULL(i.IndexPages, 0) IndexPages,
         (ISNULL(i.IndexPages, 0) * 8) [IndexPageSizeKB]
     FROM
         spaceUsed s LEFT JOIN IndexPages i
           ON s.object_id = i.object_id
     ORDER BY
         s.SchemaName,
         s.TableName

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
    i.name [IndexName],
    o.name [TableName],
    o.create_date [Created] -- this is the table, not the index
FROM
    sys.indexes i INNER JOIN sys.objects o
      ON i.object_id = o.object_id
WHERE
    o.name IN ('BusinessEntityContact','UnitMeasure')

Results:



     






-- #2. crdate for primary/unique constraint indexes
SELECT
    name [IndexName],
    crdate [Created]
FROM
    sys.sysobjects
WHERE
    xtype IN('pk','uq'-- all you need to find pk/uq constraints
    AND name IN
('PK_BusinessEntityContact_BusinessEntityID_PersonID_ContactTypeID','PK_UnitMeasure_UnitMeasureCode')

Result:
    







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.  

    https://msdn.microsoft.com/en-IN/library/ms173760.aspx