Author: way0utwest

Recently I was at a UK user group meeting and Simon Sabin of SQL Skills presented a short “SQL Nugget” on UDFs and some of the issues with performance. He then showed a better way using a TVF and mentioned that in many cases the CLR can perform on par with system functions for simple code.

A person in the audience mentioned that it wasn’t that simple. Many companies might have concerns over deploying CLR code in assemblies to their SQL Servers. He cited that there was concern about actually having someone write “code” that runs inside SQL Server.

I’m concerned about the CLR, but not necessarily from a stability standpoint. As much as I think there could be performance issues, I don’t think that the code written by a developer or DBA and installed in SQL Server means that the core engine is necessarily less stable. Sure there may be issues with their code, but are they more serious than the issues of crappy client code? Or poorly written queries?

After all, we deploy code all the time on our servers, and it might be just as poorly written. I’ve seen sp_prepareXML cause issues, and I’ve seen XPs, provided by Microsoft, lower the stability of servers.

So is it just CLR being a black box that worries people? Do they have CLR concerns about .NET assemblies being deployed on IIS?

And if they are, then shouldn’t that mean that better training, code reviews,and extensive testing are needed?

The CLR can be a great choice in some cases. It’s a specialized tool, one worth looking at, but not necessarily the one for all situations. Just be aware that you may have a bit of a battle if you choose to use it on your SQL Servers.

If you have deployed the CLR, let me know how it went and what challenges you faced.

I’m not sure what happened here. I walked up to my desktop, wiggled the mouse, and nothing happened. In the true definition of insanity, I then did it a few more times and achieved the same result. Hitting the keyboard didn’t help and it appeared frozen.

Weird, but no cause for concern immediately. I clicked the power button a couple times, which tends to shut things and and when that didn’t work, held it in for a power down. When the computer restarted, it came back up as though it had been in hibernate mode. I had a full desktop, which surprised me. However I was more concerned when it was operating very, very slow.

Everything seemed to take 30-40seconds or more, even opening a mail message. Since I have a quad core machine, x64, 8GB RAM, that’s unusual. Things usually fly around the screen. Then I happened to notice a reminder pop up from Outlook that said it was 2,000-some days overdue.

Huh? Like an episode of the twilight zone, I opened the time applet to see this:

I snapped a picture, changed the time back (which is interesting) and then rebooted. Things sped up and everything was back to normal, but I was a little concerned. How did my desktop move 29 years into the future? This picture still has the 2039 timestamp, so it’s always at the top of my picture list.

Very weird. Not something I’d expect in Windows 7, but I’ll keep an eye on it. This machine has been rock solid for over six months, so I’m not looking forward to any more surprises like this.

I saw Joe Webb’s post, and then Gethryn Ellis’ post as well on when SQL Server last restarted. It’s something that can be handy when troubleshooting and trying to figure out what’s going on with SQL Server.

I’ve also used it to track potential issues as well, like when others might be changing things on the server, like patching, altering configuration (memory) or something else.

So how do you do it? Here’s what I’ve done. I’ve used before. I use this function in SQL Server to read the error log in T-SQL.

exec xp_readerrorlog

That’s good since the first timestamp here is the time that SQL Server started. It’s before the databases are even accessible. Now I need to grab that one date. I’ve wrapped this in a small bit of code.

DECLARE  @logger TABLE
(
    dt datetime,
    Process varchar(50),
    txt varchar(MAX)
)
INSERT @logger exec xp_readerrorlog
SELECT TOP 1 dt FROM @logger

I can store this value in a table, which is what I usually do, and then I can watch it over time, set an alert on it in a daily report, whatever.

Not perhaps as elegant as some of the other solutions, but one that’s worked for me.

Been thinking a lot about leadership lately, especially the lack of it. Hearing stories from friends about what they deal with, reading, it’s kind of funny. This cartoon seemed to fit well.