Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Запрос о размере таблиц БД  [new]
supremum_nsk
Member

Откуда:
Сообщений: 50
Помогите пожалуйста составить запрос возвращающий список таблиц в БД и их размер в таблице.
В sys.sysobjects данных о размере нет.
30 июн 10, 10:52    [9022677]     Ответить | Цитировать Сообщить модератору
 Re: Запрос о размере таблиц БД  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Юзать sp_spaceused. Или расковырять ее код и вытащить нужное.
30 июн 10, 10:57    [9022723]     Ответить | Цитировать Сообщить модератору
 Re: Запрос о размере таблиц БД  [new]
supremum_nsk
Member

Откуда:
Сообщений: 50
Гавриленко Сергей Алексеевич, намёк понял ! Благодарю !
30 июн 10, 10:58    [9022750]     Ответить | Цитировать Сообщить модератору
 Re: Запрос о размере таблиц БД  [new]
komrad
Member

Откуда:
Сообщений: 5735
supremum_nsk
Гавриленко Сергей Алексеевич, намёк понял ! Благодарю !


скрипт с этого форума (автора не помню):

select top 20 object_name(id) tbl, indid ,
reserved/128. as reserv, 
dpages/128. as data,
(reserved - dpages)/128. as delta from sysindexes
where indid in (0,1)
order by 4 desc
30 июн 10, 16:36    [9026196]     Ответить | Цитировать Сообщить модератору
 Re: Запрос о размере таблиц БД  [new]
Aleksey-K
Member

Откуда: Москва
Сообщений: 3116
supremum_nsk
Помогите пожалуйста составить запрос возвращающий список таблиц в БД и их размер в таблице.
В sys.sysobjects данных о размере нет.

Вот такой запрос посылается на сервер, когда вы используете Reports-> Standard Reports -> Disk Usage by Table

