Как ускорить sql server
Ускоряем MS SQL Server для 1С в 2 раза
Общие настройки
Запускаем SQL Server Management Studio и вводим данные для подключения к серверу баз данных.
Кликаем правой кнопкой мыши по серверу и выбираем Свойства:
В открывшемся окне переходим на вкладку «Память» и ограничиваем потребление оперативной памяти в графе «Максимальный размер памяти сервера (МБ)»:
Теперь переходим на вкладку «Процессоры» и выставляем «Максимальное число рабочих потоков» в значение 2048 и ставим галочку Поддерживать приоритет SQL Server:
Настройки базы данных
В SQL Server Management Studio раскрываем «Базы данных», кликаем правой кнопкой мыши по рабочей базе и нажимаем Свойства :
Теперь переходим на вкладку «Файлы» и в настройках Авторасширения настраиваем расширение файла базы до 250 Мб и лога до 100. Также не лишним будет ограничить размер лога до 4096 Мб:
Нажимаем OK и закрываем Management Studio
Результат
Для проверки результатов оптимизации, запустим отладку, замер производительности и сформируем оборотно-сальдовую ведомость.
Оптимизация производительности SQL Server с использованием индексов
Введение
Как известно, индексы повышают производительность аналогично оглавлению или предметному указателю в кнгие. Прочитав несколько статей в интернете и пару глав из книжек, хотелось бы узнать, насколько индексы помогают увеличить скорость выборки данных из SQL Server. Рассмотрим на примере.
Для этого нам понадобятся две таблицы, связанные внешним ключом. В главной таблице будет 10 000 строк, во второй — 1 000 000. В первой таблице будет первичный ключ и 2 столбца с текстом, вторая таблица будет содержать первичный ключ, 2 текстовых поля, 2 числовых поля, дата, вычисляемый столбец и внешний ключ.
Структура базы данных
Вставка данных
Запускаем консольное приложение, жмём «1» и ждём. У меня ушло минут 15-20 на вставку.
Описание результатов
В этом разделе будем рассматривать запросы выборки в проиндексированных и непроиндексированных столбцах.
Один кластеризованный индекс создаётся неявно — это первичный ключ.
Поиск по первичному ключу
Выполним поиск строки по первичному ключу, указывая на конкретный номер строки из десяти тысяч:
Время, затраченное на поиск: 1,523 секунды.
Повторный запрос затратил 0,9 секунды, а третий — 0,1 секунды.
Выполним второй запрос с большой селективностью из этой же таблицы:
Первый запрос затратил 0,3 секунды, второй — 0,26 сек., третий — 0,25 сек.
Теперь выполним аналогичные запросы к таблице, содержащий миллион записей. По конкретному номеру строки:
Время, затраченное на поиск: 1,4 сек., второй запрос показал 1,36 сек.. Далее будет показано среднее время запроса.
Столбцы первичного ключа уже имеют индекс, поэтому повысить производительность скорее всего не получится. Исходя из результатов можно сказать, что повторные запросы с теми же параметрами увеличивают скорость поиска.
Поиск по шаблону
Рассмотрим поиск текста по шаблону с индексированными и непроиндексированными столбцами:
Без индекса такой запрос выполняется за 0,172 секунды из 10 000 строк. С индексом
такой выполняется за 0,112 секунды, а с составным индексом, содержащим поля «name» и «description», запрос занял 0,09 секунды.
Запрос
выполняется за 0,172 секунды без индекса и 0,112 секунды с индексом.
Выполним поиск по шаблону к таблице, содержащей миллион записей.
Этот запрос без индексации занял бы в среднем 1,7 секунды, тогда как с индексом столбца будет потрачено 0,15 секунды.
Поиск по дате
Для начала выполним запрос малой селективности
Без индекса он займёт в среднем 0,7 сек., проиндексированный — 0,22 сек.
Запрос большой селективности (420600 строк)
Без индекса — 19,219 сек, с индексом только поля даты — 9,9 сек.
Запрос по дате к вычисляемому столбцу
Этот запрос без индекса займет 12,2 секунды, тогда как с индексом — 9,75. Важно учесть то, что исходный столбец, содержащий дату, уже был проиндексирован.
Объединение таблиц
При объединении таблиц советуют всегда индексировать столбец, являющийся внешним ключом. В следующем примере мы убедимся, что это на самом деле так.
Такой запрос вернёт 101 строку. Без индекса — 0,422 сек, а с индексом — 0,122 сек. Как мы видим, скорость запроса увеличилась более чем в три раза. Рассмотрим остальные запросы, связанные с объединением.
Но индекс не всегда увеличивает производительность. Например, запрос (420 000 строк)
показал одинаковые результаты без индекса, с индексом только внешнего ключа, внешнего ключа и даты, с составным индексом, одновременно включающим в себя эти два столбца — примерно 6,8 секунд.
Объединение с поиском по шаблону
Выполним объединение с поиском по шаблону среди миллиона записей:
Без индексов запрос займёт 0,766 секунды и вернёт 5 строк.
С индексом только внешнего ключа — 0,4 сек.;
С индексом только текстового поля — 0,125 сек.;
С индексом внешнего ключа и индексом текстового поля — 0,109 сек.;
С составным индексом, содержащим внешний ключ и текстовое поле — 0,35 сек..
Сравнение результатов
Рассмотрим результаты в виде графиков. Вертикальная ось показывает затраченное время (а не скорость!).
1. Выборка по первичному ключу. Так как уже есть кластеризованный индекс, рассмотрим большую и малую селективную выборку из одной таблицы:
Из этого графика видно, что выборка большого количества данных обрабатывается быстрее.
2. Поиск по первичному ключу с объединением таблиц. Следующий график показывает, что индексирование внешнего ключа ускоряет операцию поиска более чем в три раза:
3. Поиск по шаблону текста. Из графика видно, что производительность во много раз вырастает, если использовать индекс при большом объёме данных:
4. Поиск по дате. Индексы значительно увеличивают производительность как для малой выборки, так и большой в пределах одной таблицы:
Объединение таблиц и поиск по дате показал одинаковые результаты как с индексами, так и без них.
5. Поиск по вычисляемому столбцу. Индекс также уменьшает время поиска.
6. Поиск по шаблону с объединением. Индекс текстового поля и оба индекса значительно увеличивают производительность:
Подведём итоги
Итак, мы видим, что индексирование значительно повышает SELECT запросы, однако индексы занимают дополнительную память и периодически должны быть перестроены. Оптимальное решение сложно найти, поэтому разработчики и администраторы баз данных тратят много времени на поиск золотой середины, к тому же индексы необходимо реорганизовывать. Но всё же, если у вас большое количество данных, то ответ, использовать индексы или нет, очевиден.
Устранение неполадок с медленными запросами на SQL Server
Введение
В этой статье описывается, как справиться с проблемой производительности, которую приложения могут испытывать совместно с SQL Server: медленная производительность определенного запроса или группы запросов. Если вы устраняете проблему с производительностью, но не изолировали проблему для определенного запроса или небольшой группы запросов, которые выполняются медленнее, чем ожидалось, см. в перенастройке Monitor и Tune for Performance перед продолжением.
В этой статье предполагается, что вы использовали статью 298475, чтобы сузить область проблемы, и что вы зафиксили след SQL Profiler с определенными событиями и столбцами данных, которые подробно описаны в статье 224587.
Настройка запросов базы данных может быть многогранной попыткой. В следующих разделах рассматриваются общие элементы, которые необходимо изучить при проверке производительности запроса.
Оригинальная версия продукта: SQL Server
Исходный номер КБ: 243589
Проверка наличия правильных индексов
Одна из первых проверок, выполняемых при медленном выполнении запроса, — анализ индекса. Если вы изучаете один запрос, вы можете использовать анализ запроса в помощник по настройке ядра СУБД в SQL анализаторе запросов; Если у вас есть SQL профилировка большой рабочей нагрузки, вы можете использовать помощник по настройке ядра СУБД. Оба метода используют SQL Server оптимизатор запросов, чтобы определить, какие индексы будут полезны для указанных запросов. Это эффективный метод определения того, существуют ли правильные индексы в базе данных.
Сведения о том, как использовать помощник по настройке ядра СУБД, см. в разделе «Запуск и использование помощник по настройке ядра СУБД» в SQL Server Books Online.
Если вы обновили приложение из предыдущей версии SQL Server, различные индексы могут быть более эффективными в новой сборке SQL Server из-за изменений оптимизатора и двигателя хранения. Этот помощник по настройке ядра СУБД позволяет определить, улучшит ли изменение стратегии индексирования производительность.
Удаление всех советов по запросам, таблицам и присоединиться к ним
Подсказки переопределяют оптимизацию запроса и могут запретить оптимизатору запросов выбирать самый быстрый план выполнения. Из-за изменений оптимизации подсказки о том, что повышение производительности в более ранних версиях SQL Server может не повлиять на производительность в более поздних SQL Server сборках. Кроме того, подсказки о присоединиться могут привести к снижению производительности на основе следующих причин:
Подсказки join препятствуют автоматической параметровизации и кэшации плана запросов.
Если вы используете подсказку о присоединиться, это означает, что вы хотите принудить порядок присоединиться ко всем таблицам в запросе, даже если эти присоединяются явно не используют подсказку.
Если анализируемый запрос содержит какие-либо подсказки, удалите их, а затем переоформите производительность.
Изучение плана выполнения
После подтверждения существования правильных индексов и того, что никакие подсказки не ограничивают способность оптимизатора создавать эффективный план, можно изучить план выполнения запроса. Для просмотра плана выполнения запроса можно использовать любой из следующих методов:
Если вы захватили событие MISC: План выполнения в SQL Profiler, оно произойдет непосредственно перед событием StmtCompleted для запроса для ID системного процесса (SPID).
SQL Анализатор запросов: план графического показа
Чтобы запрос был выбран в окне запроса, щелкните меню запроса и нажмите кнопку Отображаемая оценка плана выполнения.
Если хранимая процедура или пакет создает временные таблицы и ссылается на них, необходимо использовать заявление SET STATISTICS PROFILE ON или явно создать временные таблицы перед отображением плана выполнения.
SHOWPLAN_ALL и SHOWPLAN_TEXT
Чтобы получить текстовую версию предполагаемого плана выполнения, можно использовать параметры SET SHOWPLAN_ALL SHOWPLAN_TEXT SET. Дополнительные сведения см. в SHOWPLAN_ALL (T-SQL) и set SHOWPLAN_TEXT (T-SQL) SQL Server Books Online.
Если сохраненная процедура или пакет создает и ссылается на временные таблицы, необходимо использовать параметр SET STATISTICS PROFILE ON или явно создать временные таблицы перед отображением плана выполнения.
При отображии предполагаемого плана выполнения графически или с помощью SHOWPLAN запрос не выполняется. Поэтому, если вы создаете временные таблицы в пакете или в сохраненной процедуре, вы не сможете отобразить предполагаемые планы выполнения, так как временные таблицы не будут существовать. ПРОФИЛЬ STATISTICS сначала выполняет запрос, а затем отображает фактический план выполнения. Дополнительные сведения см. в разделе SET STATISTICS PROFILE (T-SQL) SQL Server Books Online. При выполнении в SQL анализаторе запросов это отображается в графическом формате на вкладке План выполнения в области результатов.
Дополнительные сведения о том, как отобразить предполагаемый план выполнения, см. в разделе Display the Estimated Execution Plan in SQL Server Books Online.
Изучение вывода Showplan
Вывод Showplan содержит много сведений о плане выполнения, который SQL Server для определенного запроса. Ниже приведен ряд основных аспектов плана выполнения, которые можно просмотреть, чтобы определить, используете ли вы оптимальный план:
Правильное использование индекса
Вывод showplan отображает каждую таблицу, которая участвует в запросе, и путь доступа, используемый для получения данных из нее. С помощью графического шоуплана переместим указатель по таблице, чтобы увидеть сведения для каждой таблицы. Если используется индекс, вы увидите Index Seek; Если индекс не используется, вы можете просмотреть таблицу сканирования для кучи или кластерного сканирования индекса для таблицы с кластерным индексом. Кластерное сканирование индекса указывает на то, что таблица проверяется с помощью кластерного индекса, а не для непосредственного доступа к отдельным строкам.
Если вы определяете, что существует полезный индекс и он не используется для запроса, вы можете попытаться заставить индекс с помощью подсказки индекса. Дополнительные сведения о подсказках индекса см. в разделе FROM (T-SQL) SQL Server Books Online.
Правильный порядок ок.
Вывод showplan указывает, в каких таблицах заказов, участвующих в запросе, присоединяются. Для вложенных циклов присоединяется верхняя таблица, указанная на внешней, и она должна быть меньше двух таблиц. Для присоединяется к hash, верхняя таблица становится вводом сборки и также должна быть меньше двух таблиц. Однако обратите внимание, что порядок менее критичен, так как процессор запроса может отменить вводы сборки и зонда во время выполнения, если обнаружится, что оптимизатор принял неправильное решение. Вы можете определить, какая таблица возвращает меньше строк, проверив оценки подсчета строк в выводе showplan.
Если вы определите, что запрос может извлечь выгоду из другого порядка присоединиться, вы можете попробовать принудить порядок присоединиться с помощью подсказки присоединиться. Дополнительные сведения о подсказках о присоединиться см. в разделе FROM (T-SQL) SQL Server Books Online.
Использование подсказки о подстройки в большом запросе неявно заставляет порядок присоединяться к другим таблицам в запросе, как если FORCEPLAN бы он был за установлен.
Правильный тип присоединиться
SQL Server использует вложенный цикл, хаш и слияния присоединяется. Если при медленном выполнении запроса используется одна техника для окнуа, вы можете попытаться заставить другой тип присоединиться. Например, если запрос использует hash join, вы можете заставить вложенные циклы присоединиться с помощью подсказки присоединиться LOOP. Дополнительные сведения о подсказках о присоединиться см. в разделе «FROM (T-SQL)» в SQL Server Books Online.
Использование подсказки о подстройки в большом запросе неявно заставляет тип присоединиться к другим таблицам в запросе, как если FORCEPLAN бы был установлен.
Если вы используете многопроцессорный компьютер, вы также можете узнать, используется ли параллельный план. Если используется параллелизм, вы увидите событие PARALLELISM (Gather Потоки). Если определенный запрос медленно используется при использовании параллельного плана, вы можете попробовать принудить не параллельный план с помощью подсказки OPTION (MAXDOP 1). Дополнительные сведения см. в разделе SELECT (T-SQL)» SQL Server Books Online.
Дополнительные сведения об использовании вывода плана выполнения Showplan см. в следующих SQL Server Books Online:
Сохранение плана выполнения в формате XML
Сравнение и анализ планов выполнения
Справка о логических и физических операторах Showplan
Поскольку оптимизатор запросов обычно выбирает оптимальный план выполнения для запроса, Корпорация Майкрософт рекомендует использовать подсказки о присоединиться, подсказки запроса и подсказки таблицы только в качестве последнего средства и только в том случае, если вы опытный администратор базы данных.
Третья космическая скорость для MS SQL Server
В сентябре компания DataCore представила новую линейку продуктов MaxParallel и первый продукт из серии — MaxParallel for SQL Server. MaxParallel делает простую вещь – ускоряет работу базы данных MS SQL, не требуя для этого никаких изменений самой базы (ее оптимизации и тп.) или аппаратной части (увеличения числа процессоров, памяти и тп.).
В чем идея: практически все современные сервер БД являются многоядерными, и приложения с успехом используют эти ядра для параллелизации вычислений. Но процесс ввода-вывода остается последовательным и использует одно процессорное ядро. И если заставить планировщик ввода-вывода использовать больше процессорных ресурсов, БД будет работать быстрее. По крайней мере, сможет работать быстрее. Уникальность MaxParallel состоит не только в том, что она ускоряет БД без серьезного вмешательства, но также в том, что она устраняет «узкое место», которое по-другому не устранить. Ни увеличение числа ядер в сервере, ни ускорение дисков на бэк-энде не изменит тот факт, что сама ОС использует для обработки ввода вывода только одно процессорное ядро. Единственным решением было бы распределение нагрузки между множеством серверов – больше серверов, следовательно, больше инстансов ОС и как следствие более производительная подсистема ввода-вывода. Но это подход дорогой и даже очень.
Еще до официального анонса продукта мы организовали закрытое тестирование продукта у ограниченного круга заказчиков. И у одного из них была следующая задача: с базой данных работают пользователи. Таких пользователей около 40. Нужно увеличить число пользователей, сохранив при этом приемлемое время отклика (или уменьшив его) а также увеличив число транзакций на пользователя (или опять таки, увеличив его). Вот что компания имела в начале:
При увеличении числа пользователей на 10 человек, отклик увеличился почти в 5 раз, а число транзакций снизилось на
20%. А это неприемлемо. Попробовали включить кэширование. Это дало прирост производительности, но не тот на который рассчитывал заказчик.
Причем стоит обратить внимание на то, что в обоих случаях (с кэшированием и без) падение производительности происходит тогда когда процессорных ресурсов в сервере еще более чем достаточно.
Внедрение DataCore имело положительный эффект:
Число пользователей смогли увеличить в 3 раза, сохранив при этом приемлемое время отклика. Причем для ускорения БД были использованы процессорные ресурсы, которые в остальных случаях было не задействованы.
Инсталляция
Процесс установки MaxParallel довольно простой, но все-таки нужно учесть несколько моментов:
Интерфейса управления как такового у MaxParallel нет, есть только иконка в трее которая открывает окошко с одной только настройкой – «Вкл/Выкл» и окном для активации лицензии.
Управление решением производится через командную строчку, для чего предусмотрена утилита DcsMpAdmin.exe. С ее помощью можно включать/выключать сервис, выбрать диски для ускорения, ограничить объем памяти для сервиса MaxParallel. Но большинству эта утилита не понадобится.
Кому это поможет?
MaxParallel лучше всего проявляет себя ускоряя базу данных MS SQL 2008, чуть меньше на версиях 2012 и выше т.к. в них интенсивно используется in-memory. Но и тут эффект видно невооруженным глазом. Особенно хорошо ускоряются параллельные нагрузки на БД т.е. те случае когда производится много INSERT/UPDATE, а также создаются огромные отчеты.
Когда MaxParallel не поможет:
Как попробовать
MaxParallel можно бесплатно попробовать в течении 30 дней, для чего имеется полнофункциональный триальный период. Самый простой способ понять, подходит ли MaxParallel для вашего окружения, это замерить параметры текущей работы MS SQL в терминах транзакций в секунду, времени отклика и времени создания отчетов, а также измерить потребление системных ресурсов, таких как память и процессорное время. После этого поставить MaxParallel, запустить его и провести повторные измерения, и сравнить результаты.
Можно также попробовать версию MaxParallel развернутую в MS Azure, надо только иметь аккаунт на Azure (это бесплатно). Там вам на час дадут виртуальную машину Windows Server 2012R c 8 ядрами на базе Intel Xeon E5-2673v3, 28ГБ памяти, с MS SQL 2014, MaxParallel и HammerDB. Можно самостоятельно проверить разные сценарии, но только на синтетике.
Работа HammerDB до MaxParallel.
Работа HammerDB после запуска MaxParallel.
В довесок предлагается довольно понятный документ, описывающий по шагам процесс тестирования. Но, при наличии знаний и навыков работы с MS SQL можно воплотить любой сценарий.
Лицензирование
MaxParallel продается по подписке сроком на один год и лицензируется также как MS SQL т.е. по ядрам и редакциям – Standard, Enterprise, Web, Express, Developer. Лицензия на MaxParallel должна совпадать с редакцией MS SQL. Стоимость озвучена на сайте:
Также, для пользователей облачных ресурсов Azure можно купить виртуальные машины с MaxParallel на Azure Marketplace:
Заключение
Компания DataCore выпустила на рынок интересный инструмент, который найдет свое место в инфраструктуре. Это не универсальное решение, но сфера его применения ясна и четко определена. В дальнейшем компания планирует выпустить аналогичные решения для других областей: файловый сервер, Exchange (от инженеров я слышал даже слова “Linux”). Я призываю всех попробовать MaxParallel, и если на это нет аллергии, поделиться результатами тестирования. С Наступающим Новым Годом и успехов вам!
Повышение скорости работы SQL-запросов
Сразу оговорюсь, запросы в примерах – Transact SQL, он мне как-то роднее =)
Но принципы, в общем-то, должны работать везде.
Статья не претендует на новизну, и тем более, на полноту. Я лишь попытался вспомнить часто встречающиеся ошибки или недочеты в запросах, которые приводят к медленной работе с БД.
Поиск показал, что статья частично пересекается с этим топиком, но не во всем =)
Типы данных в полях
Самое очевидное – не использовать типы данных «с запасом». То есть если у нас есть поле «ICQ» типа VarChar, делать его длиннее 10 символов бессмысленно. Аналогично, если есть внешний ключ к справочнику, в котором всего несколько записей, нет смысла задавать ему тип Int, хватит и SmallInt. Несмотря на очевидность ошибки, встречается повсеместно.
Использование * в запросе
Вообще говоря, было много споров на эту тему, но я стараюсь не использовать «*» в SQL-запросах.
Во-первых, явное перечисление выбираемых полей повышает читабельность кода.
Во-вторых, в выборке далеко не всегда нужны все поля таблицы. А если мы связываем в запросе несколько таблиц, то практически всегда конструкция «Select *» потянет из базы в выборку кучу ненужных полей, например, ключи, по которым связаны таблицы. Столкнулся один раз с ситуацией, когда в таблице в текстовом поле хранились наименования файлов, а в бинарном поле – их содержимое. И запрос, который должен был всего лишь выдавать список файлов, грузил в память сервера еще и их содержимое. Тормозило это безбожно.
Использование курсоров
Вариант 1, с курсором:
Вариант 2, без курсора:
Понятно, что на таком простом примере вариант 2 очевиден. Но при более сложных вычислениях, для простоты реализации программист выбирает вариант с курсором – и ощутимо проигрывает в скорости.
Использование индексов
Без комментариев. Про индексы забывают сплошь и рядом, особенно новички.
Использование хранимых процедур
При выполнении сложных вычислений, использующих много значений из БД, их лучше оформить в виде хранимых процедур на сервере, нежели вычислять на клиентской стороне – зачем передавать на клиент исходные данные для вычислений, когда можно передать только результат.
Использование временных таблиц
Периодически, особенно при работе с результатами вычислений или сводными данными, проще и быстрее один раз запустить сложный запрос на выборку всех необходимых данных, сохранить его результаты во временную таблицу и дальше работать уже с ней, нежели каждый раз запускать сложный и медленный запрос.
Пожалуй это все, что пришло в голову навскидку, если статья кого-нибудь заинтересует, можно повспоминать еще.