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

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)

buserKaktus_, в башке крутится 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

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

К сожалению не помогло.
Уменьшили только что на 10 Гб. Увидели в sp_configure результат. Увидели в обычном Task Manager, что ОС стала видеть 12 свободных гигов памяти, но количество локов и выделенной памяти под локи не изменилось.
Для интереса поигрались и нижней границей min server memory - тоже не помогло.

Поставили 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 млн.

Жду когда пользователи начнут работать интенсивнее чтобы пробить эти показатели.

Попробую описать то, что сейчас произошло у меня на экране.
Количество блокировок подскочило до 9 миллионов увлекая за собой потолок Allocation и увеличивая память под локи.
Lock Allocation поднялся до 6 млн. Память увеличилась почти до 500 мбайт. Могу с цифрами приврать, т.к. наблюдал в динамике. Правда ошибка всеравно возникла. Но одна.
Блокировки упали быстро.
Lock Allocation после спада блокировок постепенно минуты за две вернулся к своим любимым 2.7 млн, а память под блокировки к своим любимым 260 мбайтам.
Наблюдаю.

TaPaKKaktus_,

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

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

Что имеется ввиду под эскалацией блокировок в объектах пока не очень понимаю - сейчас почитаю. Теорию я понимаю, но не очень понял про отключение.

Lock escalation thresholds are determined dynamically by SQL Server and do not require configuration.

step_ks,

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

Ниже рекомендовано почему-то 98 Гб. Завтра попробуем это тоже потестировать. Или поставить по выбору системы.

Промежуточный итог.
Насколько Вы поняли выше - потолок в 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 млн локов.
Детектив прям получается.