SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using SHERLOCK to Monitor Blocking
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 05/16/2005 :  23:35:05  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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 - 05/17/2005 :  13:29:26  Show Profile  Reply with Quote
Good stuff...and good presentation.



Brett

8-)
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 05/17/2005 :  14:11:52  Show Profile  Reply with Quote
does this presentation suggest that we run the blocker script at all times?



-ec
Go to Top of Page

ramdas
Posting Yak Master

USA
181 Posts

Posted - 05/17/2005 :  15:57:47  Show Profile  Reply with Quote
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
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 05/17/2005 :  17:48:08  Show Profile  Visit graz's Homepage  Reply with Quote
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.
Go to Top of Page

amachanic
SQL Server MVP

USA
169 Posts

Posted - 05/20/2005 :  13:32:34  Show Profile  Visit amachanic's Homepage  Reply with Quote
Eyechart,

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



---
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
Go to Top of Page

grambowk
Starting Member

2 Posts

Posted - 05/25/2005 :  04:34:19  Show Profile  Reply with Quote
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
Go to Top of Page

dtomyn
Starting Member

2 Posts

Posted - 05/25/2005 :  13:39:39  Show Profile  Reply with Quote
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.
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 05/25/2005 :  15:24:19  Show Profile  Visit graz's Homepage  Reply with Quote
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.
Go to Top of Page

grambowk
Starting Member

2 Posts

Posted - 05/26/2005 :  04:23:36  Show Profile  Reply with Quote
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
Go to Top of Page

shamking
Starting Member

7 Posts

Posted - 06/01/2005 :  04:14:50  Show Profile  Reply with Quote
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
Go to Top of Page

MesterLars
Starting Member

3 Posts

Posted - 06/15/2005 :  06:07:53  Show Profile  Reply with Quote
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?
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 08/02/2005 :  13:58:30  Show Profile  Reply with Quote
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

Go to Top of Page

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1167 Posts

Posted - 08/15/2005 :  05:02:13  Show Profile  Reply with Quote
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!
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 08/15/2005 :  10:31:03  Show Profile  Visit graz's Homepage  Reply with Quote
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.
Go to Top of Page

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1167 Posts

Posted - 08/16/2005 :  08:22:04  Show Profile  Reply with Quote
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!
Go to Top of Page

kap_gemini
Starting Member

USA
5 Posts

Posted - 10/05/2005 :  16:47:27  Show Profile  Send kap_gemini a Yahoo! Message  Reply with Quote
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
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 10/05/2005 :  17:36:52  Show Profile  Reply with Quote
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
Go to Top of Page

kap_gemini
Starting Member

USA
5 Posts

Posted - 10/05/2005 :  21:38:21  Show Profile  Send kap_gemini a Yahoo! Message  Reply with Quote
But i don't have their email address, can you give me that

Kapil Gupta
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 10/05/2005 :  22:48:03  Show Profile  Reply with Quote
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
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

593 Posts

Posted - 04/26/2006 :  15:49:13  Show Profile  Send TRACEYSQL an ICQ Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000