Код для оценки потенциальной экономии пространства ключа кластеризации для каждой таблицы

Автор: Paul Randal, Code to list potential cluster key space savings per table

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

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

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

Наслаждайтесь!

Page Life Expectancy — это не то, что вы думаете…

Автор: Paul Randal, Page Life Expectancy isn’t what you think…

Существует много споров о счётчике производительности Buffer Manager — Page Life Expectancy, в основном вокруг того, что люди продолжают цитировать 300 в качестве порога для начала беспокойства о проблеме (что в наши дни является полной ерундой). Это слишком низкое значение, чтобы быть точкой, на которой стоит начинать беспокоиться, если ваш PLE падает и остаётся на этом уровне. Джонатан предложил лучшее число, основанное на размере вашего буферного пула — см. нижнюю часть его статьи здесь.

Но не поэтому я пишу сегодня: я хочу объяснить, почему в настоящее время Page Life Expectancy в большинстве случаев не даёт вам полезной информации.

Проблемы производительности из-за неэффективного использования памяти буферного пула

Автор: Paul Randal, Performance issues from wasted buffer pool memory

В апреле я запустил опрос, в котором просил вас прислать информацию о ваших буферных пулах — сколько памяти используется для страниц файлов данных и какая часть этой памяти хранит пустое пространство. Я получил данные с 1394 серверов по всему миру — спасибо!

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

• Вы будете выполнять больше операций чтения ввода-вывода, поскольку большая часть рабочей нагрузки не может поместиться в буферном пуле.
• Вы будете выполнять больше операций записи ввода-вывода, так как «ленивому писателю» (lazywriter) придётся вытеснять грязные страницы из буферного пула.
• Вы можете столкнуться с ожиданиями RESOURCE_SEMAPHORE, потому что запросы не могут получить необходимые им назначения памяти для выполнения запросов.
• Вы можете вызвать чрезмерное количество перекомпиляций планов, если кэш планов слишком ограничен.
• И целый ряд других проблем.

Одной из проблем с памятью, которую Кимберли подробно обсуждала в прошлом году (и подробно обучает этому на наших курсах по настройке производительности), является раздувание кэша однократно используемых планов (single-use plan cache bloat), когда большая часть кэша планов заполнена планами, которые используются один раз и никогда больше не пригодятся. Вы можете прочитать об этом в трёх постах в её категории Plan Cache, а также о том, как выявить раздувание кэша планов и что с этим можно сделать.

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

Структуры хранения #5 – XML-индексы

Автор: Hugo Kornelis, Storage structures 5 – XML indexes;

После рассмотрения дискового строчного хранения, индексов columnstore, оптимизированных для памяти структур и оптимизированных для памяти columnstore, пришло время обратить внимание на структуры хранения, которые используются только для определённых типов данных. Первой «жертвой» станет XML-индекс.

Ожидания SOS_SCHEDULER_YIELD и спин-блокировка LOCK_HASH

Автор: Paul Randal, SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock

В этой статье я хотел бы показать пример возникновения ожиданий SOS_SCHEDULER_YIELD и того, как может показаться, что причиной является спин-блокировка.

Первоначально я опубликовал эту статью, а затем обсудил его с моим хорошим другом Бобом Уордом (Bob Ward) из службы поддержки продуктов, который усомнился в моих выводах, основываясь на своём опыте (спасибо, Боб!). После более глубокого исследования я обнаружил, что моя первоначальная версия была неверной, поэтому это исправленная версия.

Как исследовать задержки подсистемы ввода-вывода изнутри SQL Server

Автор: Paul Randal, How to examine IO subsystem latencies from within SQL Server

За последние несколько месяцев я читал лекции на курсах и конференциях о том, как получить представление SQL Server о подсистеме ввода-вывода и какие задержки она испытывает, так что пришло время написать об этом в блоге, чтобы помочь всем остальным.

Важные соображения при настройке производительности

Автор: Paul Randal, Important considerations when performance tuning

Что, по вашему мнению, является самым важным аспектом при настройке производительности, если бы пришлось выбрать что-то одно? Суть в том, что в общем случае ответ всегда «зависит», но некоторые вещи оказываются главной проблемой чаще, чем другие. Давайте пройдёмся по каждому возможному ответу и посмотрим, как сильно они могут повлиять на производительность, если что-то не так. Это ни в коем случае не исчерпывающий список того, что может вызвать проблемы, а просто мои размышления в конце воскресенья, как обычно.

Кластерный или некластерный индекс для случайного GUID?

Автор: Paul Randal, Clustered or nonclustered index on a random GUID?

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

Тут я не собираюсь вдаваться в дискуссию о GUID против bigint в качестве идентификатора, или о случайном GUID против GUID, генерируемого NEWSEQUENTIALID(), так что, пожалуйста, не комментируйте эти вопросы, здесь они не актуальны. Я просто хочу ответить на вопрос: каким должен быть этот индекс?

Перенос индексов в новую файловую группу: Microsoft по-прежнему вас хэйтит

Автор: Erik Darling, Moving Indexes To A New Filegroup: Microsoft Still Hates You

В какой-то момент вы захотите перенести некоторые индексы в новую файловую группу. Возможно, вы разделяете данные по разным хранилищам, возможно, вы наводите порядок после того, как кто-то поместил всё в PRIMARY и ушёл, а может, у вас есть свои причины, и они меня не касаются.

Какова бы ни была причина, можно было бы подумать, что в базе данных, существующей со времён президента Ельцина, эта проблема уже решена.

Но это не так.

Насколько всё плохо, зависит от того, что именно вы переносите. Давайте пройдёмся от наименее болезненного к самому болезненному, потому что боль здесь поучительна.

Диагностика проблем производительности

Авторы: Сунил Агарвал (Sunil Agarwal), Борис Барышников (Boris Baryshnikov), Кит Элмор (Keith Elmore), Юрген Томас (Juergen Thomas), Кун Чен (Kun Cheng), Бурзин Патель (Burzin Patel)

Технические рецензенты: Жером Халманс (Jerome Halmans), Фабрисио Возника (Fabricio Voznika), Джордж Рейня (George Reynya)

Troubleshooting Performance Problems in SQL Server 2008

Краткое содержание: Иногда неправильно спроектированная база данных или система, неправильно настроенная под рабочую нагрузку, может вызывать замедление работы SQL Server. Администраторам необходимо активно предотвращать или минимизировать проблемы, а когда они возникают — диагностировать причину и принимать корректирующие меры. В этом документе содержатся пошаговые рекомендации по диагностике и устранению распространённых проблем производительности с использованием общедоступных инструментов, таких как SQL Server Profiler, Performance Monitor, динамические административные представления (DMV), а также расширенные события (Extended Events) и сборщик данных.

Настройка производительности CDC

Автор: Штеффен Краузе (Steffen Krause)

Соавторы: Санджай Мишра (Sanjay Mishra), Гопал Ашок (Gopal Ashok), Грег Ивкофф (Greg Yvkoff), Жуй Ван (Rui Wang)

Технические рецензенты: Бурзин Патель (Burzin Patel), Денни Ли (Denny Lee), Гленн Берри (Glenn Berry, MVP SQL Server), Джозеф Сак (Joseph Sack), Линдси Аллен (Lindsey Allen), Майкл Редман (Michael Redman), Майк Рутрафф (Mike Ruthruff), Пол С. Рэндал (Paul S. Randal, SQLskills.com), Tuning the Performance of Change Data Capture in SQL Server 2008

Краткое содержание: Отслеживание изменений данных (Change Data Capture, CDC) — это новая функция в SQL Server, которая предоставляет простой способ отслеживания изменений данных в наборе таблиц базы данных для последующей передачи этих изменений во вторую систему, например, в хранилище данных. В этом документе содержатся рекомендации по настройке параметров отслеживания изменений данных для максимальной производительности захвата данных при минимальном влиянии на производительность производственной нагрузки. Область действия этого документа ограничена захватом изменяемых данных и процессом очистки. Запрос изменённых данных не входит в область действия данного технического документа.

Правильно ли установлены смещения разделов диска, размеры страйпа RAID и единицы выделения в NTFS?

Автор: Paul Randal, Are your disk partition offsets, RAID stripe sizes, and NTFS allocation units set correctly?

Я только что настраивал некоторое новое оборудование и хотел провести некоторое фоновое чтение, чтобы убедиться, что я использую правильное смещение раздела диска, размер страйпа RAID и размер единицы выделения NTFS для достижения наилучшей возможной производительности для создаваемых томов.

Советы и хитрости для высокой производительности FILESTREAM

Автор: Paul Randal, High-performance FILESTREAM tips and tricks

У меня было много вопросов о производительности FILESTREAM и о том, как заставить NTFS хорошо масштабироваться. Я только что закончил писать 30-страничный технический документ о FILESTREAM для команды SQL Server, который должен быть опубликован до конференции PASS 2008 в ноябре. Хотя мой технический документ не совсем о производительности, в нём есть длинный раздел о настройке системы для достижения высокой производительности FILESTREAM. В этой статье я хочу дать список рекомендаций, которые помогут вам добиться хорошей производительности. Все они более подробно описаны в техническом документе.

