Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Лохматим локами бодрого старичка из 2000-ых.  [new]
Kaktus_
Member

Откуда: Ростов-на-Дону
Сообщений: 314
Прошу помощи в странной ситуации.
Имеем бодрого старичка:

Microsoft SQL Server 2000 - 8.00.2066 (Intel X86) May 11 2012 18:41:14 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
(Соответственно это SQL Server 2000 Enterprise 32bit на 64битном Windows 2003 Server Enterprise).

Физической памяти в сервере 64 Гб.
sp_configure
max server memory (MB) 62000
min server memory (MB) 40000

Счетчик ТоталСерверМемори также показывает выделенные 62 Гб памяти.
DBCC MEMORYSTATUS
в разделе Buffer Counts / Commited показывается 7936000 буфферов

Другими словами сервер реально оперирует 62 Гбайтами оперативной памяти.

В связи с часто появляющимися ошибками
"The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.."

Пытаемся установить количество Lockов вручную через параметр locks через sp_configure.
Ставим туда 50000000 (50 млн) это по 96 байт на каждый lock всего 4,6 Гигабайта.

Получаем в логе при загрузке сервера следующие два сообщения:
"Can't allocate 50000000 locks on startup, reverting to 2774698, (25% of committed memory)"
"Using static lock allocation. [2774698] Lock Blocks, [2774698] Lock Owner Blocks."

Получается, что 2.774.698*96/1024/1024=254 мегабайта. Т.е. у меня вместо 62 Гб он считает что есть всего гигабайт. Почему?!
Очень прошу помощи.

Если чем-то поможет развернутый DBCC MEMORYSTATUS - вот оно.
Buffer Distribution Buffers
------------------------------ -----------
Stolen 35433
Free 3716
Procedures 60189
Inram 0
Dirty 102119
Kept 0
I/O 0
Latched 2205
Other 7732338

(9 row(s) affected)

Buffer Counts Buffers
------------------------------ -----------
Commited 7936000
Target 7936000
Hashed 7836657
InternalReservation 2776
ExternalReservation 166
Min Free 1936
Visible 130064

(7 row(s) affected)

Procedure Cache Value
------------------------------ -----------
TotalProcs 8471
TotalPages 60189
InUsePages 34191

(3 row(s) affected)

Dynamic Memory Manager Buffers
------------------------------ -----------
Stolen 95593
OS Reserved 10544
OS Committed 10522
OS In Use 9637
General 4135
QueryPlan 66379
Optimizer 0
Utilities 271
Connection 1527

(9 row(s) affected)


Global Memory Objects Buffers
------------------------------ -----------
Resource 2666
Locks 33133
XDES 237
SQLCache 1116
Replication 2
LockBytes 2
ServerGlobal 36

(7 row(s) affected)


Query Memory Objects Value
------------------------------ -----------
Grants 1
Waiting 0
Available (Buffers) 45208
Maximum (Buffers) 45408

(4 row(s) affected)

Optimization Queue Value
------------------------------ -----------
Optimizing 0
Waiting 0
Available 64
Maximum 64

(4 row(s) affected)
15 ноя 18, 11:44    [21734979]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4534
Kaktus_, в башке крутится PAE/AWE
15 ноя 18, 12:03    [21735005]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
Kaktus_
Member

Откуда: Ростов-на-Дону
Сообщений: 314
buser
Kaktus_, в башке крутится PAE/AWE


Хм. Сейчас поковыряюсь. Сейчас boot.ini пустой. Там только

[boot loader]
timeout=5
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, x64 Enterprise RU" /noexecute=optout /fastdetect
15 ноя 18, 12:13    [21735021]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4534
Kaktus_, дождитесь кого кто собаку на этом съел... How to configure SQL Server to use more than 2 GB of physical memory
15 ноя 18, 12:33    [21735058]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
f000
Member

Откуда: 61
Сообщений: 48
я конечно может сейчас чушь скажу, но может это относится к стэку?
тогда только 1Гб без вариантов
15 ноя 18, 13:19    [21735125]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
f000
Member

Откуда: 61
Сообщений: 48
а вообще такое ощущение, что 2Гб памяти не хватает под ось и внутреннюю память SQL
62Гб это только буфер пул и иже с ним, манагер памяти сильно меняли после 2008
моя рекомендация - попробовать уменьшить max server memory до 58Гб
15 ноя 18, 13:33    [21735146]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
Kaktus_
Member

