Anton Sidorov homepage

Bookmark this to keep an eye on my project updates!

Follow me on GitHub

MS SQL Server

Масштабируемость

HA:

  • Read Only Replica
    • log-shipping
    • Always On Group из за использования возможности чтения со вторичной реплики (read-intent access)
      • Увеличение в скорости формирования в два и более раза, возможность использования maxdop
      • Не потребляются ресурсы первичной реплики
        • tempdb
        • Сеть
        • I/O
        • CPU
    • Log-shipping VS Always on
      • AlwaysOn High Availability Group (HAG), is easier to maintain than log shipping
      • Возможно больше трудностей с AlwaysOn, экспертиза DBA более высокая трубется

Паттерны

  • delete rows VS update rows state - удаление блокирующая и длительная операция

Секционирование

Секционирование (партиционирование горизонтальное)

Производительность

Зависит от:

Способы анализа производительности

SQL Trace Profiler (Deprecated)

  1. Записать с помощью SQL Server Profiler (или AnjLab.SqlProfiler) запросы, исполняемые при запуске функции (например редактирование анкеты)
  2. Добавить метки времени в начало и в конец запроса CONVERT(nvarchar(30), GETDATE(), 126)
  3. Запустить скрипт на локальном сервере и на сервере разработчика
  4. Вычислить времени выполнения на локальном сервере и на сервере разработчика (ручным способом)/ Результаты позволяют уверенно говорить о причинах медленной загрузки страниц (например редактирование анкеты) в браузере.

Benchmark Load Test

Стратегии оптимизации запросов

  • можно использовать индексы
  • другие варианты запроса
    • To write sargable queries:
      • Avoid using functions or calculations on indexed columns in the WHERE clause
      • Use direct comparisons when possible, instead of wrapping the column in a function
      • If we need to use a function on a column, consider creating a computed column or a function-based index, if the database system supports it
  • сохранение промежуточных результатов
  • Для проверки быстродействия запроса: SET STATISTICS TIME ON
  • Для проверки статистики ввода/вывода: SET STATISTICS IO ON
  • Для вывода плана запроса: SET STATISTICS XML ON

Настройки

  • Настройки СУБД get
    • SELECT * from sys.configurations ORDER BY name

Параллелизм MAXDOP

  • SQL OLTP Max degree of parall maxdop
    • Max Degree of Parallelism - задает максимальное количество потоков, которые могут быть выделены каждому запросу (по умолчанию стоит 0-ограничение только самой операционной системой и редакцией MS SQL Server)
    • Cost Threshold for Parallelism - оценочная стоимость параллелизма (по умолчанию стоит 5)
    • Max DOP - задает максимальное количество потоков, которые могут быть выделены каждому запросу на уровне базы данных (но не более, чем значение свойства «Max Degree of Parallelism») (по умолчанию стоит 0-ограничение только самой операционной системой и редакцией MS SQL Server, а также ограничение по свойству «Max Degree of Parallelism» всего экземпляра MS SQL Server)
  • Для выявления нехватки процессорного времени достаточно воспользоваться системным представлением sys.dm_os_schedulers.
    • показатель runnable_tasks_count постоянно больше 1, то существует большая вероятность того, что количество ядер не хватает экземпляру MS SQL Server.
    • select max([runnable_tasks_count]) as [runnable_tasks_count] from sys.dm_os_schedulers where scheduler_id<255;
  • алгоритм действий для OLTP-систем для настройки свойств параллелизма:
    • сначала запретить параллелизм, выставив на уровне всего экземпляра Max Degree of Parallelism в 1
    • проанализировать самые тяжелые запросы и подобрать для них оптимальное количество потоков
    • выставить Max Degree of Parallelism в подобранное оптимальное количество потоков, полученное из п.2, а также для конкретных баз данных выставить Max DOP значение, полученное из п.2 для каждой базы данных
    • проанализировать самые тяжелые запросы и выявить негативный эффект от многопоточности. Если он есть, то повышать Cost Threshold for Parallelism.
    • Для таких систем как 1С, Microsoft CRM и Microsoft NAV в большинстве случаев подойдет запрет многопоточности
  • Как определить maxdop
    • I set the Maximum Degree of Parallelism to 2, which means the query still uses parallelism but only on 2 CPUs.
    • However, I keep the Cost Threshold for Parallelism very high. This way, not all the queries will qualify for parallelism but only the query with higher cost will go for parallelism. I have found this to work best for a system that has OLTP queries and also where the reporting server is set up.

Мониторинг

Онлайн

Исторически

Performance Reports

  • Стандартные отчеты Performance dashboard
    • Performance
      • Top Queries by Average IO
      • Top Queries by Average CPU Time
      • Object Execution Statistics
      • Missing Index
      • IO Statistics
      • Expensive Queries – Duration
  • Data Collection - сбор авто метрик за период времени в отдельной БД с sql plan
    • Query Statistics History: by CPU, duration, IO, Physical Reads, Logical Reads
    • Server Activity History: CPU, RAM, IO, Network, Waits
    • Версия MS SQL с 2008: используем 2012, DWH 2016
    • Блокировки Dead Locks
    • Waits - ожидания
    • Latches - внутренние блокировки

TODO

  • http://f1incode.blogspot.com/2011/07/i_28.html
  • http://f1incode.blogspot.com/2011/08/performance-testing-part-2.html
  • http://www.itcommunity.ru/Msgs/default.aspx?MessageID=60
  • http://msmvps.com/blogs/irinanaumova/archive/2011/05/06/1792775.aspx
  • http://www.mssqltips.com/tip.asp?tip=1039

Version

  • 2012 11.0.x.x
    • use 11.0.6579.0
  • 2014 12.0.x.x
    • Always On Availability Groups
  • 2016
    • use CTI
    • Query Store

2019

Плюсы:

Минусы:

  • больше нет Service Pack, только Cumulative Updates

Upgrade