SELECT
	(row_number() over(order by a3.name, a2.name))%2 as l1,
	a3.name AS [schemaname],
	a2.name AS [tablename],
	a1.rows as row_count,
	(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved, 
	a1.data * 8 AS data,
	(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
	(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
FROM
	(SELECT 
		ps.object_id,
		SUM (
			CASE
				WHEN (ps.index_id < 2) THEN row_count
				ELSE 0
			END
			) AS [rows],
		SUM (ps.reserved_page_count) AS reserved,
		SUM (
			CASE
				WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
				ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
			END
			) AS data,
		SUM (ps.used_page_count) AS used
	FROM sys.dm_db_partition_stats ps
	GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN 
	(SELECT 
		it.parent_id,
		SUM(ps.reserved_page_count) AS reserved,
		SUM(ps.used_page_count) AS used
	 FROM sys.dm_db_partition_stats ps
	 INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
	 WHERE it.internal_type IN (202,204)
	 GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY a3.name, a2.name
Если надо, то его можно подкорректировать "под себя".
С уважением, Алексей
30 июн 10, 16:44    [9026264]     Ответить | Цитировать Сообщить модератору
 Re: Запрос о размере таблиц БД  [new]
db-admin
Guest
Если в общем виде нужно, для одной БД, то вот так

use БД
go
EXEC sp_spaceused @updateusage = N'TRUE'
go

select o.id,OBJECT_NAME(o.id),i1.dpages as dpages, i1.rowcnt as rows,
cast((ISNULL(SUM(i1.reserved), 0) + ISNULL(SUM(i2.reserved), 0)) * 8./1024 as decimal (18,2)) as reserv_mb,
cast((ISNULL(SUM(i1.dpages), 0) + ISNULL(SUM(i2.used), 0)) * 8./1024 as decimal (18,2)) as data_mb,
cast(((ISNULL(SUM(i1.used), 0) + ISNULL(SUM(i2.used), 0)) - (ISNULL(SUM(i1.dpages), 0) + ISNULL(SUM(i2.used), 0))) * 8./1024 as decimal (18,2)) as index_size,
cast(((ISNULL(SUM(i1.reserved), 0) + ISNULL(SUM(i2.reserved), 0))
- (ISNULL(SUM(i1.used), 0) + ISNULL(SUM(i2.used), 0))) * 8./1024 as decimal (18,2)) as unused_mb,
data =(convert(char (8),getdate(),112))
FROM sysobjects o
LEFT OUTER JOIN sysindexes i1 ON i1.id = o.id AND i1.indid < 2
LEFT OUTER JOIN sysindexes i2 ON i2.id = o.id AND i2.indid = 255
WHERE OBJECTPROPERTY(o.id, N'IsUserTable') = 1
OR (OBJECTPROPERTY(o.id, N'IsView') = 1 AND OBJECTPROPERTY(o.id, N'IsIndexed') = 1)
GROUP BY o.id, i1.dpages,i1.rowcnt
Если же данные нужны больше, чем по одной базе, то создаете справочник БД на сервере и это дело курсором. Работает и на MSSQL 2000, 2005,2008. Сам так собираю статистику роста БД по всем серверам. Этот кусок относиться непосредственно к размеру таблиц и индексов.
Надеюсь поможет.
30 июн 10, 18:21    [9027020]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Запрос о размере таблиц БД  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 487
Запрос вида
DBCC UPDATEUSAGE (0);
CREATE TABLE #t([имя таблицы] varchar(255), [строк] varchar(255), [зарезервировано] varchar(255), [всего данных] varchar(255), [размер индексов] varchar(255), [свободно] varchar(255));

INSERT INTO #t
exec sp_msforeachtable N'exec sp_spaceused ''?''';
select sum(convert(bigint,REPLACE([зарезервировано], ' KB', ''))) [зарезервировано], 
sum(convert(bigint,REPLACE([всего данных], ' KB', ''))) [всего данных], 
sum(convert(bigint,REPLACE([размер индексов], ' KB', ''))) [размер индексов], 
sum(convert(bigint,REPLACE([свободно], ' KB', ''))) [свободно]
from #t


вернул мне следующий результат (в кб)
зарезервировано  всего данных  размер индексов    свободно
92334352	   12421784	   5696840	  74215728

Т.е свободного места в таблицах примерно 80%(!)
Как может повлиять сжатие файлов базы данных на быстродействие при выполнении запросов? Увеличиться ли оно? Каковы могут быть отрицательные последствия?
12 мар 12, 19:11    [12234585]     Ответить | Цитировать Сообщить модератору
 Re: Запрос о размере таблиц БД  [new]
Glory
Member

Откуда:
Сообщений: 104751
Шамиль Фаридович
Как может повлиять сжатие файлов базы данных на быстродействие при выполнении запросов? Увеличиться ли оно?

Сжатие файлов не есть дефрагментация данных в таблицах
Поэтому быстродействие запросов не изменится

Шамиль Фаридович
Каковы могут быть отрицательные последствия?

Следующий запрос на добавление-изменение данных потребует места в базе. Которое опять придется запрашивать у оп.системы
12 мар 12, 19:53    [12234797]     Ответить | Цитировать Сообщить модератору
 Re: Запрос о размере таблиц БД  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 487
Glory,
возможно ли осуществить дефрагментацию?
13 мар 12, 09:52    [12236463]     Ответить | Цитировать Сообщить модератору
 Re: Запрос о размере таблиц БД  [new]
Glory
Member

Откуда:
Сообщений: 104751
BOL - Reorganizing and Rebuilding Indexes
13 мар 12, 11:19    [12237101]     Ответить | Цитировать Сообщить модератору
 Re: Запрос о размере таблиц БД  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 487
Glory,
спасибо за ссылку. Вот что меня смутило.
1. В описании dbcc shrinkdatabase сказано:
Выполнение операции DBCC SHRINKDATABASE без указания параметра NOTRUNCATE или 
TRUNCATEONLY равносильно выполнению операции DBCC SHRINKDATABASE с параметром NOTRUNCATE 
после выполнения операции DBCC SHRINKDATABASE с параметром TRUNCATEONLY.

По моему, логичнее было бы выполнять сначала с параметром NONTRUNCATE, а потом с TRUNCATEONLY. Что это, ошибка перевода?

2. Я провел перестроение всех индексов БД. Все индексы имеют практически нулевую дефрагментацию.
Запрос вида
DBCC UPDATEUSAGE (0);
CREATE TABLE #t([имя таблицы] varchar(255), [строк] varchar(255), [зарезервировано] varchar(255), 
[всего данных] varchar(255), [размер индексов] varchar(255), 
[свободно] varchar(255));

INSERT INTO #t
exec sp_msforeachtable N'exec sp_spaceused ''?''';
select sum(convert(bigint,REPLACE([зарезервировано], ' KB', ''))) [зарезервировано], 
sum(convert(bigint,REPLACE([всего данных], ' KB', ''))) [всего данных], 
sum(convert(bigint,REPLACE([размер индексов], ' KB', ''))) [размер индексов], 
sum(convert(bigint,REPLACE([свободно], ' KB', ''))) [свободно]
from #t


возвращает следующий результат (в кб)
зарезервировано  всего данных  размер индексов    свободно
762160	             552920	     208952	     288

Но
sp_helpdb Testing
выдает
Testing	        1503552 KB   data only
Testing_log	1024 KB	      log only

То есть размер файла данных почти в 2 раза больше, чем размер всех таблиц и индексов. На что расходуется 740 мб?
23 мар 12, 13:59    [12301857]     Ответить | Цитировать Сообщить модератору
 Re: Запрос о размере таблиц БД  [new]
Glory
Member

Откуда:
Сообщений: 104751
Шамиль Фаридович
По моему, логичнее было бы выполнять сначала с параметром NONTRUNCATE, а потом с TRUNCATEONLY. Что это, ошибка перевода?

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

Шамиль Фаридович
На что расходуется 740 мб?

запустите sp_spaceused для базы и узнайте
23 мар 12, 14:26    [12302147]     Ответить | Цитировать Сообщить модератору
 Re: Запрос о размере таблиц БД  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 487
Glory,
это с англоязычного BOL:
Running DBCC SHRINKDATABASE without specifying either the NOTRUNCATE option or the TRUNCATEONLY option is equivalent to running a DBCC SHRINKDATABASE operation with NOTRUNCATE followed by running a DBCC SHRINKDATABASE operation with TRUNCATEONLY.
И если я правильно перевожу, то здесь сначала с опцией NOTRUNCATE, а затем с TRUNCATEONLY. Так как на самом деле?
sp_spaceused для БД выдает:
database_name database_size unallocated space
Testing	      1568.06 MB	   716.08 MB
Reseved  Data      Index_size  Unused
770288KB 559648KB   210064KB     576KB

В хэлпе сказано, что unallocated space - Место в базе данных, не зарезервированное для объектов базы данных. То есть базу можно спокойно сжимать на эти 716 МБ? Но если запустить DBCC SHRINKDATABASE(0), то сильно фрагментируются индексы. Почему это происходит?
23 мар 12, 15:06    [12302567]     Ответить | Цитировать Сообщить модератору
 Re: Запрос о размере таблиц БД  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Шамиль Фаридович
В хэлпе сказано, что unallocated space - Место в базе данных, не зарезервированное для объектов базы данных. То есть базу можно спокойно сжимать на эти 716 МБ? Но если запустить DBCC SHRINKDATABASE(0), то сильно фрагментируются индексы. Почему это происходит?
Шринк может отрезать место только с конца файла. Если там что-то есть, оно будет перенесено на новое место. Отсюда ваша фрагментация.

И потом, 716 Мб - места что ли жалко? O_o
23 мар 12, 15:11    [12302613]     Ответить | Цитировать Сообщить модератору
 Re: Запрос о размере таблиц БД  [new]
Glory
Member

Откуда:
Сообщений: 104751
Шамиль Фаридович
И если я правильно перевожу, то здесь сначала с опцией NOTRUNCATE, а затем с TRUNCATEONLY. Так как на самом деле?

В смысле "так на самом деле" ? Вы запустили SHRINKDATABASE и получили другие результаты ?

Шамиль Фаридович
То есть базу можно спокойно сжимать на эти 716 МБ?

Да


Шамиль Фаридович
Но если запустить DBCC SHRINKDATABASE(0), то сильно фрагментируются индексы. Почему это происходит?

Потому что при сжатии происходит перемещение страниц
Или вы думаете, что все свободные страницы у вас аккуратно расположены в конце файла, причем компактно и последовательно ?
23 мар 12, 15:13    [12302639]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить