Когда использовать sort_in_tempdb при восстановлении индексов?


16

Мы обсуждаем, использовать ли параметр SORT_IN_TEMPDB для наших таблиц DW. Я понимаю, что при использовании этой опции больше записей, хотя они более последовательны. У нас есть SAN (который, как известно, медленно замедляется), поэтому в нашем случае мы хотим как можно больше ограничить количество записей. Я считаю, что tempdb находится на отдельном LUN (набор дисков).

У нас есть много места на диске в нашем файле данных и в нашем файле tempdb. В этом случае мы могли бы использовать SORT_IN_TEMPDB?

Единственное, что меня поразило это замечание по этому Answer

При восстановлении индекса вам потребуется в два раза больше пространства индекса + 20% для сортировки. Поэтому в целом для восстановления каждого индекса в вашем db вам нужно всего 120% вашего самого большого индекса в вашей БД. Если вы используете SORT_IN_TEMPDB, вы выигрываете только 20%, вам все равно нужен дополнительный 100% в файле . Более того, использование sort в tempdb увеличивает нагрузку на IO , так как вместо того, чтобы называть индекс один раз в файл данных , вы теперь записываете его один раз в tempdb, а затем записываете его в файл . Так что это не всегда идеально.

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

Что было бы лучшим способом проверить это? Просто перестроив таблицу с помощью и без опции и запишите время?

Редактировать: У нас есть 8 файлов tempdb, каждый 15GB. У нас есть набор флагов TF 1117/1118 и включен IFI. В настоящее время мы делаем смесь перестройки с опцией sort_in_tempdb и без нее.

Спасибо!

SQL Server 2012 Enterprise

16

SORT_IN_TEMPDB означает, что SQL сервер будет использовать tempdb выделить временное пространство, в отличие от выделения пространства в базе данных пользователя, чей индекс является восстановление. Это означает, что вам потребуется меньше свободного места в вашей пользовательской базе данных во время операции восстановления индекса и более свободного места в tempdb.

Это дает вам лучшее преимущество, когда tempdb находится на другом наборе дисков (LUN) из пользовательской базы данных.

От SORT_IN_TEMPDB Option - BOL:

Если параметр SORT_IN_TEMPDB установлен в положение ON и Tempdb находится на отдельном наборе дисков из файловой группы назначения, во время первой фазы считывает из страниц данных происходит на другой диск из записи в рабочую область сортировки в tempdb. Это означает, что чтение дисков ключей данных обычно продолжается более последовательно по всему диску, а записи на диск tempdb также обычно являются серийными, также как и записи для создания окончательного индекса. Даже если другие пользователи используют базу данных и получают доступ к отдельным адресам диска, общий порядок чтения и записи более эффективен, если указан SORT_IN_TEMPDB, чем когда это не так.

Обязательно прочитайте disk space requirements when SORT_IN_TEMPDB is ON.

/медленно, возможно, неправильно SAN

Вы знаете болевые точки. Почему вы не работаете с администратором SAN, чтобы исправить это? Неправильная конфигурация и медленная SAN вызовут все виды problems like slowness.

Некоторые важные моменты, чтобы отметить:

Что было бы лучшим способом проверить это?

Да, вы должны проверить его analyzing the waitstats при перестроении индекса и без SORT_IN_TEMPDB. Измерьте время выполнения, а также при выполнении PROD, убедитесь, что вы делаете это во время окна обслуживания или меньше активности сервера. Также check your read/write data and log latency.

Я не уверен, что у вас есть Instant file initialization, но это принесет пользу при восстановлении во время авторазбора файлов данных и при создании новой базы данных (просто для полноты).

  0

Я отредактировал свой комментарий с моей настройкой tempdb. Спасибо, не знал о серийном онлайн-совете по перестройке. Я сделаю еще несколько тестов и попытаюсь получить доступ к администратору SAN, который, к сожалению, был менее приветлив. Существуют ли какие-либо конкретные waitstats, которые я должен сравнивать (например, pageIOLatch)? Наши tempdb пишет супер высокие (4000ms), что ужасно. До 40 мс для основных БД. Это может быть вопросом в другое время, хотя ...! 31 июл. 152015-07-31 20:22:02

  0

@Gabe, вы должны показать свои правильные факты администратора SAN, что это действительно проблема SAN - латентность чтения/записи - [sys.dm_io_virtual_file_stats] (http://www.sqlskills.com/blogs/paul/how-to-examine- IO-подсистема-латентность-из-за-SQL-сервера /). Ваш tempdb на отдельном LUN? 31 июл. 152015-07-31 20:38:47