Michael J. Swart

September 29, 2008

Passing XML Data from the App to the Database

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:


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;
        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>
        cmd.Parameters.Add("@p1", SqlDbType.Xml).Value = new SqlXml(xr);
        cmd.Connection = new SqlConnection(@"Data Source=mswart\SQL2008;Initial Catalog=tempdb;Trusted_Connection=True");
        if (cmd != null && cmd.Connection != null && (cmd.Connection.State == ConnectionState.Open))

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


Michael J. Swart

A better XML shredding example

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:

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"/>

WITH XMLNAMESPACES ('http: //www.MySampleCompany.com' AS MY) 
   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.

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

June 11, 2008

Identifying High CPU SQL Processes

Filed under: Technical Articles — Tags: , , , , , , — Michael J. Swart @ 12:09 pm

UPDATE February 13, 2012: As of this update, this post is about four years old. And a lot of things have changed since then. For one, the video is no longer available (as many have discovered). But if you’re trying to identify a query or queries that are consuming the most CPU, I strongly recommend using the sp_whoisactive stored procedure. It’s the method I use now rather than using process explorer What follows is the original 2008 post:

Recently when trying to identify sql processes consuming the most CPU, I came across this most excellent paper written by Jeff Stevenson. It’s called Identifying High CPU SQL Processes.

It describes how to identify which sql processes that are consuming the most cpu cycles. The good part is that he describes how to do it using only tools that are available via SQL Server or the Operating System (i.e. Profiler, Performance Monitor, Management Studio).

It was very useful but the most tedious part of the process was finding the SQL Server process id. The process required a scavenger hunt where it was necessary to find the instance, then thread instance id, then kernel process id, then SQL process id. This required two performance monitor sessions with very awkward settings.

I believe I have an improvement that allows us to skip a number of steps. It requires the use of a free Microsoft tool, but the drawback is that the tool is not available either with the operating system or SQL Server. It’s a free download and the installation is easy, but if, as a dba, you get a call to troubleshoot a problem on a remote server, and the remote server doesn’t have this tool, then you won’t be able to use this tool.

The tool I’m talking about is Process Explorer (made by the guys once known as SysInternals). It’s a free download here. I highly recommend it. At least install it on your development box.

I’ve attached a video (no audio) that gives you an idea of what’s involved when identifying the kpid:

The video shows how to use Process Explorer to find the SQL instance and the thread of the offending sql process. In this particular case, I sort the processes by cpu and I see the largest CPU consumer is sqlservr.exe (If it’s not then this post isn’t for you). I select the SQL Server process and click on the properties icon to launch the properties window. On the image tab I learn that the database instance that is consuming cpu is called “SQL2005”. On the threads tab I learn that the thread id is 3960. So 3960 is what SQL Server refers to as the kernel process id (kpid).

You now have the SQL Server instance and the kpid of the process. After that, it’s time to find out what this process is doing. You can do this by following Jeff Stevenson’s method that I linked to at the top of this post. It’s a matter of using a set of sql queries similar to the following:

select * from master..sysprocesses where kpid = 3960
-- turns out to have spid = 55

dbcc inputbuffer(55)
-- shows what 55 is doing:

-- and if absolutely necessary:
kill 55 -- be careful with this :-)

There’s one last thing I want to mention. You may find that all the connections are taken. It’s an very likely scenario if SQL Server is misbehaving. In this case you can log in using the Dedicated Administrator’s Connection. Definitely practice this before any crisis so that you don’t have to look up any syntax at the time. Essentially, you want to log onto ADMIN:SERVER\INSTANCE instead of SERVER\INSTANCE.

Update !!!
In an extremely timely post, John Paul Cook describes how to get around the drawback of not having sysinternals installed on the remote machine. If the machine can access the internet, then you can simply run the following:

« Newer Posts

Powered by WordPress