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

Откуда: Санкт-Петербург
Сообщений: 671
День добрый!
SQL 2012 + MS Access 2010 клиент.
Аксес регулярно вызывает процедуру sp_MShelpcolumns при открытии форм для своих нужд. После переноса базы с 2008 на 2012 сервер эта процедура стала виснуть на некоторых view, в том числе и при запуске через студию. Подскажите пожалуйста, где искать проблему? Спасибо!

EXEC sp_MShelpcolumns 'some_view' --зависает намертво
17 сен 12, 15:58    [13176940]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Student007
Аксес регулярно вызывает процедуру sp_MShelpcolumns
Вот уроды.
Может это провайдер так делает? Хотя не, врядли.

Смотрите current_text выполнения и локировки.
17 сен 12, 16:28    [13177266]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
Student007
Member

Откуда: Санкт-Петербург
Сообщений: 671
Выцедил кусок кода из sp_MShelpcolumns, который приводит к зависанию. Этот кусок одинаковый в SQL Server 2008 и 2012. Прикрепляю два плана выполнения, один из 2008-го, а другой из 2012-го сервера к одинаковому view одинаковых баз. Можете пожалуйста взглянуть, может будет понятна разница?

+ сам запрос
DECLARE @tablename nvarchar(517)='logistics.q_job_charges_list'
DECLARE @flags int = 0
DECLARE @orderby nvarchar(10) = null
DECLARE @flags2 int = 0
declare @objid int = object_id(@tablename)


		select c.name, c.colid, st.name,
         case when bt.name in (N'nchar', N'nvarchar') then c.length/2 else c.length end,
			ColumnProperty(@objid, c.name, N'Precision'),
			ColumnProperty(@objid, c.name, N'Scale'),
				-- col_numtype for DaVinci:  use sp_help-type prec/scale filtering for @flags2 & 1
			case when (@flags2 & 1 <> 0 and bt.name in (N'tinyint',N'smallint',N'decimal',N'int',N'real',N'money',N'float',N'numeric',N'smallmoney',N'bigint'))
					then 1 else 0 end,
				-- Nullable
			convert(bit, ColumnProperty(@objid, c.name, N'AllowsNull')),
				-- Identity
			case when (@flags & 0x40000000 = 0) then convert(bit, ColumnProperty(@objid, c.name, N'IsIdentity')) else 0 end,
				-- Non-DRI Default (make sure it's not a DRI constraint).
			case when (c.cdefault = 0) then null when (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0) then null else schema_name(sysod.schema_id) + N'.' + d.name end,
				-- Non-DRI Rule
			case when (c.domain = 0) then null else schema_name(sysor.schema_id) + N'.' + r.name end,
				-- Physical base datatype
			bt.name,
				-- Initialize flags to whether it's a length-specifiable type, or a numeric type, or 0.
			case when st.name in (N'char',N'varchar',N'binary',N'varbinary',N'nchar',N'nvarchar') then 0x0001
					when st.name in (N'decimal',N'numeric') then 0x0002
					else 0 end
					-- Will be NULL if column is not UniqueIdentifier.
					+ case isnull(ColumnProperty(@objid, c.name, N'IsRowGuidCol'), 0) when 0 then 0 else 0x0008 end,
				-- Identity seed and increment

-- Fix for Raid # 53682 --
			case when (ColumnProperty(@objid, c.name, N'IsIdentity') <> 0) then CONVERT(nvarchar(40), ident_seed(@tablename)) else null end,
--			case when (ColumnProperty(@objid, c.name, N'IsIdentity') <> 0) then ident_seed(@tablename) else null end,  --
			case when (ColumnProperty(@objid, c.name, N'IsIdentity') <> 0) then CONVERT(nvarchar(40), ident_incr(@tablename)) else null end,
--			case when (ColumnProperty(@objid, c.name, N'IsIdentity') <> 0) then ident_incr(@tablename) else null end,  --

				-- DRI Default name
			case when (@flags & 0x0200 = 0 and c.cdefault is not null and (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0))
					then object_name(c.cdefault) else null end,
				-- DRI Default text, if it does not span multiple rows (if it does, SQLDMO will go get them all).
			case when (@flags & 0x0200 = 0 and c.cdefault is not null and (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0))
					then t.id else null end,
         c.iscomputed,
         c.id,
				-- Not For Replication
			convert(bit, ColumnProperty(@objid, c.name, N'IsIdNotForRepl')),
         convert(bit, ColumnProperty(@objid, c.name, N'IsFulltextIndexed')),
         convert(bit, ColumnProperty(@objid, c.name, N'UsesAnsiTrim')),
				-- Non-DRI Default owner and name
			case when (c.cdefault = 0) then null when (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0) then null else schema_name(sysod.schema_id) end,
			case when (c.cdefault = 0) then null when (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0) then null else d.name end,
				-- Non-DRI Rule owner and name
			case when (c.domain = 0) then null else schema_name(sysor.schema_id) end,
			case when (c.domain = 0) then null else r.name end,
           -- column level collation
         c.collation,
           -- IsIndexable
         ColumnProperty(@objid, c.name, N'IsIndexable'),
         c.language
		from dbo.syscolumns c
				-- NonDRI Default and Rule filters
			left outer join (dbo.sysobjects d join sys.all_objects sysod on d.id = sysod.object_id)  on d.id = c.cdefault
			left outer join (dbo.sysobjects r join sys.all_objects sysor on r.id = sysor.object_id)  on r.id = c.domain
				-- Fully derived data type name
			join dbo.systypes st on st.xusertype = c.xusertype
				-- Physical base data type name
			join dbo.systypes bt on bt.xusertype = c.xtype
				-- DRIDefault text, if it's only one row.
			left outer join dbo.syscomments t on t.id = c.cdefault and t.colid = 1
					and not exists (select * from dbo.syscomments where id = c.cdefault and colid = 2)
		where c.id = @objid
		order by c.colid


К сообщению приложен файл (estimated_plans.zip - 39Kb) cкачать
17 сен 12, 18:31    [13178372]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
Student007
Member

Откуда: Санкт-Петербург
Сообщений: 671
Mnior
<...>Смотрите current_text выполнения и локировки.


Подскажите пожалуйста, какие запросы выполнить, чтобы это посмотреть?
17 сен 12, 18:45    [13178450]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
step_ks
Member

Откуда:
Сообщений: 936
Student007, sp_updatestats запускали после переноса бд на 2012?
17 сен 12, 23:13    [13179438]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
Student007
Member

Откуда: Санкт-Петербург
Сообщений: 671
step_ks,

Да, индексы перестраивал, статистики обновлял с FULLSCAN.
17 сен 12, 23:25    [13179473]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
VladimirKr
Member

Откуда: СПб
Сообщений: 1062
Student007
День добрый!
[/SRC]SQL 2012 + MS Access 2010 клиент.
Аксес регулярно вызывает процедуру sp_MShelpcolumns при открытии форм для своих нужд...
[src sql]
EXEC sp_MShelpcolumns 'some_view' --зависает намертво


Нужды, вообщем-то, понятные. Но их реализация...
Имхо,
Лучше в аксе переделать: в источнике записей формы указывать не хранимую процедуру, а ее TSQL вызов:
exec dbo.myproc

Тогда, правда, при открытии формы акс возможно сделает дополнительно вызов этого TSQL в режиме set fmtonly.
Еще вариант: Recordsource формы сделать пустым, а при открытии (ну и вместо Requery, если нужно)

Set Me.Recordset=CurrentProject.Connection.Execute("exec dbo.myproc")
18 сен 12, 09:58    [13180259]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
VladimirKr
Member

Откуда: СПб
Сообщений: 1062
А, источник формы - view, сорри.
Тогда select * from some_view или (лень смотреть, но, возможно, акс будет парсить select и все равно вызывать sp_MShelpcolumns), если не поможет - exec ('set nocount on;select * from some_view') в источник формы.
во втором случае, чтобы форма была обновляемой, придется указать UniqueTable и ResyncCommand.
18 сен 12, 10:13    [13180334]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Student007
Mnior
<...>Смотрите current_text выполнения и локировки.

Подскажите пожалуйста, какие запросы выполнить, чтобы это посмотреть?
11313410
18 сен 12, 19:17    [13184653]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
И last_wait_type, wait_resource и т.п. там же посмотрите
18 сен 12, 19:19    [13184669]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
Student007
Member

Откуда: Санкт-Петербург
Сообщений: 671
Mnior,

Запускал вот такой запрос сервере, где проблемный запрос висит уже пару часов:
+ sys.dm_exec_sessions
SELECT dmes.session_id, dmes.original_login_name
 , dmer.wait_time, dmer.wait_type, dmer.last_wait_type, dmer.cpu_time, dmes.memory_usage, dmes.reads, dmes.writes, dmes.logical_reads
 , s.text
 , substring
   (
    p.text
    , dmer.statement_start_offset/2+1
    , case dmer.statement_end_offset 
       when -1 then 2147483647 
       else (dmer.statement_end_offset - dmer.statement_start_offset)/2 + 1 
      end 
   ) sql_text
FROM sys.dm_exec_requests dmer
JOIN sys.dm_exec_sessions dmes ON dmes.session_id = dmer.session_id
CROSS APPLY sys.fn_get_sql(dmer.sql_handle) s
CROSS APPLY sys.dm_exec_sql_text(dmer.sql_handle) p
WHERE dmes.session_id <> @@SPID

С вот таким результатом:

К сообщению приложен файл. Размер - 24Kb
18 сен 12, 19:42    [13184812]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
И что за звпрос в sql_text?
insert #sp...
Кстати да, проц кушает много. Может и так, что статистика на системных таблах левая.
Поймайте план запроса, можем сравнить с нашим.
18 сен 12, 20:48    [13185037]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
Student007
Member

Откуда: Санкт-Петербург
Сообщений: 671
Mnior,

А как план поймать, если запрос бесконечный и через sys.dm_exec_query_plan возвращается NULL

insert #sp - это как раз вставка в табличку #sphelpcols:

К сообщению приложен файл. Размер - 51Kb
18 сен 12, 21:11    [13185115]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
step_ks
Member

Откуда:
Сообщений: 936
Student007
Mnior,

А как план поймать, если запрос бесконечный и через sys.dm_exec_query_plan возвращается NULL


Профайлером, события группы Performance.
18 сен 12, 23:23    [13185617]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
Student007
Member

Откуда: Санкт-Петербург
Сообщений: 671
step_ks,
У меня экспресс редакция =(

Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
Feb 10 2012 19:13:17
Copyright (c) Microsoft Corporation
Express Edition on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (WOW64) (Hypervisor)
18 сен 12, 23:31    [13185659]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
step_ks
Member

Откуда:
Сообщений: 936
Student007
step_ks,
У меня экспресс редакция =(

Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
Feb 10 2012 19:13:17
Copyright (c) Microsoft Corporation
Express Edition on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (WOW64) (Hypervisor)

тогда см. справку по процедурам sp_trace% или выполните запрос в студии с включенным Estimated plan
19 сен 12, 07:46    [13186014]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
Student007
Member

Откуда: Санкт-Петербург
Сообщений: 671
step_ks
выполните запрос в студии с включенным Estimated plan

Я это уже делал, но вот еще вариант: два близких вью. На одной запрос делается меньше секунды, а на другой так и висит =(

+ views
CREATE VIEW logistics.q_bill_list
WITH  VIEW_METADATA
AS
SELECT bl_id, bl_bill_id, bl_item, bl_item_table, bl_item_id, bl_job_id, 
    (SELECT job FROM k_job WHERE job_id = bl_job_id) job, --этот фокус нужен, чтобы для аксеса запрос был обновляемым
    bl_charge_id,
    (SELECT charge FROM k_charge WHERE ch_id = bl_charge_id) charge, --этот фокус нужен, чтобы для аксеса запрос был обновляемым
    bl_sort, bl_text, bl_formula, bl_eur, bl_usd
FROM bill_list

CREATE VIEW logistics.q_bill_list_print WITH VIEW_METADATA 
AS
SELECT bl.bl_id, bl.bl_bill_id, bl.bl_job_id, kjob.job, kjob.client, bl.bl_sort, kch.charge, 
 bl.bl_text, bl.bl_formula, bl.bl_eur, bl.bl_usd, bl.bl_item
FROM bill_list bl 
LEFT OUTER JOIN logistics.k_job kjob ON kjob.job_id = bl.bl_job_id
LEFT OUTER JOIN logistics.k_charge kch ON kch.ch_id = bl.bl_charge_id


К сообщению приложен файл (plans.7z - 50Kb) cкачать
19 сен 12, 08:43    [13186127]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
За опускание схемы в именах объектов - вешают за яйца.
19 сен 12, 10:56    [13187005]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Student007
(SELECT job FROM k_job WHERE job_id = bl_job_id) job, -- этот фокус нужен, чтобы для аксеса запрос был обновляемым
А можете прокомментировать?
19 сен 12, 14:37    [13189334]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
Student007
Member

Откуда: Санкт-Петербург
Сообщений: 671
Mnior
За опускание схемы в именах объектов - вешают за яйца.


Вешают за яйца тех, кто не знает какая схема используется по умолчанию =)
Добавил схемы, но ситуация не изменилась.

Mnior
Student007
(SELECT job FROM k_job WHERE job_id = bl_job_id) job, -- этот фокус нужен, чтобы для аксеса запрос был обновляемым
А можете прокомментировать?

В аксесе есть табличные формы, а у них источник записей вида "SELECT * FROM some_view". Когда some_view - это logistics.q_bill_list_print (с джойнами), то аксес не может понять, что этот вью обновляемый и не позволяет вносить изменения на форме. Но когда переписать вью в виде logistics.q_bill_list, то аксес полагает, что вью обновляемый и вносить изменения дает. Полагаю, что решение об обновляемости источника данных аксес делает как раз через анализ вывода sp_MShelpcolumns, игнорируя WITH VIEW_METADATA, триггеры INSTEAD OF UPDATE и другие достижения цивилизации =(
19 сен 12, 18:55    [13191522]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
Student007
Member

Откуда: Санкт-Петербург
Сообщений: 671
Сужал проблему и вот к чему пришел:

SELECT IDENT_SEED('logistics.q_bill_list_print') --выполняется мгновенно
SELECT IDENT_SEED('logistics.q_bill_list') --выполняется вечно
19 сен 12, 22:22    [13192523]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Student007
Вешают за яйца тех, кто не знает какая схема используется по умолчанию =)
Добавил схемы, но ситуация не изменилась.
Нет такого как схема по умолчанию для запросов. Оно есть только для создаваемых объектов (т.е. вы можете вместо CREATE VIEW schema.name написать CREATE VIEW name) и только. За это не вешают, за это бьют по затылку за не соблюдение стандартов/форматов.
Когда нет схем то привязки нет и сервер только на момент вызова будет подбирать объекты, по цепочке, что и сказывается на производительности. Не говоря что банально непонятно и не надёжно и дыряво (кстати был такой вид атак, кажись).

Student007
игнорируя WITH VIEW_METADATA
Уроды.
Только не очень понятно как он не видит. Может вы имеете ввиду что там типа Expression и поэтому нельзя такое VIEW обновлять через метаданные?
Блин, ну зачем же так издеваться над собой и сервером, можно банально добавить так:
,k.job + 0 AS job
,k.job + '' AS job
,Convert(<Type>,k.job) AS job -- Наверно так лучше
Что, разве не катит?
19 сен 12, 22:36    [13192615]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Student007
Сужал проблему и вот к чему пришел:
SELECT IDENT_SEED('logistics.q_bill_list_print') --выполняется мгновенно
SELECT IDENT_SEED('logistics.q_bill_list') --выполняется вечно
Может вы не там и не везде схемы прописали? Хотя врятли.

Но если прописали всё верно, то там видимо что-то плохо с под-запросами обрабатывается (Ну тогда сделайте как я прописал).
Вот только непонятно как. Кажись не видны в трассе вызовы системных функций, поэтому гипотетически там может банально на какой-то ColumnProperty/ObjectProperty зависает. Кстати этими системными вызовами запрос сильно перегружен. Один два раз можно простить, но так - изврат. И вообще зря они так вообще разрешили - а всё от лени.
19 сен 12, 22:57    [13192718]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Student007,

Для начала, накатите CU3
Если не поможет, попробуйте SQL Server 2008 R2.

Кстати, еще можете попробовать поменять провайдера в строке подключения SQLOLEDB <-> SQLNCLI
19 сен 12, 23:18    [13192826]     Ответить | Цитировать Сообщить модератору
 Re: sp_MShelpcolumns зависает на некоторых view  [new]
step_ks
Member

Откуда:
Сообщений: 936
Student007
Сужал проблему и вот к чему пришел:

SELECT IDENT_SEED('logistics.q_bill_list_print') --выполняется мгновенно
SELECT IDENT_SEED('logistics.q_bill_list') --выполняется вечно

с DBCC CHECKDB всё нормально? что с блокировками (exec sp_who2) во время вечного?
19 сен 12, 23:24    [13192851]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить