Michael J. Swart

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

September 16, 2009

What the devil?

Filed under: Miscelleaneous SQL — Tags: , , — Michael J. Swart @ 8:59 pm

The devil you know vs. the devil you don’t.
A good friend of my told me about an issue she recently came across. It seems that a particular group of admins implemented SQL Server database mirroring in order to implement a kind of relatively cheap high availability solution. It’s a useful strategy that’s discussed in a number of places. Here and here for example. 

Well, something went wrong or I wouldn’t be writing right now. I’m not familiar with all of the details, but there was some wrinkles with the performance of the network and the database server became extremely slow. (despite what you might think, this really isn’t an article about database mirroring) It’s ironic that the very thing that was implemented to increase up-time was the cause of down-time.

From another perspective, this is a case of “The devil you know vs. The devil you don’t.” Here, they traded the devil they knew – that is, not having a warm standby server – with a devil they didn’t – in this case, production uptime now depended on network performance in an unexpected way.

Other examples of known vs. unknown devils:

  • Known bugs in version 1.0 vs. Unknown introduced bugs in version 2.0
  • Raid 0 vs. Misconfigured SANs
  • Reliability of municipal power vs. Possibly fawlty UPS systems
  • Add your own examples to the comments! (best two comments get a No-Prize – extra points for irony.)

I’ve actually witnessed the unknown devils above. And as a lover of irony, I smiled (tempered slightly by sympathy for those whose necks were on the line).

The devil is in the details
In the examples I’ve mentioned above, the unknown devils reared their heads because of exposure to an unforeseen risk. What can you do about it though? Well, what else is there to do? Get to know that unknown devil. Test, test, test with all the time and resources available to you. There really is no short-cut. And sometimes, with the right testing, if you’re thorough enough you can get it right…

God is in the details*
Getting it right means you get the high availability you were hoping for. The uninterupted power. Secure systems, the robust bug free software. Maybe even five nines.

I’ll leave you with one last devil you know vs. devil you don’t example. It’s a picture of me and my brother. Presumably you know me and not him. But which is the evil one? Increased difficulty: neither of us have a goatee. Unfair: the photos have been scrambled.

Who’s the evil one?

* — BTW, “God is in the details” is a quote by architect Mies van der Rohe. I’m in good company when quoting architects for this industry; you’ve heard of design patterns right?

Michael J. Swart

September 10, 2009

View Blocked Process Reports using the Deadlock Graph Control

Filed under: Technical Articles — Tags: , , , , , , , — 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:
  1. 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)
  2. Copy the script below and run it in Management Studio.
  3. Click the blocking chain xml of interest to you.
  4. Save the xml file as an xdl file.
  5. 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.

September 8, 2009

Finding the Lead Blocker in a Set of Blocked Process Reports

Filed under: Technical Articles — Tags: , , , , — Michael J. Swart @ 5:18 am

Traffic jam by lynac on FlickrTakeaway: Skip to the end for a script that reports the lead blocker for a set of Blocked Process Reports. And tune in later this week for a nifty graphing trick for blocked process chains.

On Blocking
Sometimes SQL Server is like a highway, and is prone to traffic jams. Blocking processes (not to be confused with deadlocked processes) are processes that are waiting for resources that are being used by other processes.

Every now and then people talk about blocking chains. These are blocked processes that are waiting in a line. Process A is waiting for Process B. Process B is waiting for Process C. etc… In all cases, you want to find the lead blocker (the head of the chain) and address any issues that that process has. Think of the traffic jam analogy, it’s no good trying to focus on the Toyota that’s waiting patiently; you have to focus on the stalled Honda in the front.

I can think of two good ways to analyze blocking and blocking chains. The first method analyzes the current state of the database. The other focuses on collected trace data:

Method 1: Look at the current state of the database:
Look at the DMVs that are available, or at least to the sys.processes table. These tables provide data about the current state of the database. These tables have been discussed by Tim Chapman here and by Kalen Delaney here (Kalen’s script was written before 2005 but is still awesome). Or in short, try a variety of other solutions found here: http://www.lmgtfy.com/?q=sql+blocking.chain. I’m not going to add another solution with this post.

Method 2: Look at Blocked process report events in a trace:
Another method of analyzing blocking chains looks at data that is retrieved in one or more Blocked processes report. These Blocked process reports are generated and captured using a server-side trace or by using SQL Profiler. The blocked process report holds a ton of useful information. You can find a good example here (courtesy Johnathan Kehayias): http://jmkehayias.blogspot.com/2008/09/monitoring-for-blocked-processes-on-sql.html

Notice that there are two xml nodes under the blocked-process-report tag. These two refer to two processes, a blocked process and a blocking process (always two there are, no more, no less). So what happens if you have a blocking chain involving 12 processes? Then you’ll have 11 events in your trace. 11 blocked process reports with the same timestamp. This can make it tricky to find the lead blocker (the stalled Honda).

One way is to scan through the trace to find the process that’s been blocked the longest (the blocked process report with the largest duration). The blocking process is likely to be the lead blocker.

Or you can use this script:

WITH rankedEvents AS
(
   SELECT ROW_NUMBER() OVER (PARTITION BY EndTime ORDER BY Duration DESC) AS myRank ,
       CAST (TEXTData AS XML) AS TEXTdata
   FROM blocked
   WHERE EventClass = 137
)
SELECT TEXTdata
FROM rankedevents
WHERE myRank = 1

where blocked is the name of the table where SQL trace data is stored.

Later this week I plan to describe a method where I use SQL Server’s Deadlock Graph control to display blocking chains.

Michael J. Swart

December 11, 2008

Eliminated NULL values

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

I’ve gotten a bunch of questions recently about the following warning:

Warning: Null value is eliminated by an aggregate or other SET operation.

Hopefully, with this post, I’ll be able to explain what this error means and give some examples about what (if anything) you should do about it.
The aggregate or set operation is likely a SUM or COUNT. Microsoft has a list here of all the aggregate functions. So essentially, when you see this error message, it means you’re aggregating a bunch of values and one of the values that is being summed or counted is null. Consider the following example:
CREATE TABLE tempTable
(
   i float
)
GO

INSERT tempTable VALUES (1);
INSERT tempTable VALUES (2);
INSERT tempTable VALUES (NULL);
GO

SELECT COUNT(i) AS [count i:] FROM temptable
SELECT COUNT(1) AS [count 1:] FROM temptable
SELECT COUNT(*) AS [count *:] FROM temptable
SELECT SUM(i) AS [sum i:] FROM temptable
And the results would look something like:
count i:
-----------
2
Warning: Null value is eliminated by an aggregate or other SET operation.

count 1:
-----------
3

count *:
-----------
3

sum i:
----------------------
3
Warning: Null value is eliminated by an aggregate or other SET operation.
So now you have to decide what do you want to do about it:
Change the null to a value that is not null:
This is the most popular approach. For example:
select sum(i)
becomes
select sum(ISNULL(i, 0))
or the equivalent COALESCE if you like the ANSI standard:
select sum(COALESCE(i, 0))
And
select count(i)
should be written
select count(*)
or
select count(1)
any way.
Filter out the rows that can be null
Not so popular. Often it’s because of other requirements for the query. But if at all possible, try something like this:
SELECT SUM(i) FROM tempTable WHERE NOT i IS NULL
Suppress the warning
The ostrich’s answer with:
SET ANSI WARNINGS OFF
Or finally:
Ignore the warning
A variation of the previous suggestion. If the results are what you’re looking for, then don’t worry about it.
Michael J. Swart

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

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:

\\live.sysinternals.com\tools\procexp.exe
Michael J. Swart

April 30, 2008

What are you waiting for?

Filed under: SQL Scripts,Technical Articles — Tags: , , — Michael J. Swart @ 5:04 am

Update July 5, 2011: Many people are coming to this blog post through search engines trying to figure out what LAZYWRITER_SLEEP means. I looked at this article and determined that it’s not clear. I call the wait type ignorable, but I don’t say why. So here’s why: LazyWriter_Sleep:  Happens when a lazy writer task is suspended and waits to do more work. Since lazy writer tasks are background tasks, you don’t have to consider this state when you are looking for bottlenecks. Hope this helps… By the way, a much better article than this one is here: Wait Statistics by Paul Randal. What follows now is the original article:

The sys.dm_os_wait_stats helps to see where server processes are spending most of their time waiting. If the top cause is LAZYWRITER_SLEEP or SQLTRACE_BUFFER_FLUSH, then you’re all right. If not, then you’ve got some digging to do. Maybe start with sys.dm_exec_requests.

What are you waiting for now?

The times that are shown are cumulative since the start of the SQL Server service. I use this script to figure out what SQL Server processes are waiting for now:

select * into #BufferWaitStats from sys.dm_os_wait_stats

-- wait ten seconds or so
select ws.wait_type,
      ws.waiting_tasks_count - bws.waiting_tasks_count as waiting_tasks_count,
      ws.wait_time_ms - bws.wait_time_ms as wait_time_ms,
      ws.max_wait_time_ms,
      ws.signal_wait_time_ms - bws.signal_wait_time_ms as signal_wait_time_ms
from sys.dm_os_wait_stats ws
join #BufferWaitStats bws
      on ws.wait_type = bws.wait_type
order by wait_time_ms desc

--clean up
drop table #BufferWaitStats
Michael J. Swart
« Newer Posts

Powered by WordPress