Archive for February, 2009

Find the Windows Server Name

Thursday, February 19th, 2009

I was working with SQL2000 on a windows cluster.  I needed to write the results of a query to a test file on the local drive.  I was using @@servername to get the server name to write the file.  Unfortunately that appends the instance name and gave me a problem.  So I went looking for how to remove the instance name and I found a pretty neat trick.

I ran the command: select @@servername and received ‘server\instance’

I ran the command: select @@servicename and received ‘instance’

So I thought there must be a way to subtract @@servicename from @@server name, then I tried this:

select replace(@@servername,’\’ + @@servicename,”)

My result was: ‘server’

That I can work with.

SuperStart

Saturday, February 14th, 2009

Today was the end of an excellent conference for 4th, 5th, & 6th graders.  SuperStart is a production of CIY.  If you have a child in that age group and you have the opportunity to go, it is so worth your time.   If your church is hosting the conference it is a total blast to volunteer.  Our day started at 6:30 am at Meijer to pick up 66 dozen donuts, yes, that is 792 donuts to pump into kids to get the day started.   Believe it or not, all the donuts actually made it to the church.  There were over 1300 kids and leaders preregistered for the conference.   Then we helped get the breakfast line setup, then it was off to fun.  My wife and I ran the “arcade” room.  That consisted of 7 Wiis and a Xbox360.  All of these were connected to projectors and projected up on the walls.    Our job was to let the kids have fun.  With that hardware, failure was not an option.  We rotated every 4 to 5 minutes to get everyone a chance to play.  There was also three inflatable obstacle courses.  I snuck in there and raced our middle school pastor through one of the courses.  He won.  But I’m sure I get a re-match in the future.  Then the kids went onto the program.  It had incredible praise and worship by Yancy and a three session program focusing on being friends all wrapped around a competition by two illusionists to see who was best.  The kids voted by means of a decibel meter.  112 db of screaming pre-teens was the winning score.  It was incredible.  The CIY team were awesome people, very organized, very competent, and connected instantly with the kids.  I would never spoil the outcome, but these illusionists were quite good.  I really enjoyed their competition.  One of the parts of the day is called plug and play.  During this time, our church rented out a local skating rink and we all went skating.  We had a great time and my kids are getting better at skating.  In conclusion,  SuperStart was excellent and my kids can’t wait for next year.  So if you have kids that age and have the opportunity, it is well worth the small fee (less than $50.00 for the conference)

Update: Degrees of Parallelism

Friday, February 13th, 2009

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.

Things You Know Now

Thursday, February 12th, 2009

Denny Cherry tagged me in a blog thread with a question asking “What do you wish you knew when you started?”.  So here you go.  As you know I am still relatively new to the SQL profession so I’m still figuring out what I don’t know.  I seem to find several every day, so I’m going to step back to my IT career in general.  I think some will continue to ring true.

Don’t be afraid of new technology.

For a long time I had a love/hate relationship with new technology.  I love getting into it, learning about it, and deploying it.  It’s quite a rush to be the first one to attain knowledge and being the go-to person for something cutting edge.  But I also had a fear of new technology.  “Will this technology and automation make me obsolete?”  A few years ago blades started to trickle into the datacenters.  This was one of those times.  I can take a blade out of a box and slide it into a chassis, boot from the SAN image, and a failed server is fixed.  Wow, they don’t need someone of my salary to do that.  No more messing with cables, world wide names, KVM swaps.  But I found just the opposite is true.  They needed someone of my salary level to get it working and stable.  Hey guess what, others can do the break/fix, I get to do more fun stuff.  Linux and virtualization at that time.  I’m wondering how the cloud will fit this model.

The manual is not the enemy.

I don’t know why I do it, I guess it’s a macho thing.  I would never read the manuals, in public.  But somehow the CD would slip into my backpack when we were having a problem and somehow magically I would have a brilliant idea over night that would solve the problem.  If I would have just grabbed the CD and gone to my desk I could have saved hours of “poke and hope” troubleshooting.  Now I have no problem saying “lets see if we are doing this the way the people who built it wants it done.”  I always hate it after struggling for hours to get something working, I look at the manual and in big black print I read something like this “before installing the flux capacitor make sure the knueter valve is set to 42% or the capacitor will not initially charge”  and I think “crap I cranked that puppy all the way up, no wonder it didn’t work.”

See the big picture

I used to just worry about my piece of the puzzle.  Give me a task and a due date and get the heck out of my way.  But I learned to take a step back and see how my piece fit into the whole puzzle and look to see how my knowledge and experience can assist the project as a whole.  What is hard for some may be easy for me, or I have a different approach that make it easier for everyone.  Ask questions respectively, which will lead into my next topic.

Watch my mouth

This one got me into real trouble.  I was in a meeting and a manager was describing a problem and had some pronoun problems.  “It” referred to 4 or 5 different things.  I couldn’t follow the description with a GPS and a big orange “you are here” arrow.  Not thinking about how it would sound, I asked if they could explain it again using a few less pronouns.  That was offensive to that manager, and to my manager who wasn’t even in the meeting to hear it.  I do believe there is a note in my personnel file about this one.  Asking better questions in private would have been a much better path.  That manager and I are back to being friends, but that experience is never out of my mind.

So now it’s my turn to pass this along.  Let see, who hasn’t been hit….  How about

Jimmy May (@aspiringgeek on Twitter)

Kevin Hill (@kevin3NF on Twitter)

Starting up database ‘SomeDbName’

Wednesday, February 4th, 2009

I 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, 2009

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.