SQL Server Performance, pt I

If your in Amsterdam, you have to look at windows.

Ok, so my career brought me back to Windows and Microsoft products. Bummer. One of my tasks recently required me to go over SQL Server 2005 Performance guidelines and Troubleshooting techniques and attending a lab of some sort where this performance guru shared some really nice tips.

It was actually very pleasant, and I want to share with you all some of the things I got from the training and if MS SQL Server is your thing, maybe you'll get some value out if this post.

So, first things first:
1 - tempdb: You can read more about it in this Technet article. The default size for this very important db is only 8 MB. Change it according to the few basic rules outlined in the link above.

2- Gathering generic performance statistics. MSSQL Tips gives a very nice overview on how and what kinds of statistics you can gather in MS SQL Server. I find this very useful to start a performance evaluation. Better yet, the Performance Dashboard add-on that can be downloaded here. It's too big and complex for this post, but if you need some pointers, ask me. Another really useful set of tools, jointly referred to as RML Utilities for SQL Server can be found here. Do some read-up on them and enjoy.

3 - I/O. This is one one the key elements in performance for just about any application. This might be a good time to introduce you to the SQL Server Best Practices
. The whitepaper on Predeployment I/O Best Practices is a must read if you don't know the basics on how to measure and improve this performance parameter.

4 - Isolation Levels. Be sure to read the Books Online on the 6 supported isolation levels in SQL Server 2005. Read uncommitted (or nolock), read committed, repeatable read, serializable, read committed snapshot and snapshot. Understand how these different levels affect concurrency.

5 - Stored Procedures. In most situations, SP's will provide better performance over queries any day. The reason for this is two-fold: The Data and Procedure caches, aka Memory Hogs R' us. A nice article I found on this can be read here.

There is so much more to say about this topic that I might consider transforming this in a series of posts. It would be just too much for a single post and there are several areas that I find would be really interesting to go over in depth, provided that I have the time to actually write something that is. :-)

Managing MS SQL Server is much more than pointing and clicking and therefore it is very easy to get it wrong. On the other hand, this is really valuable knowledge if you're considering a career as a performance engineer / consultant. Or even if you're just writing a Web App (RoR springs to mind) that you just can't figure out how to go about squeezing out every last bit of performance of, really knowing what you can do with the database layer is a definite plus.

Forget OLPC. OSPC is more like it!

Need help explaining your children why there is a server in the house?



This will help.

Wanna buy Microsoft for 1 Million Dollars?

Yes you can! And you know you want to!

But be sure to read this first.

30 (+10) reasons to switch

This is a post i thought i'd never write. It's not that i don't find the discussion fascinating, but that in my experience people (read: non geeks) seldomly need (or want) to change their OS.

Better yet, most people don't use an OS, they use a set of applications. But if the same applications are either cross platform or platform agnostic, then it might make sense to discuss the possibility of switching.

I'm doing it now because i saw a post earlier this week about reasons to change to Linux, and the whole thing got me going. I really don't think my opinion will make you change your mind, but it _does_ come from a someone who is a Windows specialist, a Mac fan-boy and a Linux geek (all me, at different times).

Oh, and this is my first post from TextMate!

10 reasons to use Windows
- It comes pre-installed with ~80% of the PCs sold worldwide;
- You don't know what a driver is (except if its behind a wheel) and couldn't care less;
- Most software is written for Windows, all new technology (Blue Ray; HDVD; DRM) is made for Windows;
- You can go to chat rooms, use file sharing software, visit google and read from Windows;
- You don't know how exciting it is for a script kiddie to see what files you have on your "My Documents" folder;
- You mostly play games, but don't have a games console;
- You don't know that there _are_ other OSs that you could use;
- You use your computer as an electric typewriter with a monitor. What is an OS anyway?;
- You can't afford a Mac, and Linux doesn't offer you the trust of a big corporation behind it;
- You are a VB, XBase, .Net and\ or a MSSQL pro;


10 reasons to use Linux or other *NIX OSs
- You are curious by nature;
- You know a little bit about computers, but want to know more;
- You are a programmer, a security or systems expert, or any other sort of Hacker (or want to be one);
- You can't afford a Mac, and Windows isn't configurable enough;
- You laugh at something like "rm -rf /usr/bin/laden" or you understand the sentence "thank @Divinity I had that shell script to change my MOTD with events from calendar.* or I would have missed LISP's birthday last wednesday";
- Somebody shown you what Beryl, XGL or E17 can do. And now everything else fades in comparison;
- You saw a video on youtube of a guy playing around on a touch screen and flipping windows with his thumbs;
- You believe in Open Source ideals;
- You are a security \ privacy junkie;
- You have DVDs from more that one region or you can't stand the idea behind DRM;


10 reasons to use Mac OSX
- You are a creative professional. (And i don't mean those of you who can write a 20 line telnet replacement);
- You are easily influenced by Apple's halo effect or Steve Jobs reality distortion field;
- You want a computer that just works. And looks good enough to turn heads;
- OSX is a UNIX on visual ecstasy (in-joke, i'll explain another day);
- You don't mind using a closed source OS that is based on Open Source and whose OSI model layer 7 (again, in-joke) can be replaced with FOSS variants;
- You would never replace a CPU, motherboard or graphics card on a laptop by yourself;
- It comes pre-installed with ~10% of the PCs sold worldwide, including your own;
- You are really good at Windows (an expert of sorts) but your antivirus spouts messages about Viruses and other threats that try to creep into your PC and you wonder how it's like on the other side;
- No matter how much you tweak Linux (or how much software you put on top of XP for that matter), it still doesn't feel as snappy or look like OSX;
- Your favorite band is putting out GarageBand files of their new album (I'm still excited about that, sorry);


10 reasons not to use a Computer
- you don't work with computers;
- you own a DVD player, a good Stereo, a Cell Phone and Cable TV;
- Your prefer to listen to CDs instead of ripping them;
- No one ever picked up a girl by formatting her hard drive;
- You don't know how people spend so much time writing and reading without touching a single sheet of paper;
- You where the one behind this. You will now pursue a new career;
- You where the one behind this. You will now pursue a new career;
- You like practicing sports that are incompatible with a flat ass;
- You don't own a portable media player apart from an old Walkman;
- Your fingers are too thick for such small keys;

On the subject of goodwill

This is not good news for Microsoft. Their latest OS hasn't even begun to make its way into the corporate desktop and already the claims on unbreakable security are a thing of the past.

Or were they ever anything but marketing?

The question I most often hear regarding the transition for the new OS is "Why bother?" To answer this, I made a funny little mental exercise.

What would I do if I was in charge at Microsoft (or at least if they listened to me).

Well, to start with, I would take the number of available versions of Vista down from 7(5?) to only 3.

Vista Corporate Edition (VCE) - Drop the show off (Aero will be turned off by policies on most cases anyway);
Better security and networking. This would eliminate the need for mandatory hardware upgrades in the enterprise and would thus lead to wider embracing by CTOs and CFOs alike.

Vista Media Hub (VMH) - A version with some great visual apparatus, geared towards the home user with basic needs beyond media and internet access.

Aero would be a must, so maybe this could lead to a more reliable hardware certification \ standardization process (much needed IMHO) were Vista Ready meant "Ready to run full specs with full performance".

Vista Gamer (VG) - Recognizing that a vast majority of PC users mostly use their OSs (at home that is) to load and play games, Microsoft should make a version of their newest SO much to the liking of the one that powers their own Xbox 360.(Not necessarily compatible, though that would be a blast).

Networking and Security services striped to a minimum, very compact and hu.. lightweight. Take advantage of the relationships with Hardware Vendors and maximize performance.

Next if my list would be taking better advantage of what comes out of their own labs and projects (label it as beta, if you will) and deliver a companion DVD full of goodies like Photostory (does it still exist?) Expression (even a lite \ demo version) and Photosynth (for VMH).

Also a good option, would be throwing VirtualPC, SQL2005 and Visual Studio Express into a Companion DVD for VCE and finally XNA Game Studio for the Gamer version.

As for the Office suit, i wouldn't touch it (with a long stick).

Eliminate the fabricated need for upgrades (personally I’ll stick with Office XP for as long as I can, or must) and make it compatible with open-document standards for publicity if nothing else.

Finally on the server front. Well this is going to be a biggie... So I'll put it more as a wish list than anything else.

1- Native Support for "foreign" file systems.
2- Show us what WinFS can do. If it turns out not to be all that much adopt ZFS (ouch).
3- U-N-I-X C-O-M-P-A-T-I-B-I-L-I-T-Y L-A-Y-E-R (or SFU done right, or cygwin in a companion CD).
4- Gui-less version. With option for graphic administration from remote desktop or even trough a web browser. This would be a great selling point for Application, Web, DB and File servers. And for low-end servers too.

Ok, so most MSCEs would be left to dry without point and click. But the good ones would adapt, and the best ones won't tell the difference (if it can't be scripted, it can't be done, right?)

Everybody loves a self explanatory error message....

...and this ain't a very good example of one, is it?

Search and Zip (revised and improved)

This is a little vbs script I wrote the other day. It's purpose is to find all log files in a given directory, create a zip archive (using 7-Zip command line interface) of each individual file (created before today, so I don't zip anything currently in use) and delete the original file.

I hope somebody else finds it useful... Although I know that someone can probably out best me with a Perl one-liner..

Here goes!

'Function: Compress and delete log files
'Description: Finds all files with the extension ".log"
'from inside the folder(s) passed as arguments, create a ".zip"
'archive with the same name and delete the original file
'Example: cscript compress.vbs d:\logs "d:\big app logs"

strComputer = "."
str7z = "C:\7-Zip\7z.exe"
i=0

If WScript.Arguments.Count = 0 then
WScript.Echo "Directory path missing. Usage: cscript zippit.vbs dir1 ""dir 2"" (etc)"
Else
'For each folder passed as an argument
For Each strArgument in WScript.Arguments
strDir = WScript.Arguments.Item(i)
Set oShell = WScript.CreateObject("WScript.Shell")
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
'fiel collection inside strDir folder
Set colFiles = objWMIService.ExecQuery _
("ASSOCIATORS OF {Win32_Directory.Name='"& strDir &"'} Where " _
& "ResultClass = CIM_DataFile")
'Process monitor; will report upon closed \ finished processes
Set colMonitoredProcesses = objWMIService. _
ExecNotificationQuery("select * from __instancedeletionevent " _
& "within 1 where TargetInstance isa 'Win32_Process'")

If colFiles.Count >0 Then

'for each file
For Each objFile In colFiles
'only logs mater.any other file is skiped

If WMIDateStringToDate(objFile.CreationDate) > now() And objFile.Extension = "txt" Then
'compression routine
oShell.Run ""& str7z &" a -tzip """& strDir &"""\"& objFile.FileName &".zip "_
& " """& strDir &"""\"& objFile.FileName &".txt"
WScript.Echo "Created the file "& strDir &"\"& objFile.FileName &".zip "

'log deletion routine
z = 0
'wainting Loop (sleep until zip is done)
Do While z = 0
Set objLatestProcess = colMonitoredProcesses.NextEvent
If objLatestProcess.TargetInstance.Name = "7z.exe" Then
'7z process ended; i can leave the waiting loop and delete the original file
objFile.Delete
WScript.Echo "Deleted the file "& strDir &"\"& objFile.FileName &"."& objFile.Extension &" "
z = 1
Exit Do
End If
Loop

End If
Next
End if

i = i+1
Next
End If

'change date format returned by wmi_script
Function WMIDateStringToDate(dtmDate)
WMIDateStringToDate = CDate(Mid(dtmDate, 5, 2) & "/" & _
Mid(dtmDate, 7, 2) & "/" & Left(dtmDate, 4) _
& " " & Mid (dtmDate, 9, 2) & ":" & _
Mid(dtmDate, 11, 2) & ":" & Mid(dtmDate, _
13, 2))
End Function

WScript.Quit

'as with everything else on this blog.... Creative Commons apply to this code.

my version of this week in tech

Like most geeks I spend much of my off (work) time trying out software solutions just for the sake of knowing how it works and evaluating my options.

So this week I got interested in streaming again. Apple as a good offer. You should check it out. Not only is Darwin Streaming Server free, it is also open source (well not _exactly_ open source, but you can get the source), and they offer you pre-compiled binaries for Linux and Windows.

ESR, one of my personal heroes seems to have fallen out of love for Red Hat and is currently trying to land a job with Ubuntu. Mark Shuttleworth, it has been rumored, was seen buying a one way ticket to the next space shuttle for tourists (tm). Probably no relation what so ever between these events.

Steve Balmer said that Vista's poor sales are a consequence of pirating, giving linux lovers and CTOs all around the world another reason to suspect Microsoft’s stance on security. I mean, if they can't protect their own software...

Of course that since that they're being so generous with their licensing this time, I'm sure the adoption rate will not reflect the sales figures.

Mono is now supporting native Visual Basic code in their new release.

Sarcasm mode on:
Well, Mono belongs to Novell, Novell seems to be 0wn3d by Microsoft so I'm not surprised.
Sarcasm mode off:
Really, this is great news for me and, I'm sure, to some other ex-VB addicts.

Finally, it seems that Terrasoft (Yelow Dog Linux, anyone?) is giving me another reason to buy a PS3.

Vista pitch

I'm still trying to digest a discussion I had last friday at work.

I was discussing with some friends the real cost of upgrading (if possible) to Vista, and a Microsoft employee who apparently had nothing better to do but listening in on our conversation decided to offer himself to get us a discount on a Vista purchase.

I just have to tell you is sales pitch and then you tell me if I should consider his offer.

"Well, I would never buy it myself if I didn't have such a discount..."

Ok, to be honest I already made my mind to not buy it anytime soon, so i'll just tell you my answer straight up.

"Wow, then i can save my money to get a new graphics card and buy more RAM! What a nice offer. If only I really needed a new OS to _do_ the same things i do now."

Ok, so now I'm labeled as anti-Microsoft at work, which just isn't true. I'm not _anti_ anything.

I take great pride on being a Microsoft technologies specialist, and I have been using all of Microsoft's OSs since 3.11. With some notable exceptions.

oh yeah?

Well yes, i have to say something about the omnipresent iPhone.

To start with, i don't think it's all that great.

I mean, i was reading the macworld live feed when it was announced and my heart wouldn't stop pounding with the excitement as the feature set was announced... and that was why i chose not to blog about it until now.

I knew from previous experience that wheenever apple releases a new product, i get terribly excited, but as soon as i am able to make a clear-headed analysis i come to the conclusion that it ai't such a big thing at all.

For now i'll just say that it's a great new iPod. And it makes phone calls. It is not a great new device that will revolucionize the way people use the phone. Or the PDA.

Maybe when it gets to europe, Apple will realize that they will need something more to compete in the mobile phone market. There are already similar offering from other manufacturers.

And on the Blue Corner, we've had some recent announcements that didn't mean a thing to me.

Vista ? Don't think ill even touch it anytime soon (unless i get one of these).

Windows Home Server ? What?? Pay to have another brick at home?? Does it have the same power saving features as other Windows?

And what is it with that backup utility?

Seriously now. The idea isn't new but it is great. Have you heard of FreeNAS ? All you need is old cheap hardware and some googling!

A Linux user’s experience with a Macbook

Well if many people found it strange that I, being an VB \ MS SQL professional programmer and MCSA was a devoted Linux hobbyist and advocate, then what would they say if they found that I was becoming an Apple fanboy now?

The word “Triple-Traitor” comes to mind, although I don’t think that’s even a word…

I bought a black Macbook two months ago because I just couldn’t resist its charm and because now, with the Intel processors, I actually had one less excuse not to use the best looking hardware in the market. I knew that thanks to Apple’s Boot Camp I could use Win XP without virtualization hassles if I needed (and I do, really).

Obviously my first reaction to actually owning a Mac was akin to a child left alone in a chocolate factory. Apple really invests in the “buying experience”. It’s hard to explain, but I could swear that the casing’s interior and the laptop itself smell like apples… and I didn’t even noticed that it didn’t include a mouse or any other “gifts”. It’s just the laptop, the installation DVDs, Apple Remote, a power supply and that’s it.

Once I got past the drooling, I started the rather simple and straightforward process of installing Windows XP with Boot Camp. The instructions on how to do it are all over the net, so I won’t go about it here.

One thing I noticed immediately when first logging on to XP, is that it seems to respond slowly, there’s a feeling of hiccups just in moving the mouse pointer that I assume are due to “only” having 512 MB of RAM and using shared video memory, since I’ve gorked a bit for similar user experience and couldn’t find any such complaints.

The other major catch is the keyboard. Not having a delete key, is easy to live with, but not being able to easily write symbols like “@ “and “€ “? Now THAT’S hard to explain.

Next came the “usual” process of fitting and tweaking XP to my personal taste and needs. That meant about 1 hour of modifying default settings, from visual effects to the size and functionalities of command.com. I’ve grown so accustomed to changing default settings that I almost don’t even notice just how many times I have to open regedit, reboot, or go into administrator mode to get everything just perfect. If anyone is interested in finding out what the process was, just ask.

Finally I got to spend some time on Mac OSX. I’d already done all the updates, so I was left to change the default font size, setting up a non-administrator account, turning dock hiding and zoom on, readjust the app selection on the dock to fit my user profile and change the default wallpaper. That was it, really. I could automagically connect to my Linux box, had access to my ISP and a neighbour’s Wireless network that I use just to check if wifi works Po) . And using Microsoft Remote Desktop Client from Mactopia, I even get easy access to my Windows 2003 DNS and DB Server.

As to bundled apps, Mac OSX is, all things considered, much like Ubuntu, or SuSE Linux. It comes with a good selection that allows you to be immediately “in business” but the best tools come from the community.
The iLife suit is sweet, very easy to use even if somewhat limited for advanced user other than myself (I’ve heard), FrontRow is great, and there is also a SQL DBMS in it too. But what really got me by surprise was Xcode, Automator, Script Editor and Ruby being installed by default. I am totally addicted! Honest!

All in all I’m very happy with my Macbook and I would defiantly recommend anyone, even sysadmins with a little patience and some time to spare that need or are considering a new laptop to buy this one.