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.