Michael J. Swart

RSS Feed
RSS Feed
Email Me
Email Me
About Me
About Me

April 20, 2011

A New Way to Examine Blocked Process Reports

Filed under: SQL Scripts,SQLServerPedia Syndication,Technical Articles — Tags: , , , , — Michael J. Swart @ 12:00 pm

Solving concurrency problems are a large part of troubleshooting. Often solutions include tuning the blockers to minimize the blocked time or tweaking locks and isolation levels to make processes play nicely with each other. But to dig into the problem, you have to understand the blocking chain.

If you’re troubleshooting a concurrency problem that’s happening on your server right now then you can get information from the DMVs or even better, by using Adam Machanic’s Who Is Active stored procedure.

But what if the excessive blocking behavior is intermittent? Then the best strategy is to monitor the server and try to capture a SQL trace that includes the “blocked process report” event. I’ve had a lot of luck with that event, it can really tell you a story about excessive blocking. But I find that interpreting the trace can be tricky, especially when there’s a large blocking chain. Sorting through hundreds of events to find the lead blocker is not fun.

New and Free: sp_blocked_process_report_viewer

So I wrote a script! And I stuffed it in a stored procedure! Here’s the syntax (BOL-Style):
Syntax

sp_blocked_process_report_viewer [@Trace = ] 'TraceFileOrTable'
    [ , [ @Type = ] 'TraceType' ]

Arguments
[@Trace = ] ‘TraceFileOrTable’

    Is the name of the trace table or trace file that holds the blocked process reports

[@Trace = ] ‘TraceType’

    Is the type of file referenced by TraceFileOrTable. Values can be TABLE, FILE or XMLFILE. The default is FILE

Download it Now!

Go to the https://github.com/mjswart/sqlblockedprocesses site and download it. Once you’re there, click on the big green download button (as shown to the right) and you’ll have the stored procedure!

Here’s a sample output. It shows clearly who the lead blocker is:

A screenshot showing output for this sproc

Nicely organized, at least better than usual

I’m Promoting This Script to a Project

Although, you still have to know how to read a blocked process report, this utility makes the structure of the blocking chain clear. I find this script useful for my own purposes. In fact I like it enough that I’m going to maintain it on github as:  SQL Server Blocked Process Report Viewer

Let Me Know How It Goes

Run the script! Use it! Tell your friends. Tell me what you think of it (for once in my life, I’m seeking out criticism).

Going forward, I do have some plans for the script. There’s a number of things I eventually want to do with it:

  • Add error handling
  • Really make the sproc perform well (it’s already decent).
  • Develop a test suite (sample traces that exercise the procedure)
  • There’s an opportunity to look up object names, index names and sql text based on object ids if the traces belong to the local server.
  • A SQL Server Management Studio plugin. A treeview control would really be useful here. (This might be easier after Denali comes out)

I plan to do the work, but if you’re really keen and you want to pitch in, you’re welcome to. If you see any errors you can

September 29, 2008

Passing XML Data from the App to the Database

Filed under: Technical Articles — Tags: , , , , , , , — Michael J. Swart @ 5:27 pm

Passing xml data from the app to the database

So I recently talked about how SQL Server can take xml data and shred it into something more relational.

I wanted to follow it up with this post and show an example of how to use ADO.Net to make use of SQL’s xml data type and pass it to an application.

First say that you have a stored procedure with this signature:

CREATE PROCEDURE ShredThis(@p1 XML)
AS

Then you can use that procedure from within C# code with something like this:

private void button1_Click(object sender, EventArgs e)
{
    SqlCommand cmd = null;
    try
    {
        cmd = new SqlCommand();
        cmd.CommandText = "ShredThis";
        cmd.CommandType = CommandType.StoredProcedure;
        XmlReader xr = XmlReader.Create(new StringReader(@"
        <book author=""J K Rowling"" Title=""Harry Potter"">
            <chapter number =""1"" name=""the boy who lived""><chapter>
            <chapter number =""2"" name=""diagon alley""><chapter>
            <chapter number =""3"" name=""quidditch""><chapter>
            <chapter number =""4"" name=""dementors""><chapter>
            <chapter number =""5"" name=""voldemort's back""><chapter>
        <book>"));
        cmd.Parameters.Add("@p1", SqlDbType.Xml).Value = new SqlXml(xr);
        cmd.Connection = new SqlConnection(@"Data Source=mswart\SQL2008;Initial Catalog=tempdb;Trusted_Connection=True");
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
    }
    finally
    {
        if (cmd != null && cmd.Connection != null && (cmd.Connection.State == ConnectionState.Open))
        {
            cmd.Connection.Close();
        }
    }
}

You can cut and paste this into any app and adjust it to how you need it. Remember a couple things:

  • Don’t forget the using clauses to make sure the code compiles
  • Check out the SqlXml class.
  • The SqlDbType enumeration has an Xml entry. This is what it’s used for

Cheers,

Michael J. Swart

A better XML shredding example

Filed under: SQL Scripts,Technical Articles — Tags: , , , , , , , — Michael J. Swart @ 12:41 pm

Passing data into the database as a set has always been a challenge. There have been a number of approaches used for various purposes. And those approaches are discussed in many different places already.

If you only have to support SQL 2008, then table valued parameters are definitely the way to go.

If you have to support SQL 2005 (like myself) then other methods have to be used. Such as by parsing CSVs. Or my new favorite method of shredding xml.

Shredding XML
I like this method because it maintains data/script separation which is important from a security point of view.

One way of shredding xml is by using the nodes() method of the xml data type. The official documentation is here, but it wasn’t clear how to use this method for the business case I mentioned.

Here’s my example which I use as a template. Maybe you’ll find it useful too:

DECLARE @data XML
SET @data = '<root xmlns="http: //www.MySampleCompany.com">
 <book author="J K Rowling" title="Philosopher''s Stone">
  <chapter number="1" name="the boy who lived"/>
  <chapter number="2" name="the rest"/>
 </book>
</root>';

WITH XMLNAMESPACES ('http: //www.MySampleCompany.com' AS MY) 
SELECT 
   chapters.node.value('../@title', 'nvarchar(50)') AS bookTitle,
   chapters.node.value('../@author', 'nvarchar(50)') AS bookAuthor,
   chapters.node.value('@number', 'int') AS chapterNumber,
   chapters.node.value('@name', 'nvarchar(50)') AS chapterName
FROM @data.nodes('//MY:chapter') AS chapters(node)

The results look like this

bookTitle             bookAuthor  chapterNumber chapterName 
--------------------- ----------- ------------- ------------------ 
Philosopher's Stone   J K Rowling 1             the boy who lived 
Philosopher's Stone   J K Rowling 2             the rest

Also, if you don’t use namespaces with your XML, you just omit the WITH clause.

Update!
Check out this post to see how this feature looks from the app side (using c#).

Michael J. Swart

Powered by WordPress