В тази статия ще разгледаме популярни инструменти, T-SQL заявки и скриптове за откриване и решаване на различни възможни проблеми с производителността на SQL Server. Тази статия ще ви помогне да разберете, когато вашият SQL Server няма достатъчно ресурси (памет, процесор, IOP на дискове), да намерите ключалки и да откриете бавни заявки. Нека да видим какви са вградените инструменти и безплатни скриптове и помощни програми на трети страни за анализ на състоянието на Microsoft SQL Server.
Съдържание:
- SQL Server диагностични инструменти
- Откриване и разрешаване на проблеми с производителността на SQL Server
SQL Server диагностични инструменти
Ако правилно сте диагностицирали проблема, тогава половината работа вече е свършена. Помислете какви инструменти обикновено се използват от системния администратор за диагностициране на различни проблеми в SQL Server:
- T-SQL - Най-мощният, прост и незаменим инструмент за отстраняване на проблеми и анализ на ефективността на SQL Server. Почти всички други инструменти за работа със SQL Server използват T-SQL. Няма нищо, което не можете да направите с T-SQL.
- Студио за управление на SQL Server - Без SSMS е почти невъзможно да се работи със SQL Server. Използвайки SSMS, можете да разгледате монитора на дейността, да анализирате плана за заявки, да разгледате параметрите на сървъра или базата данни и много други неща.
- Журнали за грешки в SQL Server и Windows - ако нещо се обърка, дневникът на грешките е на първо място, който системният администратор изглежда. Журналът за грешки в SQL Server може да се гледа чрез SSMS. Регистрациите на Windows могат да бъдат преглеждани чрез прихващането на eventvwr.msc.
- Windows Resource Monitor - resmon.exe е незаменим инструмент на Windows за бърза оценка на състоянието на сървърните ресурси. Използването на RAM и процесора може да се гледа през диспечера на задачите, но подробното използване на мрежата и твърдия диск може да се гледа само чрез resmon и perfmon.
- Windows System Monitor (Монитор на производителността) - Perfmon.exe е основният инструмент за наблюдение на Windows, съдържа различни „броячи“, както системни показатели, така и приложения, включително SQL Server. Обикновено парфмоновите броячи се обработват с помощта на други системи за мониторинг, например Zabbix, тъй като в perfmon е неудобно да се съхраняват и преглеждат данни за изминалото време..
- Приложения на трети страни - Има много платени и безплатни приложения за мониторинг на SQL Server. Например едно от безплатните приложения е dbForge Monitor от компанията Devart Приложението е инсталирано като допълнение към SSMS и ви позволява да покажете много удобно табло за показване на текущото състояние на вашия SQL Server (информация за използването на паметта, процесора, натоварвания, заключвания, процеси, информация за архивиране, „тежки SQL заявки“, производителност на дисковата подсистема и т.н. )..
- Сценарии Брентозар - Това е популярно решение за диагностициране на настройките и здравето на 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 - набор от заявки за наблюдение на процесор / RAM / диск 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..
Анализ на използването на RAM на SQL Server
Първо трябва да определите колко памет е налична в SQL Server. За да направите това, стартирайте SSMS (SQL Server Management Studio), отидете на сървъра и отидете на свойствата на сървъра (RMB по име на сървър в Object Browser).
Самият наличен RAM капацитет няма да ви каже нищо. Трябва да сравните този номер с паметта, използвана в диспечера на задачите и самия двигател на SQL Server, използвайки DMV.
В диспечера на задачите в раздела Подробности намерете sqlservr.exe и вижте колко RAM използва този процес.
- Ако сървърът например има 128 GB RAM, а процесът sqlservr.exe използва 60 GB, а SQL Server няма ограничения за RAM, тогава имате достатъчно RAM.
- Ако SQL Server използва 80-90% от набора или максималната RAM, тогава трябва да гледате DMV. Имайте предвид, че sqlservr.exe няма да може да използва цялата RAM памет. Ако сървърът има 128 GB, тогава sqlservr.exe може да използва само 80-90% (100-110 GB), тъй като останалата част от паметта е запазена за операционната система.
Имайте предвид, че процесът на SQL Server не изпраща RAM обратно към системата. Например, вашият SQL Server обикновено използва 20 GB памет, но с месечен отчет увеличава потреблението до 100 GB и дори когато изчисляването на отчета е завършено и сървърът работи в предишния режим, процесът на SQL Server все пак ще използва 100 GB, докато услугата не се рестартира..
Дори и да сте сигурни, че сървърът има достатъчно RAM памет, няма да е излишно да знаете точно количеството консумирана RAM памет..
Можете да разберете реалното използване на оперативната памет Динамични изгледи на управление. DMV са административни зрители. С DMV можете да диагностицирате почти всеки проблем в SQL Server.
Нека видим sys.dm_os_sys_memory, за удобство използваме заявката:
SELECT total_physical_memory_kb / 1024 AS [Обща физическа памет], available_physical_memory_kb / 1024 AS [Налична физическа памет], total_page_file_kb / 1024 AS [Общ файл на страницата (MB)], available_page_file_kb / 1024 AS [Файл на страницата (MB)], 100 - ( 100 * Cast (наличен_physical_memory_kb AS DECIMAL (18, 3)) / Cast (total_physical_memory_kb AS DECIMAL (18, 3))) AS „Процент използван“, system_memory_state_desc AS [Състояние на паметта] ОТ sys.dm_os_sys_memory;
Помислете за всеки изходен параметър:
- [Обща физическа памет] - количеството RAM, налично в операционната система. На някои сървъри може да покаже малко повече от реално инсталираното.
- [Налична физическа памет] - количеството RAM, налично за SQL Server, с изключение на вече заснетия SQL Server.
- [Общ файл на страница (MB)] - Обем „ограничение за ангажиране“. Задължително ограничение = RAM + всички суап файлове. Тоест, ако имате 32 GB RAM и 16 GB пейджинг файл на сървъра, ограничението за ангажиране ще бъде 48 GB.
- [Наличен файл на страница (MB)] - Размер на файла на страницата.
- Използван процент - процент на използваната RAM. В самия sys.dm_os_sys_memory няма такъв параметър, но той се изчислява по формулата available_physical_memory_kb / total_physical_memory_kb
- [Състояние на паметта] - RAM статус. Полето system_memory_state_desc съдържа състоянието на потребление на RAM под формата на текст. Стойността на това поле се счита въз основа на другите две: system_low_memory_signal_state и system_high_memory_signal_state. Можете да ги изберете директно, ако се нуждаете от булев / битов формат на данните. За всички 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 памет..
Ако трябва да се уверите, че сървърът има достатъчно RAM памет, можете да разгледате само полетата system_low_memory_signal_state, system_high_memory_signal_state и system_memory_state_desc. Ако system_low_memory_signal_state = 1, сървърът очевидно няма достатъчно RAM памет.
Използване на процесора в SQL Server
Натоварването на процесора е по-лесно да се определи, тъй като може да се направи в диспечера на задачите. За да разберете текущото натоварване на процесора, намерете процеса sqlservr.exe в диспечера на задачите
Ако искате да знаете товара за миналото време, можете да използвате заявката:
Не забравяйте да промените @lastNMin на номера, от който се нуждаете за минути.ДЕКЛАРИРАНЕ @ts BIGINT; DECLARE @lastNmin TINYINT; SET @lastNmin = 30; SELECT @ts = (SELECT cpu_ticks / (cpu_ticks / ms_ticks) ОТ sys.dm_os_sys_info); ИЗБЕРЕТЕ ТОП (@lastNmin) Dateadd (ms, -1 * (@ts - [timetamp]), 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 (' (./ Запис / SchedulerMonitorEvent / SystemHealth / ProcessUtilization) [1] ',' int ') AS [SQLProcessUtilization], [timestamp] FROM ( ИЗБЕРЕТЕ [времеви знак], CONVERT (XML, запис) КАК [запис] ОТ sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR 'И запис LIKE' %% ') AS x) AS y ORDER BY record_id DESC;
В резултат на това получаваме ежеминутна статистика за използването на процесора.
Анализ на натоварването на дискове на SQL Server
Нека да разгледаме зареждащите дискове в операционната система. За да направите това, стартирайте resmon.exe. 
Нуждаем се от раздел диск. В раздел Дискова активност Показват се файловете, до които се осъществява достъп, и скоростта на четене / запис в текущия момент. Филтрирайте този раздел по Общо (щракнете върху Общо). Най-отгоре ще бъдат файловете, които в момента се възползват максимално от устройството. В случая на SQL Server, това може да бъде полезно да се определи коя база данни най-много зарежда диска..
Разделът за съхранение показва всички дискове в системата. В този раздел се нуждаем от 2 параметъра - Активно време и дискова опашка. Активното време като процент показва натоварването на диска, тоест ако видите на диска C: \ Active Time равен на 90, това означава, че ресурсът за четене / запис на диска в момента се използва на 90%. Графата Disk Queue показва опашката за достъп до диска и ако стойността на опашката не е нула, дискът е 100% зареден и не може да се справи с натоварването. Също така, ако активното време е близо 100, тогава дискът се използва почти на границата на възможностите си по скорост.
Преглед на ключалките в SQL Server
След като се уверихме, че сървърът разполага с достатъчно ресурси, можем да пристъпим към преглед на ключалките.
Бравите могат да се видят през Activity Monitor в SSMS, но ще използваме T-SQL, тъй като тази опция е по-удобна и визуална. Изпълняваме заявката:
ЗАДЪЛЖИТЕЛНО НАЧАЛО НА ИЗБЕРЕТЕ SPID, Блокиран, ЗАМЯНА (ЗАМЯНА (T.TEXT, CHAR (10), "), CHAR (13),") КАТО ПАРТИДА В # T ОТ sys.sysproces R CROSS APPLY sys.dm_exec_sql_text (R. SQL_HANDLE) ТРЯБВАЙТЕ С БЛОКЕРИ (SPID, BLOCKED, NEVEL, BATCH) КАТО (ИЗБЕРЕТЕ SPID, BLOCKED, CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) КАТО НИВО, ПАРТИДАТА ОТ #TR ГДЕ (БЛОКИРАН = 0 ИЛИ Блокиран = SPID) И съществува (ИЗБЕРЕТЕ * ОТ # T R2 КЪДЕ R2.BLOCKED = R.SPID И R2.BLOCKED R2.SPID) UNION ВСИЧКИ ИЗБЕРЕТЕ R.SPID, R.BLOCKED, CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS NEVEL, R.BATCH ОТ #T AS R ВЪТРЕШНИТЕ СЕ СЛУЧАЙТЕ НА БЛОКЕРИТЕ НА R. BLOCKED = BLOCKERS.SPID WHERE R. BLOCKED> 0 AND R. BLOCKED R.SPID) ИЗБЕРЕТЕ N "+ REPLICATE (N '|', LEN (NEVEL) / 4 - 1) + CASE WHEN ( LEN (NEVEL) / 4 - 1) = 0 THEN 'HEAD -' ELSE '| ------' END + CAST (SPID AS NVARCHAR (10)) + N "+ BATCH AS BLOCKING_TREE ОТ BLOCKERS ПОРЪЧКА ПО НИВО ASC НАПРАВЕТЕ ТАБЛИЦА #T GO Тази заявка връща списък от ключалки под формата на дърво. Това е удобно в експлоатация, тъй като обикновено, ако се случи едно заключване, провокира други. По подобен начин в монитора на активността или в изхода на sp_who2 можете да видите полето „Блокирано от“.
Ако заявката не върна нищо, няма заключване.
Ако заявката върна някои данни, тогава трябва да анализирате веригата.
HEAD означава, че тази заявка е причината за всички други ключалки под дървото. 64 е идентификаторът на процеса (SPID). След това се записва тялото на заявката, което причини заключването. Ако имате достатъчно сървърни ресурси, най-вероятно въпросът е в самата заявка и във взаимното обжалване на някои обекти. За да бъдете по-точни, трябва да анализирате конкретната заявка, която е причинила заключването.
Политики на SQL Server
Дори когато всичко работи добре за вас и няма оплаквания, всъщност може да има много проблеми, които ще се появят по-късно. SQL Server има правила за това..
Политиката в SQL Server е, грубо казано, проверка на правило за съответствие с дадена стойност. Например, използвайки политики, можете да се уверите, че автоматичното свиване е изключено във всички бази данни на сървъра. Помислете за пример за импортиране и изпълнение на политика
В SSMS се свържете със сървъра, на който искате да изпълните политики (Управление -> раздел Управление на политиките).
Импортирайте файла Auto Shrink.xml на базата данни. Hit Оценява
Съществуват две бази данни в случай на node1, test1 и test2. Автоматичното свързване е активирано на test2. Нека видим подробностите. 
Политиката определя включения параметър AutoShrink, като в описанието обикновено са написани обясненията на правилата. В този случай се дава обяснение защо по-добре се свива автоматично свиване..
Политиките могат да се изпълняват както по график, така и при поискване (еднократно). Резултатите от изпълнението на политиката могат да бъдат намерени в дневника на политиките..
Когато инсталирате SQL Server, трябва да изберете само използваните компоненти на DBMS и да зададете настройките в съответствие с хардуерната конфигурация на вашия сървър. Винаги се уверете, че сървърът има достатъчно ресурси и че няма ключалки на сървъра
Най-мощният инструмент за диагностика на SQL Server е T-SQL и DMV. Също така се препоръчва да изградите денонощно наблюдение на SQL Server и неговата инфраструктура, за да откриете всички възможни проблеми..
