Today I came across a link to a neat little script for SQL 2005 / 2008 to help derive the head blocker in a blocking chain. That script can be found here. I was looking at the script and thought it looked pretty cool and also wondered why it might look a little familiar. Not too big of a concern since many admin type scripts can look familiar.
Then I noticed that the title contained the words Activity Monitor. I wondered to myself, was this taken from activity monitor? So, I decided to play with Profiler for a bit to see if I could generate the same query. The process really isn’t too difficult. Here is what I did.
- Open Activity Monitor. Simple enough to do. Right click the instance (in SSMS) you wish to monitor, select Activity Monitor from the context menu.
- Open Profiler. Again from within SSMS, click the tools menu and then select SQL Server Profiler.
- Start a New trace. When starting the trace ensure that you are connecting to the same instance as you started Activity Monitor. Select the TSQL-Replay template (other tsql will probably work, this is the one that I used). Goto the events selection tab and click on the Column Filters button. Select “LoginName” and then expand “Like” on the right hand side. In the new text box, type the login you are using to authenticate to the SQL Instance in question (domainuser or SQLLogin).
- Click run and browse through the results for a bit. It shouldn’t take too long for that query to pop up.
- Once you find the query, make sure you stop the trace.
What is the point in this? Well, as explained in that other blog, sometimes it is just better to run a query than to run Activity Monitor. Furthermore, you can take this method and trap many of the queries that Microsoft built into that tool. You can either use them for learning or troubleshooting. This is just one more tool to have in your arsenal in order to become better at your job.
