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.
Posts Tagged ‘Performance’
Update: Degrees of Parallelism
Friday, February 13th, 2009
Starting up database ‘SomeDbName’
Wednesday, February 4th, 2009I love it when I learn something within a half hour of getting to work. I had an unrelated alert on a vendor supplied database server. As I was investigating that problem I came across two things. One was funny, one I actually learned something. First the funny: they had named the sysadmin account ‘God’. Wasn’t that from the terrifically awesome, how can I live without seeing that one, movie ‘Hackers?’ But now onto what I learned. I looked at the SQL Server Log and I had hundreds of entries that said “Starting up database ‘DbName’” These were appearing several time a second. I’m thinking. This has got to be a waste of precious CPU. I know the server site at 10% utilized even when the two people we bought it for are using it, but that’s beside the point. Its CPU darn it. Well, I have to admit that message meant nothing to me. So I asked my favorite co-worker, Mr. Google, what was up. He told me the server is set to Auto Close. I looked at the options and he, of course, was right. Auto shrink was enabled also. So, I fixed those two issues and now the database server is chugging along at 9.98% utilized. It’s only 7:44 am and I believe my work is done here. Time to twitter for the next few hours till quitting time. Crap, it only works that way in movies. Another database needs my care.
Degrees of Parallelism
Tuesday, February 3rd, 2009If 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.
