MSFT SQL Server - MS SQL потребляет много оперативной памяти

[Tonny_Bennet]

Здравствуйте.



Имеется сервер 1С и подключенный к нему через сеть в 1 Гбит/с сервер MS SQL

Версия MS SQL 2008 R2

Microsoft SQL Server Management Studio 10.50.1600.1

Клиентские средства служб Microsoft Analysis Services 10.50.1600.1

Компоненты доступа к данным (MDAC) 6.0.6002.18005

Microsoft MSXML 3.0 6.0

Microsoft Internet Explorer 7.0.6002.18005

Microsoft .NET Framework 2.0.50727.4016

Операционная система 6.0.6002







Операционная система Win2008 Ent 64-bit

Подробная конфигурация сервера

Корпус Supermicro CSE-733TQ-665B Low Noise Mid tower CSE-733TQ-665B

Серверная материнская плата Supermicro X8DTi-O X8DTi-O

Процессор Intel Xeon E5620 AT80614005073A 2.40GHz,12M,5.86GT/s, LGA1366 (80W), DDR3-1066 4Cores/8Threads (Westmere) Tray AT80614005073AB

Охлаждение Intel Thermal Solution (Combo) BXSTS100C for LGA-1366, 55** & 56** Xeon CPU series Max 130W BXSTS100C

Серверная оперативная память DDR3 4096Mb PC1333 Kingston ECC Reg Dual Rank KVR1333D3D8R9S/4G

Накопитель SSD 128Gb Kingston SSDNow + V100 2.5" SATAII SV100S2/128GZ 2 шт.

Переходник 3.5" to 2.5" HDD Tray MCP-220-00043-0N

Жесткий диск HDD 2000 Gb Seagate (5900rpm) 64Mb SATAIII ST2000DL003







Из 2-х SSD дисков сделан RAID1. Система и базы хранятся на нём. У сервера единственная задача - MS SQL!



Всего 11 баз данных общим объёмом на 57 ГБ. В одной из баз (имеется в виду 1С) весом в 16 ГБ одновременно трудится около 20 человек. Иногда возникают вопросы связанные со скоростью работы типа: "Документ долго проводится", "Отчёт долго формируется".

Во время этих жалоб следил за загруженностью ресурсов сервера. Единственное что смущает так это то, что оперативка, выделенная серверу (12 ГБ), полностью забита!!! Перезапустил службу MS SQL. На графике ниже видно резкое падение - это и есть момент рестарта службы. Жалобы прекратились. А потом примерно в 00-20 снова растёт загрузка - на это время сделан JOB на резервное копирование всех баз.







Мне интересно почему не очищается оперативка? Где могут быть слабые места в данной системе? Можно ли и стоит ли при помощи JOB создать задание, которое после создания бекапа перезапускает сервер (JOB создавал приходящий программист 1С)? Почему во время активной работы пользователей загрузка сети не превышает 4 Мбит/с (по-моему должно быть больше)?

[Delirium]

Цитата Tonny_Bennet:



Из 2-х SSD дисков сделан RAID1. Система и базы хранятся на нём

MSFT SQL Server - MS SQL потребляет много оперативной памяти

Ой как не красиво то. Во первых, зеркало - далеко не самый удачный вариант рейд-массива для SQL, тем более что файлы логов и транзакций хранятся на этом же массиве. Во вторых, было бы неплохо узнать, что же там за JOB такой, что тормозит работу пользователей. В третих, не указано, КУДА делаются бекапы.

Ждем ответов и будем думать дальше




Цитата Tonny_Bennet:



Почему во время активной работы пользователей загрузка сети не превышает 4 Мбит/с (по-моему должно быть больше)

MSFT SQL Server - MS SQL потребляет много оперативной памяти

Почему должно быть больше? У меня 110 человек сидят в SQL базах, загрузка сети почти 0. Все зависит не от сети, а от того, как работает клиентская и серверная части базы данных.

[Busla]

Цитата Delirium:



зеркало - далеко не самый удачный вариант рейд-массива для SQL

MSFT SQL Server - MS SQL потребляет много оперативной памяти

Какой массив лучше сделать тогда?




Цитата Delirium:



тем более что файлы логов и транзакций хранятся на этом же массиве

MSFT SQL Server - MS SQL потребляет много оперативной памяти

Как стоит разделить эти файлы? (Я так понимаю что речь идёт о файлах *.mdf и *.log)




Цитата Delirium:



было бы неплохо узнать, что же там за JOB такой

MSFT SQL Server - MS SQL потребляет много оперативной памяти

Пообщался с человеком который всё это настраивал. Он мне сказал что тормоза не из-за резервного копирования (оно втечение нескольких минут происходит) а из-за переиндексации базы. Нашёл это задание (точнее то задание из-за которого по-моему всё вешается). Ошибок в логах этого задания нет.







Verify that automation is enabled.



Код:

Код: Выделить всё

IF (msdb.dbo.fn_syspolicy_is_automation_enabled() != 1)
        BEGIN
            RAISERROR(34022, 16, 1)
        END

Purge history.



Код:

Код: Выделить всё

EXEC msdb.dbo.sp_syspolicy_purge_history

Erase Phantom System Health Records.



Код:

Код: Выделить всё

if ('$(ESCAPE_SQUOTE(INST))' -eq 'MSSQLSERVER') {$a = '\DEFAULT'} ELSE {$a = ''};
(Get-Item SQLSERVER:\SQLPolicy\$(ESCAPE_NONE(SRVR))$a).EraseSystemHealthPhantomRecords()

Программист сказал, что возникала ошибка из за которой он какой-то из пунктов задания отключил.

Нашёл ошибку в другом задании (это создание резервной копии) в старых логах но это может и не она.



Пакет лежит в Maintenance Plans\MaintenancePlan ... как его оттуда достать я не знаю

Ошибка

Код:

Код: Выделить всё

Дата		20.11.2011 14:47:54
Журнал		Журнал заданий (MaintenancePlan.ВложенныйПлан_1)
Идентификатор шага		1
Сервер		MARS
Имя задания		MaintenancePlan.ВложенныйПлан_1
Имя шага		ВложенныйПлан_1
Продолжительность		00:08:32
Серьезность Sql		0
Идентификатор Sql-сообщения		0
Оператору отправлено сообщение электронной почты		
Оператору отправлено сообщение командой Net send		
Оператору отправлено сообщение на пейджер		
Предпринято повторов		0
Сообщение
Выполняется от имени пользователя: MARS\SYSTEM.Программа выполнения пакетов Microsoft (R) SQL Server  Version 10.50.1600.1 for 64-bit  (C) Корпорация Майкрософт (Microsoft Corporation), 2010. Все права защищены.    Начало: 14:47:54  Выполнение: 2011-11-20 14:47:54.67    Источник: {27CAE927-5CA1-4A19-8EBB-1CEDBC8062E4}     Выполнение запроса "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% завершено  Конец выполнения  DTExec: завершено исполнение пакетаDTSER_FAILURE (1).  Начало: 14:47:54  Готово: 14:56:26  Прошло:512.245 секунд.  Не удалось выполнить пакет.  Шаг завершился с ошибкой.

Ещё одна ошибка

Код:

Код: Выделить всё

Дата		31.10.2011 0:00:00
Журнал		Журнал заданий (MaintenancePlan.ВложенныйПлан_1)
Идентификатор шага		1
Сервер		MARS
Имя задания		MaintenancePlan.ВложенныйПлан_1
Имя шага		ВложенныйПлан_1
Продолжительность		00:55:25
Серьезность Sql		0
Идентификатор Sql-сообщения		0
Оператору отправлено сообщение электронной почты		
Оператору отправлено сообщение командой Net send		
Оператору отправлено сообщение на пейджер		
Предпринято повторов		0
Сообщение
Выполняется от имени пользователя: MARS\SYSTEM....for 64-bit  (C) Корпорация Майкрософт (Microsoft Corporation), 2010. Все права защищены.    Начало: 0:00:00  Выполнение: 2011-10-31 00:00:01.37    Источник: {27CAE927-5CA1-4A19-8EBB-1CEDBC8062E4}     Выполнение запроса "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:36.92    Источник: Восстановить индекс     Выполнение запроса "USE [BUH2011]  ".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.16    Источник: Восстановить индекс     Выполнение запроса "ALTER INDEX [_Acc14_ByCode_SR] ON [dbo].[_Acc14] R...".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.16    Источник: Восстановить индекс     Выполнение запроса "USE [BUH2011]  ".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.18    Источник: Восстановить индекс     Выполнение запроса "ALTER INDEX [_Acc14_ByDescr_SR] ON [dbo].[_Acc14] ...".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.18    Источник: Восстановить индекс     Выполнение запроса "USE [BUH2011]  ".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.20    Источник: Восстановить индекс     Выполнение запроса "ALTER INDEX [_Acc14_ByField460_SR] ON [dbo].[_Acc1...".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.20    Источник: Восстановить индекс     Выполнение запроса "USE [BUH2011]  ".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.21    Источник: Восстановить индекс     Выполнение запроса "ALTER INDEX [_Acc14_ByOrder_SR] ON [dbo].[_Acc14] ...".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.21    Источник: Восстановить индекс     Выполнение запроса "USE [BUH2011]  ".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.23    Источник: Восстановить индекс     Выполнение запроса "ALTER INDEX [_Acc14_ByParentCode_RSR] ON [dbo].[_A...".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.23    Источник: Восстановить индекс     Выполнение запроса "USE [BUH2011]  ".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.24    Источник: Восстановить индекс     Выполнение запроса "ALTER INDEX [_Acc14_ByParentDescr_RSR] ON [dbo].[_...".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.24    Источник: Восстановить индекс     Выполнение запроса "USE [BUH2011]  ".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.26    Источник: Восстановить индекс     Выполнение запроса "ALTER INDEX [_Acc14_ByParentField459_RSR] ON [dbo]...".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.26    Источник: Восстановить индекс     Выполнение запроса "USE [BUH2011]  ".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.27    Источник: Восстановить индекс     Выполнение запроса "ALTER INDEX [_Acc14_ByParentOrder_RSR] ON [dbo].[_...".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.27    Источник: Восстановить индекс     Выполнение запроса "USE [BUH2011]  ".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.30    Источник: Восстановить индекс     Выполнение запроса "ALTER INDEX [PK___Acc14__AC8ED0C40D30B51A] ON [dbo...".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.30    Источник: Восстановить индекс     Выполнение запроса "USE [BUH2011]  ".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.31    Источник: Восстановить индекс     Выполнение запроса "ALTER INDEX [_Acc14_ExtDim455_ByLineNo_RNR] ON [db...".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.31    Источник: Восстановить индекс     Выполнение запроса "USE [BUH2011]  ".: 0% завершено  Конец выполнения  Выполнение: 2011-10-31 00:08:37.33    Источник: Восстановить индекс     Выполнение запроса "ALTER INDEX [_Acc14_ExtDim455_IntKeyInd] ON [dbo]....".: 0% завершено  Конец выполнения  Выполнение: 20...  Не удалось выполнить п...  Шаг завершился с ошибкой.

Цитата Delirium:



КУДА делаются бекапы.

MSFT SQL Server - MS SQL потребляет много оперативной памяти

Бекапы делаются на большой жёсткий диск


Цитата Tonny_Bennet:



Жесткий диск HDD 2000 Gb Seagate (5900rpm) 64Mb SATAIII ST2000DL003

MSFT SQL Server - MS SQL потребляет много оперативной памяти

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






Цитата Delirium:



Почему должно быть больше?

MSFT SQL Server - MS SQL потребляет много оперативной памяти

Не знаю... просто думал что могло быть и больше

[Delirium]

Цитата Tonny_Bennet:



Какой массив лучше сделать тогда?

MSFT SQL Server - MS SQL потребляет много оперативной памяти

5. Или 10. И логи (ldf-файлы) должны быть на других жестких дисках. Не разделах, а именно дисках. Это заметно снизит дисковую нагрузку, особенно если у баз данных модель восстановления выставлена в Full, а не Simple.



Всего 11 баз. Из них только одна 1С? И именно на 1С, я так понимаю, делается космическая переиндексация какая то?

Что меня всегда убивало в 1С, так это то, как они умудряются своим софтом вешать такие вещи как SQL Server. Но это так, в качестве оффтопа.



Пойдем другим путем. Как часто выполняется переиндексация? Одновременно с основным JOB-ом ежедневно в 00-20? Или чаще?



Создание бекапов мы в расчет не берем, эта технология отлажена и из-за нее настолько тормозить не будет.



Я бы сделал следующее:

1. Через Profiler с утра пораньше, когда нет пользователей в базе, помониторил бы, какие обращения идут к SQL серверу и какие задачи выполняются.

2. Через Management Studio помониторил бы активные соединения, есть ли незакрытые транзакции и что они выполняют.

3. В момент жалоб еще внимательней повторил бы пункт.2



Согласно этому анализу будет примерно видно, в чем проблема.

Ну и собственно, Performance Monitor от OS Windows еще никто не отменял. И на OsZone, если я не ошибаюсь, есть отличные статьи по оптимизации SQL Server. Настоятельно рекомендую к прочтению .(

http://www.oszone.net/4482/Microsoft_SQL_Server

)



ПО поводу вытаскивания задания из плана. Под руками дома sql нет, попробую в понедельник посмотреть на работе, ну или может, пораньше кто нибудь подскажет здесь

[Tonny_Bennet]

Цитата Delirium:



Всего 11 баз. Из них только одна 1С?

MSFT SQL Server - MS SQL потребляет много оперативной памяти

Они все 1С... просто компания представляет собой набор юридическийх лиц. Самая большая из них 16 ГБ это торговля в которой и работает много народу. Остальное бухгалтерские базы в которых работают только бухи - 4 человека.




Цитата Delirium:



И именно на 1С, я так понимаю, делается космическая переиндексация какая то?

MSFT SQL Server - MS SQL потребляет много оперативной памяти

Космическая переиндексация по-моему затрагивает все базы.




Цитата Delirium:



Как часто выполняется переиндексация?

MSFT SQL Server - MS SQL потребляет много оперативной памяти

Все задания запускаются ночью 1 раз. Всего их 2. Что из них переиндексация я не знаю.



Задание MaintenancePlan.Вложенный_план_1, которое я не могу достать, выполняется в 00:00.

Задание syspolicy_purge_history которое я попытался описать выше выполняется в 02:00.



Теперь понимаю что скорее всего проблема в MaintenancePlan.Вложенный_план_1 .... нужно его как то вытащить.


Цитата Delirium:



Настоятельно рекомендую к прочтению .(

http://www.oszone.net/4482/Microsoft_SQL_Server

)

MSFT SQL Server - MS SQL потребляет много оперативной памяти

Уже начал читать

[Tonny_Bennet]

Нашёл что делает MaintenancePlan.Вложенный_план_1. Лежит он в Планах обслуживания

Скриншот

Вроде бы ничего криминального. Так что почему на каком-то шаге забивается оперативка и потом не очищается я не знаю ...



Может скрипт спотыкается на какой-то базе? В предыдущей ошибке была указана база buh2011...

[Tonny_Bennet]

Цитата Tonny_Bennet:



Задание syspolicy_purge_history которое я попытался описать выше выполняется в 02:00.

MSFT SQL Server - MS SQL потребляет много оперативной памяти

Это задание не трогаем, оно стандартное, и у всех запускается в 2 ночи. Выполняется за секунду.



Проблема однозначно во втором плане. Точнее, не в самом плане, а в данных в БД.

Что можно сделать: - ПКМ на плане - Wizard - запустить мастер плана. Пройтись по шагам, посмотреть, что он выполняет. Думаю, не ошибусь в предположении, что там выставлена опция Rebuild Indexes - и далее будет список баз, где это надо провести. Убираешь базу Buh2011, сохраняешь план, смотришь на результаты после запуска плана. Если проблема исчезнет, значит БД мы локализовали и будем смотреть дальше. Если же нет, то убирай еще БД из плана, вычленяя источник проблемы.

[Delirium]

Цитата Delirium:



что там выставлена опция Rebuild Indexes

MSFT SQL Server - MS SQL потребляет много оперативной памяти

Выше в скриншоте MaintenancePlan.Вложенный_план_1 видно, что есть шаг "Восстановить индекс".



Слева на панели есть "Реорганизация индекса" и "Перестроение". Я думаю в данном переводе Престроение и есть Rebuild Indexes. Если да, то как видно в этом плане данный шаг не используется.



Стоит ли исключать БД из шага "Восстановить индекс"?



Сегодня обработка отработала нормально (без ошибок) но оперативка снова в полке

Сообщение в журнале

Дата 28.11.2011 0:00:00

Журнал Журнал заданий (MaintenancePlan.ВложенныйПлан_1)



Идентификатор шага 1

Сервер MARS

Имя задания MaintenancePlan.ВложенныйПлан_1

Имя шага ВложенныйПлан_1

Продолжительность 01:02:16

Серьезность Sql 0

Идентификатор Sql-сообщения 0

Оператору отправлено сообщение электронной почты

Оператору отправлено сообщение командой Net send

Оператору отправлено сообщение на пейджер

Предпринято повторов 0



Сообщение

Выполняется от имени пользователя: MARS\SYSTEM....0.1 for 64-bit (C) Корпорация Майкрософт (Microsoft Corporation), 2010. Все права защищены. Начало: 0:00:00 Выполнение: 2011-11-28 00:00:00.88 Источник: {27CAE927-5CA1-4A19-8EBB-1CEDBC8062E4} Выполнение запроса "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.37 Источник: Восстановить индекс Выполнение запроса "USE [BUH2011] ".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.40 Источник: Восстановить индекс Выполнение запроса "ALTER INDEX [_Acc14_ByCode_SR] ON [dbo].[_Acc14] R...".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.40 Источник: Восстановить индекс Выполнение запроса "USE [BUH2011] ".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.42 Источник: Восстановить индекс Выполнение запроса "ALTER INDEX [_Acc14_ByDescr_SR] ON [dbo].[_Acc14] ...".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.42 Источник: Восстановить индекс Выполнение запроса "USE [BUH2011] ".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.44 Источник: Восстановить индекс Выполнение запроса "ALTER INDEX [_Acc14_ByField460_SR] ON [dbo].[_Acc1...".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.44 Источник: Восстановить индекс Выполнение запроса "USE [BUH2011] ".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.46 Источник: Восстановить индекс Выполнение запроса "ALTER INDEX [_Acc14_ByOrder_SR] ON [dbo].[_Acc14] ...".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.46 Источник: Восстановить индекс Выполнение запроса "USE [BUH2011] ".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.48 Источник: Восстановить индекс Выполнение запроса "ALTER INDEX [_Acc14_ByParentCode_RSR] ON [dbo].[_A...".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.48 Источник: Восстановить индекс Выполнение запроса "USE [BUH2011] ".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.50 Источник: Восстановить индекс Выполнение запроса "ALTER INDEX [_Acc14_ByParentDescr_RSR] ON [dbo].[_...".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.50 Источник: Восстановить индекс Выполнение запроса "USE [BUH2011] ".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.52 Источник: Восстановить индекс Выполнение запроса "ALTER INDEX [_Acc14_ByParentField459_RSR] ON [dbo]...".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.52 Источник: Восстановить индекс Выполнение запроса "USE [BUH2011] ".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.54 Источник: Восстановить индекс Выполнение запроса "ALTER INDEX [_Acc14_ByParentOrder_RSR] ON [dbo].[_...".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.54 Источник: Восстановить индекс Выполнение запроса "USE [BUH2011] ".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.57 Источник: Восстановить индекс Выполнение запроса "ALTER INDEX [PK___Acc14__AC8ED0C40D30B51A] ON [dbo...".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.57 Источник: Восстановить индекс Выполнение запроса "USE [BUH2011] ".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.60 Источник: Восстановить индекс Выполнение запроса "ALTER INDEX [_Acc14_ExtDim455_ByLineNo_RNR] ON [db...".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.60 Источник: Восстановить индекс Выполнение запроса "USE [BUH2011] ".: 0% завершено Конец выполнения Выполнение: 2011-11-28 00:08:40.61 Источник: Восстановить индекс Выполнение запроса "ALTER INDEX [_Acc14_ExtDim455_IntKeyInd] ON [dbo]....".: 0% завершено Конец выполнения Выполнение: 2011-1... Пакет выполнен усп... Шаг успешно выполнен.







P.S. В сообщениях о успешном завершении резервного копирования нет ни слова о каких то других базах кроме BUH2011. Может и не стоит обращать внимание на неё? Просто эта база стоит первая в списке на копирование.

[Tonny_Bennet]

Тебе нужно отловить, связано ли увеличение расхода оперативки с обработкой какой то конкретной базы, или же это происходит независимо от базы. Убирая по одной базы из плана, можно выловить источник проблемы. А дальше будем думать

[Delirium]

Цитата Delirium:



Убирая по одной базы из плана, можно выловить источник проблемы

MSFT SQL Server - MS SQL потребляет много оперативной памяти

Получится сделать только на выходных. Как сделаю - отпишусь.