Article: Using SHERLOCK to Monitor Blocking

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Using SHERLOCK to Monitor Blocking

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-05-16 : 23:35:05

I recently received an email from Roberto Farah who works at Microsoft's PSS. Roberto is the co-author of a tool called Sherlock that helps analyze blocking problems in SQL Server. It pulls together the information from sp_blocker_pss80 and provides it in an easy to read format. Microsoft has been using it internally and decided to release it to the public. The download includes the program and a short PowerPoint presentation explaining its use. I'll post additional information after I've had a chance to work more with it tomorrow. (This is a .NET application.) Thanks Roberto!

Article Link.

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-17 : 13:29:26

Good stuff...and good presentation.

Brett

8-)

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-05-17 : 14:11:52

does this presentation suggest that we run the blocker script at all times?

-ec

ramdas
Posting Yak Master

181 Posts

Posted - 2005-05-17 : 15:57:47

Hi folks,
In the sherlock application, it asks to choose a blocking log file where do you get the file from.
Thank you

Ramdas Narayanan
SQL Server DBA

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-05-17 : 17:48:08

If you read the linked support document it describes how to run sp_blocker_pss80 in a loop inside osql.exe to generate the log file.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

amachanic
SQL Server MVP

169 Posts

Posted - 2005-05-20 : 13:32:34

Eyechart,

I think only when you suspect that blocking is contributing to performance problems.

---
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net

grambowk
Starting Member

2 Posts

Posted - 2005-05-25 : 04:34:19

quote:
Originally posted by graz

If you read the linked support document it describes how to run sp_blocker_pss80 in a loop inside osql.exe to generate the log file.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

What linked document? I can only see the the documenation that comes with the downloads, which are just presentation notes.

I tried outputting the results to an output file using osql but I get an exception when I analyze the file using Sherlock so I might be doing something wrong.

Can someone provide a link to documenation on Sherlock?

Thanks,

Karl

dtomyn
Starting Member

2 Posts

Posted - 2005-05-25 : 13:39:39

Why was this not made available via support.microsoft.com? Also, I can't seem to locate any information on Microsoft's site. As much as I would like to use this tool, I am a bit hesitant to use something that has no documentation and no verifiable publisher and no source code.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-05-25 : 15:24:19

This is pretty much unsupported software. The linked document is here (http://support.microsoft.com/default.aspx?scid=kb;en-us;271509). It's a description of the pss_blocker sproc and the not the Sherlock tool. Sherlock was written by a few people inside MS. It's not an official product and has no support. The only documentation I'm aware of is the PPT presentation inside the ZIP file. If you find Sherlock helpful feel free to use it. If not, I certainly understand.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

grambowk
Starting Member

2 Posts

Posted - 2005-05-26 : 04:23:36

Does anyone know what the Interval field, in the Sherlock Analysis represents?

At first I thought that the interval was the length of time for which the given spid(s) were blocked but judging by the results I'm getting out if Sherlock this cannot be the case.

Thanks

shamking
Starting Member

7 Posts

Posted - 2005-06-01 : 04:14:50

quote:
Originally posted by grambowk

Does anyone know what the Interval field, in the Sherlock Analysis represents?

At first I thought that the interval was the length of time for which the given spid(s) were blocked but judging by the results I'm getting out if Sherlock this cannot be the case.

Thanks

Date Time = 2005-05-31 16:05:34.187 Interval = 45.094 seconds
Date Time = 2005-05-31 16:05:34.203 Interval = 0.016 seconds

Interval is the time difference between 2 samples. 0.016 seconds

MesterLars
Starting Member

3 Posts

Posted - 2005-06-15 : 06:07:53

When I try to analyze the output file I get this entry in the internals.dat file

Sherlock
Object reference not set to an instance of an object.
at m..ctor(String fileName)
at o.a(String A_0, i A_1, TreeView A_2)

Any ideas?

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-02 : 13:58:30

Roberto just sent me a new version of Sherlock (version 1.3). In the email he states that "This is a new version that now reads memUsage values without raising exception"

This tool is pretty cool, and it really helps with analyzing the blocker script output.

Graz, if you don't have the latest version I can email it over to you so you can put it online. Just let me know.

-ec

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-08-15 : 05:02:13

Hyperlink not working, or am I being a blonde yak?

I can't seem to get into the article itself...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-08-15 : 10:31:03

The link in the article was correct but the link in this forum post hadn't been updated (though it still should have worked). In any case, it's been updated.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-08-16 : 08:22:04

Now I Understand - I was being a blonde-yak.

I thought the article header would link to a more detailed article. Re-reading, I realize that the information on the "home page" is the entire article. I've followed the link and DL'ed it. Thanks

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

kap_gemini
Starting Member

5 Posts

Posted - 2005-10-05 : 16:47:27

Hi,
Once i collected data from blocker script and opening it in Sherlock.exe it gives me error

“An exception was raised ,please see the internals.dat file ! exception = Exception from BlockingLogReader:: Analyze(). “

extract from internals.dat
5/10/2005 15:25:26:887
Is SPID causing blocking.
Spid causing blocking: 54
Blocking record saved in array.
mscorlib
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: startIndex
at System.String.IndexOf(String value, Int32 startIndex, Int32 count)
at m.b()
at m.b(i A_0)
Sherlock
Exception from BlockingLogReader::Analyze().
at m.b(i A_0)
at o.a(String A_0, i A_1, TreeView A_2)

The output ext is .out
kapil

Kapil Gupta

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-10-05 : 17:36:52

quote:
Originally posted by kap_gemini

Hi,
Once i collected data from blocker script and opening it in Sherlock.exe it gives me error

“An exception was raised ,please see the internals.dat file ! exception = Exception from BlockingLogReader:: Analyze(). “

extract from internals.dat
5/10/2005 15:25:26:887
Is SPID causing blocking.
Spid causing blocking: 54
Blocking record saved in array.
mscorlib
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: startIndex
at System.String.IndexOf(String value, Int32 startIndex, Int32 count)
at m.b()
at m.b(i A_0)
Sherlock
Exception from BlockingLogReader::Analyze().
at m.b(i A_0)
at o.a(String A_0, i A_1, TreeView A_2)

The output ext is .out
kapil

Kapil Gupta

I recommend you email the author of the software for help. It is doubtful that they will monitor this thread.

-ec

kap_gemini
Starting Member

5 Posts

Posted - 2005-10-05 : 21:38:21

But i don't have their email address, can you give me that

Kapil Gupta

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-10-05 : 22:48:03

quote:
Originally posted by kap_gemini

But i don't have their email address, can you give me that

Kapil Gupta

It is in the application.

-ec

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-04-26 : 15:49:13

The command
osql -E -SBSGLABDB -i "\\networkpath\checkblk.sql" -ocheckblk.out -w2000

I cannot seem to run it from DOS ..is it possible to put inside of DTS Job and run it and still have the output file.

Where would it put the output file.

Thanks

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources