You are here: Home > 2009 > May
Archive | May, 2009

Profiler: Deadlock Graph Event

by on May 23, 2009

Until recently I’d never used the deadlock graph event in Profiler. I’ve been pretty lucky and haven’t had to troubleshoot deadlocks much until now. The deadlock graph turns out to be quite nice!

When  you add this event, it uses the TextData column to describe the deadlock in XML. You can copy the contents and parse them, or just read through it to see details on the transactions involved in the deadlock. Profiler also displays a graphical picture of the deadlock. I find the picture is helpful mostly when going through the XML itself, but the whole thing is pretty great.

The XML itself looks to be about the same as you get from trace flag 1222 in the SQL log, but you get the picture and the context of the other events you are profiling in sequence as well.

Note: the deadlock graph won’t work when  you are applying certain filters in SQL 2005: See Kalen Delaney’s Post

Here is a sample look at how profiler displayed one of our deadlocks: DeadlockGraph

Comments { 0 }

Replication – Updates that Don't Flow Through

by on May 23, 2009

This past week at work we found an instance where a replicated table (transactional push) was out of sync on the subscriber. In evaluating how to address the situation, we did some testing in pre-production and discovered the following (using profiler)

  • If you update a row on the published  table by setting a date column to  itself, the row will not be updated at the subscriber. No check is performed to verify the value at the subscriber.
  • If you update a row on the published table by incrementing a date column by one millisecond, the row will not be updated at the subscriber.
  • If you update a row on the published table that is not at the subscriber, an error will result and the row will not be inserted.

The resolution we went with was to pause processing, re-push the article, reset the application, and use a verify process in our application to detect what had changed in the replicated table. Happily the  table was only 14GB so the whole thing went pretty smoothly.

Why was the table out of sync? I found the root cause, but that’s a different story.

Comments { 0 }

Finding Plans and Stats for Queries like '%something%'

by on May 16, 2009

I often need to find a query plan in the cache for a process that has run long overnight. Typically I’ll be able to figure out from our logging some of the tables involved in the query. Sometimes I will have most of the executing text but won’t know exactly what dates or reference points were included.

Even when I have enough information to get an estimated plan, it’s usually really helpful if I can pull the actual plan out of the cache along with runtime statistics.

The query below is what I use at this point to try to find these plans– I also sometimes use it just to look for long running queries in general.

One note to remember– the last_execution_time field is the time of the plan activities at the last execution. So if you’re looking for a query that ran for an hour, this time would show at the beginning of that execution. (The logging on my systems is done after a batch of activities complete, so I always have to do a bit of work to figure out approximately when the activity would have started and look around that time for the plan.)

Continue Reading →

Comments { 0 }