Notes to Self: Debugging Deadlocks on SQL Server

Here are are some tips that I’ve learned from our company’s Database Whisperer in the last year. Every once in a few months, I stumble upon some weird database problem and I’m glad I can call Michael for help. Here are some tips I wanted to write down after tracking down a problem earlier this week.

Set a low blocked process threshold. Here’s how to configure it using SQL Server Management Studio. Run a stored procedure sp_configure and look for a variable named blocked process threshold in its output. If the configured value (in seconds) is too high, run the following commands to set it to 5 seconds:

sp_configure 'blocked process threshold', 5

reconfigure

How to get Blocked process reports using SQL Server Profiler. Open SQL Server Profiler and start a new trace. In the TraceProperties dialog, Events Selectiontab, uncheck all events.

Check the Show all events check-box. In the long menu of events, expand the Errors and Warnings node and check Blocked process report. Check Show all columns and check all possible check-boxes in the Blocked process report row.

How to interpret the blocked process report. The report contains, in an XML-like format, information about the blocked and the blocking process. The inputbuf elements let you see the SQL code your program is running, but sometimes they only identify the executing database object by ID, like this:

Proc [Database Id = 11 Object Id = 1744113354]

Then a simple query (in SQL Server Management Studio)

select object_name(1744113354)

identifies this object as a certain stored procedure. Okay, I probably won’t forget this part.

The blocked-process element contains execution stack, but there is one problem: its frames are presented in a very cryptic way, like this:

<frame line="1" stmtstart="74" sqlhandle="0x02000000ec598609171ca0e0d3bae80cc5a4c0e34169572c"/>

To decode them, the following one-line query is handy:

select * from sys.dm_exec_sql_text(0x02000000ec598609171ca0e0d3bae80cc5a4c0e34169572c)

That’s about it for now. For real database knowledge, read Michael’s blog. Here, I just want to write down a few things that were important to me in the last few days to make sure I don’t forget.

A Note on XML Serialization

A coworker asked me recently how to serialize an array of objects, belonging to different classes deriving from the same base class, such that the XML elements representing these objects revealed the class they belong to. He wanted the XML to look like this:

<ArrayOfBase>
    <Derived1 />
    <Derived2 />
    <Derived1 />
    ...
</ArrayOfBase>

I remembered doing exactly that a while ago, but I couldn’t remember exactly what I did. But it wasn’t difficult for us and didn’t take long to find the answer. Here it is:

Class RowSet is a DTO (data transfer object) containing a matrix of data cells.

[Serializable]
public class RowSet {
    [XmlElement( ElementName = "row" )]
    public Row[] Rows { get; set; }
    ...
}

Note how the XmlElement attribute is applied to a collection. It defines serialization of individual elements rather than the collection itself. Row is defined as a one-dimentional array of abstract Cells.

[Serializable]
public class Row {
    [XmlElement( Type = typeof( DataCell ),
                 ElementName = "d" )]
    [XmlElement( Type = typeof( NullCell ),
                 ElementName = "n" )]
    public Cell[] CellArray { get; set; }
    ...
}

Cell is an abstract class and DataCell and NullCell derive from it. RowSet serialized into XML looks like this:

<rows>
    <row>
        <d value=... />
        <n />
        <n />
        <d ... />
        ...
    </row>
    <row>
        ...
    </row>
    ...
</rows>

How To Spot a Possible Twitter Scam

If a Twitter application claims to do something that is not supported by Twitter API, then it is probably a scam. I got this idea from Dhananjay Nene a long time ago, when an application claimed to be able to find out who has viewed your Twitter page.

Quite a few people used an app named Twifficiency, which advertised itself as follows: “In a nut shell, Twifficiency calculates your twitter efficiency based upon your twitter activity. This includes how many people you follow, how many people follow you, how often you tweet and how many tweets you read.”

The highlighted part looks like a red flag.

The 10th Anniversary of the Joel Test

It may be hard to believe it was that long ago, but it was indeed on August 9, 2000 that Joel Spolsky wrote his famous article, The Joel Test: 12 Steps to Better Code

The test is based on a simple idea: answer 12 yes-or-no questions and if you answered “Yes” 12 times or close to that, you have a very mature and effective software engineering organization. If most of your answers were “No”, then you’re in serious trouble, or you will be when your hero programmer decides to leave. Regardless of how you score on this test, the questions you answered “No” tell you how you can improve.

Before you begin to criticize this test, consider that it was written a very long time ago and in what was a really a different era (the Agile Manifesto was published only the following year and we think Agile has been around for a while).

Over the last ten years, countless software developers asked themselves these questions, thought “we should really be doing this this…”, and then actually did it. At the same time, advancements in our profession were taking place and chipping away at the relevance of these questions.

Do you use source control? Is that even a question?

Do you make daily builds? Here, there may be a big difference between two ways to answer “yes”, for instance, between running a script every 24 hours just to build the thing and improvement based on continual feedback from a continuous integration system.

Do you have a spec? “No” may now even be the preferred answer. Imagine an awkward scene: someone walks into a room full of Agile Manifesto signatories and asks, “Do you guys have a spec?” True, there is an important place for specification in agile software development (such as executable specifications used in agile acceptance testing), but the spec described in the Joel Test is of a different kind, the big one written up-front and before everything else.

Do you do hallway usability testing? Okay, if you are a software developer and you never leave your desk and expect your manager to give you tasks, then you are probably developing something unusable. (Mike Cohn wrote in User Stories Applied that software development managers are some of the worst user proxies.) If that is your situation, you can discover a lot by stepping outside the cubicle and asking someone to use your program. But your team can probably discover the same insights, only earlier, by applying user stories and engaging in conversations with more relevant user proxies.

A Medium-Sized List of Agile Practices

I wanted to compile a list of important agile practices, big enough to include all the important ones, but not so big as to be overbearing, intimidating and unhelpful to agile beginners.

You would probably be right to interrupt me at this point by asking, wouldn’t such a list constitute a process? Doesn’t the Agile Manifesto teach that we should value individuals and interactions over processes and tools?

You would be right because the Agile Manifesto says nothing about using, for example, continuous integration. But how many of the early adopters of agile refuse to use it? Hardly any. At the same time, the word agile is used by many more people now than ten years ago and stories like “we’ve implemented scrum; we now hold status meetings daily instead of weekly” abound.

So, the list of recommended practices will of course come with disclaimers and caveats. Each listed practice is optional and following all of them won’t automatically make you agile. But let’s try to compile the list first.

I visited a blog, NOOP.NL, whose author, Jurgen Appelo, is both a recognized agile expert and seems to be a fan of top-100 lists. He did The Big Agile Practices Survey a while back and then published the results. Out of the survey’s many questions, I decided to consider the following three:

• Which practices are REALLY AGILE, or… highest level of agility?
• Which practices are REALLY IMPORTANT, or… highest level of importance?
• Which practices are REALLY APPLIED, or… highest level of application?

Which practices are REALLY AGILE, or… highest level of agility?

Area	Best practice	Score
Process	Iteration Planning / Planning Game / Sprint Planning	98.8%
Process	Velocity	98.1%
Process	Sprint Backlog	98.1%
Process	Daily Stand-up Meeting / Daily Scrum	97.6%
Organization	Self-Organizing Team / Scrum Team	97.5%
Process	Story Points	96.9%
Organization	Scrum Master	96.3%
Process	Sprint Review / Iteration Demo	96.3%
Process	Timeboxing / Fixed Sprints / Fixed Iteration Length	96.0%
Requirements	Product Backlog	95.6%

Which practices are REALLY IMPORTANT, or… highest level of importance?

