Nov 262014
 
Gravy

I’m also grateful for my grandma’s homemade gravy

Back in the early days of this blog, I wrote about what I was thankful for in response to Jason Strate’s post asking about it. Now, 4 years later, I am hosting a redux, and your contributions are very welcome!

In looking back on the past year, I feel like I have an incredible amount of blessings in my life. While there are far too many to count, here are some that really stand out:

Michelle: Everyone deserves to have someone in their life who loves and supports them no matter what. To be able to marry that person is the icing on the cake. Michelle encourages me to follow my dreams, no matter how crazy they are. I couldn’t have asked for a better partner in crime, or in life.

My Health: When people (such as my grandmother) make comments like “at least you have your health” I used to snicker on the inside. Now, at my ripe old age, I’m starting to understand what that really means. Not only is being ill miserable, but it can get incredibly expensive, especially here in the U.S. I’m very glad that I and my family still have our health.

My Job and My Team: Sure I’ve griped in the past, but it’s still called “work” for a reason, no matter how pleasant it can be. All in all I have a wonderful work environment that’s full of amazing, intelligent people. On top of that, my employer sees the value in training and conferences, and supports the fact that I enjoy presenting and sharing my knowledge with others. I really feel like I’ve hit the job jackpot.

The SQL Community: The old saying goes that if you want to get better at something, surround yourself with people who are smarter than you. I like to make the addendum that those people who are smarter you should also be willing to share their knowledge. At this point, I’m describing the SQL community, and I’ve benefited tremendously in terms of knowledge, contacts, and friendships over the past few years. I’m afraid to think what my career would be like right now if not for all you wonderful people.

This Blog: I’m grateful for the fact that I can write and that others out there are willing to read it and provide feedback. I wasn’t really sure what I was doing 4 1/2 years and 149 posts ago (this one is my 150th!) but a huge thanks to all of you who have been along for the ride!

My Home: We have a nice house in a decent location, and on top of that we also have indoor plumbing and sewers. Lots of people in the World don’t have this. Sometimes the little things make a huge difference (especially when a middle-of-the-night trip to the bathroom doesn’t involve shoes or a flashlight.) What’s not to be grateful for here?

My Parents: I’m extremely fortunate to have grown up in a home with both of my parents, who gave up absolutely every iota of their pre-kid lives to make sure my brother and I never went without anything. They encouraged us, taught us, and gave us a kick in the pants (or two!) when necessary. I’d like to think we’ve both made them proud.

So that’s what I’m thankful for this year. If you’d like to contribute to my blog party, post something by Sunday 30 November and link back to the original post. (If you could post a link in the comments section that would be even better!) I’ll write a summary of all them and get them posted next week.

A very Happy Thanksgiving to all my readers who celebrate it!

 

 

Nov 172014
 

Freedom_From_WantIt’s almost Thanksgiving time here in the United States – that wonderful holiday where families and friends gather to share a meal and give thanks for all our many immaterial blessings. (And then for maximum irony, we head out the following day to battle each other in stores for heavily-discounted electronics while avoiding being trampled to death.)

One common tradition is to go around the table on Thanksgiving and have each person say what they’re thankful for. To gather all our #sqlfamily and friends around a dinner table would be an immense undertaking (and the buffet line would be super long). Instead, let’s do it blog-style. Jason Strate did this a few years ago with great results, and the community has changed a lot since then. I think it’s time for a reboot, and I’m happy to host.

So what are you thankful for? Your family? The wonderful people in your life? Your sweet new smartphone? The fact that you deep fried a turkey last year and didn’t burn your house down? There are no wrong answers. Whatever you’d like to share, write a blog post about it and link back to this one, much like T-SQL Tuesday. (And if you want to help me out even more, please leave a link to it in the comments!) Whatever I see by Sunday 30 November I’ll cover in a recap post.

 

Nov 132014
 

My marathon week of #SQLFamily is through, and that means it’s time to start writing about it! First up is Red Gate’s SQL in the City in Seattle. Having been an attendee when they came to Chicago back in 2012, I was especially honored to be able to return as a presenter!

SQL in the City was held at Marion Oliver McCaw Hall, a cavernous performing arts hall and the home of the Seattle Opera and Pacific Northwest Ballet. It’s located at Seattle Center, which conveniently happens to be the terminus of the Seattle Monorail. Even more conveniently, my hotel was only a block from the other terminus of the Seattle Monorail. Me, ride a train? Yes, please!

Built along with the Space Needle (and many other structures at Seattle Center) for the Century 21 Exposition (AKA the 1962 World’s Fair), both original monorail trainsets are still in service from the line’s opening in March of 1962. They very much look the part of what I’d consider a 52-year-old vision of the future to be. Even more impressively, they both have over 1 million miles on them – no small feat when the track is only a mile long! Only thing that could have made it cooler was if they warned you to please stand clear of the doors….

The trip there aside, the venue had all kinds of interesting spaces in varying sizes and shapes, and I’m sure I didn’t even see half of it! The massive tiered foyer provided an excellent space for networking and demonstration of Red Gate’s products, while the meeting rooms were the perfect fit for presentations.

The first session of the day was the keynote, “Ship Often, Ship Safe”, highlighting the capabilities of Red Gate’s Data Lifecycle Management (DLM) tools. After that, I headed to a talk entitled “You Did WHAT To My Transaction Log?” by Gail Shaw and Tony Davis. This was a hilarious look at ways you can make a problem so much worse by using *helpful tips* found on internet forums. Of course in the end they showed how to fix the problem the correct way. From there it was break time and some networking, and then off to Matt Slocum’s talk on 101 stupid things your colleagues do when setting up SQL Server.

After that it was time for lunch, and then I retreated to the ready room to make sure my slides and demos were ready for my presentation on Passive Security for Hostile Environments. I had a wonderful audience that asked a ton of questions both during and after. All the feedback I’ve seen to date has been positive, so I’ll go ahead and declare my session a success!

With my session all done, I headed over to Ed Leighton-Dick’s case study on SQL source control adoption. He delivered an excellent summary of his experiences with source control to a packed room – I ended up sitting on the floor!

Following that was happy hour, with drinks, snacks, and plenty of conversation. Every attendee also got their choice of a Red Gate book to take home.

All in all it was a wonderful day. A huge thanks to Red Gate for all their efforts in putting together such an amazing – and free – event that not only offers some excellent knowledge but also provides plenty of opportunity for interaction with presenters, Red Gate experts, and other attendees. I hope I can find myself at another one in the future!

 

Oct 272014
 

It’s my favorite time of year again – Autumn. It gets colder, the days get shorter, leaves change colors, and there’s that little annual event called PASS Summit. This year I have even more reasons to love it because I was able to work 2 more events into my trip: SQL Saturday Oregon and Red Gate’s SQL in the City in Seattle. It’s going to be an amazing week (which also means I’m going to be running around like crazy)! Here’s a partial list of things I’ll be up to and places I’ll be!

Friday 31 October

Flying to my 2nd favorite place on Earth: Portland, Oregon! (Nothing will ever top the Magic Kingdom for me.) I’ll be in town around lunch time, giving me the entire afternoon to get settled, see some sights, and visit the Columbia Store.  If time permits, I may also hit up the Oregon Rail Heritage Center (trains!) to check on SP 4449, the only Southern Pacific GS-4 in existence. Last time I was there, she was being overhauled.

Oh yeah, it’s also Halloween. I’ve never allowed myself to leave Portland without a trip to Voodoo Doughnut, but to not head there on Halloween would be especially sacrelicious. I can never decide which one to get, so I guess I’ll just have to buy a box…

Saturday 1 November

SQL Saturday 337 LogoSQL Saturday, Portland style! I’m very fortunate to once again be presenting! I had a wonderful time there two years ago and am looking forward to it again. I’ll be giving my talk entitled “VLDBs: Lessons Learned”, which is a great collection of tips and tricks I’ve acquired while spending the last 3.5 years working with databases 20TB and larger.

Sunday 2 November

TIME CHANGE – Yeah, not cool. I love “falling back” when daylight saving time ends, but this time it could seriously mess up my schedule if I forget about it. I’m taking Amtrak to Seattle (see? another train!) and forgetting to reset my clocks means I’ll be an hour late. Let’s just hope that doesn’t happen.

Monday 3 November

SQL in the CitySQL in the City! I had a wonderful time when Red Gate brought SQL in the City to Chicago 2 years ago, and I’m thrilled to be attending another one as a presenter! I’ll be giving my presentation on “Passive Security for Hostile Environments”, which has proven very popular. Registration is still open, so if you’re going to be in Seattle on Monday, sign up and join us!

Another extra-curricular event I never miss is the Networking Dinner, put on each year by Andy Warren (@sqlandy) and Steve Jones (@way0utwest). This year’s is at Buffalo Wild Wings from 6-9 pm. It’s a wonderful way to meet new people and see plenty of familiar faces as well.

Tuesday 4 November

Tuesday morning’s kind of up in the air for me, but I’m certainly not going to sleep in. This sounds like the perfect opportunity to get my fill of Top Pot Doughnuts! I’ll also be taking advantage of the discounted certification exam pricing available at summit. 50% off – it’s totally worth it!

Alas, there’s some official business to take care of – there are meetings for chapter leaders in the afternoon that I’ll be attending.

The official PASS Summit Welcome Reception is this evening as well, with great opportunities for networking!

Wednesday 5 November

Nothing gets the blood flowing like an early morning run (or walk)! Join us for an early morning #sqlrun / #sqlwalk, organized by Jes Borland (@grrl_geek).

I’m very fortunate to be presenting on Wednesday – I’ll be taking part in a panel debate on how to interview a DBA with Jen McCown (@JenniferMcCown), Sean McCown (@KenpoDBA), Adam Machanic (@AdamMachanic), and Michelle Ufford (@sqlfool) at 1:30pm in room 401. It’s going to be awesome – hope to see you there!

I haven’t completed my schedule of sessions I’ll be attending yet, but there are a few that I’ll be sure not to miss. One of them is Bob Ward’s (@bobwardms) annual half-day brain-melter session. This year’s is on SQL Server I/O. Should be awesome!

Wednesday night is the Exhibitor Reception – be sure to stop and talk with the exhibitors and sponsors that make this (and so many other) events possible!

The highlight of the evening will undoubtedly be #SQLKaraoke at the Hard Rock Cafe, sponsored by Pragmatic Works. It was a blast last time – can’t wait to do it again!

Thursday 6 November

On Thursday, skip the normal lunch and head to the Women in Technology Luncheon!

Thursday is also #sqlkilt day! As far as I can tell, kilt day has it’s origins back in 2009, but it’s grown to be quite the event. Keep your eyes peeled for the latest in traditional Scottish fashion!

Thursday evening is the Community Appreciation Party, held at Seattle’s EMP Museum.

Friday 7 November

Another day of amazing sessions, and then I’ll be taking the train back to the airport to head home. So long, Seattle – hope to see you next year!

Sunday 9 November

This isn’t related, but I’ll add it anyway. In my immense wisdom I signed up to run a half-marathon the day after I return home from a week where 3 hours of sleep is considered a good amount. I’m sure I’ll be in top shape for running!

What a week it’s going to be! Can’t wait to see as many of you as possible!

Jun 262014
 

I’ve been told on two occasions that the absence of the MEDIUMINT datatype is a serious shortcoming of SQL Server. If you’re not familiar with MEDIUMINT, it’s an integer type available in MySQL that consumes 3 Bytes and is capable of storing values ranging from -8,388,608 to 8,388,607 (signed) or from 0 to 15,777,215 (unsigned).

While SQL Server has many datatypes, it has nothing that exactly matches MEDIUMINT. In SQL Server, if you wanted to store a value between 32,768 and 2,147,483,647 you would need to use the INT datatype, each of which takes up 4 Bytes. The next smallest integer datatype is SMALLINT, which has a maximum value of 32,767 and only needs 2 Bytes. I’m not sure the lack of a MEDIUMINT datatype is really a shortcoming, but if you find yourself in a situation where it’s necessary to store a significant number of values in the 3 Byte range, I’m going to let you in on a little secret: SQL Server does have 3 Byte integers, they’re just lurking behind the scenes.

While being able to choose a 3 Byte integer may have some value, not having to choose it and reaping the benefits as if you did has even more value. Row Compression can do just that. SQL Server’s integer types (TINYINT, SMALLINT, INT, and BIGINT) are fixed-width, meaning they all consume the same amount of storage space whether or not the value they are storing warrants it. For instance, if a column (perhaps a foreign key reference) is only storing the values 1 through 5, only one Byte is necessary to store those values. TINYINT would be the most appropriate in this case, as it only stores a single Byte. Were that column defined as an INT, it would consume 4 Bytes, three of which would not be necessary. Enabling Row Compression changes all this, because it can free up space that’s not necessary for storing a value in a particular row.

Row compression lets the storage engine vary the storage format of fixed-width types, effectively treating them as if they were variable-width. This means that if an integer column has a row storing the value 5, only one Byte will be used. Similarly if the next row stores the value 500, only two Bytes are necessary for that row. But what if an application is storing the value 40,000? The SMALLINT data type (2 bytes) can only store values up to 32,767, so a third Byte would be necessary to get to 40,000. Can SQL Server store an integer as 3 Bytes internally, or can it only go up to 4 Bytes since the next largest datatype available to users is INT? Fortunately, we can experiment and find out.

To begin, let’s create a database

CREATE DATABASE [RowCompressionTest];
GO
USE [RowCompressionTest]
GO

and add a table

CREATE TABLE dbo.NumberData (
Value INT,
CONSTRAINT PK_NumberData PRIMARY KEY (Value)
);

and populate it with some values

INSERT INTO dbo.NumberData (Value) VALUES (0), (5), (500), (5000), (40000);

Now let’s look at how those values are stored. To view this information, trace flag 3604 must be enabled. This trace flag redirects some output values to the client so we can see them.

DBCC TRACEON (3604);

From here, 2 undocumented and unsupported commands are needed to find what we’re looking for. DBCC IND and DBCC PAGE are both completely safe and used internally by the SQL Server team at Microsoft, however please exercise caution should you decide to use them on a production system. Paul Randal has written great blog posts explaining both DBCC IND and DBCC PAGE in detail, so I won’t duplicate the effort here.

First, use DBCC IND to find the first (and, in this case, the only) data page used by the dbo.NumberData table. The file number is in the PageFID column, the page number is in the PagePID column, and you want the row where PageType = 1. In my case this is page 288, but your page number will probably differ.

DBCC IND ('RowCompressionTest','dbo.NumberData',1);

Next you will use DBCC PAGE to examine the contents of the data page you just found the number of. To do this, substitute your file number and page number in the query below. Mine are 1 and 288, respectively.

DBCC PAGE ('RowCompressionTest',**File_ID**,**Page_ID**,3)

There’s a lot of information returned here, and again please refer to Paul Randal’s posts if you are curious about what it all means. For the purpose of this demonstration, scroll down to where it says:

Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4

Value = 5

This is the first column of the first row (slot). The next line down you see it has printed that the value is 5, which is exactly what we inserted. What’s of interest here is that it says the Length is 4, and the physical length is also 4. The length is in Bytes, and since this column was created with the INT type, the length of 4 is indeed correct. That value 5 is using 4 Bytes of storage, even though it could just as easy fit into a single Byte.

Now scroll down to:

Slot 4 Column 1 Offset 0x4 Length 4 Length (physical) 4

Value = 40000

The value 40,000 also has a (logical) length of 4 and a physical length of 4. Even though this value could be stored in 3 Bytes, choosing an INT means it’s taking up 4 Bytes whether we’d like it to or not.

Now let’s see what happens when Row Compression enters the game. Run the statement below to enable it.

ALTER INDEX PK_NumberData ON dbo.NumberData REBUILD WITH (Data_Compression = ROW);

Because the index was rebuilt, all the page numbers change. Now you’ll need to run DBCC IND again to get the file and page number, and plug that into DBCC PAGE.

DBCC IND ('RowCompressionTest','dbo.NumberData',1);
DBCC PAGE ('RowCompressionTest',**File_ID**,**Page_ID**,3)

Scroll down to Slot 1 Column 1 again and you’ll see something like this:

Almost identical to what we saw before, but now the physical length is only 1 instead of 4. This means that with row compression enabled, the value 5 is stored physically (on disk) using a single Byte instead of 4. But what about that 40,000 value?

Slot 4 Column 1 Offset 0x3 Length 4 Length (physical) 3

Value = 40000

The value 40,000 consumes 3 Bytes instead of 4 when row compression is enabled, just like the MEDIUMINT datatype. So if anyone ever gives you a hard time for SQL Server not having a 3-Byte integer type, be sure to tell them that a) they really need to get a life, and b) SQL Server can do it very intelligently behind the scenes!