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

Откуда:
Сообщений: 132
Всем доброго времени суток!

Имеется БД в которую пишут несколько клиентов используя OLEDB.
На sql 2014 - эта БД единственная.
Содержимое БД смотрим через своё приложение - OLEDB.

При выполнении запроса просмотра с несколькими JOIN результат получаем в течении времени "R".
Если в это время начинается запись новых данных, то время выдачи результата увеличивалось до бесконечности(прерывали выполнение приложения).

SQL-серверу уменьшили максимальный размер памяти с максимума, до 85%.
Результаты стали выдаваться, но время "R" - увеличилось в 4-5 раз.

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

После завершения работы SELECT - добавление делалось мгновенно.

До этого БД была на SQL 2008 и таких проблем замечено не было.

Приложение для просмотра запускаем на той же машине, на которой установлен SQL сервер.

Куда смотреть и что почитать для исправления задержек?

Спасибо.
23 сен 16, 12:39    [19700036]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
TaPaK
Member

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

вы открыли новую главу - блокировки :)
23 сен 16, 12:43    [19700057]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
blonduser
Member

Откуда:
Сообщений: 132
С блокировкой вставки все понятно.
Но вопрос был в том что в разы замедляется SELECT.

При том что на 2008 такого не наблюдается.
23 сен 16, 14:36    [19700766]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
blonduser
С блокировкой вставки все понятно.
Но вопрос был в том что в разы замедляется SELECT.

При том что на 2008 такого не наблюдается.

а блокировки на select по вашему не влияют? если что то поменялось - значить в 2014 у вас другая настройка. Уровень изоляции по умолчанию какой? какой в запросах? Отключение эскалации?
23 сен 16, 14:38    [19700786]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
blonduser
С блокировкой вставки все понятно.
Но вопрос был в том что в разы замедляется SELECT.

При том что на 2008 такого не наблюдается.


в 2014 по сравнению с 2008 изменился оптимизатор (не так координально как в 2005 по сравнения с 2000 но все же), смотрите планы.
Значит запросы кривые в плане учета индексов.
Это если конечно вы правильно решили, что блокировки тут не причем.
23 сен 16, 14:46    [19700847]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
blonduser
после запуска SELECT все операции добавления ожидают своего времени не потребляя ресурсов процессора.

Куда смотреть и что почитать для исправления задержек?

Ну так и посмотрите чего они ждут.
sys.dm_os_waiting_tasks
24 сен 16, 02:04    [19703554]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
blonduser
Member

Откуда:
Сообщений: 132
WarAnt
в 2014 по сравнению с 2008 изменился оптимизатор (не так координально как в 2005 по сравнения с 2000 но все же), смотрите планы.
Значит запросы кривые в плане учета индексов.
Это если конечно вы правильно решили, что блокировки тут не причем.


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

На виртуалку установил SQL 2014 размер диска 100 ГБ, что бы в базу никто не писал.
Скопировал базу размер 25 ГБ (около 5 млн. записей). Осталось свободное место около 50 ГБ.

Запустил на выполнение запрос (представлю его схематически)

SELECT *
FROM TABLE1
LEFT JOIN TABLE2 ...
...
LEFT JOIN TABLE_N
WHERE TABLE2.field1 LIKE '%a%'
ORDER BY TABLE1.field0


Время выполнения запроса пару минут. Результат 60 записей.

Запустил тот же запрос, но убрал сортировку!!!
Запрос выполнялся больше двух часов. tempdb - увеличилась до 50 ГБ, т.е. заняла все свободное место.
Вместо результата получил сообщение об ошибки - "Could not allocate space for object..." Место закончилось.

При том что на SQL 2008 результат получен в обоих случаях и за реальное время.

Как можно это объяснить? Под нагрузкой даже не стал ставить эксперимент.

P.S. - запросы я переделал. Но фак очевиден.
29 сен 16, 13:23    [19723407]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
blonduser
WarAnt
в 2014 по сравнению с 2008 изменился оптимизатор (не так координально как в 2005 по сравнения с 2000 но все же), смотрите планы.
Значит запросы кривые в плане учета индексов.
Это если конечно вы правильно решили, что блокировки тут не причем.

Как можно это объяснить?
29 сен 16, 22:46    [19725977]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
blonduser
Member

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

Планы это хорошо.
Но под каждую версию SQL-сервера создавать другие индексы для одной и той же структуры БД это перебор!

После добавления индексов согласно плану изменить в WHERE порядок условий, то скорость выполнения запроса изменяется в 10.
Тут уже никакие планы не помогают.

Еще раз повторюсь - под 2008 ничего подобного не наблюдается.
5 окт 16, 13:37    [19746055]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
komrad
Member

Откуда:
Сообщений: 5249
blonduser
Еще раз повторюсь - под 2008 ничего подобного не наблюдается.


у базы какой compatibility level?

select name,compatibility_level from sys.databases  where name='БАЗА'
5 окт 16, 13:46    [19746090]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
Владислав Колосов
Member

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

не проводите тесты на виртуалках, если не знаете как их настроить для работы SQL сервера.
5 окт 16, 14:56    [19746495]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
blonduser
Member

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

У меня установлен 100.
Если его вернуть на 120, то скорость выполнения запросов становится почти одинаковой(разница 1-2 секунды).
А вот если выполнить тот же самый запрос без сортировки, то он уходит в "астрал" - прождал 15 минут и отменил запрос.

Установил опять сотню. запрос выполнился за реальное время.
5 окт 16, 15:39    [19746728]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
blonduser
Member

Откуда:
Сообщений: 132
Владислав Колосов,

У меня и 2008 на виртуалке.
Но проблемы на физической машине такие же.

Поделитесь как правильно настроить виртуалку для работы с SQL-сервером?
5 окт 16, 15:49    [19746787]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
Владислав Колосов
Member

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

поищите документ с названием "Best Practices for Virtualizing and Managing SQL Server".
5 окт 16, 16:35    [19747021]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
o-o
Guest
blonduser
После добавления индексов согласно плану изменить в WHERE порядок условий, то скорость выполнения запроса изменяется в 10.

лабуда какая-то.
от перестановки условий в WHERE вообще ничего не меняется
5 окт 16, 21:03    [19748127]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
o-o
Guest
blonduser
А вот если выполнить тот же самый запрос без сортировки, то он уходит в "астрал" - прождал 15 минут и отменил запрос.

есть ли в запросе оконные функции с order by,
где соответствующий индекс имеет *обратный* порядок сортировки?
5 окт 16, 21:06    [19748132]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
blonduser
Судя по всему, оптимизатор для SQL 2014 писал "однорукий семи-жоп".
blonduser
Планы это хорошо.
Но под каждую версию SQL-сервера создавать другие индексы для одной и той же структуры БД это перебор!
С таким аттитюдом, я бы вам посоветовал сменить работу. Идите сразу в MS работать! Вы бы им оптимизатор переписали. Если конечно поняли бы как он в принципе работает. Пока что вы даже план то прочитать не можете.

blonduser
После добавления индексов согласно плану изменить в WHERE порядок условий, то скорость выполнения запроса изменяется в 10.
Смысла в вашем высказывании нет никакого.
blonduser
Тут уже никакие планы не помогают.
Вы не можете по плану понять где сервер ошибается в оценках?
blonduser
Еще раз повторюсь - под 2008 ничего подобного не наблюдается.
Жизнь не стоит на месте. Вы еще 2000-ый себе поставьте.
6 окт 16, 00:25    [19748630]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
Yayaadmin
Guest
o-o
blonduser
А вот если выполнить тот же самый запрос без сортировки, то он уходит в "астрал" - прождал 15 минут и отменил запрос.

есть ли в запросе оконные функции с order by,
где соответствующий индекс имеет *обратный* порядок сортировки?


В таком случае он просто бы не использовал параллелизм (если это функции ранжирования). Если же аналитика тогда он активно исп. spool в tempdb а не в памяти если указан range, а не row. Или есть еще аспекты?
6 окт 16, 08:46    [19748952]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
Yayaadmin
Guest
o-o
blonduser
А вот если выполнить тот же самый запрос без сортировки, то он уходит в "астрал" - прождал 15 минут и отменил запрос.

есть ли в запросе оконные функции с order by,
где соответствующий индекс имеет *обратный* порядок сортировки?


Нашел!
SELECT CustomerID, SalesOrderID,
SUM(TotalDue) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID)
AS RunningTotal
FROM Sales.SalesOrderHeader;

SELECT CustomerID, SalesOrderID,
SUM(TotalDue) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID
ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM Sales.SalesOrderHeader;

The execution plans report that the relative cost for each query is 50%. Take a look at
the logical reads reported in the Messages tab and shown in Figure 6-5. Each query read
689 pages, which is the total number of pages in the table. Query 1 took 188,791 logical
reads from a work table, while Query 2 took 0 logical reads. Why the difference? The work
table in Query 1 was created in tempdb, and the work table in Query 2 was created in
memory.
6 окт 16, 09:07    [19749025]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
o-o
Guest
Yayaadmin
o-o
пропущено...

есть ли в запросе оконные функции с order by,
где соответствующий индекс имеет *обратный* порядок сортировки?


Нашел!
SELECT CustomerID, SalesOrderID,
SUM(TotalDue) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID)
AS RunningTotal
FROM Sales.SalesOrderHeader;

SELECT CustomerID, SalesOrderID,
SUM(TotalDue) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID
ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM Sales.SalesOrderHeader;

The execution plans report that the relative cost for each query is 50%. Take a look at
the logical reads reported in the Messages tab and shown in Figure 6-5. Each query read
689 pages, which is the total number of pages in the table. Query 1 took 188,791 logical
reads from a work table, while Query 2 took 0 logical reads. Why the difference? The work
table in Query 1 was created in tempdb, and the work table in Query 2 was created in
memory.

нет, я не про этот случай.
у него же код и на 2008-ом идет, а вш нет.
---
есть другой случай,
когда есть несовпадение порядка в order by оконной функции
и в индексе.
обычно индекс можно пройти и в обратном порядке,
но в данном случае это не происходит..пока не добавишь финальный order by.
ТС же пишет, что с order by у него быстрее
6 окт 16, 09:57    [19749197]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
o-o,

order by у него скорее всего уходит в tempdb, без него пытается разгрести всё в памяти и вполне вероятно что памяти ему мало(можно увидеть в ожиданиях), но ТС упёртый и ничего смотреть не будет :)
6 окт 16, 10:01    [19749208]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
o-o
Guest
to Yayaadmin
example
читать с места
book
Another curious case where descending indexes are useful is with window functions
that have both a window partition clause and a window order clause

to TaPaK
обычно за такой упертостью скрывается неспособность.
ну не умеет он смотреть планы, а признаться не может.
проще 2 недели трубить о чужой неспособности, MS в частности.
о своем уровне знаний он уже поведал вот этим вот:
blonduser
После добавления индексов согласно плану изменить в WHERE порядок условий, то скорость выполнения запроса изменяется в 10.
6 окт 16, 10:08    [19749246]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
Yayaadmin
Guest
o-o
to Yayaadmin
example
читать с места
book
Another curious case where descending indexes are useful is with window functions
that have both a window partition clause and a window order clause

to TaPaK
обычно за такой упертостью скрывается неспособность.
ну не умеет он смотреть планы, а признаться не может.
проще 2 недели трубить о чужой неспособности, MS в частности.
о своем уровне знаний он уже поведал вот этим вот:
blonduser
После добавления индексов согласно плану изменить в WHERE порядок условий, то скорость выполнения запроса изменяется в 10.


Прочитал, тык я об этом же и говорил, один из немногих случаев когда order by может увеличить скорость выполнения запроса. Да, если индекс читается в обратном порядке появиться сортирвка и она может исп. tempdb но чтобы до 50 гигов, не, не верю!
изменить в WHERE порядок условий, то скорость выполнения запроса изменяется в 10, возможно, если оптимизатор решил выбрать другой план основанный на какой нибудь оч древней сатистике, но в 10 раз, не, не верю!
6 окт 16, 10:37    [19749374]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
автор
изменить в WHERE порядок условий, то скорость выполнения запроса изменяется в 10, возможно, если оптимизатор решил выбрать другой план основанный на какой нибудь оч древней сатистике, но в 10 раз, не, не верю!

дурдом...
6 окт 16, 10:39    [19749384]     Ответить | Цитировать Сообщить модератору
 Re: Падение скорости выполнения запроса SELECT при добавлении записей на SQL 2014  [new]
Yayaadmin
Guest
TaPaK
автор
изменить в WHERE порядок условий, то скорость выполнения запроса изменяется в 10, возможно, если оптимизатор решил выбрать другой план основанный на какой нибудь оч древней сатистике, но в 10 раз, не, не верю!

дурдом...


Ну почему же, я сам так пробовал делать (правда перед этим очистил процедурный кэш), оптимизатор выбрал другой индекс, где статистика была старее, вот и все. (индксы типа c1,c2,c3; c2,c1,c3; c1,c3,c2 и т.д.)
6 окт 16, 10:44    [19749401]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить