Sherlock – Tool for Blocker Script analysis – SQL Server 2000 and 7.0


This blog article is about an old tool that never got a kb article (for some reason), but now has its own space here.


 


A few years ago, back in 2004, I was creating a tool for SQL Server 2000 and 7.0 to help me analyze blocking scenarios. It was my first C# application.


 


After a while, I invited my friend Kevin Tsai to become my partner on Sherlock as he had great ideas for the tool, and he’s a SQL Server specialist, here at Microsoft. Then I put all of his SQL Server knowledge about blocking analysis into the tool.


 


This tool, Sherlock, analyzes the output of the Blocker Script and shows the information using a GUI interface where you can drilldown the information.


 


With this tool the engineers, including Kevin and I, dramatically reduced the necessary time to analyze the output from the blocker script and isolate blocking problems.


Sometimes we needed to use it when working at customer’s site. After seeing how the program worked our customers started asking for the tool. So, a year after that we decided to deliver the tool for public use.


 


Sherlock comes with a PPT that explains about blocking and about the tool. It also comes with the blocker script for SQL Server 2000 and SQL Server 7.


 


My friend Fabricio Catae, a SQL Server Ranger here at Microsoft, was our official Sherlock Beta tester during Sherlock development. Now he is working on Sherlock Pro. I’m sure, from what I’ve seen, Sherlock Pro will cannibalize Sherlock. J


 


The current Sherlock version is not compatible with SQL Server 2005, but Sherlock Pro will be.


 


Here are some screenshots:


 



 



 



 



 


Comments (3)

  1. tim says:

    When I run the tool, it prompts for the the name of the output file. What name or file extension is it looking for?

    I have tried to save the output from the blocker script as a text file and as log file but it can’t find either one in the directory.

  2. The file name should be similar to this:

    CDB__Run_sp_blocker_pss80.OUT

    The extension should be .OUT.

    Sherlock is compatible with SQL Server 2000 and 7.0

    To create the blocker script, prior to Sherlock analysis, you should follow:

    How to monitor blocking in SQL Server 2005 and in SQL Server 2000

    http://support.microsoft.com/kb/271509/

  3. Spencer Tabbert says:

    Was Sherlock Pro ever made available?  I am looking for the best way to analyze blocking in SQL Server 2005.

    Spencer