Sybase Replication Server


MS SQL SERVER. Query_hash и Query_plan_hash, что в этих числах

Дмитрий Горчилин

По материалам:

Блог инженеров технической поддержки SQL Server. Microsoft. Россия

http://blogs.technet.com/b/sqlruteam/archive/2014/11/09/sql_5f00_server_5f00_query_5f00_hash_5f00_and_5f00_query_5f00_plan_5f00_hash_5f00_part_5f00_1.aspx

http://blogs.technet.com/b/sqlruteam/archive/2014/11/11/sql_5f00_server_5f00_query_5f00_hash_5f00_and_5f00_query_5f00_plan_5f00_hash_5f00_part_5f00_2.aspx

Две части, третьей нет, интрига. А почему? Попробую пояснить.



Цитата:

Анализ содержимого процедурного кэша показал, что огромное количество одинаковых по коду запросов имеют записи в процедурном кэше.

Как видно на рисунке ниже некоторые планы повторяются по 200 и более раз.

Для получения этих данных я воспользовался этим "волшебным" числом "query_hash".

За счет многократного повторения запросов израсходовано дополнительно 62 ГБ оперативной памяти.

Как видно из рисунка ниже если бы планы выполнения многократно не повторялись, то для их размещения понадобилось бы 20 ГБ вместо 82 ГБ.



Приводится скрипт, который пришлось немного докрутить.



select sum(max_plan_size_mb) from

(select eqs.query_hash,max(eqp.size_in_bytes)/1048576 max_plan_size_mb

from sys.dm_exec_query_stats eqs

join sys.dm_exec_cached_plans eqp

on eqs.plan_handle =eqp.plan_handle

group by eqs.query_hash

)as a



select sum(max_plan_size_mb ) from

(select eqs.query_hash,sum(cast(eqp.size_in_bytes as bigint))/1048576 max_plan_size_mb

from sys.dm_exec_query_stats eqs

join sys.dm_exec_cached_plans eqp

on eqs.plan_handle =eqp.plan_handle

group by eqs.query_hash

)as a



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

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



SELECT sum(cast(size_in_bytes as bigint))/1048576 as sum_plan_size_mb,count(*) as count

FROM sys.dm_exec_cached_plans



Что за чудеса?

Дело в том, что в sys.dm_exec_cached_plans МЕНЬШЕ записей, чем в sys.dm_exec_query_stats. Несколько записей sys.dm_exec_query_stats могут ссылаться на один план. Соответственно, их скриптом один и тот же план может быть посчитан несколько раз. Соответственно, несколько раз просуммирован его размер, что неправильно.



Вывод 1: дурят православных
Вывод 2:не доверяйте бездумно инженерам, особенно если это инженеры Microsoft :)



Что до третьей части- судя по всему, авторы планировали там рассказать о принудительной параметризации, но кто-то из читателей оказался шибко умным, прочел текст внимательно. Инженеры обиделись и не написали больше.
добавлено: 07 окт 15 просмотры: 747, комментарии: 0



Несколько ссылок

Любопытные выводы в плане настройки Ms sql server. Понятно, что все тесты отчасти специфичны, но приличная рабочая станция может превзойти очень даже породистый сервер. При случае надо будет попробовать
http://efsol.ru/articles/tuning-1c.html



В принципе, процессоры дошли до той стадии мощи и совершенства, что сжимать на лету выгодней, чем просто писать/читать
http://www.community.terrasoft.ru/blogs/4591


Тестирование различных накопителей, крайне познавательно.
http://habrahabr.ru/post/168711/


Основы репликации в MySQL. Даже у воробья есть сердце, даже у MySQL репликация :) Шутки шутками, но не следует забывать о том, что расходы на лицензию нулевые. То есть, на линуксе, на микросерверах или даже охапке старых рабочих станций, при тщательном планировании можно развернуть нечто вполне серьезное. При этом стоимость лицензии нулевая, стоимость железа мизерная.
http://habrahabr.ru/post/56702/
добавлено: 15 сен 15 просмотры: 592, комментарии: 0



Практическая задача- изменился тип или размерность некоторого поля

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


--Д. Горчилин. Указывает репликационные дефинишены в которых есть данная колонка

---- простая версия:

declare @colnam varchar(30)
select @colnam='<<название поля>>'

select 'alter replication definition '+char(34)+objname+char(34)+' alter columns with '+char(34)+@colnam+char(34)+
' /*'+dsname+'.'+dbname+' --- '+rs_sites.name+' */' from rs_columns,rs_databases,rs_objects,rs_sites
where
rs_columns.objid=rs_objects.objid and
rs_databases.dbid=rs_objects.dbid and
rs_databases.prsid=rs_sites.id and
colname=@colnam

---------- сложная версия, указывает еще и существующий на данный момент тип

declare @colnam varchar(30)
select @colnam='<<название поля>>'

select 'alter replication definition '+char(34)+objname+char(34)+' alter columns with '+char(34)+@colnam+char(34)+
' /*'+rs_sites.name+' --->'+dsname+'.'+dbname+' '+
case when coltype=0 then 'char'
when coltype=1 then 'binary'
when coltype=4 then 'text'
when coltype=5 then 'image'
when coltype=6 then 'tinyint'
when coltype=7 then 'smallint'
when coltype=8 then 'int'
when coltype=9 then 'real'
when coltype=10 then 'float'
when coltype=11 then 'bit'
when coltype=12 then 'datetime'
when coltype=13 then 'smalldatetime'
when coltype=14 then 'money'
when coltype=15 then 'smallmoney'
when coltype=16 then 'numeric'
when coltype=17 then 'decimal'
when coltype=18 then 'varchar'
when coltype=19 then 'varbinary'
else '*' end +
case when coltype in(0,4,18) then '('+rtrim(convert(char(5),length))+')'
else ' ' end
+' */' from rs_columns,rs_databases,rs_objects,rs_sites
where
rs_columns.objid=rs_objects.objid and
rs_databases.dbid=rs_objects.dbid and
rs_databases.prsid=rs_sites.id and
colname=@colnam
order by rs_sites.name,dsname,dbname,objname


Рекомендации как всегда- сначала думать, потом делать. Иногда удачный альтер на лету валит систему, соблюдать осторожность. Второй момент, в новых версиях новые типы данных, увидали звездочку- творчески доработали :)
добавлено: 14 сен 15 просмотры: 640, комментарии: 0



Практическая задача- добавить несколько таблиц в подписку

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


--Универсальный генератор публикаций имени Горчилина. Нужно указать имя базы откуда и куда, после нарисовать нужные артикли

select * into #rs_databases from rs_databases
select * into #rs_sites from rs_sites

--declare @db_1 varchar (30),@db_2 varchar (30),@pubnam varchar(30)
--select @db_1 ='%1'
--select @db_2 ='%2'

select @pubnam= upper(rs_databases.dsname)+lower(rs_databases.dbname) +'_'+upper(#rs_databases.dsname)+lower(#rs_databases.dbname)
from rs_databases,#rs_databases where
rtrim(rs_databases.dsname)+'.'+rtrim(rs_databases.dbname) =@db_1 and
#rs_databases.dsname+'.'+#rs_databases.dbname =@db_2

select @db_1= rs_databases.dsname+'.'+rs_databases.dbname from rs_databases where rs_databases.dsname+'.'+rs_databases.dbname =@db_1
select @db_2= rs_databases.dsname+'.'+rs_databases.dbname from rs_databases where rs_databases.dsname+'.'+rs_databases.dbname =@db_2

--select @pubnam,@db_1,@db_2


select
'-- SUB: '+subname
from rs_subscriptions,rs_databases,#rs_databases where
rs_databases.dsname+'.'+rs_databases.dbname =@db_1 and
#rs_databases.dsname+'.'+#rs_databases.dbname =@db_2 and
rs_subscriptions.pdbid=rs_databases.dbid and
rs_subscriptions.dbid=#rs_databases.dbid
group by subname

select
--subname subid type objid dbid pdbid requestdate pownerid rownerid status recovering error_flag materializing dematerializing primary_sre replicate_sre materialization_try method generation parentid security mechanism prsid objname objid dbid objtype attributes ownertype crdate parentid ownerid rowtype phys_tablename deliver_as_name phys_objowner repl_objowner has_baserepdef minvers dsname dbname dbid dist_status src_status attributes errorclassid funcclassid prsid rowtype sorto_status ltype ptype ldbid enable_seq dsname dbname dbid dist_status src_status attributes errorclassid funcclassid prsid rowtype sorto_status ltype ptype ldbid enable_seq articlename articleid type primaryname primaryowner objid pubid requestdate
'-- TABLES: '+phys_tablename+char(9)+objname+char(9)+subname
from rs_subscriptions,rs_objects,rs_databases,#rs_databases,rs_articles where
rs_databases.dsname+'.'+rs_databases.dbname =@db_1 and
#rs_databases.dsname+'.'+#rs_databases.dbname =@db_2 and
--rs_subscriptions.objid=rs_objects.objid and
rs_subscriptions.pdbid=rs_databases.dbid and
rs_subscriptions.dbid=#rs_databases.dbid and
rs_articles.articleid=rs_subscriptions.objid and
rs_articles.objid=rs_objects.objid
order by phys_tablename

select '-- ['+rs_sites.name+'] '+@db_1+' ==> ['+#rs_sites.name+'] '+@db_2+ ' {'+@pubnam+'}'
from rs_databases,#rs_databases,rs_sites,#rs_sites where
rs_databases.dsname+'.'+rs_databases.dbname =@db_1 and
#rs_databases.dsname+'.'+#rs_databases.dbname =@db_2 and
rs_databases.prsid=rs_sites.id and
#rs_databases.prsid=#rs_sites.id


select 'create publication "'+@pubnam+'" with primary at '+@db_1,

char(10)+'create article "<<ARTICLE>>" for "'+@pubnam+'" with primary at '+@db_1+' with replication definition "<<REP DEF>>"',

char(10)+'validate publication "'+@pubnam+'" with primary at '+@db_1,

char(10)+'define subscription "'+@pubnam+'" for publication "'+@pubnam+'" with primary at '+@db_1+' with replicate at '+@db_2,

char(10)+'activate subscription "'+@pubnam+'" for publication "'+@pubnam+'" with primary at '+@db_1+' with replicate at '+@db_2

drop table #rs_databases
drop table #rs_sites

go
добавлено: 09 сен 15 просмотры: 683, комментарии: 0



Как скриптом узнать размер очереди

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

select getdate ()
select 'ROUTE TO ->'+name,
'RSI',
count(*)
from rs_sites,rs_segments where
rs_sites.id=rs_segments.q_number
group by name
go

select dsname+'.'+dbname,
case when q_type=0 then 'dsi' else 'LTM' end,
count(*),
case when dist_status=1 then 'up' else '!!' end
from rs_databases,rs_segments where
rs_databases.dbid=rs_segments.q_number
group by dsname+'.'+dbname,
case when q_type=0 then 'dsi' else 'LTM' end,
case when dist_status=1 then 'up' else '!!' end
go
select '---------------------------- for resume ---------------------'
go
select 'resume connection to '+dsname+'.'+dbname+' skip transacion'+char(10)+'go'
from rs_databases
where
dist_status!=1 and src_status=1 and
prsid in (select id from rs_sites where name in (select charvalue from rs_config where optionname='oserver'))
group by 'resume connection to '+dsname+'.'+dbname+' skip transacion'+char(10)+'go'
go
добавлено: 06 сен 15 просмотры: 659, комментарии: 0



Забавная подробность BCP

Были несколько раз забавные приключения, когда bcp паковала данные мимо форинкея. А потом у счастливых владельцев этих таблиц недоумение, раз даже на грани истерики. Судя по всему, ропот был услышан MS:



2005

https://technet.microsoft.com/ru-ru/library/ms186247(v=sql.90).aspx

По умолчанию, инструкция INSERT проверяет ограничения CHECK и FOREIGN KEY. Однако инструкция INSERT ... SELECT * FROM OPENROWSET(BULK...) позволяет переопределить проверку ограничений CHECK и FOREIGN KEY.

Примечание.

Нельзя отключить ограничения UNIQUE, PRIMARY KEY или NOT NULL.



2008 R2

https://technet.microsoft.com/ru-ru/library/ms186247(v=sql.105).aspx

Примечание

Только ограничения CHECK могут быть отключены. Нельзя отключить ограничения UNIQUE, PRIMARY KEY, FOREIGN KEY или NOT NULL.



То есть, в 2008 R2 проверку на форинкей отключить нельзя, а в 2005 можно.

Ну а в целом конечно форинкеи враг репликации. Таблицы иногда получают из разных источников, иногда возникают расхождения в данных. Упала репликация- за это голова болит вроде бы уже как и не у владельцев сервера- получателя. Лучше бы на стороне получателя убрать все проверки кроме PK. Это не всегда возможно, но очень желательно.
добавлено: 03 сен 15 просмотры: 554, комментарии: 2



Практическая задача- сдуть очередь на полуживой сервер

Периодически возникала проблема- некий сервер помирает, его бы отписать, да накопившиеся в очереди транзакции не дают. В принципе, очередь можно просто сдуть. Но по ряду причин этот метод не всегда хорош, лучше бы оно отработало штатно. Еще один вариант- в базе побило несколько таблиц, транзакции по ним мешают. Именно по этим таблицам репликацию бы и отписать бы, но очередь не дает. Хочется сохранить очередь, выборочно удалив транзакции по некоторым таблицам. После их можно будет пересоздать, изменить структуру, закачать bcp. Иногда даже подкладывали вместо умершего сервера базу учебного сервера, и он быстренько сдувал очередь. Сделать то надо всего ничего- по таблицам идущим в эту базу создать пустой fsc, поднять. Репсервер честно все их применит- пустые. В базе при этом сами таблицы не нужны, достаточно репликационных объектов (вроде rs_lastcommit).
В литературе мне этот метод не встречался, но практически использовался часто.


/* Этот чудесный и замечательный скрипт позволяет сливать данные из очереди при полудохлой базе,
не выполняя каких-либо действий на sql-сервере и не очищая очередь силовыми методами*/

declare @dsname varchar(30),@dbname varchar(30)
select @dsname='%'
select @dbname='%'
select 'alter connection to "'+@dsname+'"."'+@dbname+'"'+char(10)+'set function string class to empty_function_class'

select 'create function string '+objname+
".rs_insert for empty_function_class with overwrite output language '/*empty*/'"+char(10)+'go',
char(10)+'create function string '+objname+
".rs_update for empty_function_class with overwrite output language '/*empty*/'"+char(10)+'go',
char(10)+'create function string '+objname+
".rs_delete for empty_function_class with overwrite output language '/*empty*/'"+char(10)+'go'
from rs_subscriptions,rs_objects,rs_articles,rs_databases
where rs_subscriptions.objid=rs_articles.articleid and
rs_articles.objid=rs_objects.objid and
rs_databases.dbid=rs_subscriptions.dbid and
dsname like @dsname and dbname like @dbname

select 'resume connection to "'+@dsname+'"."'+@dbname+'"'+char(10)+'go'
print 'Успехов и процветания, дорогой Дмитрий Ильич!'
добавлено: 02 сен 15 просмотры: 522, комментарии: 0



Впечатление от Ms SQL репликации

Пришлось поближе познакомиться с этим делом. Впечатлило.
Это как полтора десятка лет отработать на КРАЗе. Он большой, грубоватый, воняет соляркой.
Но в плане возить-грузить не имеет равных, залюбуешься.

И вдруг появляется некая чудо-машина. Большая светлая кабина, коробка-автомат,
гидравлика и усилители, магнитола, которой нет даже у начальника АТП.
Но какое-то оно нежное.

В целом примерно так. Вещи, которые приходилось делать хитрыми самописными скриптами тут делаются одним кликом мышки. Произвела впечатление возможность репликации изменения структуры, это было проблемой. Много необычных и местами приятных вещей. Некоторые вещи кажутся сделанными хуже, или я к ним, возможно, не привык.
добавлено: 01 сен 15 просмотры: 669, комментарии: 0



Практическая задача- взвести флаги на репликацию

Прошло некоторое время, по ряду причин не связан какими-либо обязательствами. Решил выложить некоторые старые скрипты-примочки. Мелочь вроде, но иногда именно они приносят огромную пользу. Скрипты имеют важную особенность- написаны в виде учебных, избегал псевдонимов для лучшего понимания, некоторые из этих вещей делались в формате наглядного пособия. Кроме того, гуру SQL заметят некоторые моменты в плане стиля. Я это тоже вижу, себе я пишу иначе :) В принципе, любая серьезная работа подразумевает подготовку многих скриптов, не было большой работы в которой не пришлось бы придумывать что-то новое. Творческое это дело :)

/* Запусти меня в базе RSSD, и я расскажу какие таблицы ходят с конкретного сервера */
select 'DATABASE '+dsname+'.'+dbname+' tabename:'+phys_tablename
from rs_objects,rs_databases where rs_databases.dbid=rs_objects.dbid
and (phys_tablename not like 'rs_%%')
and dsname='ИмяСервера'
/*and dbname in('если надо- название базы')*/
group by dsname,dbname,phys_tablename
order by dsname,dbname,phys_tablename

/* Запусти меня в любой базе, и я сформирую скрипт, чтобы снести флаги на репликацию в этой базе */

select
'sp_setreptable '+name+' ,false'+char(13)+char(10)+'go'
from sysobjects where type='u'
order by name

/* Запусти меня в базе RSSD, и я сформирую скрипт, чтобы взвести флаги на репликацию для конкретного сервера */

select 'use '+dbname+char(10)+'go'+char(10)+'sp_setreptable '+phys_tablename+',true'+char(10)+'go'
from rs_objects,rs_databases where rs_databases.dbid=rs_objects.dbid
and (phys_tablename not like 'rs_%%') and dsname='ИмяСервера' /*!!!!!!!!!!!!!!!!*/

/* Запусти меня в базе RSSD, и я сформирую скрипт, чтобы взвести флаги на репликацию для конкретного сервера */
-- с группировкой и указанием имени сервера на котором полученный скрипт нужно запустить

select 'use '+dbname+' /* Run Me on: '+dsname+' */'+char(10)+'go'+char(10)+'sp_setreptable '+phys_tablename+',true'+char(10)+'go'
from rs_objects,rs_databases where rs_databases.dbid=rs_objects.dbid
and (phys_tablename not like 'rs_%%') and
--!!!
dsname='ИмяСервера' /*!!!!!!!!!!!!!!!!*/
--!!!
group by
'use '+dbname+' /* Run Me on: '+dsname+' */'+char(10)+'go'+char(10)+'sp_setreptable '+phys_tablename+',true'+char(10)+'go'


/* Запусти меня в базе RSSD, и я сформирую скрипт, чтобы взвести флаги на репликацию вообще для всех серверов */
-- с группировкой и указанием имени сервера на котором полученный скрипт нужно запустить

select 'use '+dbname+' /* Run Me on: '+dsname+' */'+char(10)+'go'+char(10)+'sp_setreptable '+phys_tablename+',true'+char(10)+'go'
from rs_objects,rs_databases where rs_databases.dbid=rs_objects.dbid
and (phys_tablename not like 'rs_%%') --and
--!!!
--dsname='ИмяСервера' /*!!!!!!!!!!!!!!!!*/
--!!!
group by
'use '+dbname+' /* Run Me on: '+dsname+' */'+char(10)+'go'+char(10)+'sp_setreptable '+phys_tablename+',true'+char(10)+'go'
order by dsname,dbname

/* Скрипт имени Горчилина */
--Dmitry Gorchilin scripts for Sybase Replication Server
добавлено: 01 сен 15 просмотры: 533, комментарии: 0



По ряду причин решил изменить место работы

Вопрос несколько утратил актуальность, слегка сокращу

..размещу здесь рекомендательное письмо. Возможно, кому-то понадобятся мои знания и опыт.

Горчилин Дмитрий Ильич, Старший специалист по технологической поддержке ОДБ ГО, Департамент автоматизации ОДБ, Головной офис. Стаж работы в Приватбанке 14 лет 8 мес.
[..]
На момент увольнения Дмитрий Горчилин обслуживал репликационную систему количество подписанных на репликацию таблиц в которой 18235.
+38067 6394534
gorchilin@mail.ru
Skype: dn210370gdi

Днепропетровск, Украина
добавлено: 22 дек 14 просмотры: 2711, комментарии: 7