In my current role about a third of the databases I look after are on SQL Server. The University as a whole has more SQL Server than Oracle, but most of the key systems are on Oracle and that is what my department work on.
I’ve been working with SQL Server since version 7.x, but it has never been a primary focus of mine. As a result, I am an “adequate” SQL Server DBA, but if I ever come to you asking for a job as a SQL Server DBA, press that button on your desk that opens the trap-door under my feet. 🙂
A couple of days ago I had a message from a colleague in another team, asking me if I could take a look at a performance problem on a SQL Server database that sits under SCCM. Cue panic, cold sweats, paranoia, fear of being “found out” etc. 🙂 A few seconds later I gathered myself and thought, what would I do if this were an Oracle database? With that safety blanket firmly in hand, I plugged through the process like I would on Oracle (turn it off and on again 🙂 ) and a bit later everything was OK. It was a little bit random, but we got there.
Yesterday I got another call about the same system. A little better prepared this time, we walked through some stuff and I think we have a better understanding of the issues now. It’s not so much a SQL Server problem as a SCCM problem, in my opinion that is. 🙂
Anyway, during the process I was repeatedly asking Uncle-Google about stuff and regularly came across information by Thomas LaRock, either directly or by people quoting or pointing to his material. I was already aware of Thomas after being introduced to him online a long time ago by Jeff Smith, when he pointed out that Thomas became a Microsoft MVP on April 1st, the same day of the year I became an Oracle ACE. 🙂
So this overly long and random post is basically saying, “Thanks Thomas for putting out quality material on the interwebs!”, and if you are a SQL Server DBA, check out his blog! 🙂
Cheers
Tim…
Lol, so the solution to Oracle running slow is restart it. That’s awesome. Curious what turned out to be the issue you were having?
John: That was just a self-deprecating joke that references “The IT Crowd”, who answer the phone with, “Have you tried turning it off and on again?”. 🙂 Turning Oracle off and on usually kills performance as all the caches are cold when it comes back…
Remember, this is the first time I have ever seen a SCCM installation, so I could be talking crap, but what seems to be happening is this. If we turn everything off except the DB server, the DB is not surprisingly fine. Once we turn on the primary site (PS) server, the CPU on the DB server goes nuts. There is no blocking, but the DB is really busy. I’m told the PS server is just doing Collection Evaluation. If this was all we had running, that’s fine. When we turn on some of the other servers, can’t remember their names now, they make requests to the DB, which are almost all blocked by the PS server sessions. As time goes on, the number of blocked sessions increases and the server eventually takes a nosedive.
My question to the SCCM our guys was, is this level of collection evaluation normal? Can it be reduced (scope or frequency or both)? If not, then we certainly need to reconsider the sizing of the box, to try and make the collection evaluations quicker, to reduce the likelihood of them blocking the other sessions.
It’s kind-of tricky for me as I don’t know anything about SCCM, so I can see the problem, but I can’t make much in the way of suggestions to fix it. Since it’s a 3rd party product, we don’t have amazing knowledge of this in-house. I guess with this information in hand our folks can call their SCCM support contact, report our findings and ask for help… 🙂
Cheers
Tim…