Is it possible to track or find a the author of a permission change on a
specific object?
ex: Find who gave the update permission to the xxx user
Hi,
See the below system procedure:-
SP_HELPROTECT
Thanks
Hari
SQL Server MVP
"Christian Allard" <Christian.allard@.atvent.com> wrote in message
news:OrbpFc8rFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Is it possible to track or find a the author of a permission change on a
> specific object?
> ex: Find who gave the update permission to the xxx user
Showing posts with label track. Show all posts
Showing posts with label track. Show all posts
Monday, March 19, 2012
Monday, March 12, 2012
find query plan
We are using SQL 2005.
What is the query that uses DMVs to track a query plan of a particular sproc
running on a server?
we noticed a particular sproc thats performing poorly when being called from
the client vs when being called from QA and hence wanted to get the query
plan that was called from the client
I know you can get that in 2005 which was not possible in 2000.
What is the query ? I think I have seen it presented and it seems abit
complex that calls plan handles and has a few joins..
ThanksHassan
Try this one ( I could not remember who wrote this function)
CREATE FUNCTION statement_level_query_plan(
@.handle as varbinary(64) -- Handle for the overall query plan
)
RETURNS TABLE as
RETURN (
select
statement_nbr, -- Sequential number of statement
within batch or SP
statement_type, -- SELECT, INSERT, UPDATE, etc
statement_subtree_cost, -- Estimated Query Cost
statement_estimated_rows, -- Estimated Rows Returned
statement_optimization_level, -- FULL or TRIVIAL
statement_text, -- Text of query
statement_plan -- XML Plan To view as a graphical
plan
-- save the column
output to a file with extension .SQLPlan
-- then reopen the
file by double-clicking
from (
select
C.value('@.StatementId','int') as statement_nbr,
C.value('(./@.StatementText)','nvarchar(max)') as
statement_text,
C.value('(./@.StatementType)','varchar(20)') as
statement_type,
C.value('(./@.StatementSubTreeCost)','float') as
statement_subtree_cost,
C.value('(./@.StatementEstRows)','float') as
statement_estimated_rows,
C.value('(./@.StatementOptmLevel)','varchar(20)') as
statement_optimization_level,
-- Construct the XML headers around the single plan that will
permit
-- this column to be used as a graphical showplan.
-- Only generate plan columns where statement has an associated
plan
C.query('declare namespace
PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
if (./PLN:QueryPlan or ./PLN:Condition/PLN:QueryPlan)
then
<PLN:ShowPlanXML><PLN:BatchSequence><PLN:Batch><PLN:Statements><PLN:StmtSimple>
{ ./attribute::* }
{ ./descendant::PLN:QueryPlan[1] }
</PLN:StmtSimple></PLN:Statements></PLN:Batch></PLN:BatchSequence></PLN:ShowPlanXML>
else ()
') as statement_plan
from
sys.dm_exec_query_plan(@.handle)
CROSS APPLY
-- This expression finds all nodes containing attribute
StatementText
-- regardless of how deep they are in the potentially nested
batch hierarchy
-- The results of this expression are processed by the Select
expressions above
query_plan.nodes('declare namespace
PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/PLN:ShowPlanXML/PLN:BatchSequence/PLN:Batch/PLN:Statements/descendant::*[attribute::StatementText]')
as T(C)
) x
)
--So, the query to report all executing
--SQL statements and their plans is:
select pln.*, req.* from sys.dm_exec_requests as req
CROSS APPLY statement_level_query_plan(plan_handle) as pln
where statement_text like
'%' +
replace(
left(
substring((select text from
master.sys.dm_exec_sql_text(sql_handle)),
statement_start_offset/2,
1+ case when statement_end_offset = -1
then LEN((select text from
master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
else statement_end_offset/2 -
statement_start_offset/2
end)
,3000)
, '[','[[]') + '%'
--Even more useful,
--the function can be joined to the
--view sys.dm_exec_query_stats that reports
-- per-statement IO, CPU and duration statistics.
-- For example, to find the plans for the
--10 queries with longest average durations:
select pln.*, req.* from
(select top 10 * from sys.dm_exec_query_stats
order by total_elapsed_time/execution_count desc ) req
CROSS APPLY statement_level_query_plan(plan_handle) as pln
where statement_text like
'%' +
replace(
left(
substring((select text from
master.sys.dm_exec_sql_text(sql_handle)),
statement_start_offset/2,
1+ case when statement_end_offset = -1
then LEN((select text from
master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
else statement_end_offset/2 -
statement_start_offset/2
end)
,3000)
, '[','[[]') + '%'
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23QcCMTSFIHA.2268@.TK2MSFTNGP02.phx.gbl...
> We are using SQL 2005.
> What is the query that uses DMVs to track a query plan of a particular
> sproc running on a server?
> we noticed a particular sproc thats performing poorly when being called
> from the client vs when being called from QA and hence wanted to get the
> query plan that was called from the client
> I know you can get that in 2005 which was not possible in 2000.
> What is the query ? I think I have seen it presented and it seems abit
> complex that calls plan handles and has a few joins..
> Thanks
>|||Uri,
Could you attach the file ? The entire formatting is not coming along well
and hence nor parsing.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e5uuDDUFIHA.1056@.TK2MSFTNGP03.phx.gbl...
> Hassan
> Try this one ( I could not remember who wrote this function)
> CREATE FUNCTION statement_level_query_plan(
> @.handle as varbinary(64) -- Handle for the overall query plan
> )
> RETURNS TABLE as
> RETURN (
> select
> statement_nbr, -- Sequential number of statement
> within batch or SP
> statement_type, -- SELECT, INSERT, UPDATE,
> etc
> statement_subtree_cost, -- Estimated Query Cost
> statement_estimated_rows, -- Estimated Rows Returned
> statement_optimization_level, -- FULL or TRIVIAL
> statement_text, -- Text of query
> statement_plan -- XML Plan To view as a graphical
> plan
> -- save the
> column output to a file with extension .SQLPlan
> -- then reopen
> the file by double-clicking
> from (
> select
> C.value('@.StatementId','int') as statement_nbr,
> C.value('(./@.StatementText)','nvarchar(max)') as
> statement_text,
> C.value('(./@.StatementType)','varchar(20)') as
> statement_type,
> C.value('(./@.StatementSubTreeCost)','float') as
> statement_subtree_cost,
> C.value('(./@.StatementEstRows)','float') as
> statement_estimated_rows,
> C.value('(./@.StatementOptmLevel)','varchar(20)') as
> statement_optimization_level,
> -- Construct the XML headers around the single plan that will
> permit
> -- this column to be used as a graphical showplan.
> -- Only generate plan columns where statement has an
> associated plan
> C.query('declare namespace
> PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> if (./PLN:QueryPlan or
> ./PLN:Condition/PLN:QueryPlan)
> then
>
> <PLN:ShowPlanXML><PLN:BatchSequence><PLN:Batch><PLN:Statements><PLN:StmtSimple>
> { ./attribute::* }
> { ./descendant::PLN:QueryPlan[1] }
>
> </PLN:StmtSimple></PLN:Statements></PLN:Batch></PLN:BatchSequence></PLN:ShowPlanXML>
> else ()
> ') as statement_plan
> from
> sys.dm_exec_query_plan(@.handle)
> CROSS APPLY
> -- This expression finds all nodes containing attribute
> StatementText
> -- regardless of how deep they are in the potentially nested
> batch hierarchy
> -- The results of this expression are processed by the Select
> expressions above
> query_plan.nodes('declare namespace
> PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
>
> /PLN:ShowPlanXML/PLN:BatchSequence/PLN:Batch/PLN:Statements/descendant::*[attribute::StatementText]')
> as T(C)
> ) x
> )
>
> --So, the query to report all executing
> --SQL statements and their plans is:
> select pln.*, req.* from sys.dm_exec_requests as req
> CROSS APPLY statement_level_query_plan(plan_handle) as pln
> where statement_text like
> '%' +
> replace(
> left(
> substring((select text from
> master.sys.dm_exec_sql_text(sql_handle)),
> statement_start_offset/2,
> 1+ case when statement_end_offset = -1
> then LEN((select text from
> master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
> else statement_end_offset/2 -
> statement_start_offset/2
> end)
> ,3000)
> , '[','[[]') + '%'
>
> --Even more useful,
> --the function can be joined to the
> --view sys.dm_exec_query_stats that reports
> -- per-statement IO, CPU and duration statistics.
> -- For example, to find the plans for the
> --10 queries with longest average durations:
>
> select pln.*, req.* from
> (select top 10 * from sys.dm_exec_query_stats
> order by total_elapsed_time/execution_count desc ) req
> CROSS APPLY statement_level_query_plan(plan_handle) as pln
> where statement_text like
> '%' +
> replace(
> left(
> substring((select text from
> master.sys.dm_exec_sql_text(sql_handle)),
> statement_start_offset/2,
> 1+ case when statement_end_offset = -1
> then LEN((select text from
> master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
> else statement_end_offset/2 -
> statement_start_offset/2
> end)
> ,3000)
> , '[','[[]') + '%'
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23QcCMTSFIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> We are using SQL 2005.
>> What is the query that uses DMVs to track a query plan of a particular
>> sproc running on a server?
>> we noticed a particular sproc thats performing poorly when being called
>> from the client vs when being called from QA and hence wanted to get the
>> query plan that was called from the client
>> I know you can get that in 2005 which was not possible in 2000.
>> What is the query ? I think I have seen it presented and it seems abit
>> complex that calls plan handles and has a few joins..
>> Thanks
>|||Sure
"Hassan" <hassan@.hotmail.com> wrote in message
news:%239yIidZFIHA.2004@.TK2MSFTNGP06.phx.gbl...
> Uri,
> Could you attach the file ? The entire formatting is not coming along well
> and hence nor parsing.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e5uuDDUFIHA.1056@.TK2MSFTNGP03.phx.gbl...
>> Hassan
>> Try this one ( I could not remember who wrote this function)
>> CREATE FUNCTION statement_level_query_plan(
>> @.handle as varbinary(64) -- Handle for the overall query plan
>> )
>> RETURNS TABLE as
>> RETURN (
>> select
>> statement_nbr, -- Sequential number of statement
>> within batch or SP
>> statement_type, -- SELECT, INSERT, UPDATE,
>> etc
>> statement_subtree_cost, -- Estimated Query Cost
>> statement_estimated_rows, -- Estimated Rows Returned
>> statement_optimization_level, -- FULL or TRIVIAL
>> statement_text, -- Text of query
>> statement_plan -- XML Plan To view as a
>> graphical
>> plan
>> -- save the
>> column output to a file with extension .SQLPlan
>> -- then reopen
>> the file by double-clicking
>> from (
>> select
>> C.value('@.StatementId','int') as statement_nbr,
>> C.value('(./@.StatementText)','nvarchar(max)') as
>> statement_text,
>> C.value('(./@.StatementType)','varchar(20)') as
>> statement_type,
>> C.value('(./@.StatementSubTreeCost)','float') as
>> statement_subtree_cost,
>> C.value('(./@.StatementEstRows)','float') as
>> statement_estimated_rows,
>> C.value('(./@.StatementOptmLevel)','varchar(20)') as
>> statement_optimization_level,
>> -- Construct the XML headers around the single plan that will
>> permit
>> -- this column to be used as a graphical showplan.
>> -- Only generate plan columns where statement has an
>> associated plan
>> C.query('declare namespace
>> PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
>> if (./PLN:QueryPlan or
>> ./PLN:Condition/PLN:QueryPlan)
>> then
>>
>> <PLN:ShowPlanXML><PLN:BatchSequence><PLN:Batch><PLN:Statements><PLN:StmtSimple>
>> { ./attribute::* }
>> { ./descendant::PLN:QueryPlan[1] }
>>
>> </PLN:StmtSimple></PLN:Statements></PLN:Batch></PLN:BatchSequence></PLN:ShowPlanXML>
>> else ()
>> ') as statement_plan
>> from
>> sys.dm_exec_query_plan(@.handle)
>> CROSS APPLY
>> -- This expression finds all nodes containing attribute
>> StatementText
>> -- regardless of how deep they are in the potentially nested
>> batch hierarchy
>> -- The results of this expression are processed by the Select
>> expressions above
>> query_plan.nodes('declare namespace
>> PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
>>
>> /PLN:ShowPlanXML/PLN:BatchSequence/PLN:Batch/PLN:Statements/descendant::*[attribute::StatementText]')
>> as T(C)
>> ) x
>> )
>>
>> --So, the query to report all executing
>> --SQL statements and their plans is:
>> select pln.*, req.* from sys.dm_exec_requests as req
>> CROSS APPLY statement_level_query_plan(plan_handle) as pln
>> where statement_text like
>> '%' +
>> replace(
>> left(
>> substring((select text from
>> master.sys.dm_exec_sql_text(sql_handle)),
>> statement_start_offset/2,
>> 1+ case when statement_end_offset = -1
>> then LEN((select text from
>> master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
>> else statement_end_offset/2 -
>> statement_start_offset/2
>> end)
>> ,3000)
>> , '[','[[]') + '%'
>>
>> --Even more useful,
>> --the function can be joined to the
>> --view sys.dm_exec_query_stats that reports
>> -- per-statement IO, CPU and duration statistics.
>> -- For example, to find the plans for the
>> --10 queries with longest average durations:
>>
>> select pln.*, req.* from
>> (select top 10 * from sys.dm_exec_query_stats
>> order by total_elapsed_time/execution_count desc ) req
>> CROSS APPLY statement_level_query_plan(plan_handle) as pln
>> where statement_text like
>> '%' +
>> replace(
>> left(
>> substring((select text from
>> master.sys.dm_exec_sql_text(sql_handle)),
>> statement_start_offset/2,
>> 1+ case when statement_end_offset = -1
>> then LEN((select text from
>> master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
>> else statement_end_offset/2 -
>> statement_start_offset/2
>> end)
>> ,3000)
>> , '[','[[]') + '%'
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:%23QcCMTSFIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> We are using SQL 2005.
>> What is the query that uses DMVs to track a query plan of a particular
>> sproc running on a server?
>> we noticed a particular sproc thats performing poorly when being called
>> from the client vs when being called from QA and hence wanted to get the
>> query plan that was called from the client
>> I know you can get that in 2005 which was not possible in 2000.
>> What is the query ? I think I have seen it presented and it seems abit
>> complex that calls plan handles and has a few joins..
>> Thanks
>>
>
begin 666 Query(Problems).txt
M0U)%051%($953D-424].('-T871E;65N=%]L979E;%]Q=65R>5]P;&%N* T*
M#0H@.(" @.(" @.($!H86YD;&4@.87,@.=F%R8FEN87)Y*#8T*2 M+2!(86YD;&4@.
M9F]R('1H92!O=F5R86QL('%U97)Y('!L86X-"@.T**0T*#0I215154DY3(%1!
M0DQ%(&%S( T*#0I215154DX@.* T*#0H@.('-E;&5C=" -"@.T*(" @.(" @.("!S
M=&%T96UE;G1?;F)R+" @.(" @.(" @.(" @.(" @.(" @.+2T@.4V5Q=65N=&EA;"!N
M=6UB97(@.;V8@.<W1A=&5M96YT('=I=&AI;B!B871C:"!O<B!34 T*#0H@.(" @.
M(" @.('-T871E;65N=%]T>7!E+" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.+2T@.
M4T5,14-4+"!)3E-%4E0L(%501$%412P@.971C#0H-"B @.(" @.(" @.<W1A=&5M
M96YT7W-U8G1R965?8V]S="P@.(" @.(" @.(" @.(" @.(" M+2!%<W1I;6%T960@.
M475E<GD@.0V]S= T*#0H@.(" @.(" @.('-T871E;65N=%]E<W1I;6%T961?<F]W
M<RP@.(" @.(" @.(" @.(" @.+2T@.17-T:6UA=&5D(%)O=W,@.4F5T=7)N960-"@.T*
M(" @.(" @.("!S=&%T96UE;G1?;W!T:6UI>F%T:6]N7VQE=F5L+" @.(" @.(" @.
M("TM($953$P@.;W(@.5%))5DE!3 T*#0H@.(" @.(" @.('-T871E;65N=%]T97AT
M+" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.+2T@.5&5X="!O9B!Q=65R>0T*#0H@.
M(" @.(" @.('-T871E;65N=%]P;&%N(" @.(" @.(" @.(" @.(" @.("TM(%A-3"!0
M;&%N(" @.(%1O('9I97<@.87,@.82!G<F%P:&EC86P@.<&QA;@.T*#0H@.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M("TM(" @.(" @.<V%V92!T:&4@.8V]L=6UN(&]U='!U="!T;R!A(&9I;&4@.=VET
M:"!E>'1E;G-I;VX@.+E-13%!L86X-"@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" M+2 @.(" @.('1H96X@.
M<F5O<&5N('1H92!F:6QE(&)Y(&1O=6)L92UC;&EC:VEN9PT*#0H@.("!F<F]M
M("@.-"@.T*(" @.(" @.("!S96QE8W0@.#0H-"B @.(" @.(" @.(" @.(" @.($,N=F%L
M=64H)T!3=&%T96UE;G1)9"<L)VEN="<I(&%S('-T871E;65N=%]N8G(L#0H-
M"B @.(" @.(" @.(" @.(" @.($,N=F%L=64H)R@.N+T!3=&%T96UE;G1497AT*2<L
M)VYV87)C:&%R*&UA>"DG*2!A<R!S=&%T96UE;G1?=&5X="P-"@.T*(" @.(" @.
M(" @.(" @.(" @.0RYV86QU92@.G*"XO0%-T871E;65N=%1Y<&4I)RPG=F%R8VAA
M<B@.R,"DG*2!A<R!S=&%T96UE;G1?='EP92P-"@.T*(" @.(" @.(" @.(" @.(" @.
M0RYV86QU92@.G*"XO0%-T871E;65N=%-U8E1R965#;W-T*2<L)V9L;V%T)RD@.
M87,@.<W1A=&5M96YT7W-U8G1R965?8V]S="P-"@.T*(" @.(" @.(" @.(" @.(" @.
M0RYV86QU92@.G*"XO0%-T871E;65N=$5S=%)O=W,I)RPG9FQO870G*2!A<R!S
M=&%T96UE;G1?97-T:6UA=&5D7W)O=W,L#0H-"B @.(" @.(" @.(" @.(" @.($,N
M=F%L=64H)R@.N+T!3=&%T96UE;G1/<'1M3&5V96PI)RPG=F%R8VAA<B@.R,"DG
M*2!A<R!S=&%T96UE;G1?;W!T:6UI>F%T:6]N7VQE=F5L+ T*#0HM+2 @.(" @.
M(" @.(" @.("!#;VYS=')U8W0@.=&AE(%A-3"!H96%D97)S(&%R;W5N9"!T:&4@.
M<VEN9VQE('!L86X@.=&AA="!W:6QL('!E<FUI= T*#0HM+2 @.(" @.(" @.(" @.
M("!T:&ES(&-O;'5M;B!T;R!B92!U<V5D(&%S(&$@.9W)A<&AI8V%L('-H;W=P
M;&%N+@.T*#0HM+2 @.(" @.(" @.(" @.("!/;FQY(&=E;F5R871E('!L86X@.8V]L
M=6UN<R!W:&5R92!S=&%T96UE;G0@.:&%S(&%N(&%S<V]C:6%T960@.<&QA;@.T*
M#0H@.(" @.(" @.(" @.(" @.("!#+G%U97)Y*"=D96-L87)E(&YA;65S<&%C92!0
M3$X](FAT=' Z+R]S8VAE;6%S+FUI8W)O<V]F="YC;VTO<W%L<V5R=F5R+S(P
M,#0O,#<O<VAO=W!L86XB.PT*#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(&EF
M("@.N+U!,3CI1=65R>5!L86X@.;W(@.+B]03$XZ0V]N9&ET:6]N+U!,3CI1=65R
M>5!L86XI( T*#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.('1H96X-"@.T*(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#Q03$XZ4VAO=U!L86Y834P^
M/%!,3CI"871C:%-E<75E;F-E/CQ03$XZ0F%T8V@.^/%!,3CI3=&%T96UE;G1S
M/CQ03$XZ4W1M=%-I;7!L93X-"@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.>R N+V%T=')I8G5T93HZ*B!]#0H-"B @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.('L@.+B]D97-C96YD86YT.CI03$XZ475E<GE0;&%N6S%=M('T-"@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#PO4$Q..E-T
M;713:6UP;&4^/"]03$XZ4W1A=&5M96YT<SX\+U!,3CI"871C:#X\+U!,3CI"
M871C:%-E<75E;F-E/CPO4$Q..E-H;W=0;&%N6$U,/@.T*#0H@.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(&5L<V4@.*"D-"@.T*(" @.(" @.(" @.(" @.(" @.)RD@.87,@.
M<W1A=&5M96YT7W!L86X-"@.T*(" @.(" @.("!F<F]M( T*#0H@.(" @.(" @.(" @.
M(" @.("!S>7,N9&U?97AE8U]Q=65R>5]P;&%N*$!H86YD;&4I#0H-"B @.(" @.
M(" @.0U)/4U,@.05!03%D@.#0H-"BTM(" @.(" @.(" @.(" @.(%1H:7,@.97AP<F5S
M<VEO;B!F:6YD<R!A;&P@.;F]D97,@.8V]N=&%I;FEN9R!A='1R:6)U=&4@.4W1A
M=&5M96YT5&5X= T*#0HM+2 @.(" @.(" @.(" @.("!R96=A<F1L97-S(&]F(&AO
M=R!D965P('1H97D@.87)E(&EN('1H92!P;W1E;G1I86QL>2!N97-T960@.8F%T
M8V@.@.:&EE<F%R8VAY#0H-"BTM(" @.(" @.(" @.(" @.(%1H92!R97-U;'1S(&]F
M('1H:7,@.97AP<F5S<VEO;B!A<F4@.<')O8V5S<V5D(&)Y('1H92!396QE8W0@.
M97AP<F5S<VEO;G,@.86)O=F4-"@.T*(" @.(" @.(" @.(" @.(" @.<75E<GE?<&QA
M;BYN;V1E<R@.G9&5C;&%R92!N86UE<W!A8V4@.4$Q./2)H='1P.B\O<V-H96UA
M<RYM:6-R;W-O9G0N8V]M+W-Q;'-E<G9E<B\R,# T+S W+W-H;W=P;&%N(CL-
M"@.T*(" @.(" @.(" @.(" @.(" @.("]03$XZ4VAO=U!L86Y834PO4$Q..D)A=&-H
M4V5Q=65N8V4O4$Q..D)A=&-H+U!,3CI3=&%T96UE;G1S+V1E<V-E;F1A;G0Z
M.BI;871T<FEB=71E.CI3=&%T96UE;G1497AT72<I( T*#0H@.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(&%S(%0H0RD@.#0H-"B @.(" @.(" @.*2!X#0H-"B @.*0T*
M#0H-"B M+5-O+"!T:&4@.<75E<GD@.=&\@.<F5P;W)T(&%L;"!E>&5C=71I;F<@.
M#0HM+5-13"!S=&%T96UE;G1S(&%N9"!T:&5I<B!P;&%N<R!I<SH@.#0H-"G-E
M;&5C="!P;&XN*BP@.<F5Q+BH@.9G)O;2!S>7,N9&U?97AE8U]R97%U97-T<R!A
M<R!R97$-"@.T*0U)/4U,@.05!03%D@.<W1A=&5M96YT7VQE=F5L7W%U97)Y7W!L
M86XH<&QA;E]H86YD;&4I(&%S('!L;@.T*#0IW:&5R92!S=&%T96UE;G1?=&5X
M="!L:6ME#0H-"B<E)R K#0H-"G)E<&QA8V4H#0H-"FQE9G0H#0H-"B @.(" @.
M(" @.(" @.(" @.('-U8G-T<FEN9R@.H<V5L96-T('1E>'0@.9G)O;2!M87-T97(N
M<WES+F1M7V5X96-?<W%L7W1E>'0H<W%L7VAA;F1L92DI+" -"@.T*(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.("!S=&%T96UE;G1?<W1A<G1?;V9F<V5T+S(L( T*
M#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#$K(" @.(" @.8V%S92!W:&5N('-T
M871E;65N=%]E;F1?;V9F<V5T(#T@.+3$@.#0H-"B @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.('1H96X@.3$5.*"AS96QE8W0@.=&5X="!F<F]M(&UA<W1E
M<BYS>7,N9&U?97AE8U]S<6Q?=&5X="AS<6Q?:&%N9&QE*2DI("T@.<W1A=&5M
M96YT7W-T87)T7V]F9G-E="\R#0H-"B @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.("!E;'-E('-T871E;65N=%]E;F1?;V9F<V5T+S(@.+2!S=&%T96UE
M;G1?<W1A<G1?;V9F<V5T+S(@.#0H-"B @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(&5N9"D@.#0H-"B @.(" @.(" @.+#,P,# I#0H-"BP@.)ULG+"=;6UTG
M*2 K("<E)PT*#0H-"BTM179E;B!M;W)E('5S969U;"P@.#0HM+71H92!F=6YC
M=&EO;B!C86X@.8F4@.:F]I;F5D('1O('1H92 -"BTM=FEE=R!S>7,N9&U?97AE
M8U]Q=65R>5]S=&%T<R!T:&%T(')E<&]R=',-"BTM('!E<BUS=&%T96UE;G0@.
M24\L($-052!A;F0@.9'5R871I;VX@.<W1A=&ES=&EC<RX@.( T*+2T@.1F]R(&5X
M86UP;&4L('1O(&9I;F0@.=&AE('!L86YS(&9O<B!T:&4@.#0HM+3$P('%U97)I
M97,@.=VET:"!L;VYG97-T(&%V97)A9V4@.9'5R871I;VYS.@.T*#0H@.#0H-"G-E
M;&5C="!P;&XN*BP@.<F5Q+BH@.9G)O;0T*#0HH<V5L96-T('1O<" Q," J(&9R
M;VT@.<WES+F1M7V5X96-?<75E<GE?<W1A=',@.#0H-"F]R9&5R(&)Y('1O=&%L
M7V5L87!S961?=&EM92]E>&5C=71I;VY?8V]U;G0@.9&5S8R I(')E<0T*#0I#
M4D]34R!!4%!,62!S=&%T96UE;G1?;&5V96Q?<75E<GE?<&QA;BAP;&%N7VAA
M;F1L92D@.87,@.<&QN#0H-"G=H97)E('-T871E;65N=%]T97AT(&QI:V4-"@.T*
M)R4G("L-"@.T*<F5P;&%C92@.-"@.T*;&5F="@.-"@.T*(" @.(" @.(" @.(" @.(" @.
M<W5B<W1R:6YG*"AS96QE8W0@.=&5X="!F<F]M(&UA<W1E<BYS>7,N9&U?97AE
M8U]S<6Q?=&5X="AS<6Q?:&%N9&QE*2DL( T*#0H@.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.('-T871E;65N=%]S=&%R=%]O9F9S970O,BP@.#0H-"B @.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.,2L@.(" @.("!C87-E('=H96X@.<W1A=&5M96YT7V5N
M9%]O9F9S970@./2 M,2 -"@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.=&AE;B!,14XH*'-E;&5C="!T97AT(&9R;VT@.;6%S=&5R+G-Y<RYD;5]E
M>&5C7W-Q;%]T97AT*'-Q;%]H86YD;&4I*2D@.+2!S=&%T96UE;G1?<W1A<G1?
M;V9F<V5T+S(-"@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.96QS
M92!S=&%T96UE;G1?96YD7V]F9G-E="\R("T@.<W1A=&5M96YT7W-T87)T7V]F
M9G-E="\R( T*#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.("!E;F0I
M( T*#0H@.(" @.(" @.("PS,# P*0T*#0HL("=;)RPG6UM=)RD@.*R G)2<-"@.T*
`
end
What is the query that uses DMVs to track a query plan of a particular sproc
running on a server?
we noticed a particular sproc thats performing poorly when being called from
the client vs when being called from QA and hence wanted to get the query
plan that was called from the client
I know you can get that in 2005 which was not possible in 2000.
What is the query ? I think I have seen it presented and it seems abit
complex that calls plan handles and has a few joins..
ThanksHassan
Try this one ( I could not remember who wrote this function)
CREATE FUNCTION statement_level_query_plan(
@.handle as varbinary(64) -- Handle for the overall query plan
)
RETURNS TABLE as
RETURN (
select
statement_nbr, -- Sequential number of statement
within batch or SP
statement_type, -- SELECT, INSERT, UPDATE, etc
statement_subtree_cost, -- Estimated Query Cost
statement_estimated_rows, -- Estimated Rows Returned
statement_optimization_level, -- FULL or TRIVIAL
statement_text, -- Text of query
statement_plan -- XML Plan To view as a graphical
plan
-- save the column
output to a file with extension .SQLPlan
-- then reopen the
file by double-clicking
from (
select
C.value('@.StatementId','int') as statement_nbr,
C.value('(./@.StatementText)','nvarchar(max)') as
statement_text,
C.value('(./@.StatementType)','varchar(20)') as
statement_type,
C.value('(./@.StatementSubTreeCost)','float') as
statement_subtree_cost,
C.value('(./@.StatementEstRows)','float') as
statement_estimated_rows,
C.value('(./@.StatementOptmLevel)','varchar(20)') as
statement_optimization_level,
-- Construct the XML headers around the single plan that will
permit
-- this column to be used as a graphical showplan.
-- Only generate plan columns where statement has an associated
plan
C.query('declare namespace
PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
if (./PLN:QueryPlan or ./PLN:Condition/PLN:QueryPlan)
then
<PLN:ShowPlanXML><PLN:BatchSequence><PLN:Batch><PLN:Statements><PLN:StmtSimple>
{ ./attribute::* }
{ ./descendant::PLN:QueryPlan[1] }
</PLN:StmtSimple></PLN:Statements></PLN:Batch></PLN:BatchSequence></PLN:ShowPlanXML>
else ()
') as statement_plan
from
sys.dm_exec_query_plan(@.handle)
CROSS APPLY
-- This expression finds all nodes containing attribute
StatementText
-- regardless of how deep they are in the potentially nested
batch hierarchy
-- The results of this expression are processed by the Select
expressions above
query_plan.nodes('declare namespace
PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/PLN:ShowPlanXML/PLN:BatchSequence/PLN:Batch/PLN:Statements/descendant::*[attribute::StatementText]')
as T(C)
) x
)
--So, the query to report all executing
--SQL statements and their plans is:
select pln.*, req.* from sys.dm_exec_requests as req
CROSS APPLY statement_level_query_plan(plan_handle) as pln
where statement_text like
'%' +
replace(
left(
substring((select text from
master.sys.dm_exec_sql_text(sql_handle)),
statement_start_offset/2,
1+ case when statement_end_offset = -1
then LEN((select text from
master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
else statement_end_offset/2 -
statement_start_offset/2
end)
,3000)
, '[','[[]') + '%'
--Even more useful,
--the function can be joined to the
--view sys.dm_exec_query_stats that reports
-- per-statement IO, CPU and duration statistics.
-- For example, to find the plans for the
--10 queries with longest average durations:
select pln.*, req.* from
(select top 10 * from sys.dm_exec_query_stats
order by total_elapsed_time/execution_count desc ) req
CROSS APPLY statement_level_query_plan(plan_handle) as pln
where statement_text like
'%' +
replace(
left(
substring((select text from
master.sys.dm_exec_sql_text(sql_handle)),
statement_start_offset/2,
1+ case when statement_end_offset = -1
then LEN((select text from
master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
else statement_end_offset/2 -
statement_start_offset/2
end)
,3000)
, '[','[[]') + '%'
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23QcCMTSFIHA.2268@.TK2MSFTNGP02.phx.gbl...
> We are using SQL 2005.
> What is the query that uses DMVs to track a query plan of a particular
> sproc running on a server?
> we noticed a particular sproc thats performing poorly when being called
> from the client vs when being called from QA and hence wanted to get the
> query plan that was called from the client
> I know you can get that in 2005 which was not possible in 2000.
> What is the query ? I think I have seen it presented and it seems abit
> complex that calls plan handles and has a few joins..
> Thanks
>|||Uri,
Could you attach the file ? The entire formatting is not coming along well
and hence nor parsing.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e5uuDDUFIHA.1056@.TK2MSFTNGP03.phx.gbl...
> Hassan
> Try this one ( I could not remember who wrote this function)
> CREATE FUNCTION statement_level_query_plan(
> @.handle as varbinary(64) -- Handle for the overall query plan
> )
> RETURNS TABLE as
> RETURN (
> select
> statement_nbr, -- Sequential number of statement
> within batch or SP
> statement_type, -- SELECT, INSERT, UPDATE,
> etc
> statement_subtree_cost, -- Estimated Query Cost
> statement_estimated_rows, -- Estimated Rows Returned
> statement_optimization_level, -- FULL or TRIVIAL
> statement_text, -- Text of query
> statement_plan -- XML Plan To view as a graphical
> plan
> -- save the
> column output to a file with extension .SQLPlan
> -- then reopen
> the file by double-clicking
> from (
> select
> C.value('@.StatementId','int') as statement_nbr,
> C.value('(./@.StatementText)','nvarchar(max)') as
> statement_text,
> C.value('(./@.StatementType)','varchar(20)') as
> statement_type,
> C.value('(./@.StatementSubTreeCost)','float') as
> statement_subtree_cost,
> C.value('(./@.StatementEstRows)','float') as
> statement_estimated_rows,
> C.value('(./@.StatementOptmLevel)','varchar(20)') as
> statement_optimization_level,
> -- Construct the XML headers around the single plan that will
> permit
> -- this column to be used as a graphical showplan.
> -- Only generate plan columns where statement has an
> associated plan
> C.query('declare namespace
> PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> if (./PLN:QueryPlan or
> ./PLN:Condition/PLN:QueryPlan)
> then
>
> <PLN:ShowPlanXML><PLN:BatchSequence><PLN:Batch><PLN:Statements><PLN:StmtSimple>
> { ./attribute::* }
> { ./descendant::PLN:QueryPlan[1] }
>
> </PLN:StmtSimple></PLN:Statements></PLN:Batch></PLN:BatchSequence></PLN:ShowPlanXML>
> else ()
> ') as statement_plan
> from
> sys.dm_exec_query_plan(@.handle)
> CROSS APPLY
> -- This expression finds all nodes containing attribute
> StatementText
> -- regardless of how deep they are in the potentially nested
> batch hierarchy
> -- The results of this expression are processed by the Select
> expressions above
> query_plan.nodes('declare namespace
> PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
>
> /PLN:ShowPlanXML/PLN:BatchSequence/PLN:Batch/PLN:Statements/descendant::*[attribute::StatementText]')
> as T(C)
> ) x
> )
>
> --So, the query to report all executing
> --SQL statements and their plans is:
> select pln.*, req.* from sys.dm_exec_requests as req
> CROSS APPLY statement_level_query_plan(plan_handle) as pln
> where statement_text like
> '%' +
> replace(
> left(
> substring((select text from
> master.sys.dm_exec_sql_text(sql_handle)),
> statement_start_offset/2,
> 1+ case when statement_end_offset = -1
> then LEN((select text from
> master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
> else statement_end_offset/2 -
> statement_start_offset/2
> end)
> ,3000)
> , '[','[[]') + '%'
>
> --Even more useful,
> --the function can be joined to the
> --view sys.dm_exec_query_stats that reports
> -- per-statement IO, CPU and duration statistics.
> -- For example, to find the plans for the
> --10 queries with longest average durations:
>
> select pln.*, req.* from
> (select top 10 * from sys.dm_exec_query_stats
> order by total_elapsed_time/execution_count desc ) req
> CROSS APPLY statement_level_query_plan(plan_handle) as pln
> where statement_text like
> '%' +
> replace(
> left(
> substring((select text from
> master.sys.dm_exec_sql_text(sql_handle)),
> statement_start_offset/2,
> 1+ case when statement_end_offset = -1
> then LEN((select text from
> master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
> else statement_end_offset/2 -
> statement_start_offset/2
> end)
> ,3000)
> , '[','[[]') + '%'
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23QcCMTSFIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> We are using SQL 2005.
>> What is the query that uses DMVs to track a query plan of a particular
>> sproc running on a server?
>> we noticed a particular sproc thats performing poorly when being called
>> from the client vs when being called from QA and hence wanted to get the
>> query plan that was called from the client
>> I know you can get that in 2005 which was not possible in 2000.
>> What is the query ? I think I have seen it presented and it seems abit
>> complex that calls plan handles and has a few joins..
>> Thanks
>|||Sure
"Hassan" <hassan@.hotmail.com> wrote in message
news:%239yIidZFIHA.2004@.TK2MSFTNGP06.phx.gbl...
> Uri,
> Could you attach the file ? The entire formatting is not coming along well
> and hence nor parsing.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e5uuDDUFIHA.1056@.TK2MSFTNGP03.phx.gbl...
>> Hassan
>> Try this one ( I could not remember who wrote this function)
>> CREATE FUNCTION statement_level_query_plan(
>> @.handle as varbinary(64) -- Handle for the overall query plan
>> )
>> RETURNS TABLE as
>> RETURN (
>> select
>> statement_nbr, -- Sequential number of statement
>> within batch or SP
>> statement_type, -- SELECT, INSERT, UPDATE,
>> etc
>> statement_subtree_cost, -- Estimated Query Cost
>> statement_estimated_rows, -- Estimated Rows Returned
>> statement_optimization_level, -- FULL or TRIVIAL
>> statement_text, -- Text of query
>> statement_plan -- XML Plan To view as a
>> graphical
>> plan
>> -- save the
>> column output to a file with extension .SQLPlan
>> -- then reopen
>> the file by double-clicking
>> from (
>> select
>> C.value('@.StatementId','int') as statement_nbr,
>> C.value('(./@.StatementText)','nvarchar(max)') as
>> statement_text,
>> C.value('(./@.StatementType)','varchar(20)') as
>> statement_type,
>> C.value('(./@.StatementSubTreeCost)','float') as
>> statement_subtree_cost,
>> C.value('(./@.StatementEstRows)','float') as
>> statement_estimated_rows,
>> C.value('(./@.StatementOptmLevel)','varchar(20)') as
>> statement_optimization_level,
>> -- Construct the XML headers around the single plan that will
>> permit
>> -- this column to be used as a graphical showplan.
>> -- Only generate plan columns where statement has an
>> associated plan
>> C.query('declare namespace
>> PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
>> if (./PLN:QueryPlan or
>> ./PLN:Condition/PLN:QueryPlan)
>> then
>>
>> <PLN:ShowPlanXML><PLN:BatchSequence><PLN:Batch><PLN:Statements><PLN:StmtSimple>
>> { ./attribute::* }
>> { ./descendant::PLN:QueryPlan[1] }
>>
>> </PLN:StmtSimple></PLN:Statements></PLN:Batch></PLN:BatchSequence></PLN:ShowPlanXML>
>> else ()
>> ') as statement_plan
>> from
>> sys.dm_exec_query_plan(@.handle)
>> CROSS APPLY
>> -- This expression finds all nodes containing attribute
>> StatementText
>> -- regardless of how deep they are in the potentially nested
>> batch hierarchy
>> -- The results of this expression are processed by the Select
>> expressions above
>> query_plan.nodes('declare namespace
>> PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
>>
>> /PLN:ShowPlanXML/PLN:BatchSequence/PLN:Batch/PLN:Statements/descendant::*[attribute::StatementText]')
>> as T(C)
>> ) x
>> )
>>
>> --So, the query to report all executing
>> --SQL statements and their plans is:
>> select pln.*, req.* from sys.dm_exec_requests as req
>> CROSS APPLY statement_level_query_plan(plan_handle) as pln
>> where statement_text like
>> '%' +
>> replace(
>> left(
>> substring((select text from
>> master.sys.dm_exec_sql_text(sql_handle)),
>> statement_start_offset/2,
>> 1+ case when statement_end_offset = -1
>> then LEN((select text from
>> master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
>> else statement_end_offset/2 -
>> statement_start_offset/2
>> end)
>> ,3000)
>> , '[','[[]') + '%'
>>
>> --Even more useful,
>> --the function can be joined to the
>> --view sys.dm_exec_query_stats that reports
>> -- per-statement IO, CPU and duration statistics.
>> -- For example, to find the plans for the
>> --10 queries with longest average durations:
>>
>> select pln.*, req.* from
>> (select top 10 * from sys.dm_exec_query_stats
>> order by total_elapsed_time/execution_count desc ) req
>> CROSS APPLY statement_level_query_plan(plan_handle) as pln
>> where statement_text like
>> '%' +
>> replace(
>> left(
>> substring((select text from
>> master.sys.dm_exec_sql_text(sql_handle)),
>> statement_start_offset/2,
>> 1+ case when statement_end_offset = -1
>> then LEN((select text from
>> master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
>> else statement_end_offset/2 -
>> statement_start_offset/2
>> end)
>> ,3000)
>> , '[','[[]') + '%'
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:%23QcCMTSFIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> We are using SQL 2005.
>> What is the query that uses DMVs to track a query plan of a particular
>> sproc running on a server?
>> we noticed a particular sproc thats performing poorly when being called
>> from the client vs when being called from QA and hence wanted to get the
>> query plan that was called from the client
>> I know you can get that in 2005 which was not possible in 2000.
>> What is the query ? I think I have seen it presented and it seems abit
>> complex that calls plan handles and has a few joins..
>> Thanks
>>
>
begin 666 Query(Problems).txt
M0U)%051%($953D-424].('-T871E;65N=%]L979E;%]Q=65R>5]P;&%N* T*
M#0H@.(" @.(" @.($!H86YD;&4@.87,@.=F%R8FEN87)Y*#8T*2 M+2!(86YD;&4@.
M9F]R('1H92!O=F5R86QL('%U97)Y('!L86X-"@.T**0T*#0I215154DY3(%1!
M0DQ%(&%S( T*#0I215154DX@.* T*#0H@.('-E;&5C=" -"@.T*(" @.(" @.("!S
M=&%T96UE;G1?;F)R+" @.(" @.(" @.(" @.(" @.(" @.+2T@.4V5Q=65N=&EA;"!N
M=6UB97(@.;V8@.<W1A=&5M96YT('=I=&AI;B!B871C:"!O<B!34 T*#0H@.(" @.
M(" @.('-T871E;65N=%]T>7!E+" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.+2T@.
M4T5,14-4+"!)3E-%4E0L(%501$%412P@.971C#0H-"B @.(" @.(" @.<W1A=&5M
M96YT7W-U8G1R965?8V]S="P@.(" @.(" @.(" @.(" @.(" M+2!%<W1I;6%T960@.
M475E<GD@.0V]S= T*#0H@.(" @.(" @.('-T871E;65N=%]E<W1I;6%T961?<F]W
M<RP@.(" @.(" @.(" @.(" @.+2T@.17-T:6UA=&5D(%)O=W,@.4F5T=7)N960-"@.T*
M(" @.(" @.("!S=&%T96UE;G1?;W!T:6UI>F%T:6]N7VQE=F5L+" @.(" @.(" @.
M("TM($953$P@.;W(@.5%))5DE!3 T*#0H@.(" @.(" @.('-T871E;65N=%]T97AT
M+" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.+2T@.5&5X="!O9B!Q=65R>0T*#0H@.
M(" @.(" @.('-T871E;65N=%]P;&%N(" @.(" @.(" @.(" @.(" @.("TM(%A-3"!0
M;&%N(" @.(%1O('9I97<@.87,@.82!G<F%P:&EC86P@.<&QA;@.T*#0H@.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M("TM(" @.(" @.<V%V92!T:&4@.8V]L=6UN(&]U='!U="!T;R!A(&9I;&4@.=VET
M:"!E>'1E;G-I;VX@.+E-13%!L86X-"@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" M+2 @.(" @.('1H96X@.
M<F5O<&5N('1H92!F:6QE(&)Y(&1O=6)L92UC;&EC:VEN9PT*#0H@.("!F<F]M
M("@.-"@.T*(" @.(" @.("!S96QE8W0@.#0H-"B @.(" @.(" @.(" @.(" @.($,N=F%L
M=64H)T!3=&%T96UE;G1)9"<L)VEN="<I(&%S('-T871E;65N=%]N8G(L#0H-
M"B @.(" @.(" @.(" @.(" @.($,N=F%L=64H)R@.N+T!3=&%T96UE;G1497AT*2<L
M)VYV87)C:&%R*&UA>"DG*2!A<R!S=&%T96UE;G1?=&5X="P-"@.T*(" @.(" @.
M(" @.(" @.(" @.0RYV86QU92@.G*"XO0%-T871E;65N=%1Y<&4I)RPG=F%R8VAA
M<B@.R,"DG*2!A<R!S=&%T96UE;G1?='EP92P-"@.T*(" @.(" @.(" @.(" @.(" @.
M0RYV86QU92@.G*"XO0%-T871E;65N=%-U8E1R965#;W-T*2<L)V9L;V%T)RD@.
M87,@.<W1A=&5M96YT7W-U8G1R965?8V]S="P-"@.T*(" @.(" @.(" @.(" @.(" @.
M0RYV86QU92@.G*"XO0%-T871E;65N=$5S=%)O=W,I)RPG9FQO870G*2!A<R!S
M=&%T96UE;G1?97-T:6UA=&5D7W)O=W,L#0H-"B @.(" @.(" @.(" @.(" @.($,N
M=F%L=64H)R@.N+T!3=&%T96UE;G1/<'1M3&5V96PI)RPG=F%R8VAA<B@.R,"DG
M*2!A<R!S=&%T96UE;G1?;W!T:6UI>F%T:6]N7VQE=F5L+ T*#0HM+2 @.(" @.
M(" @.(" @.("!#;VYS=')U8W0@.=&AE(%A-3"!H96%D97)S(&%R;W5N9"!T:&4@.
M<VEN9VQE('!L86X@.=&AA="!W:6QL('!E<FUI= T*#0HM+2 @.(" @.(" @.(" @.
M("!T:&ES(&-O;'5M;B!T;R!B92!U<V5D(&%S(&$@.9W)A<&AI8V%L('-H;W=P
M;&%N+@.T*#0HM+2 @.(" @.(" @.(" @.("!/;FQY(&=E;F5R871E('!L86X@.8V]L
M=6UN<R!W:&5R92!S=&%T96UE;G0@.:&%S(&%N(&%S<V]C:6%T960@.<&QA;@.T*
M#0H@.(" @.(" @.(" @.(" @.("!#+G%U97)Y*"=D96-L87)E(&YA;65S<&%C92!0
M3$X](FAT=' Z+R]S8VAE;6%S+FUI8W)O<V]F="YC;VTO<W%L<V5R=F5R+S(P
M,#0O,#<O<VAO=W!L86XB.PT*#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(&EF
M("@.N+U!,3CI1=65R>5!L86X@.;W(@.+B]03$XZ0V]N9&ET:6]N+U!,3CI1=65R
M>5!L86XI( T*#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.('1H96X-"@.T*(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#Q03$XZ4VAO=U!L86Y834P^
M/%!,3CI"871C:%-E<75E;F-E/CQ03$XZ0F%T8V@.^/%!,3CI3=&%T96UE;G1S
M/CQ03$XZ4W1M=%-I;7!L93X-"@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.>R N+V%T=')I8G5T93HZ*B!]#0H-"B @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.('L@.+B]D97-C96YD86YT.CI03$XZ475E<GE0;&%N6S%=M('T-"@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#PO4$Q..E-T
M;713:6UP;&4^/"]03$XZ4W1A=&5M96YT<SX\+U!,3CI"871C:#X\+U!,3CI"
M871C:%-E<75E;F-E/CPO4$Q..E-H;W=0;&%N6$U,/@.T*#0H@.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(&5L<V4@.*"D-"@.T*(" @.(" @.(" @.(" @.(" @.)RD@.87,@.
M<W1A=&5M96YT7W!L86X-"@.T*(" @.(" @.("!F<F]M( T*#0H@.(" @.(" @.(" @.
M(" @.("!S>7,N9&U?97AE8U]Q=65R>5]P;&%N*$!H86YD;&4I#0H-"B @.(" @.
M(" @.0U)/4U,@.05!03%D@.#0H-"BTM(" @.(" @.(" @.(" @.(%1H:7,@.97AP<F5S
M<VEO;B!F:6YD<R!A;&P@.;F]D97,@.8V]N=&%I;FEN9R!A='1R:6)U=&4@.4W1A
M=&5M96YT5&5X= T*#0HM+2 @.(" @.(" @.(" @.("!R96=A<F1L97-S(&]F(&AO
M=R!D965P('1H97D@.87)E(&EN('1H92!P;W1E;G1I86QL>2!N97-T960@.8F%T
M8V@.@.:&EE<F%R8VAY#0H-"BTM(" @.(" @.(" @.(" @.(%1H92!R97-U;'1S(&]F
M('1H:7,@.97AP<F5S<VEO;B!A<F4@.<')O8V5S<V5D(&)Y('1H92!396QE8W0@.
M97AP<F5S<VEO;G,@.86)O=F4-"@.T*(" @.(" @.(" @.(" @.(" @.<75E<GE?<&QA
M;BYN;V1E<R@.G9&5C;&%R92!N86UE<W!A8V4@.4$Q./2)H='1P.B\O<V-H96UA
M<RYM:6-R;W-O9G0N8V]M+W-Q;'-E<G9E<B\R,# T+S W+W-H;W=P;&%N(CL-
M"@.T*(" @.(" @.(" @.(" @.(" @.("]03$XZ4VAO=U!L86Y834PO4$Q..D)A=&-H
M4V5Q=65N8V4O4$Q..D)A=&-H+U!,3CI3=&%T96UE;G1S+V1E<V-E;F1A;G0Z
M.BI;871T<FEB=71E.CI3=&%T96UE;G1497AT72<I( T*#0H@.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(&%S(%0H0RD@.#0H-"B @.(" @.(" @.*2!X#0H-"B @.*0T*
M#0H-"B M+5-O+"!T:&4@.<75E<GD@.=&\@.<F5P;W)T(&%L;"!E>&5C=71I;F<@.
M#0HM+5-13"!S=&%T96UE;G1S(&%N9"!T:&5I<B!P;&%N<R!I<SH@.#0H-"G-E
M;&5C="!P;&XN*BP@.<F5Q+BH@.9G)O;2!S>7,N9&U?97AE8U]R97%U97-T<R!A
M<R!R97$-"@.T*0U)/4U,@.05!03%D@.<W1A=&5M96YT7VQE=F5L7W%U97)Y7W!L
M86XH<&QA;E]H86YD;&4I(&%S('!L;@.T*#0IW:&5R92!S=&%T96UE;G1?=&5X
M="!L:6ME#0H-"B<E)R K#0H-"G)E<&QA8V4H#0H-"FQE9G0H#0H-"B @.(" @.
M(" @.(" @.(" @.('-U8G-T<FEN9R@.H<V5L96-T('1E>'0@.9G)O;2!M87-T97(N
M<WES+F1M7V5X96-?<W%L7W1E>'0H<W%L7VAA;F1L92DI+" -"@.T*(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.("!S=&%T96UE;G1?<W1A<G1?;V9F<V5T+S(L( T*
M#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#$K(" @.(" @.8V%S92!W:&5N('-T
M871E;65N=%]E;F1?;V9F<V5T(#T@.+3$@.#0H-"B @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.('1H96X@.3$5.*"AS96QE8W0@.=&5X="!F<F]M(&UA<W1E
M<BYS>7,N9&U?97AE8U]S<6Q?=&5X="AS<6Q?:&%N9&QE*2DI("T@.<W1A=&5M
M96YT7W-T87)T7V]F9G-E="\R#0H-"B @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.("!E;'-E('-T871E;65N=%]E;F1?;V9F<V5T+S(@.+2!S=&%T96UE
M;G1?<W1A<G1?;V9F<V5T+S(@.#0H-"B @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(&5N9"D@.#0H-"B @.(" @.(" @.+#,P,# I#0H-"BP@.)ULG+"=;6UTG
M*2 K("<E)PT*#0H-"BTM179E;B!M;W)E('5S969U;"P@.#0HM+71H92!F=6YC
M=&EO;B!C86X@.8F4@.:F]I;F5D('1O('1H92 -"BTM=FEE=R!S>7,N9&U?97AE
M8U]Q=65R>5]S=&%T<R!T:&%T(')E<&]R=',-"BTM('!E<BUS=&%T96UE;G0@.
M24\L($-052!A;F0@.9'5R871I;VX@.<W1A=&ES=&EC<RX@.( T*+2T@.1F]R(&5X
M86UP;&4L('1O(&9I;F0@.=&AE('!L86YS(&9O<B!T:&4@.#0HM+3$P('%U97)I
M97,@.=VET:"!L;VYG97-T(&%V97)A9V4@.9'5R871I;VYS.@.T*#0H@.#0H-"G-E
M;&5C="!P;&XN*BP@.<F5Q+BH@.9G)O;0T*#0HH<V5L96-T('1O<" Q," J(&9R
M;VT@.<WES+F1M7V5X96-?<75E<GE?<W1A=',@.#0H-"F]R9&5R(&)Y('1O=&%L
M7V5L87!S961?=&EM92]E>&5C=71I;VY?8V]U;G0@.9&5S8R I(')E<0T*#0I#
M4D]34R!!4%!,62!S=&%T96UE;G1?;&5V96Q?<75E<GE?<&QA;BAP;&%N7VAA
M;F1L92D@.87,@.<&QN#0H-"G=H97)E('-T871E;65N=%]T97AT(&QI:V4-"@.T*
M)R4G("L-"@.T*<F5P;&%C92@.-"@.T*;&5F="@.-"@.T*(" @.(" @.(" @.(" @.(" @.
M<W5B<W1R:6YG*"AS96QE8W0@.=&5X="!F<F]M(&UA<W1E<BYS>7,N9&U?97AE
M8U]S<6Q?=&5X="AS<6Q?:&%N9&QE*2DL( T*#0H@.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.('-T871E;65N=%]S=&%R=%]O9F9S970O,BP@.#0H-"B @.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.,2L@.(" @.("!C87-E('=H96X@.<W1A=&5M96YT7V5N
M9%]O9F9S970@./2 M,2 -"@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.=&AE;B!,14XH*'-E;&5C="!T97AT(&9R;VT@.;6%S=&5R+G-Y<RYD;5]E
M>&5C7W-Q;%]T97AT*'-Q;%]H86YD;&4I*2D@.+2!S=&%T96UE;G1?<W1A<G1?
M;V9F<V5T+S(-"@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.96QS
M92!S=&%T96UE;G1?96YD7V]F9G-E="\R("T@.<W1A=&5M96YT7W-T87)T7V]F
M9G-E="\R( T*#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.("!E;F0I
M( T*#0H@.(" @.(" @.("PS,# P*0T*#0HL("=;)RPG6UM=)RD@.*R G)2<-"@.T*
`
end
Wednesday, March 7, 2012
find invalid views etc.
Hi,
Newbie as I am I cannot find the method to track down invalid objects in a MS SQLServer database.
Looking through documentation and looking at internet does not provide much help.
Please help
WilcoThe only method you find is the one you create using the Mark 1 mod 0 eyeball!!
For instance ... you create and sucessfully compile a sproc. Then you alter the schema of the table and drop a column. The proc knows nothing about the altered table until you try to execute it, at which time SQL Server will throw the error.
Newbie as I am I cannot find the method to track down invalid objects in a MS SQLServer database.
Looking through documentation and looking at internet does not provide much help.
Please help
WilcoThe only method you find is the one you create using the Mark 1 mod 0 eyeball!!
For instance ... you create and sucessfully compile a sproc. Then you alter the schema of the table and drop a column. The proc knows nothing about the altered table until you try to execute it, at which time SQL Server will throw the error.
Subscribe to:
Posts (Atom)