Has anyone written routines to analyze sysjobhistory? I'm looking for a tool/routine to analyze jobs for failures, trends (such as constantly increasing run times) and other information. It's not as straighforward as I had originally hoped it would be.
I am specifically prohibited from using any 3rd-party tools (too expensive per mgmt).
I would be grateful for any input/insight.
Regards,
hmscottIts advised not to query system tables directly when system supplied stored procedures are availble, have you looked into SP_HELP_JOBHISTRY and other corresponding SPs. Refer to books online for more information.|||Satya,
Yes, I had looked at that SP. The basic issue I had with it was that the Date and time stamps are configured as INTs and are not combined into an integrated date/time field.
Your reminder is a good one, however; I am nearly finished with working on a centralized repository for job execution history. I will look again and try to avoid querying the system tables directly.
Regards,
hmscott
Showing posts with label analyzing. Show all posts
Showing posts with label analyzing. Show all posts
Sunday, February 19, 2012
Analyzing Stored Procedures - does a tool exist?
My boss wants me to go through the many stored procedures that are on our many SQL servers and generate a list of how often they are used, and then use that info to retire any stored procs that are not being used.
I did a search for tools and came across the SPUD tool at http://www.hybridx.com but the only problem with that tool is that i need to modify each stored procedure before it will start logging information on the stored proc.
Does anyone know of or use any tools that generate this sort of information in a non-invasive way? Any help would be greatly appreciated. Thanks.
There really isn't any tool that will do this sort of thing for you other
than running a constant profiler trace with sp_completed and see which ones
are called. Of coarse just because it isn't called for a day or so does not
mean it isn't used but it will give you a start and then you can narrow down
the list.
Andrew J. Kelly SQL MVP
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:6EBFC2B2-6FCC-4826-BCBA-9FFA351F2EF7@.microsoft.com...
> My boss wants me to go through the many stored procedures that are on our
many SQL servers and generate a list of how often they are used, and then
use that info to retire any stored procs that are not being used.
> I did a search for tools and came across the SPUD tool at
http://www.hybridx.com but the only problem with that tool is that i need to
modify each stored procedure before it will start logging information on the
stored proc.
> Does anyone know of or use any tools that generate this sort of
information in a non-invasive way? Any help would be greatly appreciated.
Thanks.
|||<<
> http://www.hybridx.com but the only problem with that tool is that i need
to
> modify each stored procedure before it will start logging information on
the[vbcol=seagreen]
> stored proc.
That sounds like a horrible idea to me. That creates too many opportunites
for thier code to cause problems with your code.
Andrew's suggestion of running Profiler is probably the best thing to do.
Brian
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGTvDp$cEHA.1764@.TK2MSFTNGP10.phx.gbl...
> There really isn't any tool that will do this sort of thing for you other
> than running a constant profiler trace with sp_completed and see which
ones
> are called. Of coarse just because it isn't called for a day or so does
not
> mean it isn't used but it will give you a start and then you can narrow
down[vbcol=seagreen]
> the list.
> --
> Andrew J. Kelly SQL MVP
>
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:6EBFC2B2-6FCC-4826-BCBA-9FFA351F2EF7@.microsoft.com...
our
> many SQL servers and generate a list of how often they are used, and then
> use that info to retire any stored procs that are not being used.
> http://www.hybridx.com but the only problem with that tool is that i need
to
> modify each stored procedure before it will start logging information on
the
> stored proc.
> information in a non-invasive way? Any help would be greatly appreciated.
> Thanks.
>
I did a search for tools and came across the SPUD tool at http://www.hybridx.com but the only problem with that tool is that i need to modify each stored procedure before it will start logging information on the stored proc.
Does anyone know of or use any tools that generate this sort of information in a non-invasive way? Any help would be greatly appreciated. Thanks.
There really isn't any tool that will do this sort of thing for you other
than running a constant profiler trace with sp_completed and see which ones
are called. Of coarse just because it isn't called for a day or so does not
mean it isn't used but it will give you a start and then you can narrow down
the list.
Andrew J. Kelly SQL MVP
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:6EBFC2B2-6FCC-4826-BCBA-9FFA351F2EF7@.microsoft.com...
> My boss wants me to go through the many stored procedures that are on our
many SQL servers and generate a list of how often they are used, and then
use that info to retire any stored procs that are not being used.
> I did a search for tools and came across the SPUD tool at
http://www.hybridx.com but the only problem with that tool is that i need to
modify each stored procedure before it will start logging information on the
stored proc.
> Does anyone know of or use any tools that generate this sort of
information in a non-invasive way? Any help would be greatly appreciated.
Thanks.
|||<<
> http://www.hybridx.com but the only problem with that tool is that i need
to
> modify each stored procedure before it will start logging information on
the[vbcol=seagreen]
> stored proc.
That sounds like a horrible idea to me. That creates too many opportunites
for thier code to cause problems with your code.
Andrew's suggestion of running Profiler is probably the best thing to do.
Brian
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGTvDp$cEHA.1764@.TK2MSFTNGP10.phx.gbl...
> There really isn't any tool that will do this sort of thing for you other
> than running a constant profiler trace with sp_completed and see which
ones
> are called. Of coarse just because it isn't called for a day or so does
not
> mean it isn't used but it will give you a start and then you can narrow
down[vbcol=seagreen]
> the list.
> --
> Andrew J. Kelly SQL MVP
>
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:6EBFC2B2-6FCC-4826-BCBA-9FFA351F2EF7@.microsoft.com...
our
> many SQL servers and generate a list of how often they are used, and then
> use that info to retire any stored procs that are not being used.
> http://www.hybridx.com but the only problem with that tool is that i need
to
> modify each stored procedure before it will start logging information on
the
> stored proc.
> information in a non-invasive way? Any help would be greatly appreciated.
> Thanks.
>
ANALYZING SQL SERVICE HANGUP
The SQL Service hangs up at peak time once a day. What is the best way to
analyze the problem
Hi
Run sp_who2 from Query Analyser to see if blocking occurs.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Riaz Khan" wrote:
> The SQL Service hangs up at peak time once a day. What is the best way to
> analyze the problem
>
|||Posting twice the same question in 70 minutes does not guarantee a quicker
answer.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Riaz Khan" wrote:
> The SQL Service hangs up at peak time once a day. What is the best way to
> analyze the problem
>
analyze the problem
Hi
Run sp_who2 from Query Analyser to see if blocking occurs.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Riaz Khan" wrote:
> The SQL Service hangs up at peak time once a day. What is the best way to
> analyze the problem
>
|||Posting twice the same question in 70 minutes does not guarantee a quicker
answer.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Riaz Khan" wrote:
> The SQL Service hangs up at peak time once a day. What is the best way to
> analyze the problem
>
ANALYZING SQL SERVICE HANGUP
The SQL Service hangs up at peak time once a day. What is the best way to
analyze the problemHi
Run sp_who2 from Query Analyser to see if blocking occurs.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Riaz Khan" wrote:
> The SQL Service hangs up at peak time once a day. What is the best way to
> analyze the problem
>|||Posting twice the same question in 70 minutes does not guarantee a quicker
answer.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Riaz Khan" wrote:
> The SQL Service hangs up at peak time once a day. What is the best way to
> analyze the problem
>
analyze the problemHi
Run sp_who2 from Query Analyser to see if blocking occurs.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Riaz Khan" wrote:
> The SQL Service hangs up at peak time once a day. What is the best way to
> analyze the problem
>|||Posting twice the same question in 70 minutes does not guarantee a quicker
answer.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Riaz Khan" wrote:
> The SQL Service hangs up at peak time once a day. What is the best way to
> analyze the problem
>
ANALYZING SQL SERVICE HANGUP
The SQL Service hangs up at peak time once a day. What is the best way to
analyze the problemHi
Run sp_who2 from Query Analyser to see if blocking occurs.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Riaz Khan" wrote:
> The SQL Service hangs up at peak time once a day. What is the best way to
> analyze the problem
>|||Posting twice the same question in 70 minutes does not guarantee a quicker
answer.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Riaz Khan" wrote:
> The SQL Service hangs up at peak time once a day. What is the best way to
> analyze the problem
>
analyze the problemHi
Run sp_who2 from Query Analyser to see if blocking occurs.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Riaz Khan" wrote:
> The SQL Service hangs up at peak time once a day. What is the best way to
> analyze the problem
>|||Posting twice the same question in 70 minutes does not guarantee a quicker
answer.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Riaz Khan" wrote:
> The SQL Service hangs up at peak time once a day. What is the best way to
> analyze the problem
>
Analyzing SQL Profiler trace
Hi All,
I am going over the output of a Profiler trace and I've found that the duration for many occurrences of EventClass 15 (Logout) is several seconds, up to a maximum of 20 seconds. That seems excessive just to complete a logout, so my question is, does the duration figure reflect only the time to complete the logout operation or does it include the total time that the connection has been active for?
Thanks in advance
LempsterLook up "Security Audit Data Columns" in BOL. For The logout event, the duration is the approximate time the connection was in existence.|||are you sure that is not milliseconds you are looking at?|||No, definitely seconds (20,000-odd milliseconds), but McCrowley's explanation makes sense - thanks!
:)
I am going over the output of a Profiler trace and I've found that the duration for many occurrences of EventClass 15 (Logout) is several seconds, up to a maximum of 20 seconds. That seems excessive just to complete a logout, so my question is, does the duration figure reflect only the time to complete the logout operation or does it include the total time that the connection has been active for?
Thanks in advance
LempsterLook up "Security Audit Data Columns" in BOL. For The logout event, the duration is the approximate time the connection was in existence.|||are you sure that is not milliseconds you are looking at?|||No, definitely seconds (20,000-odd milliseconds), but McCrowley's explanation makes sense - thanks!
:)
Analyzing MDX query evaluation
Are there any tools or tricks for looking at how an MDX query is evaluated in SSAS 2005? Something that indicates how aggregations are being used, for example - sort of like a SHOWPLAN for MDX.
As you might know, AS 2005 MDX query execution events are now captured in SQL Server Profiler. What's not as clear from the docs (that I've come across, anyway) is the aggregation usage info available via SQL Profiler. This was well explained at the recent SQL PASS Conference, in Session 108: "Understanding Analysis Services 2005 Aggregations from Every Angle", by Dave Wickert and Len Wyatt:
Thanks, that sounds very promising. Do you know where I can find more information about using Profiler for this purpose? That SQL PASS session doesn't appear to be downloadable (that I can find, at least), and the documentation isn't much help here (as you point out).
Thanks,
Kevin
|||Hi Kevin, CO1: Compilation CD-ROM set with full desktop capture - MP3 audio including live demos and PowerPoints (for attendees of the 2005 PASS Summit) CDROM (Attendees only) $ 195.00 
CO2: Compilation CD-ROM set with full desktop capture - MP3 audio including live demos and PowerPoints (for members of PASS who did not attend the 2005 Summit) CDRom (Members only) $ 395.00 CO3: Compilation CD-ROM set with full desktop capture - MP3 audio including live demos and PowerPoints (for non-attendees, non-members of PASS) CDRom $ 595.00
>>
As you might know, AS 2005 MDX query execution events are now captured in SQL Server Profiler. What's not as clear from the docs (that I've come across, anyway) is the aggregation usage info available via SQL Profiler. This was well explained at the recent SQL PASS Conference, in Session 108: "Understanding Analysis Services 2005 Aggregations from Every Angle", by Dave Wickert and Len Wyatt:
>>
SQL Profiler
Will tell you when:
an aggregation is used for a query
the partition data is used for a query
It won’t tell you why it decides to use or not use a particular aggregation
§>>
|||Deepak,Thanks, that sounds very promising. Do you know where I can find more information about using Profiler for this purpose? That SQL PASS session doesn't appear to be downloadable (that I can find, at least), and the documentation isn't much help here (as you point out).
Thanks,
Kevin
|||Hi Kevin,
If you're a PASS member, then the presentation slides should be downloadable, or you can order the physical media with MP3 sound. Now maybe Dave and Len are making their presentation (or equivalent info) available elsewhere - don't know!
http://www.softconference.com/250927
>>
>>
Analyzing Error log with Trace Flag 1204 turned on
Hello, I have what I believe should be a fairly simple question. I have a
server with trace flag 1204 turned on. I have entries in this log that show
deadlock information. i'm looking for information about how to analyze the
data in this log. It contains a log of information about things such as
Grant lists, keys, owners etc. I need information to explain what I'm
looking at.
The article "Troubleshooting Deadlocks" in Books Online explains what all
these terms mean.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> Hello, I have what I believe should be a fairly simple question. I have a
> server with trace flag 1204 turned on. I have entries in this log that
> show
> deadlock information. i'm looking for information about how to analyze
> the
> data in this log. It contains a log of information about things such as
> Grant lists, keys, owners etc. I need information to explain what I'm
> looking at.
|||Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
Here is an snippet from my error log:
10/30/04 22:16...
10/30/04 22:16
10/30/04 22:16Wait-for graph
10/30/04 22:16
10/30/04 22:16Node:1
10/30/04 22:16KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16Wait List:
10/30/04 22:16Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
SPID:79 ECID:0
10/30/04 22:16SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
10/30/04 22:16Input Buf: RPC Event: app_ProductUnit_RetrieveProductUnitData;1
10/30/04 22:16Requested By:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
Ec
0x73377558) Value:0x5b0
10/30/04 22:16
10/30/04 22:16Node:2
10/30/04 22:16KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16Grant List 0::
10/30/04 22:16Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:77 ECID:0
10/30/04 22:16SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
10/30/04 22:16Input Buf: RPC Event: app_ProductUnitAssoc_RetrieveTargetData;1
10/30/04 22:16Requested By:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec0x5182B558) Value:0x23a
10/30/04 22:16
10/30/04 22:16Node:3
10/30/04 22:16KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X Flags:
0x0
10/30/04 22:16Grant List 1::
10/30/04 22:16Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:71 ECID:0
10/30/04 22:16SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
10/30/04 22:16Input Buf: RPC Event: sp_executesql;1
10/30/04 22:16Requested By:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
Ec0x51DF7558) Value:0x3b4
10/30/04 22:16Victim Resource Owner:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec0x5182B558) Value:0x23a
I do not see enough information in BOL around what the information on the
lines that start with KEY represent. Are they locks currently held or locks
requested? Also I do not understand what information is included on the line
starting with ResType (i.e. what are ResType and Stype) I'm finding it very
challenging to look at this log and deduce the sequence of the calls and
locks that led to my deadlock.
If you have any further advice or links I'd appreciate it.
Mike
"Kalen Delaney" wrote:
> The article "Troubleshooting Deadlocks" in Books Online explains what all
> these terms mean.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
>
>
|||Hi Mike, and thanks, :-)
If the truth be known, I usually don't use the traceflag output for the real
troubleshooting. If I am trying to track down deadlocks, I hae this flag
enabled, and I also have a trace running to capture deadlock events. I use
this traceflag output only to get the spids and the time, and then I can
find what I need in the trace output, which shows me the statements that led
up to the deadlock. Usually that's enough to figure it out.
The keys can be either the ones being waited on or the ones requested. It
depends where in the output the line occurs.
I have quite a bit of info on interpreting this output and understand lock
resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
Locking and Blocking.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...[vbcol=seagreen]
> Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> Here is an snippet from my error log:
> 10/30/04 22:16 ...
> 10/30/04 22:16
> 10/30/04 22:16 Wait-for graph
> 10/30/04 22:16
> 10/30/04 22:16 Node:1
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Wait List:
> 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> SPID:79 ECID:0
> 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnit_RetrieveProductUnitData;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> Ec0x73377558) Value:0x5b0
> 10/30/04 22:16
> 10/30/04 22:16 Node:2
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 0::
> 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:77 ECID:0
> 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnitAssoc_RetrieveTargetData;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec0x5182B558) Value:0x23a
> 10/30/04 22:16
> 10/30/04 22:16 Node:3
> 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 1::
> 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:71 ECID:0
> 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> Ec0x51DF7558) Value:0x3b4
> 10/30/04 22:16 Victim Resource Owner:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec0x5182B558) Value:0x23a
> I do not see enough information in BOL around what the information on the
> lines that start with KEY represent. Are they locks currently held or
> locks
> requested? Also I do not understand what information is included on the
> line
> starting with ResType (i.e. what are ResType and Stype) I'm finding it
> very
> challenging to look at this log and deduce the sequence of the calls and
> locks that led to my deadlock.
> If you have any further advice or links I'd appreciate it.
> Mike
>
> "Kalen Delaney" wrote:
|||Hi Kalen,
Ok, sounds good. I have read the appropriate section out of Inside SQL
Server 2000. One last (hopefully) follow up question for you. Do you have a
sample profiler template that you commonly use to assist in troubleshooting
deadlocks? My dilemna is this...is there a way to show the queries leading
up to a deadlock only from the spids involved in the deadlock? I don't want
to capture all database SQL activity because this becomes very large very
quick.
"Kalen Delaney" wrote:
> Hi Mike, and thanks, :-)
> If the truth be known, I usually don't use the traceflag output for the real
> troubleshooting. If I am trying to track down deadlocks, I hae this flag
> enabled, and I also have a trace running to capture deadlock events. I use
> this traceflag output only to get the spids and the time, and then I can
> find what I need in the trace output, which shows me the statements that led
> up to the deadlock. Usually that's enough to figure it out.
> The keys can be either the ones being waited on or the ones requested. It
> depends where in the output the line occurs.
> I have quite a bit of info on interpreting this output and understand lock
> resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
> Locking and Blocking.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
> news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...
>
>
server with trace flag 1204 turned on. I have entries in this log that show
deadlock information. i'm looking for information about how to analyze the
data in this log. It contains a log of information about things such as
Grant lists, keys, owners etc. I need information to explain what I'm
looking at.
The article "Troubleshooting Deadlocks" in Books Online explains what all
these terms mean.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> Hello, I have what I believe should be a fairly simple question. I have a
> server with trace flag 1204 turned on. I have entries in this log that
> show
> deadlock information. i'm looking for information about how to analyze
> the
> data in this log. It contains a log of information about things such as
> Grant lists, keys, owners etc. I need information to explain what I'm
> looking at.
|||Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
Here is an snippet from my error log:
10/30/04 22:16...
10/30/04 22:16
10/30/04 22:16Wait-for graph
10/30/04 22:16
10/30/04 22:16Node:1
10/30/04 22:16KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16Wait List:
10/30/04 22:16Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
SPID:79 ECID:0
10/30/04 22:16SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
10/30/04 22:16Input Buf: RPC Event: app_ProductUnit_RetrieveProductUnitData;1
10/30/04 22:16Requested By:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
Ec
0x73377558) Value:0x5b010/30/04 22:16
10/30/04 22:16Node:2
10/30/04 22:16KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16Grant List 0::
10/30/04 22:16Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:77 ECID:0
10/30/04 22:16SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
10/30/04 22:16Input Buf: RPC Event: app_ProductUnitAssoc_RetrieveTargetData;1
10/30/04 22:16Requested By:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec
0x5182B558) Value:0x23a10/30/04 22:16
10/30/04 22:16Node:3
10/30/04 22:16KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X Flags:
0x0
10/30/04 22:16Grant List 1::
10/30/04 22:16Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:71 ECID:0
10/30/04 22:16SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
10/30/04 22:16Input Buf: RPC Event: sp_executesql;1
10/30/04 22:16Requested By:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
Ec
0x51DF7558) Value:0x3b410/30/04 22:16Victim Resource Owner:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec
0x5182B558) Value:0x23aI do not see enough information in BOL around what the information on the
lines that start with KEY represent. Are they locks currently held or locks
requested? Also I do not understand what information is included on the line
starting with ResType (i.e. what are ResType and Stype) I'm finding it very
challenging to look at this log and deduce the sequence of the calls and
locks that led to my deadlock.
If you have any further advice or links I'd appreciate it.
Mike
"Kalen Delaney" wrote:
> The article "Troubleshooting Deadlocks" in Books Online explains what all
> these terms mean.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
>
>
|||Hi Mike, and thanks, :-)
If the truth be known, I usually don't use the traceflag output for the real
troubleshooting. If I am trying to track down deadlocks, I hae this flag
enabled, and I also have a trace running to capture deadlock events. I use
this traceflag output only to get the spids and the time, and then I can
find what I need in the trace output, which shows me the statements that led
up to the deadlock. Usually that's enough to figure it out.
The keys can be either the ones being waited on or the ones requested. It
depends where in the output the line occurs.
I have quite a bit of info on interpreting this output and understand lock
resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
Locking and Blocking.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...[vbcol=seagreen]
> Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> Here is an snippet from my error log:
> 10/30/04 22:16 ...
> 10/30/04 22:16
> 10/30/04 22:16 Wait-for graph
> 10/30/04 22:16
> 10/30/04 22:16 Node:1
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Wait List:
> 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> SPID:79 ECID:0
> 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnit_RetrieveProductUnitData;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> Ec
0x73377558) Value:0x5b0> 10/30/04 22:16
> 10/30/04 22:16 Node:2
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 0::
> 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:77 ECID:0
> 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnitAssoc_RetrieveTargetData;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec
0x5182B558) Value:0x23a> 10/30/04 22:16
> 10/30/04 22:16 Node:3
> 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 1::
> 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:71 ECID:0
> 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> Ec
0x51DF7558) Value:0x3b4> 10/30/04 22:16 Victim Resource Owner:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec
0x5182B558) Value:0x23a> I do not see enough information in BOL around what the information on the
> lines that start with KEY represent. Are they locks currently held or
> locks
> requested? Also I do not understand what information is included on the
> line
> starting with ResType (i.e. what are ResType and Stype) I'm finding it
> very
> challenging to look at this log and deduce the sequence of the calls and
> locks that led to my deadlock.
> If you have any further advice or links I'd appreciate it.
> Mike
>
> "Kalen Delaney" wrote:
|||Hi Kalen,
Ok, sounds good. I have read the appropriate section out of Inside SQL
Server 2000. One last (hopefully) follow up question for you. Do you have a
sample profiler template that you commonly use to assist in troubleshooting
deadlocks? My dilemna is this...is there a way to show the queries leading
up to a deadlock only from the spids involved in the deadlock? I don't want
to capture all database SQL activity because this becomes very large very
quick.
"Kalen Delaney" wrote:
> Hi Mike, and thanks, :-)
> If the truth be known, I usually don't use the traceflag output for the real
> troubleshooting. If I am trying to track down deadlocks, I hae this flag
> enabled, and I also have a trace running to capture deadlock events. I use
> this traceflag output only to get the spids and the time, and then I can
> find what I need in the trace output, which shows me the statements that led
> up to the deadlock. Usually that's enough to figure it out.
> The keys can be either the ones being waited on or the ones requested. It
> depends where in the output the line occurs.
> I have quite a bit of info on interpreting this output and understand lock
> resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
> Locking and Blocking.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
> news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...
>
>
Analyzing Error log with Trace Flag 1204 turned on
Hello, I have what I believe should be a fairly simple question. I have a
server with trace flag 1204 turned on. I have entries in this log that show
deadlock information. i'm looking for information about how to analyze the
data in this log. It contains a log of information about things such as
Grant lists, keys, owners etc. I need information to explain what I'm
looking at.The article "Troubleshooting Deadlocks" in Books Online explains what all
these terms mean.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> Hello, I have what I believe should be a fairly simple question. I have a
> server with trace flag 1204 turned on. I have entries in this log that
> show
> deadlock information. i'm looking for information about how to analyze
> the
> data in this log. It contains a log of information about things such as
> Grant lists, keys, owners etc. I need information to explain what I'm
> looking at.|||Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
Here is an snippet from my error log:
10/30/04 22:16 ...
10/30/04 22:16
10/30/04 22:16 Wait-for graph
10/30/04 22:16
10/30/04 22:16 Node:1
10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16 Wait List:
10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
SPID:79 ECID:0
10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
10/30/04 22:16 Input Buf: RPC Event: app_ProductUnit_RetrieveProductUnitData;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
Ec:(0x73377558) Value:0x5b0
10/30/04 22:16
10/30/04 22:16 Node:2
10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16 Grant List 0::
10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:77 ECID:0
10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
10/30/04 22:16 Input Buf: RPC Event: app_ProductUnitAssoc_RetrieveTargetData;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec:(0x5182B558) Value:0x23a
10/30/04 22:16
10/30/04 22:16 Node:3
10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X Flags:
0x0
10/30/04 22:16 Grant List 1::
10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:71 ECID:0
10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
Ec:(0x51DF7558) Value:0x3b4
10/30/04 22:16 Victim Resource Owner:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec:(0x5182B558) Value:0x23a
I do not see enough information in BOL around what the information on the
lines that start with KEY represent. Are they locks currently held or locks
requested? Also I do not understand what information is included on the line
starting with ResType (i.e. what are ResType and Stype) I'm finding it very
challenging to look at this log and deduce the sequence of the calls and
locks that led to my deadlock.
If you have any further advice or links I'd appreciate it.
Mike
"Kalen Delaney" wrote:
> The article "Troubleshooting Deadlocks" in Books Online explains what all
> these terms mean.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> >
> > Hello, I have what I believe should be a fairly simple question. I have a
> > server with trace flag 1204 turned on. I have entries in this log that
> > show
> > deadlock information. i'm looking for information about how to analyze
> > the
> > data in this log. It contains a log of information about things such as
> > Grant lists, keys, owners etc. I need information to explain what I'm
> > looking at.
>
>|||Hi Mike, and thanks, :-)
If the truth be known, I usually don't use the traceflag output for the real
troubleshooting. If I am trying to track down deadlocks, I hae this flag
enabled, and I also have a trace running to capture deadlock events. I use
this traceflag output only to get the spids and the time, and then I can
find what I need in the trace output, which shows me the statements that led
up to the deadlock. Usually that's enough to figure it out.
The keys can be either the ones being waited on or the ones requested. It
depends where in the output the line occurs.
I have quite a bit of info on interpreting this output and understand lock
resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
Locking and Blocking.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...
> Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> Here is an snippet from my error log:
> 10/30/04 22:16 ...
> 10/30/04 22:16
> 10/30/04 22:16 Wait-for graph
> 10/30/04 22:16
> 10/30/04 22:16 Node:1
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Wait List:
> 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> SPID:79 ECID:0
> 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnit_RetrieveProductUnitData;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> Ec:(0x73377558) Value:0x5b0
> 10/30/04 22:16
> 10/30/04 22:16 Node:2
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 0::
> 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:77 ECID:0
> 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnitAssoc_RetrieveTargetData;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec:(0x5182B558) Value:0x23a
> 10/30/04 22:16
> 10/30/04 22:16 Node:3
> 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 1::
> 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:71 ECID:0
> 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> Ec:(0x51DF7558) Value:0x3b4
> 10/30/04 22:16 Victim Resource Owner:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec:(0x5182B558) Value:0x23a
> I do not see enough information in BOL around what the information on the
> lines that start with KEY represent. Are they locks currently held or
> locks
> requested? Also I do not understand what information is included on the
> line
> starting with ResType (i.e. what are ResType and Stype) I'm finding it
> very
> challenging to look at this log and deduce the sequence of the calls and
> locks that led to my deadlock.
> If you have any further advice or links I'd appreciate it.
> Mike
>
> "Kalen Delaney" wrote:
>> The article "Troubleshooting Deadlocks" in Books Online explains what all
>> these terms mean.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in
>> message
>> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
>> >
>> > Hello, I have what I believe should be a fairly simple question. I
>> > have a
>> > server with trace flag 1204 turned on. I have entries in this log that
>> > show
>> > deadlock information. i'm looking for information about how to analyze
>> > the
>> > data in this log. It contains a log of information about things such
>> > as
>> > Grant lists, keys, owners etc. I need information to explain what I'm
>> > looking at.
>>|||Hi Kalen,
Ok, sounds good. I have read the appropriate section out of Inside SQL
Server 2000. One last (hopefully) follow up question for you. Do you have a
sample profiler template that you commonly use to assist in troubleshooting
deadlocks? My dilemna is this...is there a way to show the queries leading
up to a deadlock only from the spids involved in the deadlock? I don't want
to capture all database SQL activity because this becomes very large very
quick.
"Kalen Delaney" wrote:
> Hi Mike, and thanks, :-)
> If the truth be known, I usually don't use the traceflag output for the real
> troubleshooting. If I am trying to track down deadlocks, I hae this flag
> enabled, and I also have a trace running to capture deadlock events. I use
> this traceflag output only to get the spids and the time, and then I can
> find what I need in the trace output, which shows me the statements that led
> up to the deadlock. Usually that's enough to figure it out.
> The keys can be either the ones being waited on or the ones requested. It
> depends where in the output the line occurs.
> I have quite a bit of info on interpreting this output and understand lock
> resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
> Locking and Blocking.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
> news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...
> >
> > Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> > Here is an snippet from my error log:
> >
> > 10/30/04 22:16 ...
> > 10/30/04 22:16
> > 10/30/04 22:16 Wait-for graph
> > 10/30/04 22:16
> > 10/30/04 22:16 Node:1
> > 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> > Flags:
> > 0x0
> > 10/30/04 22:16 Wait List:
> > 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> > SPID:79 ECID:0
> > 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> > 10/30/04 22:16 Input Buf: RPC Event:
> > app_ProductUnit_RetrieveProductUnitData;1
> > 10/30/04 22:16 Requested By:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> > Ec:(0x73377558) Value:0x5b0
> > 10/30/04 22:16
> > 10/30/04 22:16 Node:2
> > 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> > Flags:
> > 0x0
> > 10/30/04 22:16 Grant List 0::
> > 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> > SPID:77 ECID:0
> > 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> > 10/30/04 22:16 Input Buf: RPC Event:
> > app_ProductUnitAssoc_RetrieveTargetData;1
> > 10/30/04 22:16 Requested By:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> > Ec:(0x5182B558) Value:0x23a
> > 10/30/04 22:16
> > 10/30/04 22:16 Node:3
> > 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> > Flags:
> > 0x0
> > 10/30/04 22:16 Grant List 1::
> > 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> > SPID:71 ECID:0
> > 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> > 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> > 10/30/04 22:16 Requested By:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> > Ec:(0x51DF7558) Value:0x3b4
> > 10/30/04 22:16 Victim Resource Owner:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> > Ec:(0x5182B558) Value:0x23a
> >
> > I do not see enough information in BOL around what the information on the
> > lines that start with KEY represent. Are they locks currently held or
> > locks
> > requested? Also I do not understand what information is included on the
> > line
> > starting with ResType (i.e. what are ResType and Stype) I'm finding it
> > very
> > challenging to look at this log and deduce the sequence of the calls and
> > locks that led to my deadlock.
> >
> > If you have any further advice or links I'd appreciate it.
> >
> > Mike
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> The article "Troubleshooting Deadlocks" in Books Online explains what all
> >> these terms mean.
> >>
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in
> >> message
> >> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> >> >
> >> > Hello, I have what I believe should be a fairly simple question. I
> >> > have a
> >> > server with trace flag 1204 turned on. I have entries in this log that
> >> > show
> >> > deadlock information. i'm looking for information about how to analyze
> >> > the
> >> > data in this log. It contains a log of information about things such
> >> > as
> >> > Grant lists, keys, owners etc. I need information to explain what I'm
> >> > looking at.
> >>
> >>
> >>
>
>
server with trace flag 1204 turned on. I have entries in this log that show
deadlock information. i'm looking for information about how to analyze the
data in this log. It contains a log of information about things such as
Grant lists, keys, owners etc. I need information to explain what I'm
looking at.The article "Troubleshooting Deadlocks" in Books Online explains what all
these terms mean.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> Hello, I have what I believe should be a fairly simple question. I have a
> server with trace flag 1204 turned on. I have entries in this log that
> show
> deadlock information. i'm looking for information about how to analyze
> the
> data in this log. It contains a log of information about things such as
> Grant lists, keys, owners etc. I need information to explain what I'm
> looking at.|||Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
Here is an snippet from my error log:
10/30/04 22:16 ...
10/30/04 22:16
10/30/04 22:16 Wait-for graph
10/30/04 22:16
10/30/04 22:16 Node:1
10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16 Wait List:
10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
SPID:79 ECID:0
10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
10/30/04 22:16 Input Buf: RPC Event: app_ProductUnit_RetrieveProductUnitData;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
Ec:(0x73377558) Value:0x5b0
10/30/04 22:16
10/30/04 22:16 Node:2
10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16 Grant List 0::
10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:77 ECID:0
10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
10/30/04 22:16 Input Buf: RPC Event: app_ProductUnitAssoc_RetrieveTargetData;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec:(0x5182B558) Value:0x23a
10/30/04 22:16
10/30/04 22:16 Node:3
10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X Flags:
0x0
10/30/04 22:16 Grant List 1::
10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:71 ECID:0
10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
Ec:(0x51DF7558) Value:0x3b4
10/30/04 22:16 Victim Resource Owner:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec:(0x5182B558) Value:0x23a
I do not see enough information in BOL around what the information on the
lines that start with KEY represent. Are they locks currently held or locks
requested? Also I do not understand what information is included on the line
starting with ResType (i.e. what are ResType and Stype) I'm finding it very
challenging to look at this log and deduce the sequence of the calls and
locks that led to my deadlock.
If you have any further advice or links I'd appreciate it.
Mike
"Kalen Delaney" wrote:
> The article "Troubleshooting Deadlocks" in Books Online explains what all
> these terms mean.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> >
> > Hello, I have what I believe should be a fairly simple question. I have a
> > server with trace flag 1204 turned on. I have entries in this log that
> > show
> > deadlock information. i'm looking for information about how to analyze
> > the
> > data in this log. It contains a log of information about things such as
> > Grant lists, keys, owners etc. I need information to explain what I'm
> > looking at.
>
>|||Hi Mike, and thanks, :-)
If the truth be known, I usually don't use the traceflag output for the real
troubleshooting. If I am trying to track down deadlocks, I hae this flag
enabled, and I also have a trace running to capture deadlock events. I use
this traceflag output only to get the spids and the time, and then I can
find what I need in the trace output, which shows me the statements that led
up to the deadlock. Usually that's enough to figure it out.
The keys can be either the ones being waited on or the ones requested. It
depends where in the output the line occurs.
I have quite a bit of info on interpreting this output and understand lock
resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
Locking and Blocking.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...
> Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> Here is an snippet from my error log:
> 10/30/04 22:16 ...
> 10/30/04 22:16
> 10/30/04 22:16 Wait-for graph
> 10/30/04 22:16
> 10/30/04 22:16 Node:1
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Wait List:
> 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> SPID:79 ECID:0
> 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnit_RetrieveProductUnitData;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> Ec:(0x73377558) Value:0x5b0
> 10/30/04 22:16
> 10/30/04 22:16 Node:2
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 0::
> 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:77 ECID:0
> 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnitAssoc_RetrieveTargetData;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec:(0x5182B558) Value:0x23a
> 10/30/04 22:16
> 10/30/04 22:16 Node:3
> 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 1::
> 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:71 ECID:0
> 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> Ec:(0x51DF7558) Value:0x3b4
> 10/30/04 22:16 Victim Resource Owner:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec:(0x5182B558) Value:0x23a
> I do not see enough information in BOL around what the information on the
> lines that start with KEY represent. Are they locks currently held or
> locks
> requested? Also I do not understand what information is included on the
> line
> starting with ResType (i.e. what are ResType and Stype) I'm finding it
> very
> challenging to look at this log and deduce the sequence of the calls and
> locks that led to my deadlock.
> If you have any further advice or links I'd appreciate it.
> Mike
>
> "Kalen Delaney" wrote:
>> The article "Troubleshooting Deadlocks" in Books Online explains what all
>> these terms mean.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in
>> message
>> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
>> >
>> > Hello, I have what I believe should be a fairly simple question. I
>> > have a
>> > server with trace flag 1204 turned on. I have entries in this log that
>> > show
>> > deadlock information. i'm looking for information about how to analyze
>> > the
>> > data in this log. It contains a log of information about things such
>> > as
>> > Grant lists, keys, owners etc. I need information to explain what I'm
>> > looking at.
>>|||Hi Kalen,
Ok, sounds good. I have read the appropriate section out of Inside SQL
Server 2000. One last (hopefully) follow up question for you. Do you have a
sample profiler template that you commonly use to assist in troubleshooting
deadlocks? My dilemna is this...is there a way to show the queries leading
up to a deadlock only from the spids involved in the deadlock? I don't want
to capture all database SQL activity because this becomes very large very
quick.
"Kalen Delaney" wrote:
> Hi Mike, and thanks, :-)
> If the truth be known, I usually don't use the traceflag output for the real
> troubleshooting. If I am trying to track down deadlocks, I hae this flag
> enabled, and I also have a trace running to capture deadlock events. I use
> this traceflag output only to get the spids and the time, and then I can
> find what I need in the trace output, which shows me the statements that led
> up to the deadlock. Usually that's enough to figure it out.
> The keys can be either the ones being waited on or the ones requested. It
> depends where in the output the line occurs.
> I have quite a bit of info on interpreting this output and understand lock
> resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
> Locking and Blocking.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
> news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...
> >
> > Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> > Here is an snippet from my error log:
> >
> > 10/30/04 22:16 ...
> > 10/30/04 22:16
> > 10/30/04 22:16 Wait-for graph
> > 10/30/04 22:16
> > 10/30/04 22:16 Node:1
> > 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> > Flags:
> > 0x0
> > 10/30/04 22:16 Wait List:
> > 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> > SPID:79 ECID:0
> > 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> > 10/30/04 22:16 Input Buf: RPC Event:
> > app_ProductUnit_RetrieveProductUnitData;1
> > 10/30/04 22:16 Requested By:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> > Ec:(0x73377558) Value:0x5b0
> > 10/30/04 22:16
> > 10/30/04 22:16 Node:2
> > 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> > Flags:
> > 0x0
> > 10/30/04 22:16 Grant List 0::
> > 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> > SPID:77 ECID:0
> > 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> > 10/30/04 22:16 Input Buf: RPC Event:
> > app_ProductUnitAssoc_RetrieveTargetData;1
> > 10/30/04 22:16 Requested By:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> > Ec:(0x5182B558) Value:0x23a
> > 10/30/04 22:16
> > 10/30/04 22:16 Node:3
> > 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> > Flags:
> > 0x0
> > 10/30/04 22:16 Grant List 1::
> > 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> > SPID:71 ECID:0
> > 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> > 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> > 10/30/04 22:16 Requested By:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> > Ec:(0x51DF7558) Value:0x3b4
> > 10/30/04 22:16 Victim Resource Owner:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> > Ec:(0x5182B558) Value:0x23a
> >
> > I do not see enough information in BOL around what the information on the
> > lines that start with KEY represent. Are they locks currently held or
> > locks
> > requested? Also I do not understand what information is included on the
> > line
> > starting with ResType (i.e. what are ResType and Stype) I'm finding it
> > very
> > challenging to look at this log and deduce the sequence of the calls and
> > locks that led to my deadlock.
> >
> > If you have any further advice or links I'd appreciate it.
> >
> > Mike
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> The article "Troubleshooting Deadlocks" in Books Online explains what all
> >> these terms mean.
> >>
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in
> >> message
> >> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> >> >
> >> > Hello, I have what I believe should be a fairly simple question. I
> >> > have a
> >> > server with trace flag 1204 turned on. I have entries in this log that
> >> > show
> >> > deadlock information. i'm looking for information about how to analyze
> >> > the
> >> > data in this log. It contains a log of information about things such
> >> > as
> >> > Grant lists, keys, owners etc. I need information to explain what I'm
> >> > looking at.
> >>
> >>
> >>
>
>
Analyzing Error log with Trace Flag 1204 turned on
Hello, I have what I believe should be a fairly simple question. I have a
server with trace flag 1204 turned on. I have entries in this log that show
deadlock information. i'm looking for information about how to analyze the
data in this log. It contains a log of information about things such as
Grant lists, keys, owners etc. I need information to explain what I'm
looking at.The article "Troubleshooting Deadlocks" in Books Online explains what all
these terms mean.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> Hello, I have what I believe should be a fairly simple question. I have a
> server with trace flag 1204 turned on. I have entries in this log that
> show
> deadlock information. i'm looking for information about how to analyze
> the
> data in this log. It contains a log of information about things such as
> Grant lists, keys, owners etc. I need information to explain what I'm
> looking at.|||Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
Here is an snippet from my error log:
10/30/04 22:16 ...
10/30/04 22:16
10/30/04 22:16 Wait-for graph
10/30/04 22:16
10/30/04 22:16 Node:1
10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16 Wait List:
10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
SPID:79 ECID:0
10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
10/30/04 22:16 Input Buf: RPC Event: app_ProductUnit_RetrieveProductUnitData
;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
Ec0x73377558) Value:0x5b0
10/30/04 22:16
10/30/04 22:16 Node:2
10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16 Grant List 0::
10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:77 ECID:0
10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
10/30/04 22:16 Input Buf: RPC Event: app_ProductUnitAssoc_RetrieveTargetData
;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec0x5182B558) Value:0x23a
10/30/04 22:16
10/30/04 22:16 Node:3
10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X Flags:
0x0
10/30/04 22:16 Grant List 1::
10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:71 ECID:0
10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
Ec0x51DF7558) Value:0x3b4
10/30/04 22:16 Victim Resource Owner:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec0x5182B558) Value:0x23a
I do not see enough information in BOL around what the information on the
lines that start with KEY represent. Are they locks currently held or locks
requested? Also I do not understand what information is included on the lin
e
starting with ResType (i.e. what are ResType and Stype) I'm finding it very
challenging to look at this log and deduce the sequence of the calls and
locks that led to my deadlock.
If you have any further advice or links I'd appreciate it.
Mike
"Kalen Delaney" wrote:
> The article "Troubleshooting Deadlocks" in Books Online explains what all
> these terms mean.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in messa
ge
> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
>
>|||Hi Mike, and thanks, :-)
If the truth be known, I usually don't use the traceflag output for the real
troubleshooting. If I am trying to track down deadlocks, I hae this flag
enabled, and I also have a trace running to capture deadlock events. I use
this traceflag output only to get the spids and the time, and then I can
find what I need in the trace output, which shows me the statements that led
up to the deadlock. Usually that's enough to figure it out.
The keys can be either the ones being waited on or the ones requested. It
depends where in the output the line occurs.
I have quite a bit of info on interpreting this output and understand lock
resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
Locking and Blocking.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...[vbcol=seagreen]
> Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> Here is an snippet from my error log:
> 10/30/04 22:16 ...
> 10/30/04 22:16
> 10/30/04 22:16 Wait-for graph
> 10/30/04 22:16
> 10/30/04 22:16 Node:1
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Wait List:
> 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> SPID:79 ECID:0
> 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnit_RetrieveProductUnitData;
1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> Ec0x73377558) Value:0x5b0
> 10/30/04 22:16
> 10/30/04 22:16 Node:2
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 0::
> 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:77 ECID:0
> 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnitAssoc_RetrieveTargetData;
1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec0x5182B558) Value:0x23a
> 10/30/04 22:16
> 10/30/04 22:16 Node:3
> 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 1::
> 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:71 ECID:0
> 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> Ec0x51DF7558) Value:0x3b4
> 10/30/04 22:16 Victim Resource Owner:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec0x5182B558) Value:0x23a
> I do not see enough information in BOL around what the information on the
> lines that start with KEY represent. Are they locks currently held or
> locks
> requested? Also I do not understand what information is included on the
> line
> starting with ResType (i.e. what are ResType and Stype) I'm finding it
> very
> challenging to look at this log and deduce the sequence of the calls and
> locks that led to my deadlock.
> If you have any further advice or links I'd appreciate it.
> Mike
>
> "Kalen Delaney" wrote:
>|||Hi Kalen,
Ok, sounds good. I have read the appropriate section out of Inside SQL
Server 2000. One last (hopefully) follow up question for you. Do you have
a
sample profiler template that you commonly use to assist in troubleshooting
deadlocks? My dilemna is this...is there a way to show the queries leading
up to a deadlock only from the spids involved in the deadlock? I don't want
to capture all database SQL activity because this becomes very large very
quick.
"Kalen Delaney" wrote:
> Hi Mike, and thanks, :-)
> If the truth be known, I usually don't use the traceflag output for the re
al
> troubleshooting. If I am trying to track down deadlocks, I hae this flag
> enabled, and I also have a trace running to capture deadlock events. I use
> this traceflag output only to get the spids and the time, and then I can
> find what I need in the trace output, which shows me the statements that l
ed
> up to the deadlock. Usually that's enough to figure it out.
> The keys can be either the ones being waited on or the ones requested. It
> depends where in the output the line occurs.
> I have quite a bit of info on interpreting this output and understand lock
> resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
> Locking and Blocking.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in messa
ge
> news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...
>
>
server with trace flag 1204 turned on. I have entries in this log that show
deadlock information. i'm looking for information about how to analyze the
data in this log. It contains a log of information about things such as
Grant lists, keys, owners etc. I need information to explain what I'm
looking at.The article "Troubleshooting Deadlocks" in Books Online explains what all
these terms mean.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> Hello, I have what I believe should be a fairly simple question. I have a
> server with trace flag 1204 turned on. I have entries in this log that
> show
> deadlock information. i'm looking for information about how to analyze
> the
> data in this log. It contains a log of information about things such as
> Grant lists, keys, owners etc. I need information to explain what I'm
> looking at.|||Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
Here is an snippet from my error log:
10/30/04 22:16 ...
10/30/04 22:16
10/30/04 22:16 Wait-for graph
10/30/04 22:16
10/30/04 22:16 Node:1
10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16 Wait List:
10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
SPID:79 ECID:0
10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
10/30/04 22:16 Input Buf: RPC Event: app_ProductUnit_RetrieveProductUnitData
;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
Ec
0x73377558) Value:0x5b010/30/04 22:16
10/30/04 22:16 Node:2
10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16 Grant List 0::
10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:77 ECID:0
10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
10/30/04 22:16 Input Buf: RPC Event: app_ProductUnitAssoc_RetrieveTargetData
;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec
0x5182B558) Value:0x23a10/30/04 22:16
10/30/04 22:16 Node:3
10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X Flags:
0x0
10/30/04 22:16 Grant List 1::
10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:71 ECID:0
10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
Ec
0x51DF7558) Value:0x3b410/30/04 22:16 Victim Resource Owner:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec
0x5182B558) Value:0x23aI do not see enough information in BOL around what the information on the
lines that start with KEY represent. Are they locks currently held or locks
requested? Also I do not understand what information is included on the lin
e
starting with ResType (i.e. what are ResType and Stype) I'm finding it very
challenging to look at this log and deduce the sequence of the calls and
locks that led to my deadlock.
If you have any further advice or links I'd appreciate it.
Mike
"Kalen Delaney" wrote:
> The article "Troubleshooting Deadlocks" in Books Online explains what all
> these terms mean.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in messa
ge
> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
>
>|||Hi Mike, and thanks, :-)
If the truth be known, I usually don't use the traceflag output for the real
troubleshooting. If I am trying to track down deadlocks, I hae this flag
enabled, and I also have a trace running to capture deadlock events. I use
this traceflag output only to get the spids and the time, and then I can
find what I need in the trace output, which shows me the statements that led
up to the deadlock. Usually that's enough to figure it out.
The keys can be either the ones being waited on or the ones requested. It
depends where in the output the line occurs.
I have quite a bit of info on interpreting this output and understand lock
resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
Locking and Blocking.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...[vbcol=seagreen]
> Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> Here is an snippet from my error log:
> 10/30/04 22:16 ...
> 10/30/04 22:16
> 10/30/04 22:16 Wait-for graph
> 10/30/04 22:16
> 10/30/04 22:16 Node:1
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Wait List:
> 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> SPID:79 ECID:0
> 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnit_RetrieveProductUnitData;
1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> Ec
0x73377558) Value:0x5b0> 10/30/04 22:16
> 10/30/04 22:16 Node:2
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 0::
> 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:77 ECID:0
> 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnitAssoc_RetrieveTargetData;
1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec
0x5182B558) Value:0x23a> 10/30/04 22:16
> 10/30/04 22:16 Node:3
> 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 1::
> 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:71 ECID:0
> 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> Ec
0x51DF7558) Value:0x3b4> 10/30/04 22:16 Victim Resource Owner:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec
0x5182B558) Value:0x23a> I do not see enough information in BOL around what the information on the
> lines that start with KEY represent. Are they locks currently held or
> locks
> requested? Also I do not understand what information is included on the
> line
> starting with ResType (i.e. what are ResType and Stype) I'm finding it
> very
> challenging to look at this log and deduce the sequence of the calls and
> locks that led to my deadlock.
> If you have any further advice or links I'd appreciate it.
> Mike
>
> "Kalen Delaney" wrote:
>|||Hi Kalen,
Ok, sounds good. I have read the appropriate section out of Inside SQL
Server 2000. One last (hopefully) follow up question for you. Do you have
a
sample profiler template that you commonly use to assist in troubleshooting
deadlocks? My dilemna is this...is there a way to show the queries leading
up to a deadlock only from the spids involved in the deadlock? I don't want
to capture all database SQL activity because this becomes very large very
quick.
"Kalen Delaney" wrote:
> Hi Mike, and thanks, :-)
> If the truth be known, I usually don't use the traceflag output for the re
al
> troubleshooting. If I am trying to track down deadlocks, I hae this flag
> enabled, and I also have a trace running to capture deadlock events. I use
> this traceflag output only to get the spids and the time, and then I can
> find what I need in the trace output, which shows me the statements that l
ed
> up to the deadlock. Usually that's enough to figure it out.
> The keys can be either the ones being waited on or the ones requested. It
> depends where in the output the line occurs.
> I have quite a bit of info on interpreting this output and understand lock
> resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
> Locking and Blocking.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in messa
ge
> news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...
>
>
Analyzing db Growth per day
Well my question is how do i analyze db growth per day. is there a tool i can use or a method. I mean i do take a look at the task view and the files but per day it doesnt move in MB wich is weird since this is a warehouse and their are nightly loads to it inserting maybe 30000 record a night on avg.
Any help would be grately aprreciated.sp_helpdb 'MyDatabaseName'|||The easiest way to do it is to check in Enterprise manager daily to see what the current size is, then keep the daily records in a spreadsheet, or database. If you are looking for a pre-packaged too, I think Quest Software has something for that. I think Microsoft is also getting into the game, as well, with a product I can not remember the name of. Some SQL Server add on.|||Or you could write a job that copies sysobjects and sysindexes into a table that you aggregate on a regualr basis. Swipe code from sp_spaceused to understand how to use that information.
Any help would be grately aprreciated.sp_helpdb 'MyDatabaseName'|||The easiest way to do it is to check in Enterprise manager daily to see what the current size is, then keep the daily records in a spreadsheet, or database. If you are looking for a pre-packaged too, I think Quest Software has something for that. I think Microsoft is also getting into the game, as well, with a product I can not remember the name of. Some SQL Server add on.|||Or you could write a job that copies sysobjects and sysindexes into a table that you aggregate on a regualr basis. Swipe code from sp_spaceused to understand how to use that information.
Analyzing a Query in SQL Server
I have to duplicate a bunch of reports that were produced by a VB6 app. Now I'm using C#, ASP.net and Crystal Reports via VS2003. Each executes a stored procedure in SQL Server (before and now).
For each report I have a Stored Procedure and a View. The View's SQL code is identical to the Stored Procedure except that the two input parameters (startDate and endDate) are removed because Views don't allow parameters.
Some of the reports work perfectly right off the bat. But others are timing out. My initial test of the timing out is to just display the View. If it fails then I know that the the report engine will fail too.
So now I'm trying out the SQL Query Analyzer tool to execute the code in one of the Views. It's now at 29 minutes and still going - at least it hasn't timed out!
My question is this: Is there a way I could examine what's going on with the query to see why it's taking so long?
Robert WernerHave a look at the query execution plan in the query analyzer, do you see any bottlenecks there ? During execution you might encounter a deadlock. looking in the appropiate node in SQL Server Enterprise Manager (Current Activity) you might be able to find the blocking process.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||Jens,
Thank you for your suggestion. I've never used that Execution Plan before so just tried it out on one of the queries that's timing out. I took the liberty of taking a screen capture and posting it here:
http://pocketpollster.com/downloads/public/WBMCST11_Execution_Plan.jpg
Does this communicate anything to you?
Robert Werner
Vancouver, BC|||THe information which is sensitive while moving over the individual objects is missing for us, so its hard to tell. This is the estimated execution plan not the actual executed one, but as a rule of thumb you should have a look on the highest cost in the plan as well as actions like table scans. They are often costly if made for huge tables but are on the opposite cheaper then using indexes in smaller tables. There are some other thing you could check like setting the STATISTICS_IO to on or the SHOWPLAN to make the execution plan shippable to other peers with more information.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Analyze Key Influencers Questions
I will be grateful if you could answer a few more questions around Analyzing Key Influencers
1. When specifying the training data for Decision Tree, there is a SUGGEST button (Recommend inputs for currently set predictable) which recommends which input are related to the predictable attribute. It also gives a ‘Score’ for each recommended inputs. What algorithm does the SUGGEST button use? Does it use simple entropy/correlation based algorithm OR sophisticated feature selection algorithms?
2. Can I access this ‘Score’ and recommended inputs above programmatically?
3. What feature selection algorithms are used in SQL Server 2005? Can they be invoked programmatically?
5. In Logistic Regression mining model viewer, we get a chart which clearly shows what attributes favor which state of the predictable attribute. For example, income level < 23000 favors BikeBuyer = 0 (does not buy) with a score of 89.00. What algorithm is used to calculate the ‘Score’? Can LR be used as a feature selector in case where the predicted attribute is binary (select the attributes that favor one state or the other with a score of, say, greater than some threshold)?
6. You suggested using Naive Bayes to find AKIs. What if the input attributes are all continuous (predicted attribute binary)? Shouldnt I be going for LR?
Thanks bunches
MA
Please see inlines:
1. When specifying the training data for Decision Tree, there is a SUGGEST button (Recommend inputs for currently set predictable) which recommends which input are related to the predictable attribute. It also gives a ‘Score’ for each recommended inputs. What algorithm does the SUGGEST button use? Does it use simple entropy/correlation based algorithm OR sophisticated feature selection algorithms?
Entropy based, on top of a small data sample.
2. Can I access this ‘Score’ and recommended inputs above programmatically?
No
3. What feature selection algorithms are used in SQL Server 2005? Can they be invoked programmatically?
Entropy based and they cannot be invoked programatically. One could write a plug-in algorithm with the sole purpose of performing advanced feature selection/extraction operation. However, such an algorithm is not currently included in Analysis Services.
5. In Logistic Regression mining model viewer, we get a chart which clearly shows what attributes favor which state of the predictable attribute. For example, income level < 23000 favors BikeBuyer = 0 (does not buy) with a score of 89.00. What algorithm is used to calculate the ‘Score’? Can LR be used as a feature selector in case where the predicted attribute is binary (select the attributes that favor one state or the other with a score of, say, greater than some threshold)?
The score only signifies a relative importance among all other factors. Here is briefly how that viewer works:
- it generates a fake input, containing, for each input attribute state, one row, with that input attribute state and everything else on NULL (Missing)
- it runs one prediction, fetches the histogram and compares the probabilities for 0 vs 1
- it normalizes the probabilities to scores, between 0 and 100
So, assuming your model has IncomeLevel as the only input attribute, the viewer practically executes a prediction for each possible state of the IncomeLevel attribute.
If the input is continuous, then is executes a prediction for each quartile of the input attribute
Now, with this info, LR could be used for feature selection if the target is binary. However, there are a few potential issues:
- LR is implemented as Neural Networks without hidden layers. On large volumes of data, training may be slow.
- LR has a feature selection parameter itself (default is top 255). That feature selection is entropy based and it means that the LR part will only see the remaining 255 features (so be sure to adjust the value of that parameter)
- for small data sets, the coefficients detected by the Neural net for some of the features are not really meaningful (as the network training goal is reached fast, by adjusting only a few coefficients) so the relative importance may not be reliable in the lower part of the viewer (results with small scores)
If you decide to use LR, you can call directly the stored procedure used by the viewer. Furthermore, the stored proc signature allows you to pinpoint some of the inputs and sort only the others
6. You suggested using Naive Bayes to find AKIs. What if the input attributes are all continuous (predicted attribute binary)? Shouldnt I be going for LR?
Continuous inputs can be discretized to be used in Naive Bayes. Naive Bayes has a few advantages:
- it is pretty straightforward and the results are easy to interpret
- it trains very fast.
- it works with any kind of predictable targets (binary or not, discrete or continuous - via discretization)
|||Thank you very much Bogdan!!
MA
Subscribe to:
Posts (Atom)