Вопрос по sql-server – Каковы лучшие методы оптимизации производительности SQL Server? [закрыто]

17

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

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

В настоящее время я занимаюсь рефакторингом объектов базы данных во многих наших приложениях.

Так не стоит ли искать улучшения производительности, поскольку «преждевременная оптимизация - корень всего зла»?

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

Я кратко поиграл с «Консультантом по настройке движка базы данных», но не нашел его полезным. Возможно, мне просто нужно больше опыта для интерпретации результатов.

Ваш Ответ

14   ответов
11

помощью SQL Server Profiler. Когда у вас есть это, вы можете делать запросы на основе максимального и среднего времени выполнения, максимального и среднего времени процессора и (что также очень важно) количества выполнений запроса.

Поскольку я пытаюсь поместить весь код доступа к базе данных в хранимые процедуры, мне легко разбивать запросы. Если вы используете встроенный SQL, это может быть сложнее, так как изменение значения в запросе сделает его похожим на другой запрос. Вы можете попытаться обойти это, используя оператор LIKE, чтобы поместить одинаковые типы запросов в одни и те же сегменты для вычисления агрегатов (max, avg, count).

После того, как у вас есть список «10 лучших проблем», вы можете начать рассматривать их по отдельности, чтобы увидеть, может ли запрос быть переработан, может помочь индекс или если нужно внести незначительные изменения в архитектуру. Чтобы составить топ-10, попробуйте посмотреть на данные по-разному: avg * count для общей стоимости за период, max для худшего нарушителя, просто avg и т. Д.

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

Удачи

11

С точки зрения программирования баз данных, я думаю, что эта цитата - чепуха. Переписать все приложение чрезвычайно дорого, потому что ваши разработчики не хотят писать эффективный код с первого раза. Весь код t-sql следует рассматривать с точки зрения того, как он будет влиять на производительность базы данных во-вторых (целостность данных, конечно, в первую очередь). Производительность должна превосходить все, кроме целостности данных.

Да, есть вещи оптимизации, которые вы не должны делать до тех пор, пока у вас не возникнут проблемы, но некоторые вещи следует делать как само собой разумеющееся, а не исправлять позже. Написание кода, который имеет больше шансов быть эффективным, не займет больше времени, чем когда вы поймете, как вы влияете на эффективность с помощью плохого кода. Обсуждение кода курсора в Cervo является одним из примеров. Действия, основанные на множествах, почти всегда намного быстрее, чем решения с курсорами, поэтому курсоры не должны изначально записываться, когда будет делать решение на основе множеств. Почти всегда у меня уходит меньше времени на написание основанного на множестве решения, чем при написании курсора, но единственный способ добиться этого - никогда не писать курсоры.

И нет никакой причины когда-либо использовать select * вместо указания имен полей. В MSSQL вы можете перетащить эти имена из проводника объектов, чтобы вы не могли сказать мне, что это слишком сложно сделать. Но указав только те поля, которые вам действительно нужны, вы экономите сетевые ресурсы, ресурсы сервера баз данных и ресурсы веб-сервера. Так почему же программист должен когда-либо выбирать ленивый выбор select * и беспокоиться об оптимизации позже?

То же самое с индексами. Вы говорите, что делаете минимальный набор индексов. В зависимости от того, как вы определяете минимальный, это может быть нормально, но очень важно иметь индексы для всех внешних ключей, и я бы не хотел выдвигать базу данных, в которой не было индексов, в нескольких полях, которые чаще всего находятся там, где статьи. Если ваши пользователи являются внешними клиентами, а не внутренними, они не будут жаловаться на медлительность вашего сайта, они пойдут в другое место. С самого начала имеет смысл планировать эффективный доступ к базе данных.

Одна из моих основных проблем, связанных с тем, что с самого начала не учитывалась эффективность, заключается в том, что первые пару раз, когда дела идут слишком медленно, компании склонны просто бросать больше оборудования на решение этой проблемы, а не на настройку производительности. К тому времени, когда люди начинают настройку производительности, у вас есть база данных объемом в несколько гигабайт или больше со многими недовольными клиентами, которые получают тайм-ауты больше, чем результаты. На этом этапе часто почти все в базе данных необходимо переписать, а тем временем вы теряете клиентов. Я не забываю предоставлять поддержку одной компании с помощью коммерческого приложения, которое буквально занимало десять минут, пока представители службы поддержки клиентов переходили с одного экрана на другой, пытаясь помочь уже недовольным клиентам по телефону. Вы можете представить, сколько клиентов потеряла компания из-за плохо разработанных запросов к базе данных в коммерческом продукте, которые мы не могли изменить.

Indeed, я заменил курсоры на набор кода, который занимал от нескольких минут до миллисекунд, а иногда от часов до секунд. Не нужно писать такой код для начала. HLGEM
Я согласен с этим - системы баз данных включают куски металла, которые ходят туда-сюда. Эти кусочки металла не становятся экспоненциально быстрее, чем микросхемы, и неправильный запрос может привести к разнице в производительности на порядки. ConcernedOfTunbridgeWells
Продолжение: использование известных неэффективных методов, когда известные более эффективные методы занимают столько же времени или меньше, что и код, является просто плохим кодированием. Обычно это происходит потому, что люди не знают, как строится запрос. HLGEM
Преждевременная оптимизация заключается в том, чтобы избегать оптимизации чего-либо, если у вас нет измерений того, сколько времени / усилий будет сэкономлено. Отказ от использования курсоров не является преждевременной оптимизацией, поскольку существует очень убедительное и хорошо документированное улучшение производительности, позволяющее избежать их. Nat
Чтобы согласиться с точкой зрения @ nat, есть убедительная документация о разнице в производительности между многими различными методами запросов, которые дают одинаковые результаты. Использование выражений saragble where, использование Inst вместо IN или избегание коррелированных подзапросов - три вещи, которые возникают только за пределами курсоров. Но есть целые книги по настройке производительности, которые учат вас, какие плохие методы написания запросов и какие хорошие. Никто не должен писать SQL-код (или использовать ORM), пока не прочитает одну из этих книг от корки до корки. HLGEM
2

как вы профилируете, поместите запросы, которые вы считаете сложными, в SQL Query Analyzer и отобразите план выполнения. Определите части запросов, которые выполняют дорогостоящее сканирование таблиц, и переиндексируйте эти таблицы, чтобы минимизировать эти затраты.

Попробуйте эти ссылки:

Оптимизация SQL
Как оптимизировать запросы

1

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

Это точно скажет, на чем стоит сосредоточиться

1

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

Я видел приложение, в котором кто-то загружал клиенту целую таблицу событий в базе данных, а затем просматривал каждую строку одну за другой по нескольким критериям. Произошло ОГРОМНОЕ увеличение производительности при передаче критериев фильтра в базу данных и применении запроса к критериям в предложении where. Это очевидно для людей, которые работают с базами данных, но я видел, как похожие вещи возникают. Также у некоторых людей есть запросы, в которых хранится множество временных таблиц, заполненных ненужными строками, которые затем удаляются в окончательном соединении временных таблиц. В основном, если вы исключите из запросов, которые заполняют временные таблицы, то для остальной части запроса будет меньше данных, и весь запрос будет выполняться быстрее.

Курсоры очевидны. Если у вас есть миллион строк и вы идете строка за строкой, это займет вечность. В некоторых тестах, если вы подключаетесь к базе данных даже с «медленным» динамическим языком, таким как Perl, и выполняете некоторые построчные операции над набором данных, скорость все равно будет намного выше, чем курсора в базе данных. Сделайте это с чем-то вроде Java / C / C ++, и разница в скорости будет еще больше. Если вы можете найти / устранить курсор в коде базы данных, он будет работать намного быстрее ... Если вы должны использовать курсор, переписать эту часть на любом языке программирования и вытащить его из базы данных, вероятно, приведет к значительному увеличению производительности.

Еще одно замечание о курсорах, остерегайтесь кода, такого как SELECT @ col1 = col1, @ col2 = col2, @ col3 = col3, где id = @currentid в цикле, который проходит через идентификаторы, а затем выполняет операторы для каждого столбца. В основном это и курсор. Не только это, но и использование реальных курсоров часто быстрее, особенно static и forward_only. Если вы можете изменить операцию, которая должна быть установлена на основе, это будет намного быстрее ..... При этом у курсоров есть место для некоторых вещей .... но с точки зрения производительности есть штраф за использование их по сравнению с набором на основе подходы.

Также остерегайтесь плана выполнения. Иногда он оценивает операции, которые занимают секунды, чтобы быть очень дорогими, а операции, которые занимают минуты, являются очень дешевыми. При просмотре плана выполнения убедитесь, что все проверено, возможно, вставив несколько SELECT «В этой области», GETDATE () в ваш код.

1

но при использовании набора профилирования вы ДОЛЖНЫ быть уверены, что это точный набор тестовых данных, иначе инструменты настройки не смогут получить вам точный результат, который вам нужен.

Также очень полезны объекты управления с фрагментацией и отчетами об использовании в 2005 году!

1

что «преждевременная оптимизация - корень всего зла» в этом контексте - абсолютная ерунда.

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

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

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

1

Применять правильную индексацию в столбцах таблицы в базе данных

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

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

Создавать некластеризованные индексы для столбцов, которые являются

Часто используется в критериях поиска.

Используется для соединения с другими столами.

Используется как поля внешнего ключа.

С высокой избирательностью (столбец, который возвращает низкий процент (0-5%) строк от общего числа строк по определенному значению).

Используется в предложении ORDER BY.

Не используйте «SELECT *» в запросе SQL

Ненужные столбцы могут быть извлечены, что увеличит время поиска данных. Ядро базы данных не может использовать преимущество «Covered Index», поэтому запрос выполняется медленно.

Пример

SELECT Cash, Age, Amount FROM Investments; 

Вместо того

SELECT * FROM Investments;

Старайтесь избегать предложения HAVING в операторах Select

редложение @HAVING используется для фильтрации строк после того, как все строки выбраны, и используется как фильтр. Старайтесь не использовать предложение HAVING для каких-либо других целей.

Пример

SELECT Name, count (Name) FROM Investments WHERE Name!= ‘Test’ AND Name!= ‘Value’ GROUP BY Name;

Вместо того

SELECT Name, count (Name) FROM Investments GROUP BY Name HAVING Name!= ‘Test’ AND Name!= ‘Value’ ;

Попытайтесь минимизировать количество блоков подзапросов в запросе

Иногда у нас может быть несколько подзапросов в нашем основном запросе. Мы должны попытаться минимизировать количество подзапросов в нашем запросе.

Пример

SELECT Amount FROM Investments WHERE (Cash, Fixed) = (SELECT MAX (Cash), MAX (Fixed) FROM Retirements) AND Goal = 1; 

Вместо того

SELECT Amount FROM Investments WHERE Cash = (SELECT MAX (Cash) FROM Retirements) AND Fixed = (SELECT MAX (Fixed) FROM Retirements) AND Goal = 1;

Избегайте ненужных столбцов в списке SELECT и ненужных таблиц в условиях соединения

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

Не используйте агрегат COUNT () в подзапросе для проверки существования

Когда вы используете COUNT (), SQL Server не знает, что вы делаете проверку существования. Он подсчитывает все совпадающие значения либо путем сканирования таблицы, либо путем сканирования наименьшего некластеризованного индекса. Когда вы используете EXISTS, SQL Server знает, что вы делаете проверку существования. Когда он находит первое совпадающее значение, он возвращает TRUE и прекращает поиск.

Старайтесь не соединять столбцы двух типов

При объединении двух столбцов разных типов данных один из столбцов должен быть преобразован в тип другого. Столбец, тип которого ниже, является тем, который преобразован. Если вы объединяете таблицы с несовместимыми типами, одна из них может использовать индекс, но оптимизатор запросов не может выбрать индекс для столбца, который он преобразует.

Старайтесь не использовать COUNT (*) для получения количества записей в таблице

Чтобы получить общее количество строк в таблице, мы обычно используем следующую инструкцию Select:

SELECT COUNT(*) FROM [dbo].[PercentageForGoal]

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

SELECT rows FROM sysindexes
WHERE id = OBJECT_ID('[dbo].[PercentageForGoal]') AND indid< 2

Попытайтесь использовать операторы, такие как EXISTS, IN и JOINS, соответствующим образом в вашем запросе

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

Старайтесь избегать динамического SQL

Если в действительности нет необходимости, старайтесь избегать использования динамического SQL, потому что: Динамический SQL сложно отлаживать и устранять неполадки. Если пользователь предоставляет входные данные для динамического SQL, существует вероятность атак с использованием SQL-инъекций.

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

Если действительно не требуется, старайтесь избегать использования временных таблиц. Скорее используйте табличные переменные. В 99% случаев табличные переменные находятся в памяти, поэтому это происходит намного быстрее. Временные таблицы находятся в базе данных TempDb. Поэтому работа с временными таблицами требует взаимодействия между базами данных и, следовательно, будет медленнее.

Вместо поиска LIKE используйте полнотекстовый поиск для поиска текстовых данных

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

Попробуйте использовать UNION для выполнения операции «ИЛИ»

Старайтесь не использовать «ИЛИ» в запросе. Вместо этого используйте «UNION», чтобы объединить результирующий набор двух выделенных запросов. Это улучшит производительность запросов. Лучше использовать UNION ALL, если выдающийся результат не требуется. UNION ALL работает быстрее, чем UNION, так как ему не нужно сортировать результирующий набор, чтобы найти отличительные значения.

Реализация стратегии отложенной загрузки для больших объектов

Храните столбцы больших объектов (например, VARCHAR (MAX), Image, Text и т. Д.) В другой таблице, чем основная таблица, и помещайте ссылку на большой объект в основную таблицу. Извлеките все данные основной таблицы в запросе, и, если требуется загрузить большой объект, извлекайте данные больших объектов из таблицы больших объектов только тогда, когда это требуется.

Внедрить следующие полезные практики в определяемые пользователем функции

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

Внедрить следующие полезные практики в триггеры

Старайтесь избегать использования триггеров. Запуск триггера и выполнение события триггера - дорогостоящий процесс. Никогда не используйте триггеры, которые могут быть реализованы с использованием ограничений. Не используйте один и тот же триггер для разных инициирующих событий (Вставка, Обновление и Удаление). Не используйте транзакционный код внутри триггера. Триггер всегда выполняется в пределах транзакционной области кода, который запускает тригге
0

Запустите профилировщик и запишите наиболее распространенные запросы, которые вы выполняете в базе данных. Затем запустите эти запросы с включенным планом выполнения, и вы увидите, что (если что-нибудь) замедляет ваши запросы. Затем вы можете оптимизировать запросы или добавить дополнительные индексы в свои поля.

SQL Books даст вам хороший обзор функций профилирования и анализа запросов.

0

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

0

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

0

http: //www.sql-server-performance.com

В прошлом он был для меня хорошим и полезным.

-1

применимых ко всем базам данных, а затем попробовать те, которые относятся к MsSQL.

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

95% улучшений производительности будет приходиться на приложение, а не на конфигурацию сервера или ядра базы данных. Сначала разработай для корректности, потом подправь для производительности Сократить поездки в базу данных Попытайтесь выразить вещи так, чтобы они подходили для вашей модели данных Не обращайте внимания на общие рекомендации по производительности - да, в какой-то момент вы найдете системный или SQL-оператор, к которому не относится одно из этих правил.

Но ключевым моментом является то, что вы всегда должны применять правило 80-20. Это означает, что в любой системе вам нужно настроить 20% (часто гораздо меньше) вашего кода для наибольшего прироста производительности. Вот где поставщик предоставил инструменты обычно терпит неудачу, поскольку они обычно не могут угадать прикладной / бизнес-контекст исполнения.

Похожие вопросы