SQLLawman

Well, it’s been 10 days since I started to play with parallelism on this server.  I have gotten the self blocking to stop.  My CPU utilization has stabalized and my phone is not ringing.  So I think that the issues is mitigated.  I finally settled on Max Degrees of Parallelism of 2 and the Cost Threshold for Parallelism of 500.  I also have the developers looking at the code to ensure all their joins are nolock, they tell me they should be, but I am asking nicely to them to verify.  So I’m moving on to other issues unless this one pops back up.

If you are expecting a deep, well researched, thorough explanation of degrees of parallelism and cost threshold for parallelism you have come to the wrong place.  I’m sorry.  Hit the back button and choose a different link.  O.K., now that I’m alone again, I can get into my issue.  I have a vendor application, which will remain nameless, that is having some problems.  I have noticed by watching activity monitor that this database really chews up CPU.  When this app is quiet, I see CPU in the teens.  When it starts running I see 50% with 70% spikes.  But what has caught my eye, using SSMS 2008, is how much this app self blocks.  I don’t know if that is a real term, but I like it.  What I am talking about is one SPID that spawns several threads with the same SPID and those threads start blocking other threads on the same SPID.  As soon as I start seeing this I see CPU start climbing.  I also see waiting process start to grow.  It doesn’t take too long until I hear my phone ring.  The vendor has told me I can kill off these processes without harming the application.  So I’ll kill a few of them and get CPU back down to 30% – 40% and everyone is happy.  So I start wondering.  If I can decrease the number of threads that run at one time, maybe I can decrease the blocking and resolve this issue.  I read a post of someone who changed max degrees of parallelism from 0 to 1 and his problem went away.  That made perfect since to me: every process can have one CPU at a time.  But that also bothered me.  Microsoft put parallelism into the product because it helps with performance.  Turning it off can’t possibly make things faster.  There has to be a balance.  Increase the threshold, lower the number of processors there has to be a sweat spot.  But how to measure it.  That is my problem.  HMMM……  There must be a objective way to measure the application speed and make changes, watch the CPU, and the application speed.

Speaking of SSMS 2008.  I really like the activity monitor.  This seems to really give a lot more information.  The data file I/O section sorted by response time is very interesting to watch and I’m sure I will be using it in discussions with my SAN team in the future.  I just wish I could figure out how to freeze the processes section.  The refresh gets a little annoying to me.  But all in all I really like it.

Well I’ll spend some more time on this tomorrow and maybe have some solutions to my problem with this application.  Wish me luck.