shredding | Michael J. Swart

September 10, 2009

View Blocked Process Reports using the Deadlock Graph Control

Filed under: Technical Articles — Tags: "sql server 2005", "sql server 2008", "sql server", blocked process report, blocked processes, deadlock, deadlocks, shredding — Michael J. Swart @ 4:37 am

What am I looking at here?

Shown above is a set of blocked process report. But it’s being displayed using SQL Server’s Deadlock Graph Control. Here’s a detail of the graph:

I’m totally wearing my hacker hat with this. Microsoft built the deadlock graph control to display deadlock graphs generated by Microsoft’s tools. What I’ve done is to take a set of blocked process reports and reformat them to look like a deadlock graph. You can tell that it’s not a deadlock because the graph is a tree, with no cycles.

See that process with the X through it. Normally it indicates a deadlock victim. But in this case, I’ve repurposed the X to indicate the lead blocker (i.e. the stalled car in the traffic jam of blocked SQL Servers).

Why?

The graph is useful because it can tell a story graphically that would otherwise take maybe 100 times longer to understand by reading the xml reports directly.

A few years ago when I installed SQL Server 2005 for the first time, I played around with SQL Server profiler and traces in general and had one of those moments that made me say “nifty!” I was looking at a deadlock graph displayed graphically.

Not much later after that, I noticed that blocked process reports contain information very similar to deadlock graphs. It wasn’t much of a leap to think that with a little creative reformatting, I could make the deadlock graph control do something else useful with blocked process reports.

How?

So here’s what you do:

Find a trace that contains blocked processes reports. If it’s not already, save it as a table called blocked. The script below assumes that a table called blocked has already been created. (Alternatively, create an synonym called blocked that refers to a table)
Copy the script below and run it in Management Studio.
Click the blocking chain xml of interest to you.
Save the xml file as an xdl file.
Close and reopen the file in Management Studio.

The man behind the curtains.

So here’s the script I talked about. It’s also an example of me flexing my XML muscles.

Update Mar. 29, 2010 Due to the size of the script and some other formatting issues, I’m providing the script as a link rather than something to copy and paste. Download the script here.

-- Comments (5)

September 29, 2008

Passing XML Data from the App to the Database

Filed under: Technical Articles — Tags: "sql server 2005", "sql server 2008", "sql server", nodes, shredding, shredding xml, xml, xml shredding — 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

-- Comments (0)

A better XML shredding example

Filed under: SQL Scripts,Technical Articles — Tags: "sql server 2005", "sql server 2008", "sql server", nodes, shredding, shredding xml, xml, xml shredding — 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#).

-- Comments (2)