Windows проблема с часами

hi,

I have setup a new VM (VMware) with latest Windows 2019 Server Standard (as domain), Sharepoint 2019 Enterprise and SQL Server 2017 Dev.Edition. This VM is for test purposes only and I know, for production at least the SQL Server 2017 should have its own VM — but I tried it this way.

After installing SS2017 there is a high CPU consumption (50-80%) and RAM 390 MB in Windows Task Manager for

SQL Server Windows NT — 64 bit — SQL Server (MSSQLSERVER)

I found some tips regarding Memory for «server properties — RAM — maximum» which has a default value of 2147483647 and what I changed to 2000 (MB).

The host PC got 16 GB of Hardware-RAM. The VM itself got 4 GB RAM so half of it maybe used by SS2017.

But still any windows need some time to open or react on mouse click.

As this is a totally new install I’m asking myself if someone got a solution for slow reaction.

В этой статье мы рассмотрим популярные инструменты, T-SQL запросы и скрипты для обнаружения и решения различных возможных проблем с производительностью SQL Server. Эта статья поможет вам разобраться, когда вашему SQL Server недостаточно ресурсов (памяти, CPU, IOPs дисков), найти блокировки, выявить медленные запросы. Посмотрим какие есть встроенные инструменты и бесплатные сторонние скрипты и утилиты для анализа состояния Microsoft 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 неудобно хранить и смотреть данные за прошедшее время.
  

  Картинка с сайта: winitpro.ru

• Сторонние приложения — существует много платных и бесплатных приложений для мониторинга SQL Server. Например, одним из бесплатных приложений является dbForge Monitor от компании Devart. Приложение устанавливается как дополнение к SSMS и позволяет выводить очень удобный дашборд для отображения текущего состояния вашего SQL Server (информация об использовании памяти, CPU, нагрузках, блокировках, процессах, информацию о бэкапах, “тяжелых SQL запросах”, производительности дисковой подсистемы и т.д.).
  

  Картинка с сайта: winitpro.ru

• Скрипты Brentozar – это популярное решение для диагностики настроек и работоспособности SQL Server. У brentozar есть много скриптов для различных задач, но для диагностики нас интересует “sp_blitz”. Скачать можно бесплатно с официального сайта https://www.brentozar.com/blitz/. Запустите sp_Blitz.sql чтобы установить необходимые процедуры и выполните их exec sp_blitz для диагностики. Этот инструмент бесплатный и поддерживается сообществом SQL Server. Sp_blitz определит все популярные проблемы с вашим сервером и посоветует как их решить.
  

  Картинка с сайта: winitpro.ru

• Наборы 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), зайдите на сервер и зайдите в свойства сервера (ПКМ по названию сервера в Обозревателе объектов).

Картинка с сайта: winitpro.ru

Сам по себе доступный объём 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;

Рассмотрим каждый выводимый параметр:

1. [Total Physical Memory] – объём оперативной памяти доступный в операционной системе. На некоторых серверах может показывать немного больше реально установленной.
2. [Available Physical Memory] – объём оперативной памяти доступный для SQL Server, без учета уже захваченной SQL Server.
3. [Total Page File (MB)] – Объём “Сommit limit”. Commit Limit = Оперативная память + все файлы подкачки. То есть, если у вас на сервере 32 GB оперативной памяти и 16 GB файл подкачки, commit limit будет 48 GB.
4. [Available Page File (MB)] – Объём файла подкачки.
5. Percentage Used – процент занятой оперативной памяти. Такого параметра нет в самом sys.dm_os_sys_memory, но он считается по формуле available_physical_memory_kb / total_physical_memory_kb
6. [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

Картинка с сайта: winitpro.ru

Если вы хотите узнать нагрузку за прошедшее время, можно воспользоваться запросом:

Не забудьте поменять @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;

Картинка с сайта: winitpro.ru

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

Анализ нагрузки на диск SQL Server

Посмотрим на загрузку дисков в операционной системе. Для этого запустите resmon.exe.

Картинка с сайта: winitpro.ru

Нам нужна вкладка 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 #T R
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”.

Если запрос ничего не вернул, то блокировок нет.

Если запрос вернул какие-то данные, то нужно проанализировать цепочку.

Картинка с сайта: winitpro.ru

HEAD значит что этот запрос является причиной всех остальных блокировок ниже по дереву. 64 – это идентификатор процесса (SPID). После этого пишется тело запроса, который вызвал блокировку. Если у вас хватает ресурсов сервера, то скорее всего дело в самом запросе и во взаимном обращении к каким-то объектам. Для того чтобы сказать точнее, нужно анализировать конкретный запрос, который вызвал блокировку.

Политики SQL Server

Даже когда у вас всё работает хорошо и жалоб нет, на самом деле может быть много проблем, которые всплывут позже. Для этого в SQL Server есть политики.

Политика в SQL Server это, грубо говоря, проверка правила на соответствие заданному значению. Например, с помощью политик вы можете убедиться, что на всех базах на сервере выключен Auto Shrink. Рассмотрим пример импорта и выполнения политики

В SSMS, подключитесь к серверу, на котором хотите выполнять политики (Management -> раздел Policy Management).

Импортируем файл Database Auto Shrink.xml. Жмём Evaluate

Картинка с сайта: winitpro.ru

На экземпляре node1 две базы данных, test1 и test2. На test2 включен autoshrink. Посмотрим детали.

Картинка с сайта: winitpro.ru

Политика определила включенный параметр AutoShrink, в описании обычно пишется объяснения к правилам. В данном случае дается объяснение почему auto shrink лучше отключать.

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

При установке SQL Server нужно выбирать только используемые компоненты СУБД, и указывать настройки в соответствии с конфигурацией “железа” вашего сервера. Всегда следите чтобы серверу хватало ресурсов, и чтобы на сервере не было блокировок

Самым мощным инструментом для диагностики SQL Server является T-SQL и DMV. Так же рекомендуется построить круглосуточный мониторинг над SQL Server и над обслуживающей его инфраструктурой для обнаружения всех возможных проблем.

Оптимизируем Microsoft SQL Server 2014 для работы с 1С. ОС — Windows Server 2012 R2. Напишу что нужно сделать, а что не нужно.

Определяю мифичность очень просто, если мне пришлось применить настройку — реальность, не пришлось — миф.

Статья по оптимизация сервера Microsoft SQL Server 2019 для работы с 1С

Устанавливаем последний Service Pack и Cumulative Update — реальность

На текущий момент нужно обновиться до SP3.

MSSQL — устанавливаем Service Pack 3

Обязательно нужно сделать.

Выравнивание разделов — миф

Рекомендуют выравнивать сектора дисков по границе 1024Кб. Не заморачивайтесь, эта рекомендация устарела. Операционная система теперь сама выравнивает раздел как нужно для дисков более 4 ГБ.

HKLM\SYSTEM\CurrentControlSet\Services\VDS\Alignment

Картинка с сайта: internet-lab.ru

Эту настройку не применяем.

Форматирование с размером блока 64Кб

А вот здесь всё очень неявно. Есть статья Best Practice от Microsoft:

https://docs.microsoft.com/ru-ru/archive/blogs/docast/operating-system-best-practice-configurations-for-sql-server

В ней явно говорится:

Partition the Disk volumes hosting SQL databases (Data and log) with 64 KB allocation unit size:
A drive can be formatted with different sizes ranging from 512 bytes to 64K sizes with the default being 4KB (4096 bytes). This setting is also called as “Bytes Per Cluster”. The atomic unit of storage in SQL Server is a page which is 8KB in size. Extents are groups of eight 8 KB pages that are physically contiguous to each other for a total of 64 KB. SQL Server uses extents to store data. Hence, on a SQL Server machine the NTFS Allocation unit size hosting SQL database files (Including tempdb files) should be 64K.

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

Казалось бы всё ясно, 64 килобайта — наше всё. Но… Откуда ноги растут? Есть рекомендация от Microsoft с 2008 года:

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd758814(v=sql.100)

Здесь аргументированно с указанием конкретного железа, на котором производились испытания, пишут:

Note that file allocation unit (cluster) size commonly correlates with common stripe unit sizes. The performance question here is usually not one of correlation per the formula, but whether the cluster size is the NTFS default of 4,096 bytes or has been explicitly defined at 64 KB, which is a best practice for SQL Server.

Снова 64 килобайта — наше всё. Читаем дальше:

An appropriate value for most installations should be 65,536 bytes (that is, 64 KB) for partitions on which SQL Server data or log files reside. In many cases, this is the same size for Analysis Services data or log files, but there are times where 32 KB provides better performance. To determine the right size, you will need to do performance testing with your workload comparing the two different block sizes.

Получается, что SQL сервер ведёт себя лучше при размере кластера 64 килобайта, однако, в некоторых случаях, максимальной производительности удавалось достигать при размере кластера в 32 килобайта.

А теперь добавляем переменных:

• Появились новые типы массивов типа SSD и NVMe.
• Всё чаще SQL сервер устанавливается виртуально или в облаке, где файловая система виртуальной машины живёт внутри файловой системы гипервизора.
• Производители жёстких дисков и RAID контроллеров используют кеширование и дополнительные технологии ускорения работы дисков.

Ещё следует учитывать, что NTFS для разных размеров диска создаёт разные размеры блока. Стандартный размер кластера (лучше использовать это название) в файловой системе NTFS:

• ёмкость до 16 Тб — 4 килобайта
• ёмкость от 16 до 32 Тб — 8 килобайт
• ёмкость от 32 до 64 Тб — 16 килобайт
• ёмкость от 64 до 128 Тб — 32 килобайта
• ёмкость от 128 до 256 Тб — 64 килобайта

Так что если вы не хотите заморачиваться, то ставьте размер блока (кластера) при форматировании 64 килобайта. Однако не факт, что при этом ваш SQL сервер будет работать быстрее. Правильный ответ можно получить только при тестировании производительности.

Присылайте ссылки на статьи, где проводились исследования производительности для разных размеров кластера. Истина где-то там.

Database instant file initialization — частично миф, частично реальность

Рекомендуют включить возможность Database instant file initialization для пользователя, от которого запущена служба Microsoft SQL Server.

Что это за штука?

Во-первых, эта настройка влияет только на файл данных. Когда файл автоматически вырастает, то новый кусок заполняется нулями, в этот момент 1С может тормозить. Instant File Initialization (IFI) позволяет отключить это зануление.

Делается так.

1. Запускаем Local Group Policy Editor:

   gpedit.msc

2. Слева выбираем Local Computer Policy, Computer Configuration, Windows Settings, Security Settings, Local Policies, User Rights Assigment.
3. Тыкаем в Perform volume maintenance tasks.
4. Сюда добавляем юзера, от имени которого запускается SQL Server.

Однако, у меня там уже прописана группа Administrators, а пользователь, под которым работает служба SQL Server уже в этой группе.

Так что у меня ничего не пришлось настраивать. Но вы у себя проверьте.

Давайте проверим, что всё работает. Рекомендуют создать новую БД размером 5 Гб и логом 1 Мб. Вот тут тоже нужно быть внимательным, нужно создавать под тем же пользователем, от которого запускается сервис SQL. Я создам от имени другого пользователя, который тоже в группе Administrators.

Картинка с сайта: internet-lab.ru

База создалась мгновенно. На всякий случай попробую создать базу размером 50 Гб, место есть. Да, моментально на диске пропало 50 Гб и БД создалась быстро.

Вывод:

Instant File Initialization (IFI) работает из коробки, если пользователь, под которым работает сервис SQL входит в группу Administrators сервера.

Эту настройку применяем только при необходимости.

Lock pages in memory — реальность

Рекомендуют установить разрешение на Lock pages in memory (блокировку страниц в памяти) для пользователя, от которого запущена служба Microsoft SQL Server. Эта политика Windows определяет, какие учетные записи могут сохранять данные в физической памяти, чтобы система не отправляла страницы данных в виртуальную память на диске.

1. Запускаем Local Group Policy Editor:

   gpedit.msc

2. Слева выбираем Local Computer Policy, Computer Configuration, Windows Settings, Security Settings, Local Policies, User Rights Assignment.
3. Тыкаем в Lock pages in memory.
   

   Картинка с сайта: internet-lab.ru

4. Видим, что политика не настроена. Можно добавить сюда пользователя, от имени которого работает SQL Server, я просто добавляю сюда группу Administrators.

Эту настройку применяем. Нужно учитывать, что потребление оперативки возрастёт.

Power Option: High performance — реальность

Да, это реальность. По умолчанию план электропитания в Windows Server 2012 R2 — Balanced.

https://support.microsoft.com/en-au/help/2207548/slow-performance-on-windows-server-when-using-the-balanced-power-plan

Картинка с сайта: internet-lab.ru

В некоторых случаях, Microsoft не уточняет в каких, на сервере может из-за этого снижаться производительность. Переключаем план на High performance.

Картинка с сайта: internet-lab.ru

Эту настройку применяем.

Сжатие содержимого — миф

Картинка с сайта: internet-lab.ru

Рекомендуют проверить, что галка, отмеченная на картинке выше, снята. Имеется в виду папка, где лежат файлы БД. По умолчанию она снята — рекомендация миф.

Индексация файлов — миф

По умолчанию галка Allow files in this folder to have contents indexed in addition to the file properties стоит:

Казалось бы, надо снимать. Подумаем, эта галка указывает службе индексации, что содержимое нужно обработать. Зачем на сервере индексировать содержимое? А стоит ли вообще служба индексации по умолчанию? Проверяем:

Картинка с сайта: internet-lab.ru

А служба поиска не установлена! Ну и забиваем на все эти галки. Эту настройку не применяем.

Исключить файлы БД из систем резервного копирования — миф

Это классика, не нужно ничего исключать, нужно просто нормально резервирование делать.

Во-первых, не отделяйте мух от котлет. Не держите приложение 1С и SQL Server на одном сервере. MSSQL должен жить отдельно. И резервироваться он должен средствами MSSQL. И зеркалироваться.

Эту настройку не применяем.

Резервирование и обслуживание — реальность

Ссылки:

Maintenance Plans — резервное копирование и обслуживание баз данных в Microsoft SQL Server 2014

Настройка зеркалирования в Microsoft SQL Server 2014

Эту настройку применяем.

Настройка памяти — реальность

Да, память для MSSQL нужно настраивать. По умолчанию сиквел жрёт всё что может, системе начинает не хватать. Как итог — тормоза. Нужно выделить SQL серверу строго определённое количество памяти и пусть не рыпается.

Повторюсь:

Не держите приложение 1С и SQL Server на одном сервере. MSSQL должен жить отдельно.

Это позволить избежать конкуренции за ресурсы.

MSSQL — настройка памяти

Эту настройку применяем.

Настройка процессора — реальность

Настроим проц.

MSSQL — настройка процессора

Эту настройку применяем.

Расположение файлов данных — реальность

Файлы данных и файлы журналов транзакций желательно размещать на разных дисковых массивах. Если один из массивов быстрее, то лог нужно разместить там.

Картинка с сайта: internet-lab.ru

Эту настройку по возможности применяем.

Разбить tempdb на несколько файлов по количеству процессоров — миф

Миф обыкновенный:

A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core

Эту настройку не применяем.

Перенести tempdb в RAM — реальность

Сам протестировал, добился 10-кратного прироста скорости чтения-запись для tempdb.

MSSQL — переносим tempdb на RAM диск

Эту настройку по возможности применяем.

Установить параметр Max degree of parallelism = 1 — частично миф, частично реальность

В закладке Advanced есть возможность установить параметр Max degree of parallelism. Кто-то рекомендует ставить 1, чтобы запретить параллельные запросы. Смысл в том, что один толстый запрос может сожрать все процессоры и помешать другим пользователям. С другой стороны, запущенная на ночь обработка могла бы выполняться быстрее при использовании нескольких процессоров. Давайте поступим иначе — ограничим половиной процессоров. У меня их 8, ставлю 4.

https://docs.microsoft.com/ru-ru/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-2014

Эту настройку применяем только при необходимости и в зависимости от количества процессоров.

Прирост файлов БД — реальность

Меняем дефолтные настройки прироста размера файлов данных и лога.

По умолчанию прирост 10%, это никуда не годится. Если база 500 гигабайт, то одномоментно отобранные 50 Гб места могут негативно сказаться на работе.

Ставим прирост 200 Мб.

Картинка с сайта: internet-lab.ru

Эту настройку применяем.

Флаги трассировки — реальность

4199 — включает исправления оптимизатора из фиксов.

https://blogs.msdn.microsoft.com/psssql/2015/06/16/identifying-sql-server-2014-new-cardinality-estimator-issues-and-service-pack-1-improvement/

1118 — использовать разные страницы памяти.

Запускаем SQL Server Configuration Manager. SQL Server Services. SQL Server — правой кнопкой свойства. Вкладка Startup Parameters. Добавляем -T1118 и -T4199.

Картинка с сайта: internet-lab.ru

Для применения потребуется перезапустить SQL сервер.

Эту настройку применяем.

Сетевые протоколы — реальность

Протокол «Named pipes» необходимо отключить.

Запускаем SQL Server Configuration Manager. SQL Server Network Connfiguration. Protocols for MSSQLSERVER (наш инстанс). Делаем Named Pipes — Disabled.

Картинка с сайта: internet-lab.ru

Эту настройку применяем.

Отключение DFSS — миф

DFSS по умолчанию устанавливается на терминальный сервер. При чём тут SQL? Отключать нечего.

Эту настройку не применяем.

Как понять что проблема именно в SQL Server — Заходим в Диспетчер задач, на вкладке Подробности находим sqlserver и смотрим колонку ЦП.

Если это значение постонно высокое, то значит где-то идет утечка CPU. 

В этом руководстве мы собрали различные советы как решать подобную проблему 

Поиск проблемных мест в SQL Server по CPU

1. Cмотрим счетчики perfmon 

Определяем проблема в Kernel или User запросах. 

В perfmon смотрим следующие параметры: 

• Processor: % Privileged Time – Percentage of time processor spends on execution of Microsoft Windows kernel commands such as OS activity. (If more than 30% involve Windows Admins)
• Process (sqlservr): % Privileged Time – the sum of processor time on each processor for all threads of the process (SQL Kernel)
•  Processor: % User Time – percentage of time the processor spends on executing user processes such as SQL Server. This includes I/O requests from SQL Server

Если это значение  % Privileged Time / No of logical cpus больше 30%, то скорее всего дело в системных настройках, возможно антивирус. 

Используя данную инструкцию вы можете найти проблемные spID, которые в данный момент загружают процессор — https://www.mssqltips.com/sqlservertip/2454/how-to-find-out-how-much-cpu-a-sql-server-process-is-really-using/

2. Ищем проблемные процессы

SELECT * FROM sys.sysprocesses
WHERE cmd like 'LAZY WRITER' or cmd like '%Ghost%' or cmd like 'RESOURCE MONITOR'

альтернативный вариант: 

SELECT top 20 spid, kpid, dbid, cpu, memusage FROM sysprocesses
order by cpu desc 

spID с 1 до 50 — это системные. Мы можем отключать (kill spID) или смотреть запрос только для пользовательских (spID>50). 

Также пробуем использовать  хранимки exec sp_who, sp_who1, sp_who2, sp_who3 — они позволяют посмотреть все процессы и их текущее состояние. 

По spid можно найти этот запрос: 

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 78
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

Альтернативно вы можете посмотреть последний запрос, выполняющийся в рамках этого spID: 

DBCC INPUTBUFFER(60)
GO
SELECT @@SPID  -- получить SPID текущего процесса
GO

А также можно убить процесс через kill spID. Убили процесс — и посмотрели как это сказалось на загрузке. 

3. Выявление проблем через спец запросы SQL

Также попробуйте выполнить следующие запросы для поиска проблемных мест по CPU 

SELECT GETDATE() AS "RunTime", st.text AS batch, SUBSTRING(st.text,statement_start_offset / 2+1
,((CASE WHEN a.statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),st.text)) * 2)
ELSE a.statement_end_offset END)  - a.statement_start_offset) / 2+1)  AS current_statement
, qp.query_plan, a.*
FROM sys.dm_exec_requests a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) AS qp
ORDER BY CPU_time DESC

Еще один скрипт для поиска проблемных запросов по CPU:

/*
Disclaimer: I am not sure for the origin of this script/query.
This query is used in our team to identify and resolve high CPU issue

*/
--define the temptables that will hold intermediary results
IF OBJECT_ID('tempdb..#dbcc') IS NOT NULL
    DROP TABLE #dbcc

create table #dbcc(c1 varchar(15), c2 int, c3 varchar(255),spid int default 0)

IF OBJECT_ID('tempdb..#cpugroups') IS NOT NULL
    DROP TABLE #cpugroups

create table #cpugroups (sql_handle binary(20), sql_text nvarchar(50),total_cpu bigint,total_io bigint,total_sessions int, total_threads int)


--take the SPID groups that are running same code (NOT statement)
insert into #cpugroups
select top 10 sql_handle,substring((select text from fn_get_sql(sql_handle)),1,50), SUM(CPU) TotalCPUForGroup, SUM(physical_io) TotalIOForGroup, COUNT(distinct spid) TotalNoOfSessions,COUNT(*) TotalNoOfThreads
from master..sysprocesses (nolock)
where spid>50 and status<>'sleeping'
and sql_handle<>0x0 and spid<>@@spid
group by sql_handle
order by TotalCPUForGroup desc


declare @sql nvarchar(max)
declare @t table (spid int)

