Building a new PC

I mentioned earlier that I have hilariously outdated hardware at home and I finally decided to do something about that this month.

Considering that I tend to keep my computers a very, very long time I decided it was worth over-researching my plan.  I started by reading Glenn Berry’s SQL 2014 Workstation blog post about 500 times.  I also looked at lots of things on Newegg and Micro Center’s websites and eventually decided that even with sales tax Micro Center was the way to go for everything because of all the bundle pricing.

There’s a few things to update in the Glenn Berry post.  This post was largely recycled from an earlier post, so at this point you don’t want a Z87 chipset motherboard anymore.  In fact, those motherboards are what are  on the clearance racks at Micro Center right now.  You also probably don’t want the Cooler Master HAF 912 case because it doesn’t have USB 3.0 ports on the front.

This machine replaced a Dell Dimension workstation with a Pentium D 820 processor I bought in March 2007.  It’s definitely one of those, “Why didn’t I do this sooner?” moments.  I bought the cheapest thing I could find, and it came with Vista, but could not actually run it. (IMHO, allowing PC manufacturers to place Vista stickers on machines with 512 MB of RAM was a big part of the Vista failure.  But at the time there was concern that price sensitive consumers would not make the upgrade if the machines were too expensive.)  I downgraded to XP and later went to Windows 7 64-bit after maxing the RAM at 4 GB.

Here are the major components and their costs:

I used an existing Samsung EVO SSD that I was not really getting the full impact of from the Pentium D.  For the non-system files I’m using a 7200 RPM rotational disk.

This leaves 2 slots for a future update to 32 GB of RAM, and plenty of internal slots for adding future storage.  On the Windows Experience Index the system gets full scores on all facets except graphics because I am relying on the on-chip GPU.

My ancient MacPro remains, but recently got an SSD for the system disk.  A 2-socket dual-core Xeon system is still pretty decent in 2014.

SQL Saturday Philadelphia

Last weekend was SQL Saturday in Philadelphia, which marked my 3rd SQL Saturday, and even though people who stumble across this blog likely know what SQL Saturdays are all about, I thought I would take a few minutes to talk about them at a very basic level.

For years I thought SQL Saturday would be the kind of fluffy marketing stuff that you expect of free lunchtime webinars or a vendor launch event.  That is not the case at all.  I also thought that they would only be local speakers, which is totally not true.  SQL Saturday speakers exist all along the spectrum from local people speaking to a PASS audience for the first time to Microsoft Certified Masters who traveled hundreds or thousands of miles just to speak.  Yes, some are Microsoft employees, but most are not.  Marketing is strictly frowned upon, except for lunchtime sessions and the sponsor hall.

Chris Bell put together a quick video of SQL Saturday Philadelphia:

SQL Saturdays may be held at a Microsoft office, but they are not purely a Microsoft pep rally. The attendees are among the most motivated professionals out there.  These are people who are willing to give up most of a Saturday to come learn new things or meet new people in their field.  Speakers are for the most part very honest about what Microsoft is doing well and what they aren’t.

For me,  SQL Saturdays within a few hours from home will add up to a PASS Summit.  By the fall I may be able to attend Summit, but I won’t really know til we are much closer.  SQL Saturdays are a way to get the same amount of training and networking in without doing it all at once with a long flight to and from Seattle bookending it.

If you’ve been on the fence about attending a local SQL Saturday, let me push you right over the fence and yell in your ear, “Just go already!”


Brent Ozar Unlimited In-Person Training

Last week I had the opportunity to travel to Chicago for Brent Ozar Unlimited’s in-person training on SQL Server Performance for Developers.

I’ve been following Brent’s firm since their first few months and annoying him on Twitter just as long.  They firmly believe that to win business you have to give stuff away, and they have put together some great stuff in the last 3 years.

Advice on the internet is usually fraught with peril, but an association with a “big name expert” will make people accept it, especially when it’s absolute (never do X; always do Y) or quantifiable (A should always be < Z) (even though she wrote that blog post in 2008). This way lies madness, and Brent would be the first person to tell you to test, test, test in non-production environments and only implement if you’ve proven with production-sized data that your change is helping to fix a big issue that you actually have.

One of the best things about the class is that it seemed to attract a very advanced level of student. There were lots of new and interesting people to chat with and I was able to make some great new contacts.  I might have thought before that I was too advanced for this class, but it soon became apparent that my fellow students were not only SQL Saturday attendees, but speakers.

Not actually a 3 day course on 2048 clones
Not actually a 3 day course on 2048 clones

There are many breaks and pre-9am Q&A session where you can ask the gang questions on your specific burning needs.  It would really help if you could run the whole sp_Blitz suite a day or two before the training and bring a dump of that with you.

I have been working with SQL Server since 1998, and I still learned a ton of new stuff. My notebook is brimming with all kinds of new information and I setup a new SQL 2014 RTM environment so that I can go through all the demos on my own at home. You get all the slides in a very thick workbook with plenty of room for notes,  and downloadable scripts.

I also got to really know the whole team as people, and that is only going to make their webinars and video trainings more rewarding for me.  I learned that Jes runs so much so that she can still eat lots of cheese.  Jeremiah will always say something inappropriate first, then give you very good information.  I swear Kendra’s hair got more colorful as the week went on.  Doug is already showing that he loves processing high def video streams.

Assuming that the next Chicago training is at Catalyst Ranch, it’s also a great venue to chillax for a few days and learn some new things.  The food was great, the WiFi actually worked, and being in a sunny downtown location beats a hotel meeting room any day.  For me, this was a much better use of my training budget than a conference. What I learned was practical and is going to be immediately helpful at work.

Catalyst Ranch Polka Room
Catalyst Ranch Polka Room

SQL Saturday Richmond, Backwards

I am going to write a little bit about the sessions I went to at SQL Saturday,  and I am going to do them in reverse order.   So the last session I went to was Ayman El-Ghazali’s beginner session on SQL Server internals.

Without a doubt, the highlight of the session was when Ayman compared spinning magnetic spindles to flogging a donkey.  If you watch the video below, when you delete a folder, it is indeed like flogging a donkey.  It’s an expensive and ungraceful operation that helps you start to see why SSDs are such an improvement.

This leads us to an even funnier YouTube video (circa 2008) Ayman mentioned showing another big downside to spinning magnetic storage.  If you shout at it, it slows down:

One of Ayman’s storage metaphors really hit home for me, because in the DC metro area we sometimes have to pay a nickel for shopping bags.  While this a tiny amount of money, it makes us pay much closer to attention to which stores have quality bags.  For the same nickel, we can get a strong, large bag at the hardware store or a tiny, thin bag from the supermarket.  At the hardware store we get to carry more stuff but we’ve spent the same nickel.  Ayman likened this to disk allocation units (AU).  If you set your AU to 64k, you can get a full extent read for the same “nickel” if you change the default of 4k.

I really liked Ayman’s diagrams and found that was something to pick up even in a beginner session at the end of the day and I’m glad I stuck around til the end to see it.  I only wish the cookies had been real.

My only suggestion to the presenter would be to buy a clicker because running back to the laptop to advance the slides can really interrupt the flow.  I had a clicker at a previous job and found it really helped when giving presentations.

Is ABS() SARGable?

This morning I was having some issues with a query using ABS() and I went straight to the lazyweb (Twitter) to ask Brent Ozar if ABS() was SARGable.   Rather than tell me, he suggested I run some experiments and then blog about it.  This required me to restart WordPress on this domain where it hasn’t run properly in 6 or 7 years.

I recently also created a new home test environment in VMware Fusion on my Mac Pro which is a certified antique, but will soon have 15 GB of RAM.  The first thing I put into SQL 2012 was a copy of the most recent dump of because that is way more interesting than AdventureWorks.  I created a simple index on the Users table to demonstrate below how ABS() will prevent the Index Seek for a User with a Reputation of 10,000.  Both Actual Execution plans are shown below:

Screen Shot 2014-03-20 at 9.30.19 PM


So, in summary, no ABS() is not SARGable, and there is plenty more to learn about SARGability from Rob Farley.  This video from SQLBits is especially nice.