Michael J. Swart

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

May 25, 2011

Another Advantage of Consultants

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication — Tags: , — Michael J. Swart @ 12:00 pm

Takeaway: Veering away from technical content for a bit, I talk about the advantages of hiring an outside consultant to help with technical problems.

(I’m a full time employee and I have been since University. So when I push the advantages of hiring consultants, I’m not trying to sell anything. )

Many consultants market themselves by blogging and training. If you follow any SQL Server bloggers, there’s probably a fair number of them that are consultants. So they’re the first ones who will tell you all the reasons you can and should hire them.

But Michael Chrichton or Steven Spielberg can warn you against hiring the wrong one. Hiring the wrong consultant or contractor can prove deadly:

John Hammond's Fatal Mistake

But enough about that.

Why Do People Hire Consultants?

The Obvious

  • To solve technical problems of course!
  • You want to hire someone with expertise that you don’t currently have on staff.  (But not on a permanent basis)

The Not So Obvious

  • Having more exposure to the industry, they are in a  better position to spot things that are unusual or non-standard. They’ve got a more objective second pair of eyes.
  • They’re usually really well connected. Imagine they’re a SQL Internals expert, but you’ve got a Business Intelligence problem. They usually know how to find good help.
  • Somehow you’ve got extra budget and if you don’t spend it, your group will be punished next year with a smaller budget. (That situation always struck me as weird).
  • You’ve got an idea or solution and you know it’s the right solution. But you can’t implement it because you just don’t (yet) have the pull at your company. Consultants can be your hired clout.

The Surprising

My company recently engaged Microsoft’s CAT team to come talk to us. It’s not that there are any fires we want to put out. We’re just looking for the best way to continue grow and support our systems for the next 5 to 10 years.

I was really excited to talk to SQL Server experts for three days. To make the most of the consultation, we booked a meeting room for the entire time and got some of our most experienced people on hand to participate. We talked about lots of things, like scalability and about all the little headaches and issues that we face on a day to day basis.

Just stop there for a second. Imagine that same scenario in your workplace but now take out the consultant. What have you got now?.

You’ve got your best and most experienced people taking three days to discuss the largest headaches and issues facing your team.  THAT’S GOLD JERRY, GOLD! Now add in a consultant as a mediator and it works even smoother! It worked for us. There was actually a point during the engagement where the consultant (Hi Chuck!) was simply standing at the white board writing down pros and cons of various solutions that our team had come up with.

And when your consultant/mediator starts contributing good ideas that you haven’t thought of yet. Well that’s really really fun.

Conclusion: Consultants 1, Problems 0. Just don’t let them network your dinosaur park.

 

May 18, 2011

Okay, You’ve Found Blocking, Now What?

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: , , — Michael J. Swart @ 12:00 pm

Takeaway: You’ve detected, analyzed and understood blocked processes that have happened on your machine. What can you do to avoid this blocking in the future?

Those who know me well, know I’ve started a project that helps database professionals analyze the blocked process reports that SQL Server produces. I’ve talked about it before:

Barry White endorses my software ... probably

What Next?

But I got an excellent comment from Wallace Houston. He writes:

“What I’m looking for is “what to do” when blocks are already identified.  I want to try to prevent them.  In other words, how to modify my code in such a way as to avoid as many blocks as possible.”

That’s an excellent question. Wallace went on to talk about a “chain reaction” of blocking where everything locked up. Man, I’ve been there and it can be disastrous. It sometimes seems like the only thing to do is to kill the lead blocker’s process. And that feels like giving up.

So after giving it some thought. I came up with this (slightly reworded from my reply to Wallace). These are actions you can take to avoid blocking in the future.

This is Next:

There’s a lot you can do to avoid excessive blocking. I’ve learned very recently that peoples’ ideas of excessive blocking vary a lot. In my environment, I look for blocking longer than 10 seconds. Other people watch for a threshold of 10 minutes!
Either way,
  • If at all possible, tune the lead blocker. If a lead blocker can get take its lock and let it go immediately then there’s no more blocking.
  • Avoid transactions if you don’t need them (but don’t be afraid of them if you do).
  • Pick the nicest isolation level for your transaction. Read committed is nicer than repeatable read is nicer than serializable.
  • If you can get away with it, maybe put the query that’s the lead blocker in a transaction that uses “snapshot isolation”. It uses a bit of tempdb resources, but it’s awesome for concurrency problems.
  • There’s table lock hints (readpast, nolock, holdlock) for more targeted locking, but really you want to understand who’s doing what before you go down that road.
  • I’m not ashamed to say that I’ve used isolation level “read uncommitted” which is equivalent to the NOLOCK table hint. Especially when the caller can tolerate the very very rare cases of inconsistency that might result. NOLOCK is easy and you can’t argue with results – it works – but snapshot isolation is preferred because it is guaranteed to be consistent (if not current).
I remember once I had a table, let’s call it  T whose columns a,b,c,d, and e got queried a lot. But column x got updated a lot and we had blocking issues. The solution was to split the table T into T1 (a,b,c,d,e) and T2(x) with a foreign key from T2 to T1. We then updated queries that used it and got seriously reduced contention.

Progress on my Blocked Process Report Viewer

And for those that are curious, my Blocked Process Report (BPR) Viewer is coming along nicely.

Lately I’ve

  • fixed a few bugs
  • added some documentation (which you’ve already seen if you read this blog).
  • added some SQL Profiler template files and SQL Trace scripts to help collect BPR
  • added a quick and dirty test suite.

It’s pretty much good to go and release as version 1.0. But I still want to add features that makes it easier to analyze.

Stay tuned and happy block busting!

May 4, 2011

When To Use Blocked Processes Reports

Filed under: SQL Scripts,SQLServerPedia Syndication,Technical Articles — Tags: , , , — Michael J. Swart @ 12:00 pm

I introduced the SQL Server Blocked Process Report Viewer a couple weeks ago and I realize that I took something for granted. Not everyone has practice watching for Blocked Process Reports and not everyone understands how or when they can use my utility to help troubleshoot concurrency issues.

Steps For Finding Concurrency Problems

Remember: When it comes to concurrency problems, you don’t have to guess what’s wrong!!!

And I explain it flowchart style!

Wait Statistics Who Is Active SQL Trace SQL Server Blocked Process Report Viewer Event Notifications The Future – Tracking Blocking in Denali
  • Checking for LCK_M_XX waits Paul Randal has a great script that interprets the data in the dmv sys.dm_os_wait_stats. I like this script because when all else fails. This script is a great starting point for understanding where the system’s bottlenecks are.
  • Using sp_WhoIsActive Adam Machanic wrote Who Is Active as an tricked out version of sp_who and sp_who2. I recommend it because it is a great view into what’s active on your server right now. And that includes blocked processes and other concurrency issues. (i.e. For any acute problem go there. For chronic concurrency problems, come back here).
  • Using SQL Trace You might know this as Profiler. Capture a trace with the “Blocked Process Report” event which is located in the Error and Warnings event list. But don’t forget! You first have to decide on what it means for your system to have excessive blocking and configure the blocked process threshold accordingly. I’ve learned very recently that peoples’ ideas of excessive blocking vary a lot. In my own environment, I often look for blocking longer than 10 seconds. Other people use a threshold of 10 minutes!
  • Analyzing Traces With Blocked Process Report Viewer This is the tool I wrote that I hope you find useful. Right now it tells you who the lead blocker is. And I hope to expand the features into analysis soon.
  • Configuring Server for Event Notifications I’m really not too familiar with this option and don’t use it much. As an alternative you can also use WMI queries and events mapped to a sql agent job (Thanks Vincent Salard, for that tip).
  • Using Extended Events Once Denali Arrives Jonathan Kehayias knows extended events backwards and forwards. In his blog post here, he describes how in the next version of SQL Server, the blocked process report will be traceable using extended events.

An Ounce of Prevention

In an extremely timely post Kendra Little writes about understanding locks in It’s a Lock. Following much of the same steps, you can understand what your app is doing beforehand and avoid any blocking problems from the start (e.g. understanding locks held during schema changes).

Next Week

  • I’ll be releasing SQL Server Blocked Process Report Viewer. (Right now we’re only in beta and I’m also open to suggestions about a new name for the tool)
  • Understanding blocking is the first step. Next week I’ll talk about what happens after analysis. I’ll write about the steps I’ve taken and had success with. after analysis.

Powered by WordPress