Hey Guys!
Nesse post bem rápido, gostaria de compartilhar com vocês um script simples, mostrando como listar as maiores tabelas e índices do banco de dados e retornar o tamanho de cada tabela e cada índice, podendo também, listar os objetos de todos os databases da sua instância SQL Server.
Eu já havia criado alguns artigos relacionados à tamanho dos datafiles e tamanho dos discos, mas não tinha um artigo específico para tamanho das tabelas de índices, então pensei ser hora de criar algo assim.
Também já tinha compartilhado esse mesmo script no artigo SQL Server – Consultas úteis do dia a dia do DBA que você sempre tem que ficar procurando na Internet, mas a indexação não funciona tão bem para achar esse script aqui.
- SQL Server – Como identificar e monitorar o espaço em disco total, livre e utilizado pelos datafiles dos databases
- SQL Server – Como identificar e monitorar os discos, espaço em disco total, livre e utilizado
- Monitorando os eventos de crescimento automático de espaço (Autogrowth) em databases no SQL Server
Para listar as maiores tabelas do banco de dados e retornar o tamanho de cada tabela, basta executar o script abaixo no banco desejado:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SELECT TOP 100 s.[name] AS [schema], t.[name] AS [table_name], p.[rows] AS [row_count], CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb], CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb], CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb] FROM sys.tables t JOIN sys.indexes i ON t.[object_id] = i.[object_id] JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id JOIN sys.allocation_units a ON p.[partition_id] = a.container_id LEFT JOIN sys.schemas s ON t.[schema_id] = s.[schema_id] WHERE t.is_ms_shipped = 0 AND i.[object_id] > 255 GROUP BY t.[name], s.[name], p.[rows] ORDER BY [size_mb] DESC |
Result:
Caso você queira listar as maiores tabelas, considerando todos os bancos de dados da sua instância, utilize o script abaixo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
IF (OBJECT_ID('tempdb..#tamanho_tabelas') IS NOT NULL) DROP TABLE #tamanho_tabelas CREATE TABLE #tamanho_tabelas ( [database] NVARCHAR(256), [schema] NVARCHAR(256), [table_name] NVARCHAR(256), [row_count] BIGINT, [size_mb] DECIMAL(36, 2), [used_mb] DECIMAL(36, 2), [unused_mb] DECIMAL(36, 2) ) INSERT INTO #tamanho_tabelas EXEC sys.[sp_MSforeachdb] ' IF (''?'' NOT IN (''model'', ''master'', ''tempdb'', ''msdb'')) BEGIN SELECT TOP 100 ''?'' AS [database], s.[name] AS [schema], t.[name] AS [table_name], p.[rows] AS [row_count], CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb], CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb], CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb] FROM [?].sys.tables t JOIN [?].sys.indexes i ON t.[object_id] = i.[object_id] JOIN [?].sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id JOIN [?].sys.allocation_units a ON p.[partition_id] = a.container_id LEFT JOIN [?].sys.schemas s ON t.[schema_id] = s.[schema_id] WHERE t.is_ms_shipped = 0 AND i.[object_id] > 255 GROUP BY t.[name], s.[name], p.[rows] ORDER BY 5 DESC END' SELECT TOP 100 * FROM [#tamanho_tabelas] ORDER BY [size_mb] DESC |
Result:
Outra necessidade muito comum também, é encontrar os maiores índices do banco para analisar se eles ainda são úteis ou se estão ocupando muito espaço desnecessariamente.
Para isso, basta utilizar o script abaixo para retornar a lista dos maiores índices do banco de dados:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SELECT TOP(100) s.[name] AS [schema], t.[name] AS [table_name], i.[name] AS [index_name], i.[type_desc], p.[rows] AS [row_count], CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb], CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb], CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb] FROM sys.tables t JOIN sys.indexes i ON t.[object_id] = i.[object_id] JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id JOIN sys.allocation_units a ON p.[partition_id] = a.container_id LEFT JOIN sys.schemas s ON t.[schema_id] = s.[schema_id] WHERE t.is_ms_shipped = 0 AND i.[object_id] > 255 GROUP BY t.[name], s.[name], i.[name], i.[type_desc], p.[rows] ORDER BY [size_mb] DESC |
Result:
E se quiser realizar a mesma consulta, em todos os bancos da sua instância, utilize esse script aqui:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
IF (OBJECT_ID('tempdb..#tamanho_indices') IS NOT NULL) DROP TABLE #tamanho_indices CREATE TABLE #tamanho_indices ( [database] NVARCHAR(256), [schema] NVARCHAR(256), [table_name] NVARCHAR(256), [index_name] NVARCHAR(256), [index_type] NVARCHAR(50), [row_count] BIGINT, [size_mb] DECIMAL(36, 2), [used_mb] DECIMAL(36, 2), [unused_mb] DECIMAL(36, 2) ) INSERT INTO #tamanho_indices EXEC sys.[sp_MSforeachdb] ' IF (''?'' NOT IN (''model'', ''master'', ''tempdb'', ''msdb'')) BEGIN SELECT TOP(100) ''?'' AS [database], s.[name] AS [schema], t.[name] AS [table_name], i.[name] AS [index_name], i.[type_desc], p.[rows] AS [row_count], CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb], CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb], CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb] FROM [?].sys.tables t JOIN [?].sys.indexes i ON t.[object_id] = i.[object_id] JOIN [?].sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id JOIN [?].sys.allocation_units a ON p.[partition_id] = a.container_id LEFT JOIN [?].sys.schemas s ON t.[schema_id] = s.[schema_id] WHERE t.is_ms_shipped = 0 AND i.[object_id] > 255 GROUP BY t.[name], s.[name], i.[name], i.[type_desc], p.[rows] ORDER BY 7 DESC END' SELECT TOP 100 * FROM [#tamanho_indices] ORDER BY [size_mb] DESC |
Result:
And that's it, folks!
Espero que tenham gostado dessa dica, um grande abraço e até a próxima!
Portuguese 
English
Vou guardar esses scripts, vai me ajudar em algum momento, obrigado!
Tem um erro no seu relatorio de tamanho por instancia
nao pode utilizar o [top 100] no primeiro select p corre risco de excluir tabelas grandes na seleçao
Boa, Isac! Você está certo. Adicionei um ORDER BY lá no código.