The changing face of community

I am talking about Paul Randal’s twitter feed. Tweet after tweet of internals and storage engine goodness. I hope to take a little credit for getting Paul on twitter. Peer pressure is tough. Although, who is the first person he follows? A marketing dude! @BrentO WTF?

Then he turned into a monster. 50-100 tweets a day! Noisy, yes, but how do you unfollow Paul Randal?!?! That said, IMO, he is now on the cutting edge of the SQL community. Both technically and logistically. That combination is as rare a SQL Server(past or present) dev team member who also has great presentation skills. Err, wait… That would be Paul too.

Ok, I am about to go get some sandpaper, a blow torch, hydrochloric acid and my wife’s tooth brush to get that sh!t off of my nose. Shhh, don’t tell wifey.

http://twitter.com/paulrandal

This is just today!

1. #sqlconn 2008 extended events allow session-level wait state tracking. Neat demo from Gert.about 3 hours ago from TweetDeck

2. #sqlconn Don’t worry about high preemptive waits, they can be expected for some operationsabout 3 hours ago from TweetDeck

3. #sqlconn Running xp_cmdshell will show up as a PREEMPTIVE_OS_PIPEOPS wait in sys.dm_os_wait_stats.about 3 hours ago from TweetDeck

4. @datachick Truffles work well – they’re Kimberly-crack.about 3 hours ago from TweetDeck

5. @datachick I don’t think I’d get the same response from @KimberlyLTripp…about 3 hours ago from TweetDeck

6. #sqlconn Use sys.dm_os_threads to get the OS-level threads running. Then look in Process Explorer to get call stack + see why it’s waiting.about 4 hours ago from TweetDeck

7. @mrdenny That is true. I guess in the city one would pose, and on the freeway, one would zooom.about 4 hours ago from TweetDeck

8. #sqlconn Which are run by threads, running on multiple schedulers within an SMP/NUMA node 2/2 Try saying that ten times after a few beers!about 4 hours ago from TweetDeck

9. #sqlconn A session can issue multiple requests. Which can run multiple tasks. Which are run by workers 1/2about 4 hours ago from TweetDeck

10. #sqlconn 2005 RTM assigned tasks to least loaded scheduler – that code is commented out know and it just does round-robin now. Too complex.about 4 hours ago from TweetDeck

11. @mrdenny I don’t know about that – whenever I’m driving from SFO to Mountain View, the 101 seems like a race track!about 4 hours ago from TweetDeck

12. #sqlconn Assuming affinity mask is 0, you’ll have at least 1 scheduler per CPU, plus some hidden schedulers (e.g. DAC)about 4 hours ago from TweetDeck

13. #sqlconn Forget about fibers. Benchmark special, lot’s of things stop working. I totally agree.about 4 hours ago from TweetDeck

14. #sqlconn SQL does not distinguish between logical procs and physical procs – watch out for hyperthreadingabout 4 hours ago from TweetDeck

15. #sqlconn Gert’s explaining about preemptive scheduling models, makes me want to go back and read Tanenbaum’s classic OS books from college.about 4 hours ago from TweetDeck

16. @kbriankelley That’s like Wired. I don’t read every issue, but $12/year, how can you go wrong?about 4 hours ago from TweetDeck

17. Waiting for Gert Drapers to start the last session of the #sqlconnference, on What are You Waiting On?about 4 hours ago from TweetDeck

1. @AdamMachanic Scared to yell ‘Buck’: might be a thunderclap + @BuckWoody appears out of thin air. Bad enough when I know he’s coming! ;-Dabout 5 hours ago from TweetDeck

2. #sqlconn Walked into the 500-person room again and this time let loose with a thundering ‘echo’. Results were childishly impressive about 5 hours ago from TweetDeck

3. RT @KimberlyLTripp: #sqlconn #sqlpass Just blogged SQL Server MVP Deep Dives bk (w/ pic from launch @ PASS):http://bit.ly/42YNDj. Enjoy!about 6 hours ago from TweetDeck

4. #sqlconn RML Utilities looks to me like performance data collection in 2008about 6 hours ago from TweetDeck

5. @MladenPrajdic You beat me to the tweetabout 6 hours ago from TweetDeck

6. @BradDBA @heigesr2 I wondered if anyone would get the joke about 6 hours ago from TweetDeck

7. #sqlconn Use fn_trace_gettable() to treat a trace file as if it were a trace tableabout 7 hours ago from TweetDeck

8. #sqlconn Q: if you stop/start a trace, does it append to existing file or create new one? A: it appendsabout 7 hours ago from TweetDeck

9. RT @SQLChicken: @PaulRandal Here’s an article I wrote on how to create server-side trace with profiler http://bit.ly/eZQYV #sqlpass#sqlconnabout 7 hours ago from TweetDeck

10. #sqlconn But it’s far easier to get a scripted out trace definition from Profilerabout 7 hours ago from TweetDeck

11. #sqlconn Looks like setting up SQL Trace manually is pretty quirky compared to using the GUIabout 7 hours ago from TweetDeck

12. @MladenPrajdic Answer is no then I guess.about 7 hours ago from TweetDeck

13. #sqlconn Profile when a batch completes, not starts. Common mistake. You can’t get duration, cpu, etc except when it completes.about 7 hours ago from TweetDeck

14. #sqlconn When profiling, make sure you filter, but don’t over-filter.about 7 hours ago from TweetDeck

15. @MladenPrajdic What do you mean by type? XML vs varchar (max) vs text, for example?about 7 hours ago from TweetDeck

16. When I don’t know the answer to an internals question, I ask Bob Ward @bobwardms. Follow him.about 7 hours ago from TweetDeck

17. @StrateSQL Common problem. SharePoint is an example.about 7 hours ago from TweetDeck

18. Someone next to me just had a phone call and it was the muppets as a ring tone – MANAMANA!!! Doo dooo de doo doo. Excellent!about 7 hours ago from TweetDeck

19. This is the first #sqlconn ference in 3 years where I have a spare day where I can actually watch sessions and learn stuff – very cool!about 7 hours ago from TweetDeck

• 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