Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 анализ работы mssql +как снять дамп?  [new]
Serg58
Member

Откуда:
Сообщений: 65
попросили помочь с решением нескольких проблем в одной из фирм, и основная проблема(судя по всему) при работе с БД mssql.
А я немного шарю в Mysql, с mssql столкнулся впервые....


Итак, что мы имеем:
Сервер:
Процессор: Intel(R) Xeon(R) CPU E5-2630 0 @2.30Ghz 2.30 Ghz (2 процессора)
Память(RAM): 32.0 Гб
Тип системы: 32-разрядная

Windows: Windows Server Enterprise SP2

dxdiag выдаёт:
ОСь: Windows Server 2008 Enterprise 6.0
Изготовитель: IBM
Модель: System x3650 M4
Процессор: Intel(R) Xeon(R) CPU E5-2630 0 @2.30Ghz 2.30 Ghz (24 CPUs), ~2.3Ghz
Память: 32742МB RAM
Файл подкачки: 18081 Мб использовано, 47544 Мб свободно

На сервере стоит MSSQL:
select @@version

выдаёт:
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (Intel X86) Jun 28 2012 ...


Описание работы:
В фирме работают около 60 компьютеров, на которых установлена программа взаимодействующая с БД.
И основная проблема - жутко тормозит отклик программы при работе с БД.
Например вносим изменения в какую-нибудь запись, жмём "ОК" и окна программы становятся "белыми" с пометкой "не отвечает" и спустя от нескольких секунд до минут программа прогружается. Бывает, что не прогружается....ошибки различные или вылетает вообще.


Я немного посмотрел базу......
есть таблицы, в которых >50 млн строк.....у меня через Altova DatabaseSpy даже не подгрузились 200 записей, висит-висит-висит а потом ошибка.
Поэтому есть подозрение, что БД требует серьёзной чистки.

Собственно 1 вопрос:
как бы точно продиагностировать из-за чего возникают тормоза в работе пользователей?
Я погуглил, рекомендуют использовать perfmon (системный монитор) - настроить кучу датчиков следящих за процессором, памятью, сетью, файлом подкачки +нашёл на ютубе(по запросу perfmon sql) ролик как создать датчики следяющие за работой SQL.
Попробовал на своём компьютере анализировать - всё ок, удобно и объективно. Полез на сервер, так там вообще нет таких датчиков в perfmon, там датчики: Authorization Manager Applications, HTTP..., IPSec, WFP..., WSRM, MSMQ и т.д.


2 вопрос:
Как сделать бекап(дамп) БД и в случае чего его развернуть обратно?
в MySQL через phpMyAdmin можно сделать дамп БД и не боясь править, сносить и т.д. и в любой момент развернуть БД из дампа.
Я очень хочу снести 90% строк из той таблицы где свыше 50 млн строк, т.к. судя по дате строк они уже не актуальны, но сносить без бекапа как то стрёмно....


Понимаю, что всё можно найти в гугле, но решил тему создать, простите если потратил Ваше время зря. Естественно, я параллельно гуглю свою проблему.
13 фев 14, 14:53    [15563106]     Ответить | Цитировать Сообщить модератору
 Re: анализ работы mssql +как снять дамп?  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Serg58
Я немного посмотрел базу......
есть таблицы, в которых >50 млн строк.....у меня через Altova DatabaseSpy даже не подгрузились 200 записей, висит-висит-висит а потом ошибка.
Поэтому есть подозрение, что БД требует серьёзной чистки.

Используйте для работы SSMS и какой запрос вы выполнили чтобы получить результат?
Serg58
Собственно 1 вопрос:
как бы точно продиагностировать из-за чего возникают тормоза в работе пользователей?
Я погуглил, рекомендуют использовать perfmon (системный монитор) - настроить кучу датчиков следящих за процессором, памятью, сетью, файлом подкачки +нашёл на ютубе(по запросу perfmon sql) ролик как создать датчики следяющие за работой SQL.
Попробовал на своём компьютере анализировать - всё ок, удобно и объективно. Полез на сервер, так там вообще нет таких датчиков в perfmon, там датчики: Authorization Manager Applications, HTTP..., IPSec, WFP..., WSRM, MSMQ и т.д.

perfmon показывает общее состояние сервера, проблемы лежашие в плоскости архитектуры базы и архитектуры работы с ней нужно искать через профайлер и соответствующие DM сиквела
Serg58
2 вопрос:
Как сделать бекап(дамп) БД и в случае чего его развернуть обратно?
в MySQL через phpMyAdmin можно сделать дамп БД и не боясь править, сносить и т.д. и в любой момент развернуть БД из дампа.
Я очень хочу снести 90% строк из той таблицы где свыше 50 млн строк, т.к. судя по дате строк они уже не актуальны, но сносить без бекапа как то стрёмно....

как бы странно это не звучало по меркам Mysql стандартной командой сиквела backup database
13 фев 14, 15:31    [15563396]     Ответить | Цитировать Сообщить модератору
 Re: анализ работы mssql +как снять дамп?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31984
Serg58
Собственно 1 вопрос:
как бы точно продиагностировать из-за чего возникают тормоза в работе пользователей?
Я погуглил, рекомендуют использовать perfmon (системный монитор) - настроить кучу датчиков следящих за процессором, памятью, сетью, файлом подкачки +нашёл на ютубе(по запросу perfmon sql) ролик как создать датчики следяющие за работой SQL.
Первое дело - понять, что происходит на сервере. Для этого нужно научиться пользоваться профайлером (трейсом). Попробуйте, там простой интерфейс, научиться легко. Профайлер покажет, какие запросы выполняются на сервере, сколько требуют ресурсов, и позволяет показать планы запросов.

Счётчики из перфмонитора - это уже второй этап.
Serg58
2 вопрос:
Как сделать бекап(дамп) БД и в случае чего его развернуть обратно?
См. команды BACKUP DATABASE, RESTORE DATABASE

Разумеется, без бакапа даже близко к серверу подходить нельзя. Да и вообще, вопрос не "пригодится ли бкакап", а "когда пригодится".
13 фев 14, 15:34    [15563420]     Ответить | Цитировать Сообщить модератору
 Re: анализ работы mssql +как снять дамп?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31984
Serg58
есть таблицы, в которых >50 млн строк.....у меня через Altova DatabaseSpy даже не подгрузились 200 записей, висит-висит-висит а потом ошибка.
Поэтому есть подозрение, что БД требует серьёзной чистки.
Размеры таблиц нулевые. Не слышал про "Altova DatabaseSpy", используйте SSMS, как все. При выполнении конкретного запроса смотрите в другом окне sp_who2, что бы посмотреть коннекты к серверу и их статус.
13 фев 14, 15:36    [15563435]     Ответить | Цитировать Сообщить модератору
 Re: анализ работы mssql +как снять дамп?  [new]
Serg58
Member

Откуда:
Сообщений: 65
WarAnt,
автор
Используйте для работы SSMS

понял, скачал Microsoft® SQL Server® 2008 Express with Tools буду разбираться.

автор
какой запрос вы выполнили чтобы получить результат?

в Altova DatabaseSpy есть опция Retrieve data -> First 200 Rows которая отображает собственно 200 записей из таблицы. Вот покопавшись в базе нашёл огромные таблицы, у самой большой запрос count выдал 50+млн строк. При попытке вызвать First 200 Rows программа уходит в белый экран(зависает) и потом выдаёт ошибку, что превышено время выполнения запроса.

автор
perfmon показывает общее состояние сервера, проблемы лежашие в плоскости архитектуры базы и архитектуры работы с ней нужно искать через профайлер и соответствующие DM сиквела

это я понимаю, просто мне бы хотелось проверить всю систему, ибо я не на 100% уверен что проблемы в БД, может сеть, может проц....
Зависания возникают периодически, вот я планировал поставить датчики на логирование, а когда зависания начнут появляться - читать логи в момент зависания, смотреть что происходит с процессором, с активностью сети....

а тут такой облом - датчиков нет :(


alexeyvg,
спасибо огромное за наводку. Буду разбираться с SSMS и профайлером.
13 фев 14, 17:23    [15564056]     Ответить | Цитировать Сообщить модератору
 Re: анализ работы mssql +как снять дамп?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31984
Serg58
При попытке вызвать First 200 Rows программа уходит в белый экран(зависает) и потом выдаёт ошибку, что превышено время выполнения запроса.
Думаю, что всё таки Altova DatabaseSpy делает по нормальному, а не читает все данные и выводит первые 200 строк :-)

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

Блокировку можно проверить, просто выполнив запрос:
select top 100 * from БольшаяТаблица (nolock)
13 фев 14, 17:51    [15564235]     Ответить | Цитировать Сообщить модератору
 Re: анализ работы mssql +как снять дамп?  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4478
+ как-то странно...
Память(RAM): 32.0 Гб
Тип системы: 32-разрядная
13 фев 14, 18:05    [15564305]     Ответить | Цитировать Сообщить модератору
 Re: анализ работы mssql +как снять дамп?  [new]
Serg58
Member

Откуда:
Сообщений: 65
итак, запустили профайлер на получение запроса и результат. Называлось, что то вроде Batchstart и Batchend, как то так вроде бы...

последили минут 10...


Абсолютно все запросы обрабатываются менее 1 сек. Нашёл максимальный ответ, в котором запрос вернул немного больше 69 тысяч записей, он выполнялся менее секунды.

Так же почистили базу немного, из огромных таблиц(где было 50+млн записей) поудаляли чуть ли не больше половины....

Но тормоза не исчезли.


На данный момент у меня складывается впечатление, что тормозит софт на клиентских компах. Железо(и сервер и рабочие машины) отличные...а вот софт видимо кривой. Буду думать как этот момент диагностировать.
14 фев 14, 17:14    [15569558]     Ответить | Цитировать Сообщить модератору
 Re: анализ работы mssql +как снять дамп?  [new]
Serg58
Member

Откуда:
Сообщений: 65
SQL2008,
ставили сначала 64 битную, но софт рабочий не пошёл
14 фев 14, 17:57    [15569757]     Ответить | Цитировать Сообщить модератору
 Re: анализ работы mssql +как снять дамп?  [new]
Serg58
Member

Откуда:
Сообщений: 65
и снова я с продолжение проблемы)
немного разобрался, научился юзать SSMS, profiler...

вот дополнительная информация:

Размер базы данных: 28 Гб
Доступное место: 7702,65 МБ

Примеры таблиц в этой базе данных:
1 таблица:
Место занимаемое индексом: 3 499,430 MB
Число строк: 16697706
Пространство данных: 1 713,484 MB

2 таблица:
Место занимаемое индексом: 641,867 MB
Число строк: 11145057
Пространство данных: 568,648 MB

3 таблица:
Место занимаемое индексом: 1 815,047 MB
Число строк: 21556479
Пространство данных: 1 649,578 MB

это самые большие таблицы в базе.

Смотрю профайлером с фильтром Duration >300 за пару минут вывалило 37 запросов, вот процитирую парочку из них:
CPU | Reads | Writes | Duration
13650 | 5688999 | 391 | 14420
328 | 126596 | 0 | 336
796 | 462619 | 27 | 871
312 | 126604 | 0 | 328

Я пока не силён в анализе отчёта профайлера, поясните, пожалуйста, что значат столбцы?Так и не смог прогуглить описание столбцов.
Вот, например, самый первый пример, где CPU=13650 - это я обновил в клиентском ПО всю базу, откуда здесь взялись 391 Writes? Что то куда-то записалось, хотя я только читал базу, возможно лог моего обращения...?
Кстати, этот запрос в программе у меня отвечал ~14 секунд, что вообще печально...

Можно ли по данному отчёту профайлера сделать вывод, что в тормозах виноват ОГРОМНЫЙ размер БД?

И основной вопрос: как можно разделить БД на 2? в следующем виде:
1.основная БД (содержится минимальное количество данных необходимых для работы)
2.архивная БД (содержится ВЕСЬ массив данных и периодически(день/неделя) накатываются обновления с основной БД).
В теории: мы создаём полный бекап БД и разворачиваем его на архивном сервере, а потом в основной БД оставляем только свежие записи необходимые для работы - и периодически как-то их синхронизируем...(вопрос вот как эту синхронизацию реализовать).

Попутно вопрос: можно ли накатывать log backup(журнал транзакций) но исключая команды удаления?
Как бы было замечательно: мы делаем основную БД маленькой, в ней записываем журнал транзакции, и периодически накатываем его на архивную БД, но только с целью добавления и модификации записей, БЕЗ удаления строк.

или есть какие-нибудь альтернативные решения? что бы и основная БД была маленькой, и архивная была полной с синхронизацией последних изменений из основной?
25 мар 14, 11:26    [15782219]     Ответить | Цитировать Сообщить модератору
 Re: анализ работы mssql +как снять дамп?  [new]
Glory
Member

Откуда:
Сообщений: 104751
Serg58
Я пока не силён в анализе отчёта профайлера, поясните, пожалуйста, что значат столбцы?Так и не смог прогуглить описание столбцов.

"Прогуглите" хелп продукта

Serg58
Вот, например, самый первый пример, где CPU=13650 - это я обновил в клиентском ПО всю базу, откуда здесь взялись 391 Writes?

А сколько должно быть Writes ? 392 ? 390 ? 10 ? 1000 ?

Serg58
Что то куда-то записалось, хотя я только читал базу, возможно лог моего обращения...?

Вы что не видите текст запроса ? Вы не включили его в трассу ?

Serg58
Кстати, этот запрос в программе у меня отвечал ~14 секунд, что вообще печально...

И как выглядит "этот запрос" ?

Serg58
Можно ли по данному отчёту профайлера сделать вывод, что в тормозах виноват ОГРОМНЫЙ размер БД?

Разумеется, если в таблицах не будет ни одной записи, то абсолютно все запросы будут выполняться быстро

Serg58
И основной вопрос: как можно разделить БД на 2? в следующем виде:

Создать базы.
Написать программный код для поддержания состояния этих баз.

Serg58
Попутно вопрос: можно ли накатывать log backup(журнал транзакций) но исключая команды удаления?

Нет.

Serg58
или есть какие-нибудь альтернативные решения?

Возможно, репликация.
25 мар 14, 11:35    [15782291]     Ответить | Цитировать Сообщить модератору
 Re: анализ работы mssql +как снять дамп?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31984
Serg58
CPU | Reads | Writes | Duration
13650 | 5688999 | 391 | 14420
328 | 126596 | 0 | 336
796 | 462619 | 27 | 871
312 | 126604 | 0 | 328

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

Вот, например: http://msdn.microsoft.com/ru-ru/library/ms176010.aspx

Serg58
Вот, например, самый первый пример, где CPU=13650 - это я обновил в клиентском ПО всю базу, откуда здесь взялись 391 Writes? Что то куда-то записалось, хотя я только читал базу, возможно лог моего обращения...?
Например, серверу понадобилось записать промежуточные результаты. Или там пишется в временную таблицу.
Serg58
Можно ли по данному отчёту профайлера сделать вывод, что в тормозах виноват ОГРОМНЫЙ размер БД?
Нет.
Serg58
Кстати, этот запрос в программе у меня отвечал ~14 секунд, что вообще печально...
Так смотрите этот запрос. Анализируйте запрос, план и т.д.
Serg58
И основной вопрос: как можно разделить БД на 2? в следующем виде:
Ну, это второй вопрос, с производительностью это не связано. Мешать в кучу вопросы неудобно, будете путаться.
Serg58
Попутно вопрос: можно ли накатывать log backup(журнал транзакций) но исключая команды удаления?
Нет.
25 мар 14, 11:51    [15782386]     Ответить | Цитировать Сообщить модератору
 Re: анализ работы mssql +как снять дамп?  [new]
Serg58
Member

Откуда:
Сообщений: 65
Спасибо за ссылку на описание столбцов.

Вот, давайте, разберём конкретный запрос, самый первый который я цитировал выше. Вернее я разберу, а вы поправьте меня, если что где-то не так:

1.Пользователь нажимает в программе кнопку "обновить базу". Начинаем отсчёт времени: 0 сек.
2.в сетевом сниффере(на компьютере пользователя) я вижу, что запрос ушёл на сервер: 0 сек.
3.в профайлере(сервер) я вижу пришедший запрос: 0-1 сек
текст запроса:
запрос
exec usp_GetTasks @act=1

у него:
профайлер
CPU: 13650
Reads: 13650
Reads: 5688999
Writes: 391
Duration: 14420


4.на компьютере пользователя в снифере я вижу что началась закачка с сервера. Спустя 15 секунд!
5.закачка окончена, программа отобразила БД после обновления. 19 секунда.(закачка идёт ~4-5 сек)
Итого с момента нажатия на кнопку до отображения обновленной БД проходит 19 сек.

========================

Я выполняю запрос процитированный выше в SSMS, он возвращает 46272 сроки, выполняется ~15 секунд.


Вот я не могу понять....откуда берутся 15 секунд, если Duration: 14420 (это как бы 0,0144 секунды)?
Понимаю, что это объясняется просто....но не могу догнать этот момент.
25 мар 14, 12:21    [15782582]     Ответить | Цитировать Сообщить модератору
 Re: анализ работы mssql +как снять дамп?  [new]
Glory
Member

Откуда:
Сообщений: 104751
Serg58
3.в профайлере(сервер) я вижу пришедший запрос: 0-1 сек
текст запроса:
запрос
exec usp_GetTasks @act=1

у него:
профайлер
CPU: 13650
Reads: 13650
Reads: 5688999
Writes: 391
Duration: 14420

И вы смотрели, какие команды выполняются в процедуре usp_GetTasks при входном параметре @act=1 ?

Serg58
4.на компьютере пользователя в снифере я вижу что началась закачка с сервера. Спустя 15 секунд!

Duration: 14420 - процедура отработала и вернула управление/результат клиенту. Что неправильно то ?

Serg58
Вот я не могу понять....откуда берутся 15 секунд, если Duration: 14420 (это как бы 0,0144 секунды)?

In SQL Server 2005 or later, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds.
25 мар 14, 12:26    [15782639]     Ответить | Цитировать Сообщить модератору
 Re: анализ работы mssql +как снять дамп?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Serg58,

В профайлере Duration в милисекундах, в файлах трассы в микросекундах (о, MS=))
Так что 14420 мс - это как раз примерно 15 с.

Анализ конкретного медленного запросу нужно начинать с просмотра действительного плана выполнения, получите его в том же профайлере, либо включив в SSMS кнопку "Include Actual Execution Plan". (П.С. Если решите выложить действительный план сюда, то выкладывайте как файл, а не как картинку).
25 мар 14, 12:29    [15782675]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить