another deadlock question

Is there a way to run Profiler to trap just deadlock info and not all the
info on the box? I set Error 1205 in the filters but still I got lots of
extra info.
--
SQL2K SP3
TIA, ChrisRChris,
Profiler allows you to trace the following deadlock related events:
- Lock:Deadlock
- Lock:Deadlock Chain
Personally, I find the deadlocking info you can get in the errorlog from
trace flag 1205 much more helpful. To turn on this traceflag you just run:
DBCC TRACEON (3605, 1205, -1)
1205 will return info regarding the nodes involved in a deadlock
(when/if they happen). 3605 will log that info to the errorlog. -1
will apply the trace flags to all sessions (i.e. at the server level)
rather than just for the current connection. Turning on the trace flags
this way, however, is not permanent. They'll be reset (i.e. turned off)
again the next time the you stop & restart SQL. To turn them on at
start time, add the following startup parameters to the server using SQLEM:
-T3605 -T1205
Unfortunately these particular trace flags are not documented in SQL BOL
(but they're pretty well known). But BOL does have a little info about
DBCC TRACEON, DBCC TRACEOFF & DBCC TRACESTATUS (as well as SQL startup
options).
Cheers,
Mike
ChrisR wrote:
> Is there a way to run Profiler to trap just deadlock info and not all the
> info on the box? I set Error 1205 in the filters but still I got lots of
> extra info.
> --
> SQL2K SP3
> TIA, ChrisR
>|||> Profiler allows you to trace the following deadlock related events:
> - Lock:Deadlock
> - Lock:Deadlock Chain
Thanks Mike. Im using these. The problem is that I'm still getting back more
info than I like. Which is why Im trying to filter.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:ug60Q4i5EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Profiler allows you to trace the following deadlock related events:
> - Lock:Deadlock
> - Lock:Deadlock Chain
> Personally, I find the deadlocking info you can get in the errorlog from
> trace flag 1205 much more helpful. To turn on this traceflag you just
run:
> DBCC TRACEON (3605, 1205, -1)
> 1205 will return info regarding the nodes involved in a deadlock
> (when/if they happen). 3605 will log that info to the errorlog. -1
> will apply the trace flags to all sessions (i.e. at the server level)
> rather than just for the current connection. Turning on the trace flags
> this way, however, is not permanent. They'll be reset (i.e. turned off)
> again the next time the you stop & restart SQL. To turn them on at
> start time, add the following startup parameters to the server using
SQLEM:
> -T3605 -T1205
> Unfortunately these particular trace flags are not documented in SQL BOL
> (but they're pretty well known). But BOL does have a little info about
> DBCC TRACEON, DBCC TRACEOFF & DBCC TRACESTATUS (as well as SQL startup
> options).
> Cheers,
> Mike
> ChrisR wrote:
> > Is there a way to run Profiler to trap just deadlock info and not all
the
> > info on the box? I set Error 1205 in the filters but still I got lots of
> > extra info.
> >
> > --
> > SQL2K SP3
> >
> > TIA, ChrisR
> >
> >|||The deadlock reporting traceflag is 1204 NOT 1205. That is the source of
the extra information: 1205 export the analysis for every deadlock search,
which is a lock scan looking for a loop, regardless if it finds one. Only
use 1205 if you are have serious deadlock issues and need preemptive
information; otherwise, 1204 should give you sufficient information from any
detected deadlock candidates.
Sincerely,
Anthony Thomas
"ChrisR" <bla@.noemail.com> wrote in message
news:u8DRDRk5EHA.1396@.tk2msftngp13.phx.gbl...
> Profiler allows you to trace the following deadlock related events:
> - Lock:Deadlock
> - Lock:Deadlock Chain
Thanks Mike. Im using these. The problem is that I'm still getting back more
info than I like. Which is why Im trying to filter.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:ug60Q4i5EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Profiler allows you to trace the following deadlock related events:
> - Lock:Deadlock
> - Lock:Deadlock Chain
> Personally, I find the deadlocking info you can get in the errorlog from
> trace flag 1205 much more helpful. To turn on this traceflag you just
run:
> DBCC TRACEON (3605, 1205, -1)
> 1205 will return info regarding the nodes involved in a deadlock
> (when/if they happen). 3605 will log that info to the errorlog. -1
> will apply the trace flags to all sessions (i.e. at the server level)
> rather than just for the current connection. Turning on the trace flags
> this way, however, is not permanent. They'll be reset (i.e. turned off)
> again the next time the you stop & restart SQL. To turn them on at
> start time, add the following startup parameters to the server using
SQLEM:
> -T3605 -T1205
> Unfortunately these particular trace flags are not documented in SQL BOL
> (but they're pretty well known). But BOL does have a little info about
> DBCC TRACEON, DBCC TRACEOFF & DBCC TRACESTATUS (as well as SQL startup
> options).
> Cheers,
> Mike
> ChrisR wrote:
> > Is there a way to run Profiler to trap just deadlock info and not all
the
> > info on the box? I set Error 1205 in the filters but still I got lots of
> > extra info.
> >
> > --
> > SQL2K SP3
> >
> > TIA, ChrisR
> >
> >|||Yep, I always get 1204 & 1205 mixed up - I usually just turn them on one
at a time and you can tell in about 3 seconds which is the right one by
looking at the errorlog (I was going from memory this time). But, yes,
1204 is the traceflag I meant (otherwise, with 1205, the errorlog fills
up rapidly with fairly useless deadlock cycle detection crap).
My apologies for the errata.
Basically, Chris, profiler will not give you enough info to troubleshoot
your deadlocks (at least not without tracing pretty much everything,
which makes finding the info you're after very difficult). You're
better off turning on the traceflag and checking the errorlog next time
a deadlock occurs.
(It's been my observation that the 2 most common questions on
microsoft.public.sqlserver.server are 1) resolving/troubleshooting
deadlocks and 2) shrinking the transaction log.)
Cheers,
Mike.
AnthonyThomas wrote:
> The deadlock reporting traceflag is 1204 NOT 1205. That is the source of
> the extra information: 1205 export the analysis for every deadlock search,
> which is a lock scan looking for a loop, regardless if it finds one. Only
> use 1205 if you are have serious deadlock issues and need preemptive
> information; otherwise, 1204 should give you sufficient information from any
> detected deadlock candidates.
> Sincerely,
>
> Anthony Thomas
>

another deadlock question

Is there a way to run Profiler to trap just deadlock info and not all the
info on the box? I set Error 1205 in the filters but still I got lots of
extra info.
SQL2K SP3
TIA, ChrisR
Chris,
Profiler allows you to trace the following deadlock related events:
- Lock:Deadlock
- Lock:Deadlock Chain
Personally, I find the deadlocking info you can get in the errorlog from
trace flag 1205 much more helpful. To turn on this traceflag you just run:
DBCC TRACEON (3605, 1205, -1)
1205 will return info regarding the nodes involved in a deadlock
(when/if they happen). 3605 will log that info to the errorlog. -1
will apply the trace flags to all sessions (i.e. at the server level)
rather than just for the current connection. Turning on the trace flags
this way, however, is not permanent. They'll be reset (i.e. turned off)
again the next time the you stop & restart SQL. To turn them on at
start time, add the following startup parameters to the server using SQLEM:
-T3605 -T1205
Unfortunately these particular trace flags are not documented in SQL BOL
(but they're pretty well known). But BOL does have a little info about
DBCC TRACEON, DBCC TRACEOFF & DBCC TRACESTATUS (as well as SQL startup
options).
Cheers,
Mike
ChrisR wrote:
> Is there a way to run Profiler to trap just deadlock info and not all the
> info on the box? I set Error 1205 in the filters but still I got lots of
> extra info.
> --
> SQL2K SP3
> TIA, ChrisR
>
|||> Profiler allows you to trace the following deadlock related events:
> - Lock:Deadlock
> - Lock:Deadlock Chain
Thanks Mike. Im using these. The problem is that I'm still getting back more
info than I like. Which is why Im trying to filter.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:ug60Q4i5EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Profiler allows you to trace the following deadlock related events:
> - Lock:Deadlock
> - Lock:Deadlock Chain
> Personally, I find the deadlocking info you can get in the errorlog from
> trace flag 1205 much more helpful. To turn on this traceflag you just
run:
> DBCC TRACEON (3605, 1205, -1)
> 1205 will return info regarding the nodes involved in a deadlock
> (when/if they happen). 3605 will log that info to the errorlog. -1
> will apply the trace flags to all sessions (i.e. at the server level)
> rather than just for the current connection. Turning on the trace flags
> this way, however, is not permanent. They'll be reset (i.e. turned off)
> again the next time the you stop & restart SQL. To turn them on at
> start time, add the following startup parameters to the server using
SQLEM:[vbcol=seagreen]
> -T3605 -T1205
> Unfortunately these particular trace flags are not documented in SQL BOL
> (but they're pretty well known). But BOL does have a little info about
> DBCC TRACEON, DBCC TRACEOFF & DBCC TRACESTATUS (as well as SQL startup
> options).
> Cheers,
> Mike
> ChrisR wrote:
the[vbcol=seagreen]
|||The deadlock reporting traceflag is 1204 NOT 1205. That is the source of
the extra information: 1205 export the analysis for every deadlock search,
which is a lock scan looking for a loop, regardless if it finds one. Only
use 1205 if you are have serious deadlock issues and need preemptive
information; otherwise, 1204 should give you sufficient information from any
detected deadlock candidates.
Sincerely,
Anthony Thomas

"ChrisR" <bla@.noemail.com> wrote in message
news:u8DRDRk5EHA.1396@.tk2msftngp13.phx.gbl...
> Profiler allows you to trace the following deadlock related events:
> - Lock:Deadlock
> - Lock:Deadlock Chain
Thanks Mike. Im using these. The problem is that I'm still getting back more
info than I like. Which is why Im trying to filter.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:ug60Q4i5EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Profiler allows you to trace the following deadlock related events:
> - Lock:Deadlock
> - Lock:Deadlock Chain
> Personally, I find the deadlocking info you can get in the errorlog from
> trace flag 1205 much more helpful. To turn on this traceflag you just
run:
> DBCC TRACEON (3605, 1205, -1)
> 1205 will return info regarding the nodes involved in a deadlock
> (when/if they happen). 3605 will log that info to the errorlog. -1
> will apply the trace flags to all sessions (i.e. at the server level)
> rather than just for the current connection. Turning on the trace flags
> this way, however, is not permanent. They'll be reset (i.e. turned off)
> again the next time the you stop & restart SQL. To turn them on at
> start time, add the following startup parameters to the server using
SQLEM:[vbcol=seagreen]
> -T3605 -T1205
> Unfortunately these particular trace flags are not documented in SQL BOL
> (but they're pretty well known). But BOL does have a little info about
> DBCC TRACEON, DBCC TRACEOFF & DBCC TRACESTATUS (as well as SQL startup
> options).
> Cheers,
> Mike
> ChrisR wrote:
the[vbcol=seagreen]
|||Yep, I always get 1204 & 1205 mixed up - I usually just turn them on one
at a time and you can tell in about 3 seconds which is the right one by
looking at the errorlog (I was going from memory this time). But, yes,
1204 is the traceflag I meant (otherwise, with 1205, the errorlog fills
up rapidly with fairly useless deadlock cycle detection crap).
My apologies for the errata.
Basically, Chris, profiler will not give you enough info to troubleshoot
your deadlocks (at least not without tracing pretty much everything,
which makes finding the info you're after very difficult). You're
better off turning on the traceflag and checking the errorlog next time
a deadlock occurs.
(It's been my observation that the 2 most common questions on
microsoft.public.sqlserver.server are 1) resolving/troubleshooting
deadlocks and 2) shrinking the transaction log.)
Cheers,
Mike.
AnthonyThomas wrote:
> The deadlock reporting traceflag is 1204 NOT 1205. That is the source of
> the extra information: 1205 export the analysis for every deadlock search,
> which is a lock scan looking for a loop, regardless if it finds one. Only
> use 1205 if you are have serious deadlock issues and need preemptive
> information; otherwise, 1204 should give you sufficient information from any
> detected deadlock candidates.
> Sincerely,
>
> Anthony Thomas
>

annoying SQL Error msg - "INTRA-QUERY"

Hi,

Pbbly most of you know this:

Intra-query parallelism caused your server command (process ID #57) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).

I've seen MS KnowledgeBase for that (http://support.microsoft.com/default.aspx?scid=kb;EN-US;837983)

But I have some probs with it:
1. I don't have permissions for this kind of queries
"USE master
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE WITH OVERRIDE
GO"
2. I don't know what is an "intra-query parallelism"...

I'm attaching the query I use. The query runs OK for some parameters but gets this error msg on other parameters.

I'm using a single CPU on 2003 STD Edition.

Query:

create table #mytemp_table
(client_id int not null, commission_value int null)
insert into #mytemp_table (client_id, commission_value)
select client_id, sum(transfer_value) from users_transfers where transfer_type in (1,3) and is_paid = 1 group by client_id

select
u.name [Vendor],
u.email,
sum(cost) as Payment,
cmpd.vendor_id,
ua.name [Agent],
vd.join_date,
commission_value [paid],
allow_sign
from
reports ra
left outer join codes ctd on ra.affiliation_code_show = ctd.affiliation_code_show
left outer join traffic cmp on cmp.campaign_id = ctd.campaign_id
left outer join traffic_details cmpd on cmp.campaign_id = cmpd.campaign_id
left outer join userssb u on cmpd.vendor_id = u.client_id
left outer join userssb ua on cmpd.agent_id = ua.client_id
left outer join users_details vd on u.client_id = vd.client_id
left outer join #mytemp_table com_paid on com_paid.client_id = u.client_id
left outer join allow_db asd on asd.client_id = u.client_id
where
[conditions]
group by

[all the group fields]
having sum(cost) > 2999
order by u.name

drop table #mytemp_table1. running sp_configure affects the entire server. This is really not what you want to do anyway.

This is how you use Maxdop statement hint.

select
u.name [Vendor],
u.email,
sum(cost) as Payment,
cmpd.vendor_id,
ua.name [Agent],
vd.join_date,
commission_value [paid],
allow_sign
from
reports ra
left outer join codes ctd on ra.affiliation_code_show = ctd.affiliation_code_show
left outer join traffic cmp on cmp.campaign_id = ctd.campaign_id
left outer join traffic_details cmpd on cmp.campaign_id = cmpd.campaign_id
left outer join userssb u on cmpd.vendor_id = u.client_id
left outer join userssb ua on cmpd.agent_id = ua.client_id
left outer join users_details vd on u.client_id = vd.client_id
left outer join #mytemp_table com_paid on com_paid.client_id = u.client_id
left outer join allow_db asd on asd.client_id = u.client_id
where
[conditions]
group by

[all the group fields]
having sum(cost) > 2999
order by u.name
OPTION(MAXDOP 1)
--OPTION (MERGE JOIN, LOOP JOIN)

2. you probably have hyperthreading (i.e. 1 physical, 2 logical). Intra-query para means the statement is getting splitted up and executed in parallel. Each logical cpu takes a part of the query and perform the work.