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

Откуда: Киев
Сообщений: 89
Доброе время суток, вопрос не нов, хотелось бы услышать мнения, в правильную сторону ли мыслю.

На сервере Windows Server 2008-м крутится MSSQL 2008, и по большому счёту всё, сервак для него. Всего оперативы 92 Гб.
Под SQL выделено 88 Гб. На другом серваке работает 1С 8.2, хранилищем данных для которого является наш MSSQL.

В общем память забита полностью (оно и понятно!), и данные начинаются писаться на физический диск, что не есть хорошо.

Каковы мои действия? СМотреть в sys.dm_os_buffer_descriptors, вычленять запросы, которые пришли из 1С, оптимизировать?
Добавить ещё памяти не будет решением?? Ведь сколько не добавляй - скул съест всё ;)

Прошу посоветовать.
27 окт 13, 23:39    [15038932]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
komrad
Member

Откуда:
Сообщений: 5300
Roul_Duke
В общем память забита полностью (оно и понятно!), и данные начинаются писаться на физический диск, что не есть хорошо.


какие данные?

размер БД 1С какой?
28 окт 13, 00:13    [15039030]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
Roul_Duke
Member

Откуда: Киев
Сообщений: 89
komrad,
Размер базы 470 Гб,

>>пишутся данные на диск
при выполнении тяжёлых 1С запросов, увеличивается значение Physical IO writes, извините сейчас не скажу в какой системной вьюхе мониторил.
Из этого и сделал вывод, что кэш забит и скул начинает писать данные на диск.
28 окт 13, 00:57    [15039155]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
aleks2
Guest
Roul_Duke
Из этого и сделал вывод, что кэш забит и скул начинает писать данные на диск.


Ты не поверишь, но MS SQL ВСЕГДА ПИШЕТ данные на диск.
Так уж он устроен - данные надо сохранить.
28 окт 13, 07:30    [15039400]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
Александр52
Member

Откуда: Кокосовые острова ส็็็็็
Сообщений: 5133
aleks2, уверен, вы поняли о чем идет речь)

попробуйте начать с оптимизации запросов, индексы, хинты и тд.
28 окт 13, 08:48    [15039466]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34102
Блог
Roul_Duke,

вообще-то нужно смотреть счетчик своп-файла...
28 окт 13, 10:12    [15039753]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Roul_Duke
данные начинаются писаться на физический диск, что не есть хорошо

Почему это нехорошо? Может, в это время у вас обеспечивается D из аббревиатуры ACID?

Данные пишутся на диск в следующих случаях:
— commit;
— checkpoint;
— lazy writing;
— eager writing;
— hash-операции, хэш-таблица которых не помещается в ОЗУ;
— sort-операции, для которых также недостаточно ОЗУ;
— spool-операции;
— ???

Вас-то какие пункты беспокоят?
28 окт 13, 11:33    [15040181]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
komrad
Member

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

давай посмотрим что с памятью :



+
	

select @@version

SELECT
 Case When datediff(ss,create_date,getdate()) >= 86400  
     Then Convert(VarChar(5), datediff(ss,create_date,getdate())/86400)   
     + ' days '   
     Else ''  
     End  
     + Convert(VarChar(8), DateAdd(Second, datediff(ss,create_date,getdate()), 0), 108) [SQL Uptime (days hh:mm:ss)]  	
	FROM sys.[databases] 
	WHERE [database_id] = 2  /*2=tempdb*/

	
		select 
		cpu_count/hyperthread_ratio [Physical CPUs],
		cpu_count [Available CPUs],
		ceiling(physical_memory_in_bytes/1024./1024.) [Physical Memory (MB)]
		from sys.dm_os_sys_info 

	select @@servername [SQL Instance],
			(select cntr_value/1024 from master..sysperfinfo where  counter_name = 'Total Server Memory (KB)') [Current Memory (MB)],
			(select cntr_value/1024 from master..sysperfinfo where  counter_name = 'Target Server Memory (KB)') [Target Memory (MB)]

	
	select description, value_in_use
	from master.sys.configurations  
	where name in ('min server memory (MB)','max server memory (MB)')
	
	select convert(money,cntr_value/60.) [Page Life Expectancy (min)] 
	FROM sys.dm_os_performance_counters 
	WHERE [object_name] like '%:Buffer Manager%' 
	AND counter_name='Page life expectancy'


	select 'Usage by DB:  ' + convert(varchar(20),convert(numeric(10,2),count(*)/128.)) +' (MB)' [Buffer Cache] 
		FROM sys.dm_os_buffer_descriptors
		
	SELECT top 5
		CASE database_id 
			WHEN 32767 THEN 'ResourceDb' 
			ELSE db_name(q.database_id) 
			END AS [Database],
		(select convert(numeric(10,2),count(*)/128.) from sys.dm_os_buffer_descriptors where database_id=q.database_id and is_modified=0) [Clean (MB)],
		(select convert(numeric(10,2),count(*)/128.) from sys.dm_os_buffer_descriptors where database_id=q.database_id and is_modified=1) [Dirty (MB)],
		(select convert(numeric(10,2),count(*)/128.) from sys.dm_os_buffer_descriptors where database_id=q.database_id) [Total (MB)]
	FROM sys.dm_os_buffer_descriptors q
	--where is_modified = 0 
	GROUP BY database_id
	ORDER BY 2 DESC


	select 'Usage by all objects (Grouped):  ' +  
			convert(varchar(20),ceiling(sum(cast(size_in_bytes as numeric(10,2)))/1024./1024.)) + ' (MB)' [Procedure Cache]
	FROM sys.dm_exec_cached_plans

	select  objtype [Object Type], 
			convert(numeric(10,2),sum(size_in_bytes/1024.)/1024.) [Size (MB)]
			, avg(usecounts) AS [Avg Use Count]
        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024./1024. AS [Total MB - USE Count 1]
        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
	from sys.dm_exec_cached_plans 
	group by objtype
	order by 2 desc

	select 'Usage by all objects (Detailed):' [Procedure Cache]
	SELECT db_name(sco.dbid) [Database],
			sco.cacheobjtype [Cache Object Type],
			sco.objtype [Object Type], 
			convert(numeric(10,2),sum(sco.pagesused)/128.) [Size MB],
			COUNT(1) [Count] 
	from master.sys.syscacheobjects sco
	WHERE sco.dbid<>32767
	GROUP BY sco.dbid, sco.cacheobjtype, sco.objtype
	ORDER BY 4 desc
28 окт 13, 11:38    [15040216]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
Roul_Duke
Member

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

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

6 days 18:41:21

Physical CPUsAvailable CPUsPhysical Memory (MB)
1 4 89408


SQL InstanceCurrent Memory (MB)Target Memory (MB)
DC-SQL01 80000 80000


descriptionvalue_in_use
Minimum size of server memory (MB)24000
Maximum size of server memory (MB)80000


Page Life Expectancy (min)
2,15

Buffer Cache
Usage by DB: 72305.13 (MB)

DatabaseClean (MB)Dirty (MB)Total (MB)
WorkDB69756.0985.09 69886.15
tempdb1209.111171.98 2372.84


Procedure Cache
Usage by all objects (Grouped): 7436 (MB)

Object TypeSize (MB)Avg Use CountTotal MB - USE Count 1Total Plans - USE Count 1
Prepared7114.819583518.77343712418
Adhoc284.58100547.507812820
Proc24.9631958.34375038
View10.30130.0000000
Check0.6550.2578128
Trigger0.2720.2031252
UsrTab0.2210.2187501


Usage by all objects (Detailed):
DatabaseCache Object TypeObject TypeSize MBCount
WorkDBCompiled PlanPrepared7070.6728349
WorkDBCompiled PlanAdhoc279.923965
personalCompiled PlanPrepared37.16303
msdbCompiled PlanProc18.6469
ReportServerCompiled PlanProc5.0621
masterCompiled PlanPrepared3.2016


Сообщение было отредактировано: 28 окт 13, 16:10
28 окт 13, 15:34    [15041764]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
Roul_Duke
Member

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

как я понимаю, кэш под себя весь забрал SQL Plan CACHESTORE_SQLCP.

SELECT name, type,
SUM(single_pages_kb + multi_pages_kb) AS MemoryKB
FROM sys.dm_os_memory_clerks
GROUP BY name, type
ORDER BY MemoryKB desc


/*
Results
name type MemoryKB
SQL Plans CACHESTORE_SQLCP 7819736
*/

и это вполне нормальная ситуация, так? Разбираться с запросами? Партицировать данные? Делать компрессию таблиц?
это мои шаги?
28 окт 13, 15:38    [15041791]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
Roul_Duke
Member

Откуда: Киев
Сообщений: 89
Roul_Duke,
Извините, sql выделено таки 80 =)
28 окт 13, 15:39    [15041795]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
Очень печальное значение Page Life Expectancy (min), такое впечатление что вся БД в кэш перезаливается. Что по счётчикам cache ratio hits для буферного и процедурного кэшей ?
28 окт 13, 16:09    [15041977]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
komrad
Member

Откуда:
Сообщений: 5300
stavgreengo
Очень печальное значение Page Life Expectancy (min), такое впечатление что вся БД в кэш перезаливается.


это сиюминутный показатель - может быть повезло с моментом и проходил большой table scan
28 окт 13, 18:46    [15042795]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
komrad
Member

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

как я понимаю, кэш под себя весь забрал SQL Plan CACHESTORE_SQLCP.

SELECT name, type,
SUM(single_pages_kb + multi_pages_kb) AS MemoryKB
FROM sys.dm_os_memory_clerks
GROUP BY name, type
ORDER BY MemoryKB desc


/*
Results
name type MemoryKB
SQL Plans CACHESTORE_SQLCP 7819736
*/

и это вполне нормальная ситуация, так? Разбираться с запросами? Партицировать данные? Делать компрессию таблиц?
это мои шаги?


это нормально, что процедурный кэш занят планами запросов и процедур
проблема в частности в том, что его половина занята планами, у которых usecount=1
т.е. много непараметризированных ad-hoc запросов
с этим можно бороться разными методами, в частности опцией на базе "forced parametrization" или на сервере "optimize for ad hoc workloads"

однако, выбор подхода необходимо делать после изучения запросов с usecount=1


соотношение Buffer Cache / Procedure cache на сервере нормальное, так что я сомневаюсь что множественные ad-hoc запросы являются основным источником проблем

компрессия таблиц - это интересная тема, но и её надо сначала оценить

покажи самые объемные таблицы в кэше:

+
SELECT top 20
object_name(obj.object_id) [Object]
, isnull(si.name,'Table')+' [index #'+convert(varchar(10),si.index_id)+']' as [Index]
, count(*)/128  [Size (Mb)]
FROM sys.dm_os_buffer_descriptors AS bd 
	INNER JOIN 
	(
		SELECT object_id 
			,index_id ,allocation_unit_id
		FROM sys.allocation_units AS au
			INNER JOIN sys.partitions AS p 
				ON au.container_id = p.hobt_id 
					AND (au.type = 1 OR au.type = 3)
		UNION ALL
		SELECT object_id   
			,index_id, allocation_unit_id
		FROM sys.allocation_units AS au
			INNER JOIN sys.partitions AS p 
				ON au.container_id = p.hobt_id 
					AND au.type = 2
	) AS obj 
		ON bd.allocation_unit_id = obj.allocation_unit_id
	left join sys.indexes si 
		on  (si.object_id=obj.object_id and si.index_id=obj.index_id)
WHERE database_id = db_id('WorkDB')
GROUP BY obj.object_id, isnull(si.name,'Table')+' [index #'+convert(varchar(10),si.index_id)+']'
ORDER BY 3 DESC,1;


самые большие таблицы можно оценить по перспективности сжатия процедурой sp_estimate_data_compression_savings
28 окт 13, 19:15    [15042896]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
Roul_Duke
Member

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

Object Index Size(Mb)
_InfoRg2334_InfoRg2334_ByDims2342_RRSSSS [index #2] 10039
_AccumRg4114_AccumR4114_ByPeriod_TRN [index #1] 10028
_Reference5852PK___Referen__AC8ED0C46B72626B [index #1] 7989
_Document2785PK___Documen__AC8ED0C4625DF97D [index #1]7348
_InfoRg2334_InfoRg2334_ByDims_RRSSSS [index #1] 5922
_Document2786PK___Documen__AC8ED0C428666212 [index #1]4055
_AccumRg4101_AccumR4101_ByPeriod_TRN [index #1] 1953
_AccumRgAggdf3h7022_AccumR7022_ByDims5764_RT [index #3]1579
_Document2720_VT2773_Docume2720_VT2773_IntKeyInd [index #1]1261
_AccumRg4114_AccumR4114_ByDims5765_TTRN [index #5]1180
_AccumRg4114_AccumR4114_ByDims5763_RTRN [index #3]1110
_Reference5852_Refere5852_ByField5888_RR [index #6]1011
_AccumRgAggdf3h7022_AccumR7022_ByDims_TRRRRRR [index #1]1000
_Document2785_Docume2785_ByField6988_STR [index #7]917
_Document2784_VT2821_Docume2784_VT2821_IntKeyInd [index #1]757
_Reference5852_r5852_5861_5884 [index #26]659
_Document2785_Docume2785_ByDocDate_TR [index #3]582
_Reference2639PK___Referen__AC8ED0C45A67D1FA [index #1]531
_Reference3001PK___Referen__AC8ED0C40275C354 [index #1]528
_InfoRg3580_InfoRg3580_ByPeriod_TRRR [index #1]482


Сообщение было отредактировано: 29 окт 13, 12:46
29 окт 13, 12:09    [15045690]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
Roul_Duke
Member

Откуда: Киев
Сообщений: 89
Roul_Duke,

К сообщению приложен файл (results_big_table.xls - 28Kb) cкачать
29 окт 13, 12:10    [15045694]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
Для такой большой БД может быть имеет смысл подвигать recovery interval?
29 окт 13, 12:42    [15045899]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
komrad
Member

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

посмотрите Profiler-ом запросы с большим временем выполнения и IO - там наверняка tablescans
29 окт 13, 16:52    [15047871]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
Roul_Duke
Member

Откуда: Киев
Сообщений: 89
komrad,
Не наверняка, а 100%
29 окт 13, 17:07    [15047986]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
Roul_Duke
Member

Откуда: Киев
Сообщений: 89
komrad,
вот поэтому я и подумываю над партицированием, чтобы не гонять Table scan для всей таблицы. Имеет смысл??
29 окт 13, 17:10    [15048002]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
komrad
Member

Откуда:
Сообщений: 5300
Roul_Duke
komrad,
Не наверняка, а 100%


ну за консолью сиквела не я, поэтому только предполагаю

если нет возможности повлиять на запросы и нельзя индексы свои рисовать (всё-таки 1С - может и смахнуть их)
я бы попробовал оценить качество сжатия популярных таблиц
накладные расходы небольшие (ЦПУ), а читать/писать сиквелу меньше придется
29 окт 13, 17:12    [15048015]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Roul_Duke
подумываю над партицированием, чтобы не гонять Table scan для всей таблицы

В запросах есть условия, которые будут ограничивать количество сканируемых секций?
29 окт 13, 17:17    [15048038]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
Roul_Duke
Member

Откуда: Киев
Сообщений: 89
Гость333,
ну конечно, часто строятся отчёты по месячно, по квартально.
29 окт 13, 17:19    [15048056]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
Roul_Duke
Member

Откуда: Киев
Сообщений: 89
komrad,
в том то и дело, что индексы помогли бы, но 1С их удалит это раз,
два - отчёты строятся по разным полям, т.е. статических отчётов нет. В запросах постоянно фигурируют разные поля как в select
так и в where ... в общем такая вот ситуация.
Подскажите, а если добавить ещё оперативной памяти, есть ли метод рассчёта сколько нужно добавить??
Сколько сиквелу будет достаточно. Ведь, по моему, сколько не добавляй - съест всё.
29 окт 13, 17:22    [15048078]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
rahzer
Member

Откуда:
Сообщений: 2294
автор
Подскажите, а если добавить ещё оперативной памяти, есть ли метод рассчёта сколько нужно добавить??
Сколько сиквелу будет достаточно. Ведь, по моему, сколько не добавляй - съест всё.

Не забудьте посмотреть, что за сервак, какого поколения, что за мат.плата\чипсет, сколько и каких процов, а так же сколько и каких планок памяти установлено.
29 окт 13, 19:03    [15048561]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
komrad
Member

Откуда:
Сообщений: 5300
Roul_Duke
Подскажите, а если добавить ещё оперативной памяти, есть ли метод рассчёта сколько нужно добавить??
Сколько сиквелу будет достаточно. Ведь, по моему, сколько не добавляй - съест всё.


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

он съест только то, что ему дают
поставьте max memory и больше он почти не съест
29 окт 13, 19:05    [15048574]     Ответить | Цитировать Сообщить модератору
 Re: Забита память и данные пишуться на диск  [new]
Roul_Duke
Member

Откуда: Киев
Сообщений: 89
komrad,
Ясно. Спасибо большое за помощь!!
буду копать ;)
30 окт 13, 12:38    [15051824]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Microsoft SQL Server Ответить