Откуда: Ростов-на-Дону
Сообщений: 314
f000
а вообще такое ощущение, что 2Гб памяти не хватает под ось и внутреннюю память SQL
62Гб это только буфер пул и иже с ним, манагер памяти сильно меняли после 2008
моя рекомендация - попробовать уменьшить max server memory до 58Гб


К сожалению не помогло.
Уменьшили только что на 10 Гб. Увидели в sp_configure результат. Увидели в обычном Task Manager, что ОС стала видеть 12 свободных гигов памяти, но количество локов и выделенной памяти под локи не изменилось.
Для интереса поигрались и нижней границей min server memory - тоже не помогло.
15 ноя 18, 13:40    [21735161]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7383
Kaktus_,

могу предположить, что by design. Раньше и цифр таких для памяти не было.
15 ноя 18, 13:48    [21735170]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Kaktus_,

Попробуйте просто в locks 0 ставить, ни и попробовать AWE true.
15 ноя 18, 14:03    [21735191]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
Kaktus_
Member

Откуда: Ростов-на-Дону
Сообщений: 314
Поставили 0 в locks. Перезапустили.
Написало в логах при загрузке

"Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks."

Сижу смотрю на счетчики в профайлере пока минут 15 и локов нет. Но это только "пока".

Lock Memory (KB) раньше была одной полоской на 260 Мб, а теперь она болтается около тех же 260-ти и выше не поднимается.
Аналогичная ситуация с Lock Block Allocated. Она была одной полоской в 2.7 млн выделенных локов, а теперь болтается, но не поднимается выше 2.7 млн.

Жду когда пользователи начнут работать интенсивнее чтобы пробить эти показатели.
15 ноя 18, 15:28    [21735327]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Kaktus_,

ну и вообще стоит смотреть какие объекты у вас требуют столько блокировок и разбирать почему. Эскалация отключена на них?
15 ноя 18, 15:33    [21735338]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
Kaktus_
Member

Откуда: Ростов-на-Дону
Сообщений: 314
Попробую описать то, что сейчас произошло у меня на экране.
Количество блокировок подскочило до 9 миллионов увлекая за собой потолок Allocation и увеличивая память под локи.
Lock Allocation поднялся до 6 млн. Память увеличилась почти до 500 мбайт. Могу с цифрами приврать, т.к. наблюдал в динамике. Правда ошибка всеравно возникла. Но одна.
Блокировки упали быстро.
Lock Allocation после спада блокировок постепенно минуты за две вернулся к своим любимым 2.7 млн, а память под блокировки к своим любимым 260 мбайтам.
Наблюдаю.
15 ноя 18, 15:39    [21735349]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
Kaktus_
Member

Откуда: Ростов-на-Дону
Сообщений: 314
TaPaK
Kaktus_,

ну и вообще стоит смотреть какие объекты у вас требуют столько блокировок и разбирать почему. Эскалация отключена на них?


На самом деле я не настолько профессионально владею знаниями для анализа. Сижу читаю мануалы.
Насколько я понимаю - мне необходимо подловить exec sp_lock в тот момент, когда много блокировок. И в полученной выборке попытаться посмотреть что за объекты. Это я могу.
Пытался пробовать трассировать эти счетчики, но при таком количестве записей я не могу понять как там можно что-то реально разобрать.

Что имеется ввиду под эскалацией блокировок в объектах пока не очень понимаю - сейчас почитаю. Теорию я понимаю, но не очень понял про отключение.
15 ноя 18, 15:43    [21735357]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
Kaktus_
Member

Откуда: Ростов-на-Дону
Сообщений: 314
Lock escalation thresholds are determined dynamically by SQL Server and do not require configuration.
15 ноя 18, 16:10    [21735380]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Kaktus_,

| ALLOW_ROW_LOCKS = { ON | OFF}
| ALLOW_PAGE_LOCKS ={ ON | OFF}
15 ноя 18, 16:18    [21735390]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36686
TaPaK
Kaktus_,

| ALLOW_ROW_LOCKS = { ON | OFF}
| ALLOW_PAGE_LOCKS ={ ON | OFF}
не было этого в 8.0 сервере.

И да. Неплохо бы на сервер последний сервис-пак накатить.
15 ноя 18, 16:45    [21735418]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
step_ks
Member

Откуда:
Сообщений: 936
Kaktus_, pagefile в винде, случаем не отключен? Или не выставлен ли в какие минимальные значения?
15 ноя 18, 17:16    [21735452]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
Kaktus_
Member

Откуда: Ростов-на-Дону
Сообщений: 314
step_ks,

Файл подкачки живой.
Лежит на диске C:. Кстати не самый быстрый диск.
Исходный размер 65 Гб.
Максимальный размер 70 Гб.
Свободно на диске С - 119 Гб.

Ниже рекомендовано почему-то 98 Гб. Завтра попробуем это тоже потестировать. Или поставить по выбору системы.
15 ноя 18, 18:09    [21735525]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
Kaktus_
Member

Откуда: Ростов-на-Дону
Сообщений: 314
Промежуточный итог.
Насколько Вы поняли выше - потолок в 2.7 млн локов и 260 мб памяти под локи убирается с помощью выставления 0 на locks в sp_configure - т.е. включение динамического варианта.

В этом варианте потолок максимальный который мне удалось увидеть - это 6.6 млн локов и 634 мб памяти. Этот потолок не пробивается. Все, что упирается в него дольше чем на 3-4 секунды - летит ошибкой.
Помимо этого этот потолок постепенно поднимается если его подбивают туда локи и опускается если на сервере тихо. Если в низкий потолок упираются локи больше чем на 3-4 секунды - летят ошибки. Если уперлась на 1-3 секунды и спала - потолок приподнимется и ошибки не появляются.

Написал скрипт, который показывает результат exec sp_lock в группированном виде и отсортированном по убыванию количества локов. Вверху вижу виновников торжества. Тут обычные таблицы. Тысяч по 600-700 локов съедают. Так что предполагаю, что при интенсивной нагрузки на сервер (а сейчас он почти не занят) пять-семь раз по 600-700 тысяч локов одновременно - пробиваются потолки.

Соответственно остается главный вопрос. Вопрос на 50 миллионов. Как дать системе потолок в 50 млн локов.
Детектив прям получается.
15 ноя 18, 18:24    [21735541]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36686
with (paglock) спасет отца русской демократии. 50 млн локов -- это какая-то лажа.
15 ноя 18, 18:38    [21735559]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
invm
Member

Откуда: Москва
Сообщений: 9115
Kaktus_,

Проблема ваша из-за 32-bit SQL Server на 64-bit OS.
Насколько я помню, пул блокировок не отображается в AWE, в отличие от буферного пула.

Так что выходов у вас два:
- разбираться откуда столько блокировок и устранять проблему
- апгрейдится на 64-bit SQL Server (например на SQL Server 2008 R2 - он может работать на win2003)
15 ноя 18, 19:03    [21735597]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
Balbidon
Member

Откуда: Donetsk->Emerald City
Сообщений: 345
AWE не поможет, т.к., как выше указывали товарищи, 32-битный SQL Server 2000 может использовать память выше 4 Гб только для Buffer Pool. Возможно, получилось бы слегка "смягчить" последствия конкретной проблемы используя userva, что позволит процессу SQL Server выделить больше 2 Гб памяти для остальных компонентов Committed Memory. Возможно, это позволит выделить место под 3-3.5 миллиона блокировок.

Однако, как опять же заметили выше, 50 млн. это весьма немало. Имеет смысл поискать в запросах хинты типа ROWLOCK а также уровни изоляции транзакций выше стандартного (REPEATABLE READ/SERIALIZABLE), которые могут приводить к росту количества одновременно существующих в системе блокировок.
16 ноя 18, 00:25    [21735866]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
Balbidon
Member

Откуда: Donetsk->Emerald City
Сообщений: 345
Balbidon
AWE не поможет


На всякий случай уточню. Я не хотел предложить его убирать. Имелось ввиду, что в памяти выше 4 Гб блокировки не размещаются, т.е. что 52 Гб, что 62 Гб выдано серверу в вашей системе - ничего не изменит.

Так что попробуйте просто в конфигурации поиграться с /3GB /userva=…. Это, возможно, поможет смягчить проблему, пока причина появления столь большого количества блокировок не найдена. Один серьезный минус, понадобится даунтайм на каждое изменение параметра.
16 ноя 18, 00:37    [21735871]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
step_ks
Member

Откуда:
Сообщений: 936
Balbidon

Так что попробуйте просто в конфигурации поиграться с /3GB /userva=…..

64-битная ОС даст 4ГБ без этого
16 ноя 18, 12:13    [21736174]     Ответить | Цитировать Сообщить модератору
 Re: Лохматим локами бодрого старичка из 2000-ых.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7383
И все же интересно - почему нет эскалации. Где-то жестко это прописано, видимо.
16 ноя 18, 13:02    [21736275]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить