У цій статті ми розглянемо популярні інструменти, T-SQL запити і скрипти для виявлення і вирішення різних можливих проблем з продуктивністю SQL Server. Ця стаття допоможе вам розібратися, коли вашому SQL Server недостатньо ресурсів (пам'яті, CPU, IOPs дисків), знайти блокування, виявити повільні запити. Подивимося які є вбудовані інструменти та безкоштовні сторонні скрипти і утиліти для аналізу стану Microsoft SQL Server.
зміст:
- Інструменти для діагностики SQL Server
- Виявлення та вирішення проблем з продуктивністю SQL Server
Інструменти для діагностики SQL Server
Якщо ви правильно діагностували проблему, то половина роботи вже зроблена. Розглянемо які інструменти зазвичай використовуються системним адміністратором для діагностики різних проблем в SQL Server:
- T-SQL - найпотужніший, простий і незамінний інструмент для пошуку проблем і аналізом продуктивності SQL Server. Практично всі інші інструменти для роботи з SQL Server використовують T-SQL. Немає нічого такого, щоб ви не змогли зробити за допомогою T-SQL.
- SQL Server Management Studio - без SSMS практично неможливо працювати з SQL Server. За допомогою SSMS ви можете подивитися Activity monitor, проаналізувати план запиту, подивитися параметри сервера або бази даних і багато інших речей.
- Журнали помилок SQL Server і Windows - якщо щось йде не так, журнал помилок - це перше місце, куди дивиться системний адміністратор. Журнал помилок SQL Server можна подивитися через SSMS. Журнали Windows можна подивитися через оснащення eventvwr.msc.
- Монітор ресурсів Windows - resmon.exe незамінний інструмент Windows для швидкої оцінки стану ресурсів сервера. Використання оперативної пам'яті і процесора можна подивитися і через Диспетчер завдань, але детальне використання мережі і жорсткого диска можна подивитися тільки через resmon і perfmon.
- Системний монітор Windows (Performance Monitor) - Perfmon.exe це основний засіб моніторингу Windows, він містить в собі різноманітні "лічильники", як системних метрик, так і додатків, включаючи SQL Server. Зазвичай лічильники perfmon обробляють за допомогою інших систем моніторингу, наприклад, Zabbix, так як в perfmon незручно зберігати і дивитися дані за минулий час.
- сторонні додатки - існує багато платних і безкоштовних додатків для моніторингу SQL Server. Наприклад, одним з безкоштовних додатків є dbForge Monitor від компанії Devart. Додаток встановлюється як доповнення до SSMS і дозволяє виводити дуже зручний дашборда для відображення поточного стану вашого SQL Server (інформація про використання пам'яті, CPU, навантаженнях, блокування, процесах, інформацію про бекапи, "важких SQL запитах", продуктивності дискової підсистеми і т.д .).
- скрипти Brentozar - це популярне рішення для діагностики налаштувань і працездатності SQL Server. У brentozar є багато скриптів для різних завдань, але для діагностики нас цікавить "sp_blitz". Завантажити можна безкоштовно з офіційного сайту https://www.brentozar.com/blitz/. Запустіть sp_Blitz.sql щоб встановити необхідні процедури і виконайте їх exec sp_blitz для діагностики. Цей інструмент безкоштовний і підтримується спільнотою SQL Server. Sp_blitz визначить всі популярні проблеми з вашим сервером і порадить як їх вирішити.
- Набори T-SQL скриптів - зручно мати під рукою колекції різноманітних T-SQL запитів для діагностики SQL Server, так як не завжди є час писати власні запити, краще озброїтися заздалегідь. Нижче перераховані посилання на корисні T-SQL / PowerShell запити, які я часто використовую при діагностиці та тюнінгу MS SQL:
- https://github.com/SQLadmin/AwesomeSQLServer - набір запитів для моніторингу CPU / RAM / Disk IO і інших параметрів.
- https://github.com/dgavrikov/databases_scripts/tree/master/SQL%20Server - багато корисних скриптів і Лайфхак
- https://github.com/ktaranov/sqlserver-kit - Скрипти та корисна інформація. Багато прикладів роботи з SQL Server через PowerShell
Виявлення та вирішення проблем з продуктивністю SQL Server
Найпоширенішою проблемою з якою стикається системний адміністратор, який працює з SQL Server, це скарги користувачів на продуктивність запитів і самого сервера: "гальмує", "довго виконується запит", і так далі.
Перш за все потрібно переконатися, що сервера вистачає ресурсів. Розглянемо, як в SQL Server швидко проаналізувати використання пам'яті, CPU, дисків та наявність блокувань.
Аналіз використання оперативної пам'яті SQL Server
Для початку потрібно визначити скільки пам'яті доступно SQL Server. Для цього запустіть SSMS (SQL Server Management Studio), зайдіть на сервер і зайдіть в властивості сервера (ПКМ за назвою сервера в Обозревателе об'єктів).
Сам по собі доступний об'єм RAM вам нічого не скаже. Потрібно порівняти це число з використовуваної пам'яттю в диспетчері Завдань і самим двигуном SQL Server за допомогою DMV.
У диспетчері завдань, у вкладці Подробиці, знайдіть sqlservr.exe і подивіться скільки оперативної пам'яті використовує цей процес.
- Якщо на сервері, наприклад, 128 GB оперативної пам'яті, а процес sqlservr.exe використовує 60 GB і обмежень по RAM у SQL Server немає, то оперативної пам'яті вам вистачає.
- Якщо SQL Server використовує 80-90% RAM від заданої або максимальної, то в такому випадку потрібно дивитися DMV. Майте на увазі, що sqlservr.exe не зможе використовувати всю оперативну пам'ять. Якщо на сервері 128 GB, то sqlservr.exe може використовувати тільки 80-90% (100-110 GB), так як інша пам'ять резервується для операційної системи.
Майте на увазі, що процес SQL Server'a не дає оперативну пам'ять назад в систему. Наприклад, ваш SQL Server зазвичай використовує 20 GB пам'яті, але при місячному звіті він збільшує споживання до 100 GB, і навіть коли обчислення звіту закінчиться і сервер буде працювати в колишньому режимі, процес SQL Server'a все одно буде використовувати 100 GB до перезавантаження служби.
Навіть якщо ви впевнені, що оперативної пам'яті сервера вистачає, не буде зайвим точно знати обсяг споживаної RAM.
Дізнатися реальне використання RAM можна за допомогою Dynamic Management Views. DMV це адміністративні вюверів (подання). За допомогою DMV можна діагностувати практично будь-яку проблему в SQL Server.
Подивимося sys.dm_os_sys_memory, для зручності використовуємо запит:
SELECT total_physical_memory_kb / 1024 AS [Total Physical Memory], available_physical_memory_kb / 1024 AS [Available Physical Memory], total_page_file_kb / 1024 AS [Total Page File (MB)], available_page_file_kb / 1024 AS [Available Page File (MB)], 100 - ( 100 * Cast (available_physical_memory_kb AS DECIMAL (18, 3)) / Cast (total_physical_memory_kb AS DECIMAL (18, 3))) AS 'Percentage Used', system_memory_state_desc AS [Memory State] FROM sys.dm_os_sys_memory;
Розглянемо кожен виведений параметр:
- [Total Physical Memory] - обсяг оперативної пам'яті доступний в операційній системі. На деяких серверах може показувати трохи більше реально встановленої.
- [Available Physical Memory] - обсяг оперативної пам'яті доступний для SQL Server, без урахування вже захопленої SQL Server.
- [Total Page File (MB)] - Обсяг "Сommit limit". Commit Limit = Оперативна пам'ять + всі файли підкачки. Тобто, якщо у вас на сервері 32 GB оперативної пам'яті і 16 GB файл підкачки, commit limit буде 48 GB.
- [Available Page File (MB)] - Обсяг файлу підкачки.
- Percentage Used - відсоток зайнятої оперативної пам'яті. Такого параметра немає в самому sys.dm_os_sys_memory, але він вважається за формулою available_physical_memory_kb / total_physical_memory_kb
- [Memory State] - Стан RAM. Поле system_memory_state_desc містить в собі стан споживання оперативної пам'яті у вигляді тексту. Значення цього поля вважається виходячи з інших двох: system_low_memory_signal_state і system_high_memory_signal_state. Ви можете вибирати їх безпосередньо, якщо вам потрібен Boolean / bit формат даних. Для ознайомлення з усіма полями sys.dm_os_sys_memory ознайомтеся з документацією https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-memory-transact -sql? view = sql-server-ver15
Всі ці дані корисні, якщо ви хочете точно визначити скільки ваш SQL Server споживає RAM. Найчастіше це використовують, якщо є підозри що для примірника виділено занадто багато оперативної пам'яті.
Якщо Вам потрібно переконатися, що сервера вистачає RAM, ви можете дивитися тільки на поля system_low_memory_signal_state, system_high_memory_signal_state і system_memory_state_desc. Якщо system_low_memory_signal_state = 1, то сервера явно не вистачає оперативної пам'яті.
Завантаження процесора в SQL Server
Навантаження на процесор визначити простіше, так як це можна зробити в диспетчері завдань. Щоб дізнатися поточне навантаження на процесор, знайдіть в диспетчері завдань процес sqlservr.exe
Якщо ви хочете дізнатися навантаження за минулий час, можна скористатися запитом:
Не забудьте поміняти @lastNMin на потрібне вам число в хвилинах.DECLARE @ts BIGINT; DECLARE @lastNmin TINYINT; SET @lastNmin = 30; SELECT @ts = (SELECT cpu_ticks / (cpu_ticks / ms_ticks) FROM sys.dm_os_sys_info); SELECT TOP (@lastNmin) Dateadd (ms, -1 * (@ts - [timestamp]), Getdate ()) AS [EventTime], sqlprocessutilization AS [SQL Server Utilization], 100 - systemidle - sqlprocessutilization AS [Other Process CPU_Utilization] , systemidle AS [System Idle] FROM (SELECT record.value ( '(./ Record / @ id) [1]', 'int') AS record_id, record.value ( '(./ Record / SchedulerMonitorEvent / SystemHealth / systemIdle ) [1] ',' int ') AS [SystemIdle], record.value (' (./ Record / SchedulerMonitorEvent / SystemHealth / ProcessUtilization) [1] ',' int ') AS [SQLProcessUtilization], [timestamp] FROM ( SELECT [timestamp], CONVERT (XML, record) AS [record] FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR 'AND record LIKE' %% ') AS x) AS y ORDER BY record_id DESC;
В результаті ми отримаємо похвилинну статистику використання процесора.
Аналіз навантаження на диск SQL Server
Подивимося на завантаження дисків в операційній системі. Для цього запустіть resmon.exe. 
Нам потрібна вкладка Disk. У секції Disk Activity відображаються файли, до яких йде звернення, і їх швидкість read / write на поточний момент. Фільтрувати цю секцію по Total (клікніть на Total). На самому верху будуть файли, які на даний момент максимально використовують диск. У випадку з SQL Server це може бути корисно щоб визначити яка база найбільше навантажує диск на поточний момент.
У секції Storage відображаються всі диски в системі. У цій секції нам потрібні 2 параметра - Active Time і Disk Queue. Active Time в процентах відображає навантаження на диск, тобто якщо ви бачите на диску C: \ Active Time рівний 90, це означає що ресурс читання / запису диска на поточний момент використовується на 90%. Стовпець Disk Queue відображає чергу звернень до диску, і якщо значення черзі не дорівнює нулю, то диск завантажений на 100% і не справляється з навантаженням. Так само якщо Active Time близький до 100, то диск використовується практично на межі своїх можливостей по швидкості.
Перегляд блокувань в SQL Server
Після того як ми переконалися, що сервера вистачає ресурсів, можна переходити до перегляду блокувань.
Блокування можна подивитися через Activity Monitor в SSMS, але ми скористаємося T-SQL, так як цей варіант більш зручний і наочний. Виконуємо запит:
SET NOCOUNT ON GO SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR (10), "), CHAR (13),") AS BATCH INTO #T FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text (R. SQL_HANDLE) T GO WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH) AS (SELECT SPID, BLOCKED, CAST (REPLICATE ( '0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL, BATCH FROM #TR WHERE (BLOCKED = 0 OR BLOCKED = SPID) AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED R2.SPID) UNION ALL SELECT R.SPID, R.BLOCKED, CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL, R.BATCH FROM #T AS R INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED> 0 AND R.BLOCKED R.SPID) SELECT N "+ REPLICATE (N '|', LEN (LEVEL) / 4 - 1) + CASE WHEN ( LEN (LEVEL) / 4 - 1) = 0 THEN 'HEAD -' ELSE '| ------' END + CAST (SPID AS NVARCHAR (10)) + N "+ BATCH AS BLOCKING_TREE FROM BLOCKERS ORDER BY LEVEL ASC GO DROP TABLE #T GO
Цей запит повертає список блокувань у вигляді дерева. Це зручно в роботі, так як зазвичай, якщо виникає одна блокування, вона провокує за собою інші. Аналогічно в Activity Monitor або у висновку sp_who2 можна побачити поле "Blocked By".
Якщо запит нічого не повернув, то блокувань немає.
Якщо запит повернув якісь дані, то потрібно проаналізувати ланцюжок.
HEAD значить що цей запит є причиною всіх інших блокувань нижче по дереву. 64 - це ідентифікатор процесу (SPID). Після цього пишеться тіло запиту, який викликав блокування. Якщо у вас вистачає ресурсів сервера, то швидше за все справа в самому запиті і у взаємному зверненні до якихось об'єктів. Для того щоб сказати точніше, треба аналізувати конкретний запит, який викликав блокування.
Політики SQL Server
Навіть коли у вас все працює добре і скарг немає, насправді може бути багато проблем, які спливуть пізніше. Для цього в SQL Server є політики.
Політика в SQL Server це, грубо кажучи, перевірка правила на відповідність заданому значенню. Наприклад, за допомогою політик ви можете переконатися, що на всіх базах на сервері вимкнений Auto Shrink. Розглянемо приклад імпорту та виконання політики
У SSMS, підключіться до сервера, на якому хочете виконувати політики (Management -> розділ Policy Management).
Імпортуємо файл Database Auto Shrink.xml. тиснемо Evaluate
На примірнику node1 дві бази даних, test1 і test2. На test2 включений autoshrink. подивимося деталі. 
Політика визначила включений параметр AutoShrink, в описі зазвичай пишеться пояснення до правил. В даному випадку дається пояснення чому auto shrink краще відключати.
Політики можуть виконуватися або за розкладом, або на вимогу (разово). Результати виконання політики можна подивитися в журналі політик.
При установці SQL Server потрібно вибирати тільки використовувані компоненти СУБД, і вказувати настройки відповідно до конфігурації "заліза" вашого сервера. Завжди стежте серверу вистачало ресурсів, і щоб на сервері не було блокувань
Найпотужнішим інструментом для діагностики SQL Server є T-SQL і DMV. Так само рекомендується побудувати цілодобовий моніторинг над SQL Server і над обслуговуючої його інфраструктурою для виявлення всіх можливих проблем.
