SQL-Ex blog

Затем, разобравшись с этими вещами, я оцениваю следующие показатели:

• Продолжительность - не просто время, но также параллелизм: как много ядер будет задействовано и на какое время?


• Чтения - как много данных он читает.


• Выделение памяти - какое количество может резко повлиять на выполнение других запросов?

Чтобы показать, что я имею в виду, я собираюсь написать запрос в базе данных Overflow database, померить его и решить, достаточно ли он хорош, и не следует ли настроить его на более быстрое выполнение.

Вот мой запрос

Я хочу показать 250 топовых вопросов, имеющих тег "SQL Server":

SELECT TOP 250 p.Score, p.Title, p.Id AS QuestionId, u.DisplayName

FROM dbo.Posts p

INNER JOIN dbo.PostTypes pt ON p.PostTypeId = pt.Id

INNER JOIN dbo.Users u ON p.OwnerUserId = u.Id

WHERE p.Tags LIKE '%%'

	AND pt.Type = 'Question'

ORDER BY p.Score DESC;

Чтобы выяснить, достаточно ли он хорош для рабочей системы, я начну с выполнения этого:

SET STATISTICS IO, TIME ON;

При выполнении этого оператора я включаю новый набор сообщений в вывод моего запроса. Это обычно безопасно даже в рабочей системе, и он не влияет больше ни на чьи запросы - только на мои.

Затем я в меню Query (запрос) включаю фактический план выполнения (Actual Execution Plan). Это дает визуальное представление того, что SQL Server делает, чтобы выполнить запрос. Если вы переходите из другой платформы баз данных, вы могли бы назвать это showplan или query plan. Теперь выполним запрос.

Сначала посмотрим на статистику по времени запроса

Когда он финиширует, перейдем на вкладку Messages (сообщения):



Посмотрите время выполнения внизу скриншота:

 SQL Server Execution Times:

   CPU time = 72469 ms,  elapsed time = 46006 ms.

Если вы настраиваете хранимую процедуру или пакет с многими запросами, последний набор времени исполнения будет общим по всему пакету.

Обычно я не выполняю настройку, используя время, поскольку оно непредсказуемо. Оно может меняться в результате:

• Что еще делает сервер в это время.


• Как много данных находится в кэше, а сколько читается с диска.


• Что еще делает хранилище в это время.

Плюс значения времени могут изменяться от сервера к серверу. Вот над чем нужно подумать:

• Имеет ли ваш сервер тот же размер, что и рабочий?


• Имеет ли ваша база данных тот же размер, что и на рабочем сервере?


• Имеет ли ваш сервер другие отличия от рабочего сервера, например, настройки, номер версии или уровень патча?

Поскольку эти параметры могут так сильно меняться, я не сильно беспокоюсь о точных временах выполнения запроса. Когда я смотрю на время, я просто спрашиваю себя:

• Я на правильном пути? Например, если я пишу транзакционный запрос, который обращается к небольшому количеству данных, он, вероятно, должен отработать за одну или две секунды. Если я пишу отчет, для его выполнения может потребоваться 10-30 секунд.


• Превышает ли время ЦП время выполнения? Если так, то это говорит о том, что мой запрос пошел в параллель с несколькими ядрами ЦП. 5-секундный запрос, который почти не требует процессорного времени, может не иметь большого значения, но если он выполняется 5 секунд, а время ЦП 40 секунд, это говорит о том, что мой запрос захватывает 8 ядер ЦП на все 5 секунд. Я не смогу выполнить множество таких запросов, поэтому мне, вероятно, потребуется настраивать его.

Теперь посмотрим на логические чтения запроса

Ближе к верхней части скриншота SQL Server вернул по строке сообщений для каждой таблицы, которые были затронуты:

Table 'PostTypes'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'Users'. Scan count 0, logical reads 14511, physical reads 0, page server reads 0, read-ahead reads 2495, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 2122, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'Posts'. Scan count 5, logical reads 11182066, physical reads 72, page server reads 0, read-ahead reads 11173658, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Скопируйте этот фрагмент, перейдите на StatisticsParser.com и вставьте его. Вы получите замечательную таблицу с итогами для всего запроса:



Сосредоточимся на столбце “Logical Reads”: это число 8-килобайтных страниц, которые читает ваш запрос - независимо от того, в памяти эти страницы находятся или берутся с диска. (Если вы используете поколоночные индексы, вам следует посмотреть также на LOB Logical Reads, поскольку поколоночные индексы хранят свои данные несколько иначе.)

Вообще говоря, чем больше страниц читает ваш запрос, тем он медленней.

Чем больше вы знаете о рабочей нагрузке вашего собственного сервера, тем лучше вы будете чувствовать виды чтений, которые ваш сервер сможет выдержать без ступора. Когда вы только начинаете, вы можете сказать, что транзакционные запросы, выполняющие 100000 или более чтений, вероятно, вызовут проблемы. Даже при более низком значении могут быть проблемы - но если вы видите шестизначное число чтений, то, вероятно, время нажать на тормоза и заняться настройкой запроса или индекса. 128000 страниц - это 1Гб данных, и если вы обнаружили чтение 1Гб данных, только чтобы выполнить короткую транзакцию, это, похоже, станет проблемой.

В качестве бонуса взглянем на сам план

Чем уверенней вы станете настраивать запросы, тем больше вы сможете обнаружить мест, которые могут вам не понравиться:



Например, в плане моего запроса:

• Имеется желтый восклицательный знак на операторе Sort, который говорит о том, что моему запросу не хватает памяти, и сортировка должна выполняться в TempDB.


• Иконка параллелизма на многих операторах говорит о том, что SQL Server считал, что он должен был выполнить много работы, и поэтому выделил несколько ядер ЦП для её выполнения. Это само по себе не обязательно является проблемой - это может быть отличным вариантом для больших отчетов - но если я работаю с транзакционными системами, где ожидается выполнение запросов за 1-2 секунды, то мне может потребоваться настройка запроса или индексов, чтобы снизить требуемый объем работы.


• Имеются временные метки на каждом операторе, помогающие определить, какие операторы занимают наибольшее время. Эти метрики не означают то, что вы думаете, хотя: Erik Darling объясняет, что операторы построчного режима и пакетного режима по разному оценивают время.

Но что бы вы ни делали, не обращайте внимания на время в плане запроса

Если выполнить правый щелчок на иконке SELECT в плане, а затем щелкнуть Properties, то вы можете увидеть раздел, который называется QueryTimeStats (выделен на рисунке):



Ни в коем случае НЕ УВЕЛИЧИВАЙТЕ И НЕ СМОТРИТЕ НА ЭТО:



Поскольку в противном случае вы начнете задавать вопросы типа "Почему не использовать статистику CPU и elapsed time, которая выводится на вкладке Messages?"

И тогда вы поймете, что все эти данные построены на троне лжи, и никто не уверен, что эти цифры верны. Будьте проще и сосредоточтесь на логических чтениях. (Они также не являются технически точными: например, они не показывают чтения, выполненные большинством пользовательских функций.)