Introduction to Extended Events

I have done a few presentations on the intro to Extended Events and I wanted to get the content out here on my blog for you. So, here it is:

Extended Events (XEvents) were first introduced with the release of SQL Server 2008. They were powerful but not adopted due to the fact that they were difficult to understand, setup, configure and use. They were very complex to figure out compared to traditional tools such as Profiler, SQL Trace & Activity Monitor. This was because there was no user interface and all operations were command-line or query driven. The query toolset works well for DMV’s, but DBA’s were unwilling to try to navigate the minefield that they thought XEvents were.
Just what is / are XEvents ?
Well, Books On-Line describes them as: “a lightweight, highly scalable, highly configurable performance monitoring system that uses very little system resources and allows admins to collect as much or as little information as is necessary to troubleshoot or identify a performance problem.” WOW, that’s a lot of buzz words!!
Let’s break it down.

Now, an event is ANY recordable activity of significance that occurs in the normal execution path of a piece of code (disk read, memory page out, etc). The Event Tracing for Windows (ETW) framework is a process within the Windows Operating System of logging kernel or application events to a log file.

Event Tracing for Windows Concepts
Here is a diagram of the internals of the Event Tracing for Windows framework:

Event Tracing for Windows framework

The events themselves are sometimes sourced from providers which are applications that contain event tracing information.
Event tracing sessions are merely enable the capture of events that occur in the system. The events and associated information is stored in a buffer until that information is flushed.
Consumers receive the events from a log file or in real-time. Consumers can accept multiple sessions simultaneously and the system will deliver events in chronological order.
Controllers start and stop the event trace session and enable providers.

ETW References:
MSDN: Event Tracing
MSDN Magazine: Improve Debugging And Performance Tuning With ETW

The Extended Events (XEvents) feature within SQL Server is an architecture that uses and builds on this ETW framework.

XEvents Concepts
Here is a diagram of the internals of XEvents:

Exended Events within SQL Server

The XEvents Engine is a collection of services that:
     Enables the definition of events
     Enables processing event data
     Manages XEvents objects in the system
     Maintains a list of XEvents sessions and manages access to that list
An XEvents Package is a container for XEvent objects. The packages are package0, sqlserver, sqlos, SecAudit, sqlclr and ucs (unified communications stack), etc. Contained in an XEvent package is a combination of the following:
     Events: An event is a point of interest where something happens in the execution path of SQL Server. There are 446 events in SQL 2012 and 763 events in SQL 2014 and each one corresponds to an event from SQL Trace. When an event fires, that event returns state information relating to the corresponding point of code. This basic information can be added to by the use of Actions
     Actions: These are bound to the event during the definition of the Event session and perform a specific task when the event fires. This task can be: collecting additional information from memory, a stack dump and data inspection, aggregating event data, run-time statistics collection or user-input collection. In SQL 2012, there are 43 actions that can be used.
     Types: Straight forward, data type of the information contained in the event. There are 28 types in SQL 2012 including ansi_string, unicode_string, boolean, guid, filetime & callstack.
     Maps: This is the lookup table that will let the admin know what the information inside the event actually means. For example, the “lock_mode” map system table will map the key to the value for various types of locks that can occur.
     Predicates: These are the set of rules that control the circumstances under which the event actually fires and are used to evaluate events as they are processed. They also provide us with the ability to filter the events that we are capturing.
     Targets: These are the event consumers that process events, either synchronously or asynchronously. Synchronous targets have the event buffered to them by the executing thread, and asynchronous targets have the events dispatched to them by the dispatchers in the Database Engine when the buffers fill up.
XEvents Targets are the data consumers or endpoints of the event data. Targets can write to a file, store event data in a memory buffer, or aggregate event data. Targets can process event data both synchronously, on the thread that fired the event, or asynchronously on a background system thread. For performance reasons, asynchronous targets should be used whenever possible. When an event fires, the synchronous targets are served the data immediately to minimize the impact to the executing session. After all of the synchronous targets have processed the event data, the data is queued in the session buffers where it waits to be dispatched to the asynchronous targets.

XEvent Targets:
     Event File (asynchronous)- Creates two types of files, log and metadata for writing complete buffers to disk. The metadata file describes the output log file and allows the events and associated actions to be parsed correctly. The file names and locations must be provided to use this target. Use to write event session output from complete memory buffers to disk.
     Event Ring Buffer (asynchronous) – Temporarily holds event data in memory, in either a first-in first-out mode where events are discarded in the order received or, on a per-event mode where a set number of each event type is kept and then discarded.
     Event Pairing (asynchronous) – Many kinds of events occur in pairs, such as lock acquisitions and lock releases. Use to determine when a specified paired event does not occur in a matched set. Matches two events based on one or more columns present in the events. Matched pairs are then discarded leaving only unmatched events for analysis. To use this target, you must provide the events to be matched and the columns to be used for the matching.
     Event Tracing for Windows (ETW) (synchronous) – Use to correlate SQL Server events with Windows operating system or application event data. Only one ETW session to the SQL Server provider can be open at a time. For this reason, the ETW target obtains a reference to this one session for all ETW targets regardless of their XEvents session scope.
     Event Counter (synchronous) – Counts all specified events that occur during an XEvents session. Use to obtain information about workload characteristics without adding the overhead of full event collection.
     Event Bucketing (asynchronous) – Creates buckets based on the user-specified columns, and only collects and retains data for the specified source. If the event data is too large for retention, it is truncated. Use to count the number of times that a specified event occurs, based on a specified event column or action. This is also referred to as the Histogram Target.

An XEvents Session is just a collection of events, the actions that occurs when an event fires and the targets that are the destination of the event information. XEvents Session options also define the start and stop parameters, timeline of events and actions, memory buffer size, and how event loss can occur if the buffer fills up.

XEvent Session State

An XEvents Session has implied boundaries that contain the configuration and session objects (events, actions or targets). These boundaries do not prevent the same object from being used in multiple sessions. They just define the context of that specific session. Policies are attached to sessions to define buffering and dispatch.
XEvent Session Boundaries

XEvent References:
MSDN: Extended Events
MSDN: Extended Events Tools

Here is a link to the PowerPoint slide deck that I use in my presentations on this subject:
PowerPoint Slide Deck on Extended Events

Please Note:
When you install the RTM or SP1 versions of SQL Sever 2012, there is a bug in the “Activity Tracking” template within the Extended Events management console UI and wizard. My good friend Jonathan Kehayias ( blog | twitter ) discovered the bug and has posted a fix on his blog. You can get it here.

Aaaaaand, that’s it.
I will be posting a follow up soon with a demo on how to run XEvents on your server to perform some basic event capturing.


The 2013 SQLPass Summit is being held in Charlotte, NC next week and I’m excited to say that I will be there.

The SQLPass Summit is an annual SQL Server geek-fest of learning that attracts over 4000 SQL Server professionals. The core of the conference consists of the 190+ sessions where you can can learn about anything from advanced MDX to SQL Azure and of the 2 days of full-day pre-conference sessions. Additionally, there are the networking opportunities, SQL clinics and community get togethers that make the week one of the most exciting weeks of my year. Please go here for more information:

The week starts in ernest on Monday morning and I will be taking the time to get to know the Charlotte “Uptown” area in true geek style – on a Segway. I took a Segway tour around Chicago last month and it is a great way to see the city and have fun at the same time. I will be enjoying the tour offered by the Charlotte NC Tours company along with some friends. If you’d like to join us, we will be going on the 9 am “2-hour Markets, Museums & Parks Segway Tour” on Monday morning. We’d love to have you along.

Brent & Gareth on Segways - Chicago, September 2013

Brent & Gareth on Segways – Chicago, September 2013

After a morning’s hard work on a Segway, we will be convening at one of the premier craft breweries in the Charlotte area for a spot of lunch: The Olde Mecklenburg Brewery. We will be there from about 12 noon. This will also be the start of the afternoon’s activity – a lovely craft brewery tour. We will be visiting some of the great breweries listed in this excellent, informative guide by Greg Gonzalez: PASS Summiteers Guide to Charlotte, Part 4: The Craft Beer Scene. Go read it. Now.

I’m guessing that this brewery tour will continue long into the evening and I’m looking forward to enjoying some good beer and a lot of great company. Please come and join me.

My Tuesday will be filled with SQLPass community meetings and I’m really looking forward to participating in some great discussions around how to grow the grass roots SQL Server community. I am currently a SQLPass Regional Mentor and really enjoy giving back and helping data professionals get help for any issues they may have.

The Summit Welcome Reception will be held on Tuesday evening from 6:30 – 8 pm. Come and find me and say HI. I’d love to meet you.

The SQLPass Summit sessions are presented by various interesting and engaging speakers on Wednesday, Thursday & Friday and I encourage everybody to attend as many sessions as is humanly possible. I am looking forward to attending as many PDW, Big Data, performance tuning, internals and optimisation sessions as I can. Pick the sessions that you want to attend on the Schedule Builder that can be found on the SQLPass Summit website. Remember to arrive to your session early to make sure you get a seat as they can fill up quickly.

The SQLPass Chapter Luncheon will be held on Wednesday. “Connect with community leaders in your geographical area, and find SQL Server events and learning opportunities happening right in your back yard! Meet and eat with PASS Chapter Leaders and peers who can help you become more involved in your local SQL Server community.”

The Exhibitor Reception will be held on Wednesday evening from 6 – 8 pm. Make sure you go visit the vendors that support SQLPass and help make events like the Summit possible.

The big event of the week will start right after the Exhibitor Reception: The Pragmatic Works #SQLKaraoke event. Make sure to pick up your wristband at the Pragmatic Works booth in the exhibitor hall. Details can be found on this page: #SQLKaraoke. I’ll be there, singing my name away … as usual.

Phew. Anybody tired yet ? We have 2 more days to get through …

Thursday sees another very important event happening – The Women in Technology Luncheon. This occurs to help us in the technology sector understand the discussions around helping women succeed. To support this important issue, various manly men started donning kilts. I will be wearing my very comfortable Anderson tartan next Thursday, too.

Buck & Gareth in kilts - 2012

Buck Woody & me in kilts – 2012

Now, to MY event …

On Thursday evening, from about 5 – 7 pm, I, along with Mark Stacey & Christina Leo, will be hosting the 2nd Annual #SQLWine. We are inviting a select few people to join us for a intimate little wine tasting. If you’d like to join us, please let me know on Twitter – @GarethSwan. Space is limited.

Microsoft and SQLPass are sponsoring the Community Appreciation Party at the NASCAR Hall of Fame on Thrusday evening from 7 – 10 pm. This promises to be an amazing good time. Be there.

Friday promises to be an excellent day of sessions and our final important luncheon of the week is held. This is the Birds of a Feather Luncheon. If you have an important SQL Server area that you are passionate about, there are other people that feel the same way. Come to the Birds of a Feather Luncheon to discuss your favourite issue with like-minded people. I will be there hosting a table. Look me up.

Now, this is NOT the be all and end all of events that occur during the week of the Summit. There are a LOT of other things to see and do – Community Zone, SQLPass bookstore, Exhibitor Hall, SQL clinics, SQLCat team meetings, certification exam attempts, etc., etc., etc. You will never be bored. Find something that interests you. It’s going to be a wild ride !!

For those that don’t get enough community during the Summit, we’ll be doing it all again on Saturday at SQL Saturday #237 – Charlotte BI Edition 2013.

After that, I will be sleeping for approximately a week.

See you at the SQLPass Summit !!!

90 days to MCSA – Building a plan for the Challenge

In the first post in this 90 days to MCSA series, I laid out the idea that we should all work together to target achieving the MCSA certification in SQL Server 2012 and/or Windows Server 2012.

Join the Challenge
Now, it is a very good idea to set a goal and to start working towards attaining that goal. However, if you do not have a plan on how to achieve that goal, you will never get there. Let’s say you set out to climb Mount Everest. That is all very well and fine, but first, you will need a few things before you can just go off and climb the highest peak on Earth. You will need oxygen tanks. Cold weather clothing and camping equipment. An idea on exactly where Mount Everest is. You will need training on how to climb large outcrops of rock. You may even need the services of an experienced mountaineer. The point is that there will be a number of steps that will need to be followed in order to even attempt the climb.

What I will, hopefully, lay out here is a basic plan of action that we can follow to be much better prepared to pass the exams that are required for the MCSA certification. I will lay out two separate plans, one for the SQL Server 2012 certification and one for the Windows Server 2012 certification. You will need to pick one of these to follow (or both, if you’re crazy in the head, just like me !).

First of all, let’s go over some basic rules of certification and test-taking.

1. You need hands-on experience !!! The whole point of the certification is the knowledge, not the piece of paper it’s written on. A wise old lady once told me “Knowledge is a burden easily carried!” To know something is definitely not the worst thing in the world. You can never know everything, but to continue learning new things every day is the way to go through life. In order to make sure that you know the material, you will need to build a home lab and play with the software. Break it. Fix it again. Break it again. Etc.

2. You can’t possibly know everything so rely on your strengths and focus on your weaknesses. That brings me to the next rule. You can’t possibly know everything about the product, especially when there is a time crunch like we have here. Make sure you shore up your knowledge in areas where you are strong and then develop a strategy on how you will attack some of the areas where you may not understand as much.

3. You will be tested on stuff you may never have, and may never use. We are a variegated bunch and work and play in a wide variety of environments. Some DBA’s may have never worked with SQL replication in their careers (like me), while others have no idea on how to set up database mirroring (who needs disaster recovery, anyway?). Some sysadmins have never actually created an Active Directory forest from scratch (Don’t blame me. It was like this when I got here) while others have no idea how to administer DNS (the network guys do that, right ?). If you want to walk on water, you have to get out of the boat first …

4. Carve out time every day to study & practice, if your work/home life will allow One thing I will advocate for you to do, RIGHT NOW, is to go and have a talk with your family, SO, spouse, dog, friends, etc. about the fact that you will need about 10 hours a week for the next 10 – 12 weeks in order to achieve this goal. I’m not kidding. If you’re serious about furthering your life, you will need the support of those who love you. If you skip this step, there will come a time when you’re sitting in the exam room thinking “Damnit, I should have spent an extra 2 hours going over this thing …”. Fact.

5. Develop a solid test-taking strategy. I have not taken a lot of certification tests in my career. I have been skating along on good-looks & charm all my life . However, there are a LOT of good resources out there that detail how to go about the task of sitting a Microsoft exam. Here are a few that I found:
Certification Magazine
Computer Performance LTD
Pearson IT Certification

6. Reward yourself when you hit your goals. Sitting at home, on the shelf in my office, I have this LEGO set. It’s not built. It’s still sealed in the box. When I finish this challenge, I am going to spend an entire Saturday with my 6 yr old son and we are going to open it and build it and it will be GLORIOUS !! Go find something similar for yourself. We can all share our triumphs together.

7. Take time off when you need it. “All work and no play makes Jack a dull boy” You are not a robot. You will go mad if you do not take a break. Go outside every now and then. Go and replenish your bacon supplies, at least.

90 days to MCSA – SQL Server 2012

Week 1 (this week):
     Build your Plan. Following this blog post series and visiting the Microsoft 90 days to MCSA web site will help you to do that.
     Build your Lab. Here is a great article from the fine folks at Microsoft learning on how to set up a lab to prep for exam 70-461: Lab Setup Guide to Prep for Exam 70-461 Querying SQL Server 2012.
     Sign up for the Microsoft Learning 90 Days to MCSA e-newsletter. This can be found on this page.

Week 2 (next week):
     Build your Knowledge. Sign up for the Free Video Training from
     Sign up for the first exam. Microsoft is offering free second shot retake vouchers for a limited time. Go and grab 15% or more savings on certification packs here.
     Share with us in the Google+ community. Let us know how you’re doing, if you have any questions or if you just generally need to talk. We’re here for you.

Week 3:
     Build your Knowledge. Watch the Microsoft Learning Exam Prep video for the first exam that will be attempted – 70-461
     Join the Microsoft Learning Study Group. There are some great resources available in these Study Groups including discussion forums and exam prep wikis. Go and join the Database Certification Study Group.

Week 4:
     Build your Knowledge. You should be consuming the content of an exam training kit like the one offered by Microsoft Press which can be found, amongst other places, on Amazon: Training Kit (Exam 70-461): Querying Microsoft® SQL Server 2012. Also available and recommended is the Study notes and review questions book: MCSA SQL Server 2012 Exam 70-461 & 462 ExamFOCUS Study Notes & Review Questions.
     Take the 70-461 exam. By this point, you should be in a position to exert your effort into actually putting knowledge to paper. Well, digital bits of paper. You will go and attempt to pass the exam. Let us know how it goes !

Week 5 and beyond:
     The process is more-or-less repeated with the 70-462 and 70-463 certification exams. I will be updating this post with specific links that cover those exams. Keep an eye out for that.

90 days to MCSA – Windows Server 2012

Week 1 (this week):
     Build your Plan. Following this blog post series and visiting the Microsoft 90 days to MCSA web site will help you to do that.
     Build your Lab. One of the tricky things about studying Windows Server 2012 is that you need to delve into the guts of Windows Hyper-V as it is a very integral part of the OS. You will need 1-2 Windows 2012 instances and that is not including the DC. Remember, Hyper-V does not support nested Hyper-V virtual machines so you will need a physical installation of Hyper-V or the use of anopther company’s virtualisation software (VMWare or Oracle VirtualBox – What I use). Details on how I am building my virtual environment will be coming in another blog post.
     Sign up for the Microsoft Learning 90 Days to MCSA e-newsletter. This can be found on this page.

Week 2 (next week):
     Build your Knowledge. Sign up for the Free Video Training from
     Sign up for the first exam. Microsoft is offering free second shot retake vouchers for a limited time. Go and grab 15% or more savings on certification packs here.
     Share with us in the Google+ community. Let us know how you’re doing, if you have any questions or if you just generally need to talk. We’re here for you.

Week 3:
     Build your Knowledge. Watch the Microsoft Learning Exam Prep video for the first exam that will be attempted – 70-410
     Join the Microsoft Learning Study Group. There are some great resources available in these Study Groups including discussion forums and exam prep wikis. Go and join the Server Certification Study Group.

Week 4:
     Build your Knowledge. You should be consuming the content of the exam training guide offered by Microsoft Press which can be found, amongst other places, on Amazon: Training Guide: Installing and Configuring Windows Server 2012. Also available and recommended is the Exam reference book: Exam Ref 70-410: Installing and Configuring Windows Server 2012.
     Take the 70-410 exam. By this point, you should be in a position to exert your effort into actually putting knowledge to paper, well digital bits of paper. You will go and attempt to pass the exam. Let us know how it goes !

Week 5 and beyond:
     The process is more-or-less repeated with the 70-411 and 70-412 certification exams. I will be updating this post with specific links that cover those exams. Keep an eye out for that.

And that’s it. Stick to your plan and you will, successfully, achieve your goal of a brand new MCSA certification that you can take to your boss as your bargaining chip for a raise or promotion (hopefully).

I hope to see everybody that I know achieve their MCSA certification in SQL Server 2012 or Windows Server 2012.

90 days to MCSA

I saw a very cool post from Microsoft Learning on Facebook yesterday that intriged me. Microsoft Learning has put together a very nice set of training materials, videos, wikis, discussion forums and the like to help candidates prepare for the set of exams that make up the SQL Server 2012 or Windows Server 2012 MCSA certifications.

Join the Challenge

90 Days to MCSA blog post series:
1. Introduction – This post
2. Building a plan for the Challenge – 90 days to MCSA – Building a plan for the Challenge

Now, let’s break down the two certifications:

The SQL Server 2012 MCSA consists of 3 exams that need to be passed. They are 70-461: Querying Microsoft SQL Server 2012, 70-462: Administering Microsoft SQL Server 2012 Databases and 70-463: Implementing Data Warehouses with Microsoft SQL Server 2012.

The Windows Server 2012 MCSA consists of the following 3 exams that will need to be passed. They are 70-410: Installing and Configuring Windows Server 2012, 70-411: Administering Windows Server 2012 and 70-412: Configuring Advanced Windows Server 2012 Services.

More information can be found on each of the links above and by visiting the Microsoft Learning web pages for SQL Server and for Windows Server.

There are many , many different excellent resources available and the main landing page for all that great content can be found at

Now, I’m sure that I will be able to put together a good basic rundown of the various information available on the 90 Days to MCSA website, but the team at Microsoft Learning is much better at that than me. Here is the link to 90 Days to MCSA: SQL Server 2012 Edition and 90 Days to MCSA: Windows Server 2012 Edition articles that will explain eveything is great detail for you.

What *I* will be doing is moderating a study group and email list of people who have indicated to me that they’d like to achieve this certification within 90 days. I have created a Google+ community here that will be used as a discussion forum / study group and I’ll be sending out regular (every week or 2) newsletters that will contain updates, tips & tricks, etc. Please contact me if you’d like to be included in the Google+ communit or on the email list. I will also be posting various blog posts about the 90 Days to MCSA Challenge. Keep your eyes open for those. I will be adding updates to this page as I add blog posts.

Now, one thing I’d like to mention is that I will be trying to achieve BOTH certifications. I am currently a DBA consultant but my background is in systems administration. I have been tasked by the company to achieve these certifications as part of our company relationship with Microsoft. BUT, I know that these certification are very beneficial even if I didn’t *need* to do them for work, hehehe.

I hope to see everybody that I know achieve their MCSA certification in SQL Server or Windows Server.

SQL Server 2012 for the DBA

This past Wednesday night, the 18th Jan, I had the privilege to speak at the local SQL Server user group – JaxSSUG.

I was asked by Tom Brenneman to co-present on some of the new SQL Server 2012 hotness. I am currently working on SQL Server 2012 content that will be the basis of an upcoming 4-day class so I was able to put together a quick overview presentation. It was called Delta Force: SQL Server 2012 for the DBA.

JaxSSUG presentation

It was a great evening with approximately 60 people attending. I was definitely the supporting act while Adam Jorgensen (blog | twitter) headlined with his presentation on the BI Tabular Model and PowerView in SQL Server 2012.

Thanks to Scott Gleason, Tom Brenneman and JaxSSUG for putting on a great event.

You may download the slide deck for my presentation here.

SQL Server 2012 for the DBA (virtual class)

A new skill for a New Year
Pragmatic Works Training has a full and varied schedule of classes coming up. You can find the list here.
I will be teaching the SQL Server 2012 for the DBA virtual class on 19th – 22nd March. Registration is now open for this class.


SQL Server 2012 is the newest version of SQL Server available from Microsoft. This four day (half-day) course is designed to introduce SQL Server DBA’s to the new and exciting features available in that version. We use a variety of lab exercises to ensure lecture sessions are backed up by hands-on experience. With the hands-on labs, you will experience how the new features can be utilized in your everyday DBA work. This course will cover topics such as AlwaysOn, ColumnStore indexes, SQL Server Data Tools, the FileTable feature, Azure and administering the BI platform within the context of SQL Server 2012.


The target of this class is SQL Server DBA’s, developers, architects or IT professionals who have average to good T-SQL skills already. This class assumes that you have a good understanding of SQL Server fundamentals. For example, you should know and use the SQL Server Management Studio (SSMS) interface and associated toolbox items before attending this class. It is not mandatory but some experience using the Business Intelligence Design Studio (BIDS) interface will be an advantage to you.

Day 1 – Administration and Performance:

Module 1: Deprecated and Discontinued Features
Module 2: ColumnStore Indexes
Module 3: DMV’s & Extended Events
Module 4: Security
Module 5: SQLOS
Module 6: Central Management and DRU

Day 2 – AlwaysOn and Disaster Recovery:

Module 7: Availability Groups
Module 8: Using Availability Groups
Module 9: Clustering Enhancements
Module 10: Disaster Recovery Enhancements

Day 3 – Programmability & Azure:

Module 11: SQL Server Data Tools (SSDT)
Module 12: Data-tier Applications (DAC Pac’s)
Module 13: T-SQL Enhancements
Module 14: FileTable and Full-text Semantic Search
Module 15: Introduction to Azure

Day 4 – Administering the BI platform:

Module 16: SSIS Administration
Module 17: SSAS Administration
Module 18: SharePoint Administration
Module 19: Azure Reporting

On sale: $495

Twitter – #Meme15

Logo for Meme15
It’s the middle of January already and most people are curled up under a blanket by the fire, trying to keep warm. I, however, live in Florida where it’s (relatively) warm and I’m putting together this #Meme15 post. #Meme15 is a blog series thought up by Jason Strate (blog | twitter) that gives us a place to discuss a common topic and how that topic affects us personally.

This month, we have been tasked with answering one, or both, of these two questions:

1. Why should average Jane or Joe professional consider using twitter?
2. What benefit have you seen in your career because of twitter?

Well, I joined Twitter back in 2009 and was basically using it to follow people that I thought were interesting (@SteveWoz, @LanceArmstrong, etc). You know – the way the rest of the world uses Twitter. I was looking to get more and more involved in the SQL community and Brent Ozar (blog | twitter) told me to look at connecting with SQL people on Twitter. I was sceptical at first because I wasn’t sure what SQL people tweeted about apart from how big their database was and how many times they had to shrink it per week. Boy, was I wrong. I quickly got connected with some very smart people who were friendly and helpful. I attended many SQL Saturdays and started meeting these people in real life and built up some great friendships within the community.

In 2011 I was fortunate to be able to go on the second SQL Cruise (blog | twitter) and had an absolute blast. If ever anyone feels like taking a great learning vacation, SQL Cruise is the way to go. I feel that the experience was enhanced due to the Twitter friends that I had made.

I returned from the cruise refreshed and a little bit smarter. Not long after that, I received a job offer from Pragmatic Works Consulting. I’d been discussing the opportunity with them for a few months by then and the time had come for me to take the next step in my career. I am currently involved in authoring the upcoming Professional SQL Server 2012 Administration book. I am also currently putting together a 4-day SQL Server 2012 training class and will be recording the class for SSWUG in the near future. I will also be presenting the material at SQL events around the country. The success that I enjoy is due, in part, to the help and support I have found from the SQL people I interact with on Twitter. Thank you to all those people from the bottom of my heart.

To some people, Twitter may just be a place where they can find out when and where celebrities are having lunch. To me, it is a network of like-minded individuals that interact with each other for the greater good of the community. Even if it’s mostly about bacon.

Spouting tripe! or, Why do I blog? #meme15

A great man once said “If nobody said anything unless he knew what he was talking about, a ghastly hush would descend upon the earth.”
That great man was Sir Alan Patrick Herbert and he CERTAINLY knew what he was talking about.

There are many, many, many, many brilliant blogs out there and I don’t want to just add to the “noise”. One of the things that I hope to accomplish, in general, is to be more of a Producer than a Consumer.
The Western way of life is all about product consumption – information, food, money, oil, goods. Mostly food, though. I mean money. I mean information, mostly information … Definitely information.

It’s not surprising that THE most important product (in my opinion) of recent times is the i – the Internet. The i has 2 things going for it: It’s BIG and it’s TOO big. You can find just about anything on the i. Well, actually, you CAN find anything and everything on the i. That is good and bad. On one hand, the i is good for finding help when you are at a loss. However, you can get lost down a rabbit hole of time-suck if you’re not careful. There are also some very, very, bad corners of the i that you should never, ever go. But, the basis of the i is the information.

It's starting to feel a lot like Christmas ...

It's starting to feel a lot like Christmas ...

One of the main reasons I blog is to share information that is different. One of the key characteristics that I want to foster for my blog is that of uniqueness. I don’t just want to rehash the same old thing that everybody else is saying. I hope to be fresh, new, exotic and exciting. Like a good toothpaste.

I have always considered myself a warm and friendly person. (I hope others do too, or I’m in trouble). Another reason I blog is to help people in a hospitable, yet fun way. I know that I have searched for help when I have been stuck on something and I hope that my blog will help somebody (maybe only many, many years down the road).

One of the most amazing things about Pragmatic Works is the commitment to the SQL Community. This is not just a cursory “Oh, Hai. How r U? Have a doughnut …”. This is a deep rooted commitment that is entrenched in the very culture of our workplace. It is actually in our job description to blog, speak and be involved in the community as much as possible (without interfering with our normal consulting duties). I count it a privilege to be a part of that.

So, let’s be careful out there in the unpredictable world of the i.
The main thing to remember is to “Believe none of what you hear and only half of what you see.” – Benjamin Franklin.

What #SQLFamily means to me

I have a brilliant story I would like to share with you. Go ahead, grab a seat.

Two very insightful database professionals, Thomas LaRock ( Blog | Twitter ) and Karen Lopez ( Blog | Twitter ) started the #SQLFamily meme on Twitter and Tom made #SQLFamily the subject of the most recent “Meme Monday” series. The SQL Server Team, moved by the outpouring of support for and by the community, has decided to contribute and keep the meme alive ’til the end of December. In order to keep with the theme of giving and sharing, they have pledged to donate $50 for each of 400 #SQLFamily stories that are submitted. This money is being donated to an extremely worthy cause – The Pragmatic Works Foundation, which is a non-profit that provides free technical training to veterans, the jobless, and underemployed. This is an awesome gift for the Foundation and for the people that are assisted by it. Thank you very much to the SQL Server Team.

On to my part in this story.

I took over the SQL Server administration duties at a previous company when the active DBA relocated (I’m looking at you, Brent !! ( Blog | Twitter )). When he left, I asked him where I could get some training and assistance for some of the SQL issues that I would encounter and he turned me on to SQL Saturday and Twitter.

I am not alone

I had been using Twitter infrequently at the time and only to follow certain people or companies that I thought were interesting. I started interacting more on Twitter and attending SQL Saturdays. I met some great #SQLPeople and soon realised that I was not alone – We all loved working with the data and databases, nobody knew everything, we all wanted to share something and we could all learn from each other.

It was when I went to Orlando SQL Saturday #49 that I realised that this was more than just a community of people who work near, on, with and, sometimes, under the same software. This was a family of people who cared about each other on a much deeper level.

I, personally, had welcomed 25+ “strangers” to my home for the first ever SQLBBQ before the SQLCruise in August 2010 and loved every minute of it. A few weeks later, my wife and I suffered the loss of a pregnancy in it’s 10th week. Here is the blog post about that. The outpouring of support from the SQL community was amazing !! We received messages of support and prayer from people across the globe. Most of whom we had never met. But that didn’t detract from the overwhelming feeling of love that we felt. You see, I have very little “real” family here in the US. I came over from South Africa in 2001 and my immediate family (parents and siblings) are still over there. My wife’s family is awesome and they help as much as they can. I’m extremely thankful that there is a huge multi-thousand strong SQLFamily to help each other through anything that is going on. During that SQL Saturday in Orlando, I was honoured to meet many people, like Mike Walsh ( Blog | Twitter ), that had been so supportive during that trying time.

Don’t worry, this story has a happy ending – I am ecstatic to be able to announce that my wife & I are expecting again. She is 30 weeks along now and we are preparing to welcome a new baby girl into our home on or about the 4th March.

I am extremely grateful to be a part of such a great community that is so caring. I am also happy that we can contribute to bringing new members into the fold through the great work of such places as The Pragmatic Works Foundation & the support of the SQL Server Team.

Disclaimer: I do work for Pragmatic Works Consulting but still would think that the Foundation is doing excellent work even if I didn’t.

How to get Gum out of MyHair

Well, here is a fun issue I ran into this week at one of our clients that I’d like to chat about.

Now, I may not have much hair to get gum stuck in, but some people have had this happen to them before.
But, because I am a database professional and I live inside of computers, I’d like to look at a virtual gum-in-hair problem.

First, Let’s have a look at a couple of tables that I’ve got here.
MyGum table
As you can see, MyGum is a nice gum product table that lists gum types, brands & colours, etc.

MyHair table
The MyHair table has the names of a bunch of people with hair types, colours and … well, would you look at this, some of them have gum stuck in their hair.

Oh, dear. Let’s do a quick query of the 2 tables to see what kind of gum is stuck in their hair …
Table join error
Uh, oh !! Error !!
“Wow, I’ve never seen a collation error when doing a plain join of 2 tables in the same database before. Isn’t the collation set at the server or database level ?” you might say.
Well, grasshopper, that is a good question. Collation can also be set at the column level. Here’s the article in Books Online that discusses column collation :

So, let’s look at the collation on these 2 tables
MyHair MyGum collation
Would you look at that … some bloody developer, dumb user, um, silly person has gone & changed the collation of the Gum_ID column.

O-Kay, that is easy to fix. All we need to do is right-click on the table in Management Studio and select “Design” from the drop down. Then, we look at the “Collation” property under “Table Designer” for the Gum_ID column. We delete the content to set the collation to database default or click on the ellipses to choose the specific collation that we would like. We click OK and click Save. Voila, we should be good to go. Let’s try our joining query again, shall we ?
Table join success

And THAT is how to get a little bit of collation Gum out of MyHair.

WARNING : You will need to make sure that no other columns have funny collation as you will need to fix them all.
WARNING 2 : You will need to be aware of the collation of TempDB, too, as using and joining temp tables will cause this same issue.