INSERT INTO @t
SELECT DISTINCT spid FROM master..sysprocesses WHERE spid>50 and sql_handle in (select sql_handle from #cpugroups)


declare @spid int


WHILE EXISTS(select * from @t)
BEGIN
  select top 1 @spid=spid from @t
  set @sql='dbcc inputbuffer('+LTRIM(STR(@spid))+')'

  --try to retrieve the original command for all SPIDs
  BEGIN TRY
    INSERT INTO #dbcc(c1, c2, c3)
    EXEC (@sql)

    update #dbcc
    set spid=@spid
    where spid=0

  END TRY
  BEGIN CATCH
  END CATCH


  delete from @t where spid=@spid

END


select * from #cpugroups
select c3 [sql_text], count(*) NoOfSessionsRunning from #dbcc group by c3 order by 2 desc
select * from #dbcc

Для найденных элементов можно удалить план в кеше (подставив sql_handle):

DBCC FREEPROCCACHE (plan_handle_id_goes_here)

Еще 1 запрос на поиск проблем по CPU:

SELECT
	r.session_id
	,st.TEXT AS batch_text
	,SUBSTRING(st.TEXT, statement_start_offset / 2 + 1, (
			(
				CASE
					WHEN r.statement_end_offset = - 1
						THEN (LEN(CONVERT(NVARCHAR(max), st.TEXT)) * 2)
					ELSE r.statement_end_offset
					END
				) - r.statement_start_offset
			) / 2 + 1) AS statement_text
	,qp.query_plan AS 'XML Plan'
	,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
ORDER BY cpu_time DESC

И еще один: 

SELECT s.session_id,
r.status,
r.blocking_session_id 'Blk by',
r.wait_type,
wait_resource,
r.wait_time / (1000 * 60) 'Wait M',
r.cpu_time,
r.logical_reads,
r.reads,
r.writes,
r.total_elapsed_time / (1000 * 60) 'Elaps M',
Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1) AS statement_text,
Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time desc

Также посмотрите правой кнопкой на Сервере > reports> Standard reports > Top CPU queries.

4. Анализ найденных проблемных запросов 

В найденных запросах посмотрите execution plan и посмотрите где наибольший cost. 

Дополнительные рекомендации: 

• проверьте что стоят все необходимые индексы на таблицах, которые участвуют в проблемных запросах. 
• постарайтесь как можно «быстрее» в where обрезать данные для уменьшения выборки для последующей обработки. Т.е. старайтесь не допускать сканирования по очень большой таблице без использования какой либо фильтрации (либо эта фильтрация содержит затратную логику проверки условий)
• проверьте, что у вас нет затратных конвертаций типов 
• если используете множественно функции, проверьте что они используют минимум извлечений внутри.
• Вариант — поставить обновление https://support.microsoft.com/ru-ru/help/3195888/fix-high-cpu-usage-causes-performance-issues-in-sql-server-2016-and-20

    select * from sys. dm_os_spinlock_stats -- провериить что нет больших чисел у  SECURITY_CACHE и CMED_HASH_SET -  если есть  то установить обновления

• https://docs.microsoft.com/ru-ru/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-plan-transact-sql?view=sql-server-ver15 находить тяжелые процессы и смотреть их планы запросов
• Как посмотреть запрос по sql handle или plan handle — https://docs.microsoft.com/ru-ru/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sql-text-transact-sql?view=sql-server-ver15

Источники и что почитать по теме утечек CPU 

• https://support.microsoft.com/ru-ru/help/2009160/high-cpu-use-occurs-in-your-queries-on-sql-server
• https://blog.sqlauthority.com/2018/03/23/sql-server-how-to-fix-high-cpu-consumption-on-sql-server-2017-and-2016/
• https://www.c-sharpcorner.com/blogs/difference-between-spwho-spwho2
• https://docs.microsoft.com/ru-ru/archive/blogs/docast/sql-high-cpu-troubleshooting-checklist очень подробная статья.
• https://www.mssqltips.com/sqlservertip/2454/how-to-find-out-how-much-cpu-a-sql-server-process-is-really-using/
• https://www.youtube.com/watch?v=98c8spD5k5s

Альтернативная документация по поиску CPU проблем SQL Server

Что проверить в первую очередь:

• Конфигурация железа сервера.
• Дисковая подсистема.
• Свободное место.
• Антивирус на сервере стоит?
• БД (операции) не выполняются / выполняются, как часто.
• Проверка целостности
• Индексы перестройка
• Обновление статистики
• Сжатие (шринк)

Общие рекомендации

 https://sqlcom.ru/optimization_query/sql-server-performance-problems-after-moved-to-new-server/

Электропитания — использовать «Высокая производительность»

настройка кэширования записи на диск

антивирус, —  добавить папку SQL Server и файлов БД в исключения

настройка настроены параметры параллелизма (cost threshold for parallelism, max degree of parallelism)

настройка Hyper-Threading. 

Мониторинг  SSMS — «Стандартные отчеты»

 «Стандартные отчеты» в пользовательском интерфейсе Management Studio

SQL Server Management Studio предоставляет минимальный необходимый набор стандартных отчетов для получения информации в режиме пользовательского интерфейса.

Доступ к этим отчетам может быть выполнен через «Обозреватель объектов» (Object explorer) → Правый клик мыши по базе данных → «Отчеты» (Reports) → «Стандартный отчет» (Standard reports)

Перечень «Стандартные отчеты»:

Перечень «Стандартные отчеты»:

• Занято место на диске
• Использование дисковой памяти верхними таблицами
• Использование дисковой памяти таблицей
• Использование дисковой памяти секцией
• События резервного копирования и восстановления
• Все транзакции
• Все блокирующие транзакции
• Самые продолжительные транзакции
• Транзакции, блокирующие наибольшее кол-во транзакций при выполнении
• Транзакции с наибольшим кол-вом блокировок
• Статистика блокировки ресурсов по объектам
• Статистика выполнения объектов
• Журнал согласованности баз данных
• Статистика использования индекса
• Физическая статистика индекса
• Журнал изменений схемы
• Статистика пользователей
• Перечень «Пользовательские отчеты» 

Мониторинг  Activity Monitor — Монитор активности

Открыть монитор активности CTRL+ALT+A или SSMS стандарт. панель инструментов значок.

Монитор активности SQL Server 2008 объединяет данные о процессах, предоставляя наглядную информацию по выполняющимся и недавно выполнявшимся процессам.

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

Мониторинг  Reporting Services — Performance Dashboard Reports

Для наблюдения за SQL Server есть интересный пакет отчетов Reporting Services, называется он SQL Server Performance Dashboard Reports.

https://sqlcom.ru/dba-tools/sql-server-dashboard-reports/

Можноскачать

The SQL Server 2012 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SQL Server Management Studio.

Вопрос – используется ли Reporting Services?

Мониторинг  (платный)

https://sqlcom.ru/monitor/microsoft-sql-server-monitoring-solution/

(платный) от разных компаний:

    Idera — SQL Diagnostic Manager

    Red-Gate — SQL Monitor

    ApexSQL — ApexSQL Monitor

    Quest — Spotlight on SQL Server Enterprise

    SentryOne — SQL centry

Так же — вариант мониторинга SQL Server на Zabbix.

СКРИПТЫ — системные

sp_who и sp_who2

найти блокирующие и ожидающие запросы

1 — 100_1_sys.dm_exec_query_stats — most time cpu

— 100_1_sys.dm_exec_query_stats_Which Queries are taking the most time cpu to execute

sys.dm_exec_query_stats     OUTER APPLY sys.dm_exec_query_pl

3 — 100_3_sys.dm_exec_query_stats cpu-utilization

100_3_sys.dm_exec_query_stats_sql-server-cpu-utilization-io-usage-and-memory-usage.sql

sys.dm_exec_query_stats  CROSS APPLY  sys.dm_exec_plan_attributes

Результат имеет вид.

4 — 100_4_sys.dm_exec_query_stats_Тяжелые запросы

— 100_4_sys.dm_exec_query_stats_Тяжелые запросы

— Скрипт основан на представлении sys.dm_exec_query_stats:

5 — 100_5_sys.dm_tran_locks_заблокированные запросы

Быстрый способ найти заблокированные запросы

— 100_5_sys.dm_tran_locks_заблокированные запросы

— мы должны убить blocking_session_id.

— Изучите столбец BlockingText

—  Мы можем убить сессию с помощью KILL 52

6 — 100_6_Какой процессор чем загружен

— 100_6_Какой процессор чем загружен

7 — 100_7_Информация о пользователях и подключения.sql

— 100_7_Информация о пользователях и подключения.sql

— представлениям: sys.dm_exec_connections, master.sys.sysprocesses, sys.dm_exec_sessions

СКРИПТЫ — sp_WhoIsActive

Вызов:

200_2_sp_WhoIsActive_Параметры.sql

Источник:

https://whoisactive.com/

sp_whoisactive is a comprehensive activity monitoring stored procedure that works for all versions of SQL Server from 2005 through 2017.

«Who Is Active» показывает только текущую активность сервера ( кто и что вызывает нагрузку в данный момент времени.). Можно использовать для сбора и последующего анализа данных.

Внутри используется 15 DMV.

Много параметров.

                               @show_sleeping_spids = 2,  -- Показать спящие сессии

                               @show_system_spids = 1, -- Показать системные сессии

                               @show_own_spid = 1  --  Показать вашу собственную сессию

                               , @get_full_inner_text = 1 -- видеть всю активность,

                               , @get_outer_command = 1 -- что вызвало этот [sql_text]

                               , @get_task_info = 1 -- Вывести в столбец [wait_info] не только самое важное ожидание, но и все остальные:

                               , @get_transaction_info = 1 --9. Можно так же убрать агрегацию транзакций одной сессии и вывести их по отдельности:

                               , @get_additional_info = 1 --10. Вывести более детальную информацию. Будет добавлен столбец [additional_info] с информацией в формате XML

                               -- , @find_block_leaders = 1 -- количество заблокированных процессов каждой сессией:

                               , @sort_order = '[CPU] DESC' -- сортировку вывода

СКРИПТЫ — sp_Blitz

Нужно ставить внешнюю процедуру. Можно ставить в любую БД.

Источник:

https://www.brentozar.com/askbrent/

https://www.brentozar.com/first-aid/

Быстрая проверка вашего SQL Server (sp_Blitz)

Как работает:

1. Вы устанавливаете процедуру
2. Запускаете когда вам удобно

Обычно процедура выполняется от 5 до 15 секунд. Скрипт максимально проработан чтобы не препятствовать работе других пользователей, но вам всё равно лучше проверить его работу на тестовом сервере

--dbo.sp_Blitz -- общие параметры
--sp_BlitzFirst - "Why is my SQL Server slow right now?"
--sp_BlitzCache - "What are the most resource-intensive queries on this server?"
--sp_BlitzIndex - "How could I tune indexes to make this database faster?"
--sp_BlitzQueryStore - "How has this query performed over time?"
--Sorry, sp_BlitzQueryStore doesn't work on versions of SQL prior to 2016, or Azure Database compatibility < 130.
--sp_BlitzWho --Who’s running what queries right now?

SQL Profiler

SQL Profiler  —  используйте, где нужно быстро посмотреть, что там за запрос.

SQL Profiler — остается одним из самых используемых инструментов для диагностики работы SQL Server, несмотря на то, что считается устаревшим. Может быть удален в будущих версиях СУБД, является графической надстройкой для SQL Trace . Как и SQL Profiler, SQL Trace считается устаревшим инструментом и может быть удален в будущем.  SQL Trace находится в режиме поддержки и не дополняется новым функционалом.

Кол-во счетчиков (событий) = 180.

Extended Events

В SSMS в разделе «Управление -> Расширенные события -> Сеансы» Вы можете найти список всех сеансов расширенных событий.

This article describes the steps to troubleshoot high CPU utilization in SQL Server. If you are experiencing high CPU utilization in SQL Server, you can implement the steps mentioned below to troubleshoot the issue.

Suppose your monitoring system has informed you that the CPU utilization on your SQL Server is at 90% or 100%, and you’re left wondering what to do. If this sounds familiar, don’t worry. This blog post helps walk you through finding and solving the problem by providing steps on how to resolve SQL Server high CPU performance.

The common reasons for high CPU in SQL Server are as below.

The first step is to identify whether a high CPU on the server is caused by the SQL Server process or any other process.

Method 1: Task Manager

You can RDP to the SQL server and launch the task manager. On the process tab of task manager, check the high CPU-consuming process.

As shown below, the SQL Server Windows NT – 64 Bit consumes most of the CPU resources.

Method 2: Ring Buffer

As highlighted below, SQL Server is consuming most of the CPU resources.

Method 3: PowerShell

The following PowerShell script collects the counter data every 60 seconds.

