MS SQLSERVER занимает почти 100% ресурсов

Привет, народ!

У меня проблема с MS SQLSERVER на клауд сервере.
Параметры сервера 2 ядра, 4 гб ОЗУ. Server 2009 R2 SP1, SqlServer 2012
Стоит только SQlServer, даже антивируса нет.


Сама проблема:
ЗАпись на SQLSERVER происходит в основном через десктоп-приложение, а отчеты пользователи получает через наш сайт.
Как видно на скриншоте SQLSERVER занимает почти 3 гб ОЗУ и почти 100% ресурсов. В это время было активных около 100 пользователей чтения-записи на SQLSERVER.
Я хотел узнать, это правильно, что со 100 активными пользователями SQLSERVER так занимает ресурсы?
Скоро у нас будут более 500 активных пользователей, какую конфигурацию клауд сервера (или выделенного сервера) вы могли бы порекомендовать?

Заранее спасибо.

Миниатюры

     

0

Сильно общий вопрос. Это как диагностировать болезнь по фотографии. Один бог знает что у вас там в базе творится, какие запросы посылаются, что и как хранится. Может всё триггерами усеяно, а все запросы имеют вид "SELECT * FROM" + миллионы "JOIN".

В общем, никакой конкретики тут не будет. Профилируйте работу своих приложений с БД, смотрите откуда растут ноги у такого потребления ресурсов.

1

и пожирание памяти кстати не показатель ничего вообще, забудь о ней
он сожрет столько сколько дадут, запросто может пожирать даже больше чем сама БД на диске, а может и не пожирать если ограничить и ничего от этого никуда не отвалится

Добавлено через 4 минуты
причем порой даже не можно, а нужно ограничивать, т.к у винды начинается паника когда кончается память
сам по себе сервер умеет вовремя остановится (толи 80, толи 90 процентов), но вот учитывать другие приложения он ясное уже не может
так что если какой-нибудь w3wp.exe тоже захочет памяти там начнется драка и в итоге все участники скатятся в своп, нагрузят диск, повиснут запросы и понеслась

0

Сообщение от Umrbek79 почти 100% ресурсов

Многовато...
Если отключить всех клиентов - сервер будет столько кушать?
Клиентское ПО не может ввергать его пучину "трудолюбия"?

0

Спасибо за ответы.
Попробую объяснить работу сервера.

На сервере находится база данных учеников. Клиентское ПО отправляет данные посещаемости учеников за день.
Таблица посещаемости маленькая конечно, там только код ученика и время посещения занятий. Таблица посещаемости отправляется на сервер с помощью функции SQLBULKCOPY.
Таких учебных заведений, т.е. клиентов сервера около 500 (будет еще больше).
На сайте с помощью SQL запросов создаются отчеты и вычисляются проценты посещаемости по каждому учебному заведению. Любой пользователь может зайти на сайт и посмотреть отчет.
Таким образом, о количестве одновременных подключений к серверу остается только догадываться...

Я сам еще новичок по работе с MS SQL сервером. У меня возник вопрос: все форумы говорят об индексации. У меня все таблицы не индексированы. Я опасаюсь, может это влияет на производительность сервера?

Сообщение от Usaga Может всё триггерами усеяно, а все запросы имеют вид "SELECT * FROM" + миллионы "JOIN".

Триггеров нету, я сам не знаю что такое триггер
А насчет запросов, "select *" я избегаю, а JOIN ы там несколько, т.е. "ученик+посещаемость+школа+город+область " вот как-то так...

Сообщение от Hikari Если отключить всех клиентов - сервер будет столько кушать? Клиентское ПО не может ввергать его пучину "трудолюбия"?

Если отключить всех клиентов - сервер вообще ничего не кушает.
Одно клиентское подключение с инсертом SQLBULKCOPY заставляет работать сервер на 15-20% ресурсов, но это только, когда запрос активен.

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

Именно так и случается, т.е клиент отправляет данные на сервер (функцией SQLBULKCOPY если не забыли), прога пишет что успешно отправила на сервер. Я на сервер отправляю запрос с инсертом. Результат: записи не добавлены!

Что можете посоветовать?

Добавлено через 10 минут
Кстати, после перезапуска SQL сервера все работает отлично полдня, а потом потребление ресурсов этим процессом достигает почти 100%, а занимаемая память увеличивается больше 3 гб и запросы висят и отправленные данные начинают пропадать...

0

на твоем месте я бы в первую очередь выплил SQLBULKCOPY, не знаю как он работает, но будь я на месте мелкомягких реализующих такую фичу - я бы сделал отключение всего, т.е статистики, индексов, триггеров, внешних ключей и т.п, затем вставку напрямую в талицу и затем пересчет всего сразу
SQLBULKCOPY это предел производительности позволяющий добавлять >=10 тысяч записей в секунду, а со смешными 500 строк справится обычный insert за мгновение(разумеется если там нет blob на 10мб)
мне думается если 50 человек сделают такую вставку вполне возможен такой треш...

0

Сообщение от Umrbek79 Одно клиентское подключение с инсертом SQLBULKCOPY заставляет работать сервер на 15-20% ресурсов,

Вот тебе и первый источник проблемы. Как уже выше порекомендовали, попробуй заменить SQLBULKCOPY на SELECT и посмотреть, что получится.

А вот с индексами не всё так просто. Они действительно скажутся на производительности, но не обязательно в лучшую сторону. Создание и обновление индекса - процедура ни разу не бесплатная в плане затрат процессорного времени. Индексировать имеет смысл только те таблицы, данные в которые заносятся редко, и в основном происходит чтение. И колонки индексировать нужно только с уникальными значениями, иначе от индекса толку не будет. Рекомендую основательно об этом почить, прежде, чем браться.

Кстати, большое количество JOIN-ов можно заменить индексированной вьюшкой (VIEW), что даст прирост производительности, но это, опять же, при должном понимании вопроса.

А в остальном, пока что, всё тоже - профилируй, смотри что где да как.

0

Umrbek79, на глупости про, якобы, проблемы с SQLBULKCOPY можете не обращать внимания. Не в этом дело.
Для начала, запустите Activity Monitor и наблюдайте за происходящим.

1. 2 ядра на 100 активных пользователей - катастрофически мало. Если не хватает процессорных ресурсов, в Activity Monitor в Resource Waits в топе будет тип ожидания SOS_SCHEDULER_YIELD

2. Отсутствие необходимых индексов приводит, при выполнении запросов, к вычитыванию таблиц целиком. Вкратце, это негативно сказывается на производительности запросов, увеличивается конкуренции читатели-писатели, неэффективно используется буферный кеш и еще много чего.
Глупости про падение производительности записи из-за индексов можете смело игнорировать, - у вас слишком маленькая нагрузка, чтобы заметить такое падение. Глупости про бесполезность неуникальных индексов так же смело игнорируйте.

Итого: вам, судя по всему, придется наращивать аппаратные ресурсы сервера, проводить редизайн БД в части индексов и заниматься оптимизацией запросов.
Т.к. необходимых опыта и знаний у вас пока что нет, оптимальным будет разово нанять специалиста для этой работы. Заодно и поучитесь у него.

Добавлено через 7 минут
Если таблица, куда заливаются данные, будет проиндексирована, SQLBULKCOPY, без включения флага трассировки 610, станет эквивалентен обычному INSERT.
Продолжать пользоваться BULK COPY или нет, зависит от объема добавляемых данных.

0

invm, т.е. можно и нужно смело индексировать всё подряд и это только на пользу пойдёт в любом случае?

Сообщение от invm у вас слишком маленькая нагрузка, чтобы заметить такое падение

Настолько маленьная, что даже сервер не вывозит?

0

Сообщение от Usaga т.е. можно и нужно смело индексировать всё подряд и это только на пользу пойдёт в любом случае?

Не нужно утрировать. А нужно думать головой при проектировании индексов, а не опираться на догмы не понятно откуда взявшиеся. Например, согласно вашей логики, таблицы "Заказы" и "Состав заказа" не подлежат индексированию, т.к. данные в них добавляются и изменяются часто. Причем "Состав заказа" дважды, т.к. индекс по коду заказа неуникальный и смысла не имеет.

Сообщение от Usaga Настолько маленьная, что даже сервер не вывозит?

Что, доподлинно известно, что "сервер не вывозит" нагрузку на вставку?

0

Сообщение от invm Глупости про падение производительности записи из-за индексов можете смело игнорировать

Очень неудачное мнение...
Я бы не была так категорична по поводу пересчетов статистик даже если индексация делается по какой-то суперскоросной типа RushMore технологии. Нельзя сбрасывать со счетов индексацию, которая мешает вставкам.

0

Сообщение от invm Не нужно утрировать. А нужно думать головой при проектировании индексов, а не опираться на догмы не понятно откуда взявшиеся.

Я об этом прямо и сказал: существуют индексы, которые могут помочь, но только при определённом стечении обстоятельств. Дословно:

Сообщение от Usaga А вот с индексами не всё так просто. Они действительно скажутся на производительности, но не обязательно в лучшую сторону. Создание и обновление индекса - процедура ни разу не бесплатная в плане затрат процессорного времени. Индексировать имеет смысл только те таблицы, данные в которые заносятся редко, и в основном происходит чтение. И колонки индексировать нужно только с уникальными значениями, иначе от индекса толку не будет. Рекомендую основательно об этом почить, прежде, чем браться.

По поводу утрирования:

Сообщение от invm Глупости про падение производительности записи из-за индексов можете смело игнорировать,

Чем не утрирование?..

Сообщение от invm Что, доподлинно известно, что "сервер не вывозит" нагрузку на вставку?

Судя по вопросу ТС, его сервер вообщен ничего не вывозит. Он с этим сюда и обратился. И да, сервер даже вставку не вывозит:

Сообщение от Umrbek79 Именно так и случается, т.е клиент отправляет данные на сервер (функцией SQLBULKCOPY если не забыли), прога пишет что успешно отправила на сервер. Я на сервер отправляю запрос с инсертом. Результат: записи не добавлены!

Я понимаю, что тебе хочется выглядеть дофига деловым и знающим, но категоричность такая ещё никого не красила.

0

Сообщение от Usaga Я об этом прямо и сказал: существуют индексы, которые могут помочь, но только при определённом стечении обстоятельств.

Открою вам страшную тайну - любые индексы всегда помогают только при определенном стечении обстоятельств. А именно, когда оптимизатор запросов сможет их использовать и сочтет это использование выгодным.
Вы же категорично и безосновательно заявили - "Индексировать имеет смысл только те таблицы, данные в которые заносятся редко, и в основном происходит чтение". Про не менее категоричное высказывание о неуникальных индексах вообще молчу.
Проектирование индексов основано не на частоте "занесения данных", а на оценке насколько выигрыш от их использования при чтениях превосходит накладные расходы на их поддержание. И насколько вообще эти накладные расходы допустимы.

Сообщение от Usaga Чем не утрирование?..

Да ну? Может вам стоит ознакомится со значением данного слова?
ТС написал какие данные он вставляет. Можете оценить объем этих данных и частоту вставки. И потом сделать вывод о величине нагрузки и насколько просядет производительность при наличии индексов.

Сообщение от Usaga Судя по вопросу ТС, его сервер вообщен ничего не вывозит. Он с этим сюда и обратился. И да, сервер даже вставку не вывозит

И из этого следует однозначный вывод, что проблема в BULK INSERT? И индексировать ничего нельзя, ибо и так все плохо? И выводы эти сделаны по скриншоту диспетчера задач?

Сообщение от Usaga Я понимаю, что тебе хочется выглядеть дофига деловым и знающим

На брудершафт я с вами не пил. И не судите о других по себе.

1

Сообщение от invm Открою вам страшную тайну - любые индексы всегда помогают только при определенном стечении обстоятельств. А именно, когда оптимизатор запросов сможет их использовать и сочтет это использование выгодным. Вы же категорично и безосновательно заявили - "Индексировать имеет смысл только те таблицы, данные в которые заносятся редко, и в основном происходит чтение". Про не менее категоричное высказывание о неуникальных индексах вообще молчу. Проектирование индексов основано не на частоте "занесения данных", а на оценке насколько выигрыш от их использования при чтениях превосходит накладные расходы на их поддержание. И насколько вообще эти накладные расходы допустимы.

И? Как это протвиворечит моему высказыванию:

Сообщение от Usaga А вот с индексами не всё так просто. Они действительно скажутся на производительности, но не обязательно в лучшую сторону. Создание и обновление индекса - процедура ни разу не бесплатная в плане затрат процессорного времени. Индексировать имеет смысл только те таблицы, данные в которые заносятся редко, и в основном происходит чтение. И колонки индексировать нужно только с уникальными значениями, иначе от индекса толку не будет. Рекомендую основательно об этом почить, прежде, чем браться.

Я что, категорично заявил, что индексирование спасёт отца русской демократии? Или сказал, что индесы - зло? Я недвусмысленно намекнул, что индесы дадут положительный эффект только при разумном применении.

Сообщение от invm И из этого следует однозначный вывод, что проблема в BULK INSERT? И индексировать ничего нельзя, ибо и так все плохо? И выводы эти сделаны по скриншоту диспетчера задач?

Из этого следует, что проблема (если она одна) кроется ХБЗ в чём - нам тупо не придоставили информации. Про индексы я высказался выще: исследуем проблему, делаем выводы.

А вот про то, что не изменение скорости записи при наличие индексов я бы хотел узнать побольше. Ты из личного опыта это подчерпнул или как? Просто мне всегда казалось, что содержать (и обновлять) дополнительную коллекцию данных это как бы сложнее, чем этого не делать. Если бы это было на 100% эффективно, то речи бы об этом ни шло, и индексы использовались для каждого поля по умолчанию.

Мне просто очень интересно откуда вырисовывались такие категоричные заявления про глупости. Т.е. от тебя самого пришло, что:

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

... и следом:

Сообщение от invm Проектирование индексов основано не на частоте "занесения данных", а на оценке насколько выигрыш от их использования при чтениях превосходит накладные расходы на их поддержание. И насколько вообще эти накладные расходы допустимы.

Т.е. индексы - вроде как "халява", но обдумывать и взвешивать их применение, всё-таки стоит.

И в чём тут ценная мысль и ценный совет?

0

Сообщение от Usaga Я что, категорично заявил, что индексирование спасёт отца русской демократии? Или сказал, что индесы - зло? Я недвусмысленно намекнул, что индесы дадут положительный эффект только при разумном применении.

Вы недвусмысленно намекнули:

Сообщение от Usaga Индексировать имеет смысл только те таблицы, данные в которые заносятся редко, и в основном происходит чтение.

Сообщение от Usaga И колонки индексировать нужно только с уникальными значениями, иначе от индекса толку не будет.

А это, извините, - чушь.

Сообщение от Usaga Из этого следует, что проблема (если она одна) кроется ХБЗ в чём - нам тупо не придоставили информации.

И как из этого можно сделать вывод о проблемах с BULK INSERT?

Сообщение от Usaga А вот про то, что не изменение скорости записи при наличие индексов я бы хотел узнать побольше.

Разницу между "заметить" и "не изменение" понимаете?

Сообщение от Usaga Т.е. индексы - вроде как "халява", но обдумывать и взвешивать их применение, всё-таки стоит.

Научитесь внимательно читать, что вам пишут, а не додумывать и фантазировать.

0

Спасибо всем за ответы.
Насчет SQLBULKCOPY.
Я использую эту функцию для вставки записей в таблицы. Так как у нас скорость интернета небольшая (128-256кбс), построчная вставка данных в удаленный сервер 1000 записей (с полем двоичных данных, который содержит рисунок ученика) занимает более часа времени. А с SQLBULKCOPY это занимает 5-6 минут. Так что я просто не могу от него отказаться.

У меня возник вопрос насчет индексации.

Таблица учеников содержит текстовое поле "Код", другие таблицы связаны с этой по полю "Код". Но он содержит не уникальные значения. Записи в таблице ученики добавляются или меняются почти каждый день. Имеет ли смысл добавить индекс по полю "Код" в таблицу ученики?

0

Сообщение от Umrbek79 Таблица учеников содержит текстовое поле "Код", другие таблицы связаны с этой по полю "Код". Но он содержит не уникальные значения. Записи в таблице ученики добавляются или меняются почти каждый день. Имеет ли смысл добавить индекс по полю "Код" в таблицу ученики?

Вам имеет смысл найти самые ресурсоемкие запросы. Например, вот так - http://blog.sqlauthority.com/2... using-dmv/. Проанализировать их планы выполнения и на основе этого анализа принимать решение об индексировании.

0

Сообщение от Umrbek79 с полем двоичных данных, который содержит рисунок ученика

А можно узнать зачем файлы держать в базе?
Почему не использовать файловое хранилище, а в БД хранить только пути к ним?

0

Так как все пользователи активно отправляют данные на сервер функцией SQLBULKCOPY я все таки хочу попробовать заменить его чем-то.
Что можете предложить использовать вместо SQLBULKCOPY?
Я сейчас делаю так: создаю временную таблицу, туда с SQLBULKCOPY отправляю данные из десктоп приложения, потом оттуда добавляю записи в основную таблицу (INSERT...FROM...).
Скорость интернета 128-256 кбс.

0

Сообщение от Umrbek79 Я сейчас делаю так: создаю временную таблицу, туда с SQLBULKCOPY отправляю данные из десктоп приложения, потом оттуда добавляю записи в основную таблицу (INSERT...FROM...).

Зачем это было сделано? Чем это лучше прямой записи в нужную таблицу?

Сообщение от Umrbek79 острочная вставка данных в удаленный сервер 1000 записей (с полем двоичных данных, который содержит рисунок ученика)

Т.е. в каждой записи в блоке данных в БД отправляется ещё и изображение? И так от сотни пользователей? Если так, то проблемное место начинает вырисовываться

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

Ну или постарался бы пересмотреть схему БД (о которой до сих пор мало, что известно).

Или выбрал бы тариф с более мощным железом.

Или все три варианта сразу.

В общем, пока у тебя на руках нет точного диагноза с пониманием где именно у тебя в БД проблема (какие именно запросы себя плохо ведут и почему), то конкретного совета не будет. По-моему, я это уже говорил

Было бы неплохо, если бы ты выложил схему БД и код, который с этой БД работает. Тогда у знающих людей было бы больше простора для выводов и оценок.

0