Как найти самые большие объекты в базе данных SQL Server?

Как я могу найти самые большие объекты в базе данных SQL Server? Во-первых, определяя, какие таблицы (и связанные с ними индексы) являются самыми большими, а затем определяя, какие строки в конкретной таблице являются самыми большими (мы храним двоичные данные в больших двоичных объектах)?



есть ли какие-либо инструменты, чтобы помочь с такого рода анализа базы данных? Или есть несколько простых запросов, которые я мог бы запустить против системных таблиц?

623   7  

7 ответов:

Я уже давно использую этот SQL - скрипт (который я получил от кого-то, где-то-не могу восстановить, от кого он пришел), и это помогло мне немного понять и определить размер индексов и таблиц:

SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    sum(p.rows) as RowCounts,
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
    object_name(i.object_id)

конечно, вы можете использовать другие критерии заказа, например

ORDER BY SUM(p.rows) DESC

чтобы получить таблицы с наибольшим количеством строк, или 

ORDER BY SUM(a.total_pages) DESC

чтобы получить таблицы с большинством страниц (8K блоков) используется.

235  
2016-09-29 00:09:30

в SQL Server 2008 вы также можете просто запустить стандартный отчет об использовании диска по верхним таблицам. Это можно найти Правой Кнопкой Мыши БД, выбрав Отчеты- > Стандартные отчеты и выбрав нужный отчет.

50  
2017-11-13 18:58:07

вы также можете использовать следующий код:

USE AdventureWork
GO
CREATE TABLE #GetLargest 
(
  table_name    sysname ,
  row_count     INT,
  reserved_size VARCHAR(50),
  data_size     VARCHAR(50),
  index_size    VARCHAR(50),
  unused_size   VARCHAR(50)
)

SET NOCOUNT ON

INSERT #GetLargest

EXEC sp_msforeachtable 'sp_spaceused ''?'''

SELECT 
  a.table_name,
  a.row_count,
  COUNT(*) AS col_count,
  a.data_size
  FROM #GetLargest a
     INNER JOIN information_schema.columns b
     ON a.table_name collate database_default
     = b.table_name collate database_default
       GROUP BY a.table_name, a.row_count, a.data_size
       ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC

DROP TABLE #GetLargest
3  
2013-07-28 00:27:31

этот запрос поможет найти самую большую таблицу в вашем соединении.

SELECT  TOP 1 OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDER BY st.row_count DESC
3  
2017-10-10 11:37:59

при использовании среды Sql Server Management Studio 2008 в окне сведения обозревателя объектов можно просмотреть определенные поля данных. Просто найдите и выберите папку таблицы. В подробном представлении вы можете щелкнуть правой кнопкой мыши заголовки столбцов и добавить поля в "отчет". Ваш пробег может отличаться, если вы находитесь на SSMS 2008 express.

2  
2010-01-19 17:36:15

Я нашел этот запрос также очень полезным в SqlServerCentral, вот ссылка на оригинальный пост

самые большие таблицы Sql Server

  select name=object_schema_name(object_id) + '.' + object_name(object_id)
, rows=sum(case when index_id < 2 then row_count else 0 end)
, reserved_kb=8*sum(reserved_page_count)
, data_kb=8*sum( case 
     when index_id<2 then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count 
     else lob_used_page_count + row_overflow_used_page_count 
    end )
, index_kb=8*(sum(used_page_count) 
    - sum( case 
           when index_id<2 then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count 
        else lob_used_page_count + row_overflow_used_page_count 
        end )
     )    
, unused_kb=8*sum(reserved_page_count-used_page_count)
from sys.dm_db_partition_stats
where object_id > 1024
group by object_id
order by 
rows desc

в моей базе данных они дали разные результаты между этим запросом и 1-м ответом.

надеюсь, кто-то найдет полезным

2  
2014-04-15 19:54:32

ответ @ marc_s очень велик, и я использую его в течение нескольких лет. Однако я заметил, что скрипт пропускает данные в некоторых индексах columnstore и не показывает полную картину. Например, когда вы делаете SUM(TotalSpace) против скрипта и сравните его со свойством базы данных total space В Management Studio числа не совпадают в моем случае (Management Studio показывает большие числа). Я изменил скрипт, чтобы преодолеть эту проблему и немного расширил его:

select
    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

надеюсь, что это будет будьте полезны для кого-то. Этот сценарий был протестирован на больших базах данных ТБ с сотнями различных таблиц, индексов и схем.

0  
2018-10-01 21:32:17

Comments

    Ничего не найдено.
Click here to cancel reply.