Area	Best practice	Score
Construction	Source Control / Version Control	100.0%
Process	Definition of Done / Done Done	99.4%
Construction	Refactoring	98.9%
Organization	Sustainable Pace	98.6%
Construction	Frequent Delivery / Frequent Releases	98.3%
Requirements	Product Backlog	98.2%
Requirements	Requirement Prioritization	98.2%
Testing	Unit Testing	98.2%
Testing	Storytesting / Acceptance Criteria / Acceptance Testing	98.1%
Process	Retrospective / Reflection Workshop	98.0%

Which practices are REALLY APPLIED, or… highest level of application?

Area	Best practice	Score
Construction	Source Control / Version Control	100.0%
Requirements	Requirement Prioritization	94.4%
Testing	Unit Testing	93.4%
Requirements	Product Backlog	93.2%
Process	Iteration Planning / Planning Game / Sprint Planning	92.9%
Construction	Refactoring	91.8%
Process	Daily Stand-up Meeting / Daily Scrum	90.8%
Process	Timeboxing / Fixed Sprints / Fixed Iteration Length	90.8%
Construction	Daily Builds / Automated Builds / Ten-Minute Builds	90.3%
Testing	Integration Testing	88.2%

Several observations can be made:

• Notice the amazingly high percentages of respondents who said that they associate a particular practice with agile, believe that it is important, and actually apply it.
• The identified practices are not constrained to any particular agile method. For example, there is no mention of pair programming (an XP practice) and the respondents didn’t say the scrum master has to be a designated person.
• Some of the practices are mentioned more than once. The Product Backlog occurs in all three lists.
• Let’s say you don’t have a product backlog. The Agile Manifesto doesn’t say you must have it. But it should be clear now that either you have a good reason not to have it or you are missing something important.

By joining the three lists and removing the duplicates, I got a list of 21 practices. I removed the percentages (they are now unimportant), grouped related practices together and renamed the areas. Here is the result:

Area	Best practice
People	Self-Organizing Team / Scrum Team
People	Scrum Master
Timing	Timeboxing / Fixed Sprints / Fixed Iteration Length
Timing	Sustainable Pace
Timing	Frequent Delivery / Frequent Releases
Requirements	Product Backlog
Requirements	Requirement Prioritization
Requirements	Iteration Planning / Planning Game / Sprint Planning
Requirements	Sprint Backlog
Tools and Techniques	Source Control / Version Control
Tools and Techniques	Daily Builds / Automated Builds / Ten-Minute Builds
Tools and Techniques	Refactoring
Tools and Techniques	Unit Testing
Tools and Techniques	Integration Testing
Tools and Techniques	Storytesting / Acceptance Criteria / Acceptance Testing
Feedback	Daily Stand-up Meeting / Daily Scrum
Feedback	Sprint Review / Iteration Demo
Feedback	Definition of Done / Done Done
Feedback	Retrospective / Reflection Workshop
Feedback	Velocity
Feedback	Story Points

Now I want to take one more simplifying step: get rid of the tabular form of information.

Start with people. You need a scrum master and a self-organizing team. Timing-wise, your process is iterative, uses fixed-length, time-boxed iterations and emphasizes sustainable pace and frequent releases. What do you do in each iteration? You start with a product backlog, which is maintained to reflect your current prioritization of requrements. Once you have it, you can hold an iteration planning meeting at the start of an iteration and select user stories into the sprint backlog. Now, what techniques to you use to engineer the solution to satisfy your customer? Well, source control is a must, daily, automated builds (probably enabled by a continuous integration system) are helpful. There are no recommendations on how to design and write your code, but it is recommended that you refactor it. Then there is a three-pronged testing strategy: unit-, acceptance and integration tests. (Serious QA strategy will have more prongs, but we are talking about the minimal agile-specific set.)

Lastly, and most importantly, there is feedback, lots of it and in various forms. The daily scrum (or, in a literal translation from one of the foreign languages, the synchronization reunion!) and the iteration demo, when there is no avoiding the question: what is our definition of done? Retrospectives to improve the teamwork and fine-tune the practices. Velocity is very important in agile estimation and planning and to track velocity you need story points.

OK, getting to this list was labourious, but the result itself is compact as I promised at the beginning of the post.