Могут ли ключи кластерного индекса с типом GUID вызывать фрагментацию некластерных индексов?

Автор: Paul Randal, Can GUID cluster keys cause non-clustered index fragmentation?

На встрече пользовательской группы я потратил некоторое время на объяснение того, как GUID могут вызывать фрагментацию как в кластерных, так и в некластерных индексах, даже если GUID специально не включён в ключ некластерного индекса. GUID — это, по сути, случайные значения (псевдослучайные в диапазонах, если генерируются с помощью NEWSEQUENTIALID), которые также уникальны. Их уникальность делает их привлекательными для многих разработчиков в качестве значения ключа, не понимая при этом того хаоса, который они могут вызвать в производственной среде с точки зрения фрагментации и низкой производительности запросов.

Насколько сложно выбрать правильные некластерные индексы?

Автор: Paul Randal, How hard is it to pick the right non-clustered indexes?

На собрании группы разработчиков .NET в Редмонде, и во время того, как Кимберли рассказывала о пропущенных и лишних индексах, возник следующий вопрос:

«Какой некластерный индекс лучше всего использовать для запроса с условием WHERE lastname = 'Randal' AND firstname = 'Paul' AND middleinitial = 'S'?»

Кимберли сказала, что для этого случая порядок ключей не имеет значения. Я подумал секунду, а затем возразил, сказав, что наиболее селективный столбец должен быть первым. Мы согласились обсудить это с группой в конце, но я подумал ещё немного и понял (и признался группе), что она права – мне следовало бы знать, что не стоит подвергать сомнению знания Кимберли об индексировании… :-)

Разочарование от обобщений

Автор: Paul Randal, The frustration of sweeping generalizations – follow on from Search Engine Q&A #12

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

Некоторые примеры вопросов, которые порождают обобщения:

• Следует ли создавать кластерные индексы для всех таблиц? (Известный спор о кластерных индексах, как его любит называть Кимберли.)
• Следует ли перестраивать или реорганизовать индексы для устранения фрагментации?
• Какое решение высокой доступности следует использовать?

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

Журнал транзакций SQL Server. Часть 4: записи журнала

Автор: Paul Randal, The SQL Server Transaction Log, Part 4: Log Records

Ранее я рассказывал о том, зачем нужно ведение журнала, об архитектуре и циклической природе журнала, и теперь пришло время взглянуть на самое сердце системы ведения журнала — на сами записи журнала. Как всегда, рекомендую прочитать предыдущие статьи в этой серии, прежде чем читать эту.

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

Автор: Paul Randal, Search Engine Q&A #12: Should you create multiple files for a user DB on a multi-core box?

На сайте SQLServerCentral.com развернулась очень интересная дискуссия о том, стоит ли создавать несколько файлов для пользовательской базы данных, потому что на сервере несколько ЦП. Я написал пару длинных ответов в ходе дискуссии и хотел продублировать их здесь, так как считаю, что это представляет широкий интерес.

Горячее добавление ЦП и маска привязки

Автор: Paul Randal, SQL Server 2008: Hot-Add CPU (and affinity masks)

Короткая заметка сегодня, так как я готовлюсь к выступлению на собрании пользовательской группы SQL Server в Тихоокеанском Северо-Западе сегодня вечером в кампусе Microsoft в Редмонде.

SQL Server 2005 представил концепцию горячего добавления памяти (hot-add memory) для динамической обработки рабочей нагрузки. SQL Server 2008 расширяет эти возможности, добавляя также горячее добавление ЦП (hot-add CPU). Начиная с SQL Server 2025 (17.x), функция горячего добавления ЦП не рекомендуется и планируется удалить в будущей версии SQL Server. «Горячее добавление» означает возможность установить ЦП в работающую машину и затем перенастроить SQL Server для использования этого ЦП ONLINE (т.е. без какого-либо простоя приложения).

Как влияет сжатие резервных копий на загрузку процессоров

Автор: Paul Randal, SQL Server 2008: Backup Compression CPU Cost

Я давно обещал написать о встроенном сжатии резервных копий (Backup Compression). Для этой статьи я расширил базу данных AdventureWorks до 322 МБ (случайный размер, но достаточно большой, чтобы получить приемлемое время выполнения на моём сервере). Я использовал системный монитор (System Monitor) для измерения времени ЦП в пользовательском режиме (%user-mode CPU time), а также пропускной способности резервного копирования и восстановления для сжатой и несжатой операций резервного копирования, а затем и восстановления.