how to get index data size?index created and updated time and etc.

  • sorry to bother all of you, because of curiosity, how can I get the following informations? thanks!

    1. how to get the created time and updated time for an index of table ?

    2. can we get the data size taken by an index of table?

    3. can we get the last time read/insert/update/delete for a table?

    4. can we get the numbers of times of select/insert/update/delete on a table ?

  • 1 - not possible - index creation date is not stored, last index rebuild/reorg date is also not stored.  Closest you would get is using STATS_DATE to find the last time the stats where updated, but if you have auto update stats switch on, well that could be something completely different to what your expecting

    2 - Remember google is your friend - https://www.sqlshack.com/how-to-monitor-total-sql-server-indexes-size/

    3 - possible if you look at sys.dm_db_index_usage_stats, but this tracks seeks & scans, not CRUD operations, so you would want the max last_user_seek or last_user_scan to give the last access time

    4 - again as above use sys.dm_db_index_usage_stats, but again this is in seeks and scans, so you would do math on the user_seeks, user_scans, user_lookups, user_updates etc columns, but this is cumulative since last restart so if you want to do day operations you need to store that in a monitoring table and do math between yesterdays values and todays values

    Nothing tracks CRUD operations unless you put something in place to monitor and capture that like XE.

  • Ant-Green , Thank you for your kind help!

  • This may get you some of what you want.

    SELECT 
    SS.name SchemaName,
    ST.name TableName,
    ISNULL(SI.name, '') IndexName,
    SI.type_desc IndexType,
    IUS.user_updates,
    IUS.user_seeks,
    IUS.user_scans,
    IUS.user_lookups,
    SSI.rowcnt,
    SSI.rowmodctr,
    IUS.last_user_seek,
    IUS.last_user_scan,
    IUS.last_user_lookup,
    IUS.last_user_update
    FROM sys.dm_db_index_usage_stats IUS
    RIGHT OUTER JOIN sys.indexes SI ON IUS.[object_id] = SI.[object_id]
    AND IUS.index_id = SI.index_id
    INNER JOIN sys.sysindexes SSI ON SI.object_id = SSI.id
    AND SI.name = SSI.name
    INNER JOIN sys.tables ST ON ST.[object_id] = SI.[object_id]
    INNER JOIN sys.schemas SS ON SS.[schema_id] = ST.[schema_id]
    WHERE IUS.database_id = DB_ID()
    AND OBJECTPROPERTY(IUS.[object_id], 'IsMsShipped') = 0;

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • 1. Not generally available. If an index was created by adding a PRIMARY KEY constraint, sys.objects would have the create time of that index.

    2. Yes. For example:

    SELECT

    t.name AS table_name, i.name AS index_name, --p.index_id,

    CEILING(SUM(total_pages) / 128.0) AS size_mb

    FROM sys.allocation_units au

    INNER JOIN sys.partitions p ON p.partition_id = au.container_id

    INNER JOIN sys.tables t ON t.object_id = p.object_id

    INNER JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id

    WHERE au.type <> 0

    GROUP BY t.name, p.index_id, i.name

    ORDER BY t.name, i.name

    3. You can get a good part of this from sys.dm_db_index_usage_stats

    4. Yes, by capturing results of sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats over time and comparing them

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • Michael L John wrote:

    This may get you some of what you want.

    SELECT 
    SS.name SchemaName,
    ST.name TableName,
    ISNULL(SI.name, '') IndexName,
    SI.type_desc IndexType,
    IUS.user_updates,
    IUS.user_seeks,
    IUS.user_scans,
    IUS.user_lookups,
    SSI.rowcnt,
    SSI.rowmodctr,
    IUS.last_user_seek,
    IUS.last_user_scan,
    IUS.last_user_lookup,
    IUS.last_user_update
    FROM sys.dm_db_index_usage_stats IUS
    RIGHT OUTER JOIN sys.indexes SI ON IUS.[object_id] = SI.[object_id]
    AND IUS.index_id = SI.index_id
    INNER JOIN sys.sysindexes SSI ON SI.object_id = SSI.id
    AND SI.name = SSI.name
    INNER JOIN sys.tables ST ON ST.[object_id] = SI.[object_id]
    INNER JOIN sys.schemas SS ON SS.[schema_id] = ST.[schema_id]
    WHERE IUS.database_id = DB_ID()
    AND OBJECTPROPERTY(IUS.[object_id], 'IsMsShipped') = 0;

    Thank you Michael L John!

  • ScottPletcher wrote:

    1. Not generally available. If an index was created by adding a PRIMARY KEY constraint, sys.objects would have the create time of that index.

    2. Yes. For example: SELECT t.name AS table_name, i.name AS index_name, --p.index_id, CEILING(SUM(total_pages) / 128.0) AS size_mb FROM sys.allocation_units au INNER JOIN sys.partitions p ON p.partition_id = au.container_id INNER JOIN sys.tables t ON t.object_id = p.object_id INNER JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id WHERE au.type <> 0 GROUP BY t.name, p.index_id, i.name ORDER BY t.name, i.name

    3. You can get a good part of this from sys.dm_db_index_usage_stats

    4. Yes, by capturing results of sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats over time and comparing them

     

    Noted and thank you for your kind help!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply