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

Откуда:
Сообщений: 257
Добрый день.

Читаю книгу Query Perfomance (Grant Fritchey)
Глава по индексам.

Никак не могу разобраться
Есть такие данные
CREATE TABLE TEST1(C1 INT,C2 INT,C3 VARCHAR(50))

WITH NUMS
	AS(SELECT TOP(10000) ROW_NUMBER() OVER(ORDER BY(SELECT 1)) AS n
		FROM master.sys.All_Columns as1 CROSS JOIN master.sys.All_Columns as2
		)
		INSERT INTO TEST1
		SELECT n,n,'C3'
			FROM Nums
Когда делаю вот такой запрос
SET STATISTICS IO ON
UPDATE TEST1
SET C1 = 1,C2 = 2
WHERE C2 = 1
Table 'TEST1'. Scan count 1, logical reads 29, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Никак не могу понять, как мне выйти на эту цифру, я про 29, почему именно 29
И если я добавляю неверный индекс кластерный по полю С1, то у меня увеличиваются логические чтения

---Table 'TEST1'. Scan count 1, logical reads 49, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
---Table 'Worktable'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Скажем так, как мне самому без sql server выйти на эти цифры?

Заранее благодарен за Ваши ответы.
6 мар 14, 11:57    [15681536]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по теории  [new]
Glory
Member

Откуда:
Сообщений: 104751
user87
Скажем так, как мне самому без sql server выйти на эти цифры?

Что вы понимаете под "самому выйти" ?
Вы умеете читать страницы с диска и/или кэша ?
6 мар 14, 12:05    [15681621]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по теории  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8834
user87, самому никак.
6 мар 14, 12:44    [15681978]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по теории  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8834
Чтения увеличиваются, если данные разбросаны по разным страницам.
6 мар 14, 12:46    [15681992]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по теории  [new]
o-o
Guest
Владислав Колосов
user87, самому никак.


чего совсем-то никак?
для только что залитой таблицы чего бы и не посчитать,
да и DBCC IND покажет все странички.

вот для приведенной выше таблицы Slot Length = 21 + 2 байта в на каждый слот,
итого: 10000 / (8096. / (21 + 2)) = 28.4090909090909090 -> 29 страниц

ну и это же подтверждает DBCC IND: 1 IAM page + 29 data pages

+

CREATE TABLE TEST1(C1 INT,C2 INT,C3 VARCHAR(50))
go

;WITH NUMS
	AS(SELECT TOP(10000) ROW_NUMBER() OVER(ORDER BY(SELECT 1)) AS n
		FROM master.sys.All_Columns as1 CROSS JOIN master.sys.All_Columns as2
		)
		INSERT INTO TEST1
		SELECT n,n,'C3'
			FROM Nums
			
SET STATISTICS IO ON
UPDATE TEST1
SET C1 = 1,C2 = 2
WHERE C2 = 1
go

dbcc ind('test', 'test1', 1)
-----------------------------------
...
(30 row(s) affected)
6 мар 14, 13:04    [15682156]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по теории  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
user87,

Для того чтобы это посчитать, первым делом смотрим план. Там можно увидеть, какие объекты читаются, какой план обновления выбран и т.д.

В общем случае, в теории тут не получится, т.к. нужно быть оптимизатором, чтобы знать какой будет план. Но в вашем случае все очень просто, сканируется таблица TEST1. Значит, чтобы посчитать чтения, нужно посчитать, сколько занимает таблица TEST1.

Чтобы это посчитать, курим статью из BOL: Estimate the Size of a Heap - вот такая математика примерно:
автор
Num_Rows = 10000
Num_Cols = 3
Fixed_Data_Size = 8 bytes
Max_Var_Size = 2 bytes
Null_Bitmap = 2 + ((Num_Cols + 7) / 8) = 2 + (3+7)/8 = 3 bytes
Variable_Data_Size = 2 + (Num_Variable_Cols x 2) + Max_Var_Size = 2 + 1*2 + 2 = 6 bytes
Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4 = 8 + 6 + 3 + 4 = 21 bytes
Rows_Per_Page = 8096 / (Row_Size + 2) = 8096 / (21 + 2) = 352 rows
Num_Pages = Num_Rows / Rows_Per_Page = 10000 / 352 = 28.409090 = [29] pages

Чтобы проверить, выполняем:
select 
	s.page_count,
	s.max_record_size_in_bytes,
	s.min_record_size_in_bytes,
	s.avg_record_size_in_bytes 
from 
	sys.dm_db_index_physical_stats(db_id(),object_id('TEST1'),0,null,'DETAILED') s; --29 страниц, 21 байт размер строки

dbcc ind('YourDatabaseName','TEST1', 1); --30 записей, 29 страниц данных и одна IAM


Если интересны битики и байтики на страницах есть охота поковыряться, курим статьи Пола Рэндала:
Inside the Storage Engine: Anatomy of a record
Inside the Storage Engine: Anatomy of a page
6 мар 14, 13:12    [15682243]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по теории  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
select * from sys.dm_db_index_physical_stats(db_id(), object_id('TEST1'), 0, 1, default)
Значение столбца page_count.
6 мар 14, 13:12    [15682244]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по теории  [new]
user87
Member

Откуда:
Сообщений: 257
Спасибо большое за ответы, буду разбираться
6 мар 14, 13:31    [15682419]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить