• Short article\post authors on Professional Development topics as they relate to the SQL Server Professional
• SQL Bloggers who would like their existing professional development content syndicated to the PD feed like general topic on SQLServerpedia.
• Volunteers for monthly live meetings
• Help us spread the word through your networks and blogs
• Your ideas on how the Professional Dev PASS virtual chapter can serve the community.

This chapter can only be as good as its members and by helping the chapter grow, you are really helping yourself grow.

So what do I think consists of professional development? Well, you could start of with this tag but that might give you the wrong impression. Let’s see what the Internet says:

From Wikipedia:

Professional development refers to skills and knowledge attained for both personal development and career advancement. Professional development encompasses all types of facilitated learning opportunities, ranging from college degrees to formal coursework, conferences and informal learning opportunities situated in practice. It has been described as intensive and collaborative, ideally incorporating an evaluative stage ^[1] There are a variety of approaches to professional development, including consultation, coaching, communities of practice, lesson study, mentoring, reflective supervision and technical assistance.^[2]

http://en.wikipedia.org/wiki/Professional_development

That is pretty close to what I was thinking especially skills\knowledge obtained for personal development and career advancement. Here are some PD topics IMO:

• Social networking\Blogging
• Handing tough situations\office politics\obnoxious coworkers in the work place
• Negotiating offers and raises
• Career path decision trees
• Job seeking skills
• Presentation delivery techniques
• Management(Time, People, Project etc.)
• Development of organizational skills
• Volunteering in the SQL Server community

And the list could go on for a long time. Hit Arnie, Mark or myself up if this is something you would like to help out with.

What do you think should be covered in a Professional Development virtual chapter?

We are going to be talking about data skew in this post. Picture a table where you have 1 billion  rows where IsShipped = 1 but 50k rows where IsShipped=NULL. Now, let’s say you have a query that returns orders that are not shipped(NULL). During compilation, the optimizer asks the stats how many rows are IsShipped=NULL. If the stats sampling touched, few, if any of the pages, where IsShipped = NULL, then SQL Server will guess that only 1 row will be returned. This may surpass the key lookup threshold(Part 1, Part 2) and a scan would not be optimal but better than a key lookup. Get it? We will look at an example soon if not.

In the past, your options have been limited. Update stats with fullscan was usually the best option unless the table was just too large and volatile. If it was, you probably had to use index hints or OPTIMIZE FOR the lesser of the two evils. You have covering indexes and is probably a good solution for the example above unless the result set has to be very wide.

Filtered statistics are a significant improvement and can be used nicely when dealing with this problem. They are smaller so cheaper to maintain. Their scope can be very narrow so they are more accurate.

There are a couple of caveats. I wrote about the need to use full scan on very selective stats here and Kimberly Tripp wrote about the fact that they will not auto update until the entire table crosses the threshold.

Let’s look at an example.

CREATE TABLE filtered_stats
(
c1 INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
c2 datetime DEFAULT GETDATE(),
c3 CHAR(500),
c4 INT
)

--The estimated rows returned are 1 but the actual rows are 51.

--This is a bad estimate due to uneven data distribution.
SELECT c1, c2
FROM filtered_stats
WHERE c4=2

--Lets create narrow and more accurate filtered stats
--You still have to do full scan but you are touching just a fraction of the data – NOTE: Full scan is needed based on my observations here.
CREATE STATISTICS fstats ON filtered_stats(c4) WHERE c4 = 2 WITH fullscan

--This should estimate 51 no matter how many times you update stats since they are more accurate
SELECT c1, c2
FROM filtered_stats
WHERE c4=2

This is not bullet proof but I say it is an improvement. The downside include the fact that filtered stats are manually created. Manual stats updates will need to be done with a full scan. You may also say that a covering index(or filtered covering index) would kill this. That is true but there may be an actual need to return all columns of a table.

A quick wrap up: Put it in the tool box. It will be like that $50 wrench that you use once a year but it saves you 4 hours every time you touch it.

Let’s look at demo data created with this code:


CREATE TABLE filtered_stats
(
c1 INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
c2 DATETIME DEFAULT GETDATE(),
c3 CHAR(500),
c4 INT
)

--lets at 1 million rows with c4=1
--lets add 1 row with c4 = 2
--Lets add a million more with c4 =3
--Finally, lets add 50 more with c4 =3 so the estimates are skewed.
DECLARE @ctr INT = 1000000
WHILE @ctr > 0
BEGIN
   INSERT INTO filtered_stats(c3, c4)
   VALUES
   ('blah', 1)
   SELECT @ctr = @ctr-1
END
GO
INSERT INTO filtered_stats(c3, c4)
   VALUES
   ('blah', 2)
DECLARE @ctr INT = 1000000
WHILE @ctr > 0
BEGIN
   INSERT INTO filtered_stats(c3, c4)
   VALUES
   ('blah', 3)
   SELECT @ctr = @ctr-1
END
GO
DECLARE @ctr INT = 50
WHILE @ctr > 0
BEGIN
   INSERT INTO filtered_stats(c3, c4)
   VALUES
   ('blah', 2)
   SELECT @ctr = @ctr-1
END


So now we have 1 million rows with c4 = 1 and 3. We have 51 rows with c4=2.

If we create this filtered stats, we have a null histogram: CREATE statistics fstats2 on filtered_stats(c4) where c4 =2

Updating or creating with fullscan give use the following data from dbcc show_statistics.

Bug or Feature? This is what BOL says:

“When only a few values are returned, however, sampling might not provide accurate statistics. For highly selective predicates, which return relatively few values, using the WITH FULLSCAN option will improve accuracy because it will scan all of the values instead of sampling.”

So they “recommend” that you use full scan on highly selective stats. However, I think that is a bug since we do not even get unfiltered rows or rows sampled back. Not to mention that inaccurate stats means to me that numbers are returned but they are most likely less than what is actually there. I will submit it as a bug unless one of you have thoughts otherwise.

So in a nut shell, ALWAYS update filtered stats with full scan or they may not actually be doing you any good. There are also some other considerations in regards to filtered stats that Kimberly Tripp has recently written about. There is some good conversion about it as well. Conor also posted about it here.

I will have more on filtered stats coming up in the next post.

Well to start things off, my kids spilled soymilk on my laptop. I have accidental damage coverage through best buy so I have a get out of jail free card but I am going to wait on the public Win7 launch for the new models. I have been eyeing the quad core i7 Dells.

The other time leech has been Halo OSDT. It was a great game. I spent most of my time working my way through the solo campaign. The story is pretty good but the ending had me saying that is it? Fire fight is fun. Multiplayer is ok except people cheat I suck at it.

I had a major project I accidentally fell into. I took a customer to Geek Mecca when he was in town from Denmark. I decided that I was going to come back and get a home theater with blu-ray. I did and the wife made me take it back after a day or two. It was too late at that point. The electronaholic was loose and prowling electronics stores at lunch. So I researched and hunted for a deal and got an Onkyo HT-S6200. I spent a week wiring, rewiring, reading manuals, and tweaking settings in a hedonistic geekfest of primal proportions. I think I excreted nerd musk at one point.

There was just one problem. I HAD TO HAVE Dolby tru-hd or DTS-HD master audio to get the true experience out of a 7.1 sound system. Another trip to Frys and the problem was solved with a blu-ray player.

At least, I thought the problem was solved. It turns out that the blu-ray could not go through the Onkyo to my TV because it was connected HMDI to DVI and it was not HDCP compliant. About this time, I also discovered that my HD cable channels were not working either. You know where this is going…

I was planning on a new TV for the family for Christmas anyway. What was I supposed to do? Go to component and optical cables which would quadruple the amount of cables behind my TV? I had no choice. Right?

Here is the setup and I really did shop around. I got some good deals. I even checked Craig’s list for the blu-ray player and the same model was going for more used than a new one because the seller bought it 6-9 months ago when it was twice as much.

Here is the setup:

• Onkyo HT-S6200
• Samsung BP-D1600
• Mitsubishi WD65737
• Rocketfish rear wireless speaker kit

It is not video\audiophile worthy but it is an upgrade for me. Not to mention that with a wife and three kids, I can’t have an orgy of one in a pile of new electronics and bubble wrap very often.

The only thing that I am worried about is the monkey on my back whispering about the 73” of goodness in the WD73737. It is just a whisper but it is getting louder. Must. Resist. Impure. Urges.

Here are before and after pics.

Before:

After:

Relevance, please?

So, you ask, what does all that have to do with Professional Development? I just advertised some things about the kind of person I am. It is now part of my Internet Image. That first paragraph is like a tattoo. It will never go away. This is what some say should be avoided when speaking of internet image in the SQL Server(and beyond) community. They say maintain the most upmost professionalism at all times. That is one strategy and I agree it is hard to get into trouble being conservative. However, you can also be yourself if assume the following:

• (Today’s + affirmation) By acting myself, I can interact with people with the same interests as me, I am likable for who I am, and I want to associate with people like me even in a professional environment.
• I would prefer to work in an environment where I fit the general culture.
• By fitting in with the culture, I am a foot a head of an equal job candidate for same position that does not fit in the with culture.

Talking about myself again!

To use me as an example, I display my xbox gamer tag proudly on this site. I would not want to work for a company where most of the people do not own Xboxes. At the same time, I would love to work at a company where there are games in the break room.

Of course, you can cross the line and use poop or toilet analogies. At the same time, wouldn’t you prefer to work for a boss who appreciated crude internets geek humor if that is what you are into? Same goes for bowling, knitting, d&d, and wiccan magic. He77, if it was not for HR, my first question to potential job candidates would be “What is Goatse?”. I kid but as a hiring manager, I do look for technical ability first but cultural fit is a not too far behind. Look at it like this. I do not care if I walk by and one of my DBAs is reading the Onion because I know when a server explodes and the ʪĦÍŦ hits the fan, they will be there be it for three hours or thirty; what ever it takes to recover, boot, or function. That intangible quality is a job skill!

Where is the line?

I suggest that anytime you ever post anything online, you follow this decision tree.

• Picture your dream job that you want to be in DBA century(10 human years)  from now.
• Ask yourself if this will help or hurt your chances for that job?
• If the answer is yes, then ask yourself if you would be happy working for such a company\manager?
• If both answers are yes, then do not post!
• If either are no, post away.
• If drinking tasty cold alcoholic beverages, immediately post something “insightful” on your boss’s boss’s facebook wall.

So can this backfire?

There are also other circumstances that I should warn you against being yourself like if you are the only one who laughs at your jokes or is boring, obnoxious, always right, a trekkie, or a LOTR fanatic(7 hours of walking! Even the trees!).  These mostly hold true for the unforeseeable future too so if you are not confident in your future, you may want to exercise caution as well and remember that life happens. Here are some things that would make me be more conservative.

• Your job market is small. Examples include a small town or a very small niche specialty like a Sybase to SQL Server conversion specialist in Bee-eF-eeVille, Texas.
• Your experience lends to junior level jobs for now. We all started off some where. Until your technical experience outpaces any possible negative perceptions, then a conservative route made be prudent. As I said earlier, it is hard to get in trouble being conservative so when in doubt, lean tight right like a southern Baptist GOP senator in the closet.
• Religion and politics(previous bullet aside) – This is more of my personal preference because a valid argument could be made for putting this out there. I, on the other hand, think that my dream job could be working for someone that I don’t agree with on Religion\Politics and I think the hiring manager may choose an equal but “image conservative” candidate over me because of it. However, if this is your cup of tea, +10 street cred homeboy!
• You are conservative by nature. I am only making a case for being who you are. Do not portray anything you are not.
• You think you may change careers over the next 10 years. Again, life happens. Lean conservatively unless you are confident in your future.

What am I trying to say...

I have not spoken much about the benefits of this openness on “The CPU” as my wife calls it. Let me wrap this up with that. Basically, it is more fun and more natural to be ourselves. I believe you are more interesting. Why hold back personality for dry dull dialog? That said, being anything but being the utmost professional on the internet should be done after careful thought so don’t log into alt.binaries.erotica.asian.furry.midgets.multimedia with your real name! Besides, I have worked for the same company for the last 10 years and I do not think I will be looking over the next 5-10+ years so take this with a grain of salt. Lastly, the golden rule is don’t do anything you would not be proud to show yo mamma.

Speaking of…. Yo mamma so nasty we tried to flush her dirty pages with DBCC DROPCLEANBUFFERS  but it kept failing because of the stench from that puss bubble excreting from her VLF. BOOM.

This mix is bass heavy and upbeat. If you are not into electronic music or did not like my previous mixes this may be one to check out.

http://statisticsio.com/jmass%20-%20WhatTheFork.mp3

Lucien-N-Luciano – Future Pressure
TRADEMARK – Tropic Of Capricorn
CLARKE, Tuppy – Chicago (Mitch Windy City remix)
DANKE, Sean – Guiding Lights
EKKOHAUS – Thursday Morning OK
ESPOSITO, Emanuele/LUCA GIORDANO – Deepingo (Emanuele Esposito mix)
FERREIRA, Gabriel – Salmonella (Stefan Tretau remix)
Igor Krsmanovic & Andreja Z – Demolition
Pablo Bolivar – Paseo Polar
AK, Yavuz – Hoppa
REIG, Eddu – Attempt Of Cream
COENEN, Ludwig – Failover
COENEN, Ludwig – Clickoff
CERRONE, Rino – Exnovo (Exnovo Mazel Source remix)
CHOI, Jin – Everything Is Borrowed (original mix)
WIRETAPPEUR – 8 Days A Week
EKKOHAUS – Brian Said
NEUROXYDE – Yebo Yebo (Neuroxyde From Berlin rework)
DAMOLH33 – Untitled States
ELLIS, Tom – Anyhoooo (feat Suz)
007 AGENT aka THE BIG MOUNTAIN feat GONZALO HURTADO/PAULA – Our House (original mix)
Igor Krsmanovic – Missiles
WIRETAPPEUR – I Want You
CASCABEL GENTZ – Dirty Lips (Afrilounge remix)
Johnson & Haske – Breeze (Alex Young remix)
La Gosse We Don’y Know Yet
RAGANOVA – Antifonar
CARUSO, Samuele aka MANIMALISCH – Fortwegung
TRADEMARK – Elevator
DAAMEN, Camiel – Deep Love
SUPER FLU – Lyla Sucks Chestnuts
CRISS, Jay – Dark Triangles (Distaff remix)
IRON CURTIS – Peoples (Ekkohaus’ Down With The Sax mix)
JONAS & FABIAN – Beatcontrol
EKKOHAUS – Old Friends
ESSER, Darko Relieve (original)
Kruno Kereta – Portmanteau
RAGANOVA – Paplan
AK, Yavuz  – Change

In the first part, I detailed the benefits and reasons for DR in the cloud. I ended with a high level sample architecture.

In this post, we will look at an example of doing DR to the cloud with SQL Server. We will be using Database Mirroring but you could use home grown log shipping, replication, incremental nightly loads, or third party products that do block level replication to an offline instance. So lets get started.

Part one: Create the DR SQL Server

1. Hit the create server button in the web interface.

2. Select the OS and template. Note: If you want to use SQL Server Enterprise Edition, just use a Windows template and install SQL from an ISO. You will need to do this if you want to use asynchronous mirroring and compression.

3. Name and size the Server. You can skimp on the resources and add more RAM and VPU’s in the event of failover.

4. Set the network. It will need to be DMZ but we will lock it down at the firewall.

5. Organize as you wish.

6. Add disk space and power on.

Part two: Configure network access.

1. Set up an internet accessible port to the server. Do not fret. We are going to use a certificate and data will encrypted on the wire.

2. Create a node for the Mirror.

Part three: Configuring mirroring.

1. Backup your database and get it to your mirror. (I am just using RDP but if you DB is large, setup a more robust copy method)

2. Restore the database with norecovery at the mirror.

3. Send diff(if needed) and at least one log.

4. Restore diff(if needed) and the log with norecovery

5. Set up database mirroring on port 1521 using a certificate.

Please refer to this article for directions on how to set up mirroring with a cert.