Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
hichnik_andrei
Member

Откуда:
Сообщений: 29
alexeyvg
hichnik_andrei
Обслуживание базы происходит таким образом
Аааа! Там Шринк делается???


Да
12 дек 19, 22:03    [22039186]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
hichnik_andrei
Member

Откуда:
Сообщений: 29
Yasha123
да у вас блокировок воз и тележка,
вот почему рестарт и помогает.
надо смотреть, кто таблицы целиком лочит.
например, апдэйты миллиона строк на стейтмент


Как-нибудь точнее можно?
12 дек 19, 22:05    [22039188]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31331
hichnik_andrei
alexeyvg
пропущено...
Аааа! Там Шринк делается???


Да
Шринк вообще говоря операция аварийная, как, например, рестор базы.

Если вы его делаете для файлов базы, то это замедляет работу базы из за сильной фрагментации.
Если вы его делаете для файлов лога, то это замедляет работу базы из за необходимости увеличивать лог после уменьшения.
Особенно печально, если для файла установлено маленькое автоувеличение, например, 1 мб - тогда этом может вызывать заметное замедление.
Идеально - установить достаточные размеры файлов базы, и шринк не делать.
12 дек 19, 22:08    [22039190]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1351
hichnik_andrei,

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

покажите на всякий случай
select /*[name],*/ [is_auto_shrink_on], [is_auto_update_stats_on], [is_auto_update_stats_async_on], [is_parameterization_forced], [log_reuse_wait_desc] from sys.databases;


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

Сообщение было отредактировано: 12 дек 19, 22:10
12 дек 19, 22:09    [22039191]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
hichnik_andrei
Member

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

Да шаг 1Мб, сколько лучше сделать?

К сообщению приложен файл. Размер - 27Kb
12 дек 19, 22:14    [22039193]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7663
Можно включить оценку унаследованную кратности (legacy cardinality estimation) для базы и понаблюдать. Если поможет - то так и оставить.
12 дек 19, 22:18    [22039197]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
hichnik_andrei
Member

Откуда:
Сообщений: 29
[quot felix_ff#22039191]hichnik_andrei,

покажите на всякий случай
select /*[name],*/ [is_auto_shrink_on], [is_auto_update_stats_on], [is_auto_update_stats_async_on], [is_parameterization_forced], [log_reuse_wait_desc] from sys.databases;


К сообщению приложен файл. Размер - 45Kb
12 дек 19, 22:19    [22039198]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1351
hichnik_andrei,

давайте уж в догонку тогда подробности глянем:
select [counter_name], [instance_name], [cntr_value] from sys.dm_os_performance_counters where [object_name] = 'SQLServer:Locks' and [counter_name] in ('Lock Requests/sec', 'Lock Timeouts/sec', 'Number of Deadlocks/sec', 'Lock Waits/sec')
union
select [counter_name], [instance_name], [cntr_value] from sys.dm_os_performance_counters where [object_name] = 'SQLServer:Databases' and [counter_name] in ('Log Flush Waits/sec', 'Log Flush Wait Time')
union
select [counter_name], [instance_name], [cntr_value] from sys.dm_os_performance_counters where [object_name] = 'SQLServer:Wait Statistics'
order by [counter_name], [instance_name], [cntr_value] desc
12 дек 19, 22:21    [22039199]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
hichnik_andrei
Member

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

пару мин и сброшу, бэкап по расписанию делается
12 дек 19, 22:26    [22039201]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
hichnik_andrei
Member

Откуда:
Сообщений: 29
felix_ff
hichnik_andrei,

select [counter_name], [instance_name], [cntr_value] from sys.dm_os_performance_counters where [object_name] = 'SQLServer:Locks' and [counter_name] in ('Lock Requests/sec', 'Lock Timeouts/sec', 'Number of Deadlocks/sec', 'Lock Waits/sec')
union
select [counter_name], [instance_name], [cntr_value] from sys.dm_os_performance_counters where [object_name] = 'SQLServer:Databases' and [counter_name] in ('Log Flush Waits/sec', 'Log Flush Wait Time')
union
select [counter_name], [instance_name], [cntr_value] from sys.dm_os_performance_counters where [object_name] = 'SQLServer:Wait Statistics'
order by [counter_name], [instance_name], [cntr_value] desc


Модератор: Вложение удалено.


Модератор: Вложение удалено.


Сообщение было отредактировано: 13 дек 19, 01:36
12 дек 19, 22:36    [22039205]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1351
hichnik_andrei,

вы если в текущий (прямо сейчас) момент проблемы с производительностью наблюдаете

то используйте или процедуру sp_WhoIsActive она информативнее
или прям запрос выполните:
  select [session_id], [exec_context_id], [blocking_session_id], [blocking_exec_context_id], [wait_duration_ms], [wait_type], [resource_description] 
  from sys.dm_os_waiting_tasks 
  where [blocking_session_id] is not null 
     or [wait_duration_ms] > 10000


если возвращаются строки с каким либо достаточно долгим wait_type надо предметно разбираться что и кого сессии ждут.
12 дек 19, 22:36    [22039206]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1351
hichnik_andrei,

картинка для китайцев. вы сами можете разобрать что там нарисованно? :)
12 дек 19, 22:37    [22039208]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
hichnik_andrei
Member

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

редактировал уже)))

К сообщению приложен файл. Размер - 105Kb
12 дек 19, 22:39    [22039210]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
hichnik_andrei
Member

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

К сообщению приложен файл. Размер - 128Kb
12 дек 19, 22:40    [22039211]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
hichnik_andrei
Member

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

К сообщению приложен файл. Размер - 146Kb
12 дек 19, 22:40    [22039212]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
hichnik_andrei
Yasha123
да у вас блокировок воз и тележка,
вот почему рестарт и помогает.
надо смотреть, кто таблицы целиком лочит.
например, апдэйты миллиона строк на стейтмент


Как-нибудь точнее можно?

статистику соберите хотя бы за сутки,
т.е. не рестарьте сервер каждый час.
но даже за короткий период у вас на первом месте LCK_M_IX.
значит, или на страницы, или на таблицы целиком ждут IХ.
это означает, что кто-то апдэйтит тучу строк за 1 стэйтмент,
раз там уже чей-то Х
12 дек 19, 23:44    [22039252]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31331
hichnik_andrei
Да шаг 1Мб, сколько лучше сделать?

Картинка с другого сайта.
Шаг 1 Гб, нормально.
12 дек 19, 23:45    [22039253]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31331
hichnik_andrei,

Счётчики (и вообще результаты запросов) лучше копировать из SSMS, и выкладывать в таблице CSV=t и в спойлере:
+
Lock Requests/sec _Total 52260990
Lock Requests/sec AllocUnit 1
Lock Requests/sec Application 250
Lock Requests/sec Database 18206841
Lock Requests/sec Extent 7874
Lock Requests/sec File 742
Lock Requests/sec HoBT 6959
Lock Requests/sec Key 18109556
Lock Requests/sec Metadata 7977709
Lock Requests/sec Object 7903269
Lock Requests/sec OIB 0
Lock Requests/sec Page 38203
Lock Requests/sec RID 9586
Lock Requests/sec RowGroup 0
Lock Timeouts/sec _Total 417
Lock Timeouts/sec AllocUnit 0
Lock Timeouts/sec Application 0
Lock Timeouts/sec Database 0
Lock Timeouts/sec Extent 0
Lock Timeouts/sec File 0
Lock Timeouts/sec HoBT 0
Lock Timeouts/sec Key 413
Lock Timeouts/sec Metadata 2
Lock Timeouts/sec Object 2
Lock Timeouts/sec OIB 0
Lock Timeouts/sec Page 0
Lock Timeouts/sec RID 0
Lock Timeouts/sec RowGroup 0
Lock waits Average wait time (ms) 0
Lock waits Cumulative wait time (ms) per second 0
Lock waits Waits in progress 0
Lock waits Waits started per second 0
Lock Waits/sec _Total 727
Lock Waits/sec AllocUnit 0
Lock Waits/sec Application 0
Lock Waits/sec Database 16
Lock Waits/sec Extent 0
Lock Waits/sec File 0
Lock Waits/sec HoBT 0
Lock Waits/sec Key 7
Lock Waits/sec Metadata 10
Lock Waits/sec Object 694
Lock Waits/sec OIB 0
Lock Waits/sec Page 0
Lock Waits/sec RID 0
Lock Waits/sec RowGroup 0
Log buffer waits Average wait time (ms) 0
Log buffer waits Cumulative wait time (ms) per second 0
Log buffer waits Waits in progress 0
Log buffer waits Waits started per second 0
Log Flush Wait Time _Total 6809
Log Flush Wait Time db1 4
Log Flush Wait Time db2 0
Log Flush Wait Time distribution 1278
Log Flush Wait Time DWConfiguration 7
Log Flush Wait Time DWDiagnostics 0
Log Flush Wait Time DWH_STAGE 5
Log Flush Wait Time DWQueue 0
Log Flush Wait Time LibRusEc 5
Log Flush Wait Time LinkedIn 6
Log Flush Wait Time master 1944
Log Flush Wait Time MLS 0
Log Flush Wait Time model 0
Log Flush Wait Time msdb 3486
Log Flush Wait Time mssqlsystemresource 0
Log Flush Wait Time ReportServer 2
Log Flush Wait Time ReportServerTempDB 1
Log Flush Wait Time SSISDB 4
Log Flush Wait Time tempdb 2
Log Flush Wait Time test 19
Log Flush Wait Time test_mydb1 2
Log Flush Wait Time TestBackupRestore 2
Log Flush Wait Time TestPartition 0
Log Flush Wait Time Tfs_DefaultCollection 34
Log Flush Wait Time user_sysres 8
Log Flush Waits/sec _Total 14992
Log Flush Waits/sec db1 3
Log Flush Waits/sec db2 3
Log Flush Waits/sec distribution 3506
Log Flush Waits/sec DWConfiguration 3
Log Flush Waits/sec DWDiagnostics 2
Log Flush Waits/sec DWH_STAGE 2
Log Flush Waits/sec DWQueue 2
Log Flush Waits/sec LibRusEc 4
Log Flush Waits/sec LinkedIn 2
Log Flush Waits/sec master 6740
Log Flush Waits/sec MLS 3
Log Flush Waits/sec model 5
Log Flush Waits/sec msdb 4646
Log Flush Waits/sec mssqlsystemresource 0
Log Flush Waits/sec ReportServer 3
Log Flush Waits/sec ReportServerTempDB 2
Log Flush Waits/sec SSISDB 4
Log Flush Waits/sec tempdb 18
Log Flush Waits/sec test 26
Log Flush Waits/sec test_mydb1 3
Log Flush Waits/sec TestBackupRestore 3
Log Flush Waits/sec TestPartition 2
Log Flush Waits/sec Tfs_DefaultCollection 8
Log Flush Waits/sec user_sysres 2
Log write waits Average wait time (ms) 0
Log write waits Cumulative wait time (ms) per second 0
Log write waits Waits in progress 0
Log write waits Waits started per second 0
Memory grant queue waits Average wait time (ms) 0
Memory grant queue waits Cumulative wait time (ms) per second 0
Memory grant queue waits Waits in progress 0
Memory grant queue waits Waits started per second 0
Network IO waits Average wait time (ms) 0
Network IO waits Cumulative wait time (ms) per second 0
Network IO waits Waits in progress 0
Network IO waits Waits started per second 0
Non-Page latch waits Average wait time (ms) 0
Non-Page latch waits Cumulative wait time (ms) per second 0
Non-Page latch waits Waits in progress 0
Non-Page latch waits Waits started per second 0
Number of Deadlocks/sec _Total 0
Number of Deadlocks/sec AllocUnit 0
Number of Deadlocks/sec Application 0
Number of Deadlocks/sec Database 0
Number of Deadlocks/sec Extent 0
Number of Deadlocks/sec File 0
Number of Deadlocks/sec HoBT 0
Number of Deadlocks/sec Key 0
Number of Deadlocks/sec Metadata 0
Number of Deadlocks/sec Object 0
Number of Deadlocks/sec OIB 0
Number of Deadlocks/sec Page 0
Number of Deadlocks/sec RID 0
Number of Deadlocks/sec RowGroup 0
Page IO latch waits Average wait time (ms) 1
Page IO latch waits Cumulative wait time (ms) per second 0
Page IO latch waits Waits in progress 0
Page IO latch waits Waits started per second 3
Page latch waits Average wait time (ms) 0
Page latch waits Cumulative wait time (ms) per second 0
Page latch waits Waits in progress 0
Page latch waits Waits started per second 0
Thread-safe memory objects waits Average wait time (ms) 0
Thread-safe memory objects waits Cumulative wait time (ms) per second 0
Thread-safe memory objects waits Waits in progress 0
Thread-safe memory objects waits Waits started per second 0
Transaction ownership waits Average wait time (ms) 0
Transaction ownership waits Cumulative wait time (ms) per second 0
Transaction ownership waits Waits in progress 0
Transaction ownership waits Waits started per second 0
Wait for the worker Average wait time (ms) 0
Wait for the worker Cumulative wait time (ms) per second 0
Wait for the worker Waits in progress 0
Wait for the worker Waits started per second 0
Workspace synchronization waits Average wait time (ms) 0
Workspace synchronization waits Cumulative wait time (ms) per second 0
Workspace synchronization waits Waits in progress 0
Workspace synchronization waits Waits started per second 0
12 дек 19, 23:53    [22039255]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5231
hichnik_andrei
На сервере стоит :Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)

не помешает поставить хотя бы второй сервис-пак
https://sqlserverbuilds.blogspot.com/#sql2016x
13 дек 19, 00:38    [22039270]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1351
Yasha123
hichnik_andrei
пропущено...


Как-нибудь точнее можно?

статистику соберите хотя бы за сутки,
т.е. не рестарьте сервер каждый час.
но даже за короткий период у вас на первом месте LCK_M_IX.
значит, или на страницы, или на таблицы целиком ждут IХ.
это означает, что кто-то апдэйтит тучу строк за 1 стэйтмент,
раз там уже чей-то Х


у него там их всего 40 штук и общее время ожидания было 26 секунд на все IX.
по первому скрину я бы сказал что система особо ничего и не ждет.

А вот по счетчикам можно предположить следующее:

в топах запросов блокировок два типа: Metadata и Object
на metadata причем приходится порядка 38% процентов запросов на блокировки.
на базе включен auto_update_statistics, предположительно в бд преобладает в основном Ad-Hoc нагрузка что дает такую значительную разницу в типах блокировок на метаданные. (вообще метаданных хренова туча может лочиться но чаще всего лично мне встречался stats)

на втором месте object и только после него Key следовательно достаточно часто запросы идут с локом всей кучи/кластера.
RID блокировок мало Key много, в целом по базе преобладают кластеризованные таблицы.
таймауты в основном по блокировкам ключей правда может там вообще nowait типы я с счетчиком промазал немного.


итого имхо:
по видимому у вас в бд возникают достаточно часто сканирующие запросы при этом скорее всего это ad-hoc.
Вам необходимо посмотреть степень преобладающей нагрузки на базу:
with x as (
    select
	      pool_id,
		  cacheobjtype,
		  objtype,
		  size_in_bytes,
		  refcounts,
		  usecounts,
	      sum([size_in_bytes]) over (partition by pool_id, cacheobjtype, objtype order by 1/0) * 100 / sum([size_in_bytes]) over () as [percentage]
   from sys.dm_exec_cached_plans
)
select pool_id, 
	   cacheobjtype,
	   objtype,
       sum(size_in_bytes) as [bytes],
	   sum(refcounts) as refs,
	   count(1) as cnt,
	   sum(usecounts) as [uses],
	   max(percentage) as [percentage]
from x
group by pool_id, cacheobjtype, objtype


если процентно преобладает Adhoc посмотрите в сторону
sp_configure 'optimize for ad hoc workloads'

если 0 можно поставить в 1

необходимо предметно смотреть почему так часто вызываеются блокировки объектов вместо ключей => недостающие/неоптимальные индексы на таблицах

если ad-hod нагрузка значительно преобладает над Proc, и вы знаете что на бд много пишушей нагрузки то auto_update_stats на базе можно или перевести в асинхронную модель или отключить и вешать отдельный джоб на обновление статистики.

Сообщение было отредактировано: 13 дек 19, 01:39
13 дек 19, 01:34    [22039290]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31331
felix_ff
если ad-hod нагрузка значительно преобладает над Proc, и вы знаете что на бд много пишушей нагрузки то auto_update_stats на базе можно или перевести в асинхронную модель или отключить и вешать отдельный джоб на обновление статистики.
У ТС в плане обслуживания обновление статистики; конечно, auto_update_stats нужно отключить.
13 дек 19, 09:52    [22039388]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
felix_ff

у него там их всего 40 штук и общее время ожидания было 26 секунд на все IX.
по первому скрину я бы сказал что система особо ничего и не ждет.

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

а висели все, т.к. все лезут в основную таблицу,
а в ней мой нынешний начальничег апдэйтил миллионы строк одним стэйтментом.
так что там был escalation до таблицы через несколько секунд его деятельности.

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

felix_ff

необходимо предметно смотреть почему так часто вызываеются блокировки объектов вместо ключей => недостающие/неоптимальные индексы на таблицах

вот именно.
причем индексы могут даже и быть, но если способные граждане миллионы разом апдэйтят,
никакие индексы не спасут
13 дек 19, 10:45    [22039442]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
hichnik_andrei
Member

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

К сообщению приложен файл. Размер - 7Kb
13 дек 19, 15:57    [22039890]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
но это же 1С не хватило памяти, а не серверу.
а вы еще и недовольны, что сервер мало отъел.
13 дек 19, 16:03    [22039904]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! Sql не использует вместо 34гб только 10-12 Гб оперативной памяти.  [new]
Sergey Sizov
Member

Откуда:
Сообщений: 1525
hichnik_andrei,
ну вот и причина тормозов. Только к sql это не имеет ни малейшего отношения. Ибо это проблемы сервера 1с.
13 дек 19, 16:03    [22039906]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить