December 8, 2020 at 4:16 am
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 ?
December 8, 2020 at 8:02 am
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.
December 8, 2020 at 9:14 am
Ant-Green , Thank you for your kind help!
December 8, 2020 at 2:21 pm
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/
December 8, 2020 at 4:44 pm
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 will give you the shirt off *someone else's* back.
December 9, 2020 at 1:01 am
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!
December 9, 2020 at 1:38 am
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