cls
$serverName = "WindowsSQL" #enterserver name
    $Counters = @(
     ("\\$serverName" +"\Process(sqlservr*)\% User Time"),
     ("\\$serverName" +"\Process(sqlservr*)\% Privileged Time")
    )
    Get-Counter -Counter $Counters -MaxSamples 30 | ForEach {
       $_.CounterSamples | ForEach {
           [pscustomobject]@{
               TimeStamp = $_.TimeStamp
               Path = $_.Path
               Value = ([Math]::Round($_.CookedValue, 3))
            } 
           Start-Sleep -s 2
        }
    }

My SQL Server has a high %user time value that shows SQL Server consuming high CPU.

Method 4: Performance Monitor Counters

You can also use perfmon with the following counters to check the CPU utilization of SQL Server and other processes.

Counter: Process/%User Time, % Privileged Time

Instance: sqlservr

Once you have identified that the Sqlservr process is causing high CPU usage, we need to identify the queries responsible for it.

Method 1: SP_WhoISActive stored procedure:

We are using Adam Machanic‘s comprehensive activity monitoring stored procedure to monitor currently running processes. Here, you get the following valuable columns.

If you run the SP with the additional parameter @get_plans=1, it gives the execution plan of the query as well.

Exec sp_whoisactive @get_plans=1

Method 2: Dynamic Management Views

Once we have identified the problematic query, you can run the updated statistics for the involved tables. To update the statistics, you can use the UPDATE STATISTICS command.

For example, the following query performs update statistics for all indexes on [Sales].[SalesOrderDetail] table.

UPDATE STATISTICS Sales.SalesOrderDetail;  
GO

You often run the Update Statistics for the larger tables, but SQL Server takes only a small sampling percent. In this case, you can check the sample percent with the following script.

SELECT stats.name AS StatisticsName,
OBJECT_SCHEMA_NAME(stats.object_id) AS SchemaName,
OBJECT_NAME(stats.object_id) AS TableName,
[rows] AS [Rows],
last_updated AS LastUpdated, 
rows_sampled as [NumberofSamplingrows], modification_counter,
(rows_sampled * 100)/rows AS SamplePercent,
persisted_sample_percent PersistedSamplePercent
FROM sys.stats
INNER JOIN sys.stats_columns sc
ON stats.stats_id = sc.stats_id AND stats.object_id = sc.object_id
INNER JOIN sys.all_columns ac
ON ac.column_id = sc.column_id AND ac.object_id = sc.object_id
CROSS APPLY sys.dm_db_stats_properties(stats.object_id, stats.stats_id) dsp
WHERE OBJECT_NAME(stats.object_id)='EmployeeDepartmentHistory'

Look at the following columns in the output

o Rows: Total number of rows in the table

o NumberofSampleRows: Number of sampling rows

o SamplePercent

o Modification Counter: Number of updates performed after the last stats update

The following DemoTable has a 49% sampling percent for the total 421201 rows.

Therefore, you can run the UPDATE STATISTICS WITH FULL SCAN to update statistics with 100% sampling percent. It helps SQL Server to build a cost-optimized execution plan.

If you are still getting high CPU utilization for SQL Server, you can evaluate the missing index recommendations. The following query lists high CPU queries with at least one missing index warning in the execution plan.

We do not need to create all indexes recommended by SQL Server. It does not consider the existing indexes.

The following query generates the index creation script, and you can consider the indexes with the highest improvement measure. Always create indexes in a non-prod database and see if it is helpful in the production environment.

If you log heavily for XEvent or SQL Profiler trace, it might cause CPU issues. The following query lists all active XEvent or Server traces.

Review these active XEvent or Server traces and see if you need them. Stop any unwanted running traces.

Sometimes, the parameter-sensitive stored procedure or the query might cause high CPU usage. You can do the following to investigate these issues.

SELECT text, 'DBCC FREEPROCCACHE (0x' + CONVERT(VARCHAR (512), plan_handle, 2) + ')' AS dbcc_freeproc_command FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text LIKE '%person.person%'

You might need to scale up to SQL Server resources by adding more CPUs based on the workload requirement. The following query helps find the number of queries that have exceeded a certain threshold of average and maximum CPU consumption per execution(exceeds 200 ms) and have run many times(1000 times) on the system.

DECLARE @cputime_threshold_microsec INT = 200*1000
DECLARE @execution_count INT = 1000


SELECT 
    qs.total_worker_time/1000 total_cpu_time_ms,
      qs.max_worker_time/1000 max_cpu_time_ms,
      (qs.total_worker_time/1000)/execution_count average_cpu_time_ms,
      qs.execution_count,
     q.[text]
FROM
  sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
WHERE (qs.total_worker_time/execution_count > @cputime_threshold_microsec  
       OR        qs.max_worker_time > @cputime_threshold_microsec )
       AND execution_count > @execution_count
ORDER BY qs.total_worker_time DESC 
OPTION (RECOMPILE)

In this article we have discussed some of the common SQL Server high CPU issues. We also discussed how to identify and troubleshoot the SQL Server high CPU issues. If you are facing high CPU issues in SQL Server, then this article will be helpful for you.

,