Last active
March 30, 2021 08:57
-
-
Save NJLangley/6c4552efe480c75d17c811978f8bbfef to your computer and use it in GitHub Desktop.
TSQL Partitioning Scripts
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT TOP 20 | |
tables.[name] as table_name, | |
schemas.[name] as schema_name, | |
isnull(db_name(dm_db_index_usage_stats.database_id), 'Unknown') as database_name, | |
sum(allocation_units.total_pages) * 8 as total_space_kb, | |
cast(round(((sum(allocation_units.total_pages) * 8) / 1024.00), 2) as numeric(36, 2)) as total_space_mb, | |
sum(allocation_units.used_pages) * 8 as used_space_kb, | |
cast(round(((sum(allocation_units.used_pages) * 8) / 1024.00), 2) as numeric(36, 2)) as used_space_mb, | |
(sum(allocation_units.total_pages) - sum(allocation_units.used_pages)) * 8 as unused_space_kb, | |
cast(round(((sum(allocation_units.total_pages) - sum(allocation_units.used_pages)) * 8) / 1024.00, 2) as numeric(36, 2)) as unused_space_mb, | |
count(distinct indexes.index_id) as indexes_count, | |
max(dm_db_partition_stats.row_count) as row_count, | |
iif(max(isnull(user_seeks, 0)) = 0 and max(isnull(user_scans, 0)) = 0 and max(isnull(user_lookups, 0)) = 0, 1, 0) as no_reads, | |
iif(max(isnull(user_updates, 0)) = 0, 1, 0) as no_writes, | |
max(isnull(user_seeks, 0)) as user_seeks, | |
max(isnull(user_scans, 0)) as user_scans, | |
max(isnull(user_lookups, 0)) as user_lookups, | |
max(isnull(user_updates, 0)) as user_updates, | |
max(last_user_seek) as last_user_seek, | |
max(last_user_scan) as last_user_scan, | |
max(last_user_lookup) as last_user_lookup, | |
max(last_user_update) as last_user_update, | |
max(tables.create_date) as create_date, | |
max(tables.modify_date) as modify_date | |
from | |
sys.tables | |
left join sys.schemas on schemas.schema_id = tables.schema_id | |
left join sys.indexes on tables.object_id = indexes.object_id | |
left join sys.partitions on indexes.object_id = partitions.object_id and indexes.index_id = partitions.index_id | |
left join sys.allocation_units on partitions.partition_id = allocation_units.container_id | |
left join sys.dm_db_index_usage_stats on tables.object_id = dm_db_index_usage_stats.object_id and indexes.index_id = dm_db_index_usage_stats.index_id | |
left join sys.dm_db_partition_stats on tables.object_id = dm_db_partition_stats.object_id and indexes.index_id = dm_db_partition_stats.index_id | |
group by schemas.[name], tables.[name], isnull(db_name(dm_db_index_usage_stats.database_id), 'Unknown') | |
order by 5 desc |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment