SQLServerPedia Syndication

MaTT: Entropy

Decided to take a detour today in my blogging thoughts (it is Friday, after all), and opted to revisit the issues of managing a technical team (MaTT); today’s topic: Entropy.

en·tro·py

/ˈentrəpē/

Noun

A thermodynamic quantity representing the unavailability of a system’s thermal energy for conversion into mechanical work, often…

Lack of order or predictability; gradual decline into disorder.

Simply put, processes decay over time. A good manager will recognize this, and learn to intervene when those processes begin to break down. In contrast, code lives on forever (unless someone steps in to change it).

Let me give an example; about a year and a half ago, I implemented Kanban as a method of managing our workflow in the production environment. My team quickly adapted to it, and it became very easy to see exactly what people were working on, and what was getting accomplished. I thought “my job here is done”, and moved on to other things, occasionally touching base with the Kanban board to see what was going on.

This week, I realized that the process had degenerated; sure, my team was still using the board to report what they had done for the day. However, they’ve gotten in the habit of solving the problem, and then creating the card in the Done pile. Entropy. I am no longer able to predict or measure what is being done or what needs to be done.

I’m out next week (hoping my son is born by the end of the week), but when I return, I need to intervene and work toward cleaning up the process. While I firmly believe that I should trust my team and encourage them to make the right decision, part of the management process is learning when to intervene in order to shore up a process that is decaying.

SQL Server & XQuery: XPath Axes, Node Tests, and Predicates

In order to really get a grasp on what’s going on within an XQuery method in SQL Server (like .exist()), we need to examine the relationship between XQuery and XPath. Semantically, there’s little difference in the usage of the two names, since XQuery as a language uses XPath to navigate through an XML fragment; however, strictly speaking, XPath is a subset of XQuery.

XPath expressions contain one or more of three elements: an axis, a node test, and a predicate. An axis refers to the locational reference of a path when navigating an xml structure. For example consider the following XML:

<root>
  <plant>
    <fruit>tomato</fruit>
    <fruit>banana</fruit>
    <vegetable>tomato</vegetable>
  </plant>
  <animal>
    <canine>Doberman</canine>
  </animal>
</root>

and the XQuery below:

/root/plant/fruit

based on the axis (path) supplied above, the results will be the two <fruit> nodes in the XML document. It’s important to remember that in the SQL Server implementation of XPath, axis expressions always start at the top of an XML document and work down, but there are some navigational shortcuts. Below is a small table describing the axes supported:

Abbreviation	Full Syntax	Notes
/	/	steps through nodes
//	//	steps through all descendant nodes
	child	returns a specific child of a given node; e.g. /root/child::plant is the same as /root/plant
..	parent	steps up a node; .. equivalent to parent::node()
@	attribute	identified attribute-based XML
.	self	refers to the current node
*	*	refers to all nodes at the current level; e.g. /root/* will return both the <plant> and <animal> nodes from our previous example.

A node test is a part of the location path that when used with an axis restricts the XQuery to return only the results of interest. Taking our same XQuery from above:

/root/plant/fruit

The axis is the “/” symbol which tells XQuery to walk down a node at a time, and there are two node tests: plant, and fruit. The first node test tells XQuery to only look at the plant nodes, and the second node test restricts it to just the fruit nodes. The above XQuery could be shortened to:

//fruit

All nodes would be considered, and all fruit nodes would be returned, regardless of where they live in the XML hierarchy.

The final expression in XPath is the predicate; a predicate limits the results of an XPath expression through evaluation (much like the WHERE clause of a SQL statement). Predicates are specified using square brackets, and allow for the use of several functions to return either a positional reference or an evaluation of a value or node. For example, the results of the following two XQuery expressions against our test XML document:

//fruit[2]
//fruit[last()]

would return the same positional results:

<fruit>banana</fruit>

likewise, the XQuery expression below:

//fruit[text()="banana"]

also returns the same results. last() and text() are both XPath supported functions, and are part of the subset supported by Microsoft SQL Server.

Summary

Why is all of this important? It’s dry, I know. However, the foundations of XPath allow us to do some pretty powerful querying of XML before handing the results “across the bridge” to SQL Server. Now that we’ve got the basics identified, we can continue our previous discussion of the XML methods in SQL Server.

SQL Server XQuery: .exist() method

I’m trying to beef up my writing about XQuery in advance of the upcoming PASS Summit 2013; I got accepted to present, and I want to make sure that I start covering a lot of topics related to XML so that I’ve honed my focus a bit. Unfortunately, blogging doesn’t come easy to me these days, and while I would generally start a session from the beginning and move forward, I think it would be best for me to start with something easy for me to write about, even though it means I’ll be doing a bit of backtracking later.

A little bit of background

As you may already know, SQL Server supports XQuery against an XML data type through the use of five basic methods:

.query()
.value()
.exist()
.modify()
.nodes()

Conceptually, each of these methods act a bridge between the the SQL processing engine and the xml parser baked into SQL Server. This concept of a bridge allows me to think about where query work is being performed; is it being done as a SQL statement against the table containing the XML fragment of interest, or is it being performed by XQuery before bubbling up to the SQL level?

.exist()

I want to focus on probably the easiest method against the xml datatype: .exist(). Simply put, this method checks to see whether or not the results of an XQuery expression against the supplied XML value are nonempty; it returns 1 if true, and 0 if false. Let’s start with a simple sample:

DECLARE @T TABLE ( ID INT, x XML )

INSERT  INTO @T
        ( ID, x )
VALUES  ( 1, '<root><plant><fruit>tomato</fruit></plant></root>' )
,       ( 2, '<root><plant><fruit>banana</fruit></plant></root>' )
,       ( 3, '<root><plant><vegetable>tomato</vegetable></plant></root>' )


SELECT ID, X, x.exist('//root') AS "Does It Exist?" 
FROM @T

Running this query will return the following results:

Every row has an XML value in the X column which contains a node named <root>. We can change the output to identify rows which have vegetable nodes like so:

SELECT ID, X, x.exist('//root/plant/vegetable') AS "Does It Exist?" 
FROM @T

The results then change to the following:

.exist() can also be used in the WHERE clause of a SQL statement; if we want to identify only the rows which have an XML fragment containing a tomato, we can alter the SQL statement like so:

SELECT ID, X  
FROM @T 
WHERE  x.exist('//.[text()="tomato"]') = 1

Note that the .exist() method interpreted an XQuery statement to look for nodes which had a text() value of “tomato”; two rows matched that requirement, so .exist() returned a 1 for those two rows to the calling SQL statement, and the WHERE clause identified those two rows.

Wrap-up

So, I’ve covered a few basic concepts while talking about the .exist() method:

The xml methods act as a bridge between XQuery and SQL for the xml data types.
The .exist() method supports XQuery expressions including both simple paths (//<node name>) and XQuery functions (e.g, text()). This will become important later when I get around to discussing FLWOR.

2013-06-19 by Stuart Ainsworth SQL Server SQLServerPedia Syndication XML 0

#SQLSATATL simply rocked…

So, last Saturday, I went to the first SQL Saturday in Atlanta that I had absolutely no responsibility on the actual day of the event. I wasn’t an organizer, I didn’t really even volunteer. As a chapter leader for AtlantaMDF, I do have to go in and pay the bills later this week, but for the most part, I got to walk around and revel in the day.

And it was a good day. The team (led by Geoff Hiten) pulled off another great event; 555 people attended the event, and there were some AWESOME sessions throughout the day. I was in the 9 am slot, and my session on Biggish Data went well, even if (as usual) I had WAY too many slides. Speaking of slides, you should be able to download the deck from the SQLSaturday 220 schedule (as well as many other great presentations). I did have to slip out early since this weekend was my weekend with the kids. My current youngest (Grace) had to get service hours for beta club, so she got to hang out with me, but I needed to get back home early to hang out with the oldest one.

So what was cool? What did I learn?

The number one highlight for me was watching Louis Davidson demo Red Gate’s SQL Monitor tool, and he chose one of my custom metrics to use in the show. I respect Louis a lot, and it made my day.
I learned that I need to cut out about 10 slides out of my presentation.
I had a great conversation with a few people about table partitioning and performance gains; I think there’s enough material for a future session explaining why you will or won’t see performance benefits from partitioning. Hmmmm….
I had another excellent conversation with Robert Cain regarding the growth of SQLSaturday’s, and bounced around a few ideas about moving to a mid-tier model. Kind of like what SQLRally was supposed to become, but different.
I also got to hang out with Andy Leonard. He and my Grace had a good time discussing movies (Where’s my super suit?).

Mostly, I just got jazzed about being back in the #sqlfamily. Hopefully, I can build off that momentum, and start blogging again. Granted, the next few months are going to be personally interesting, but I need to make time to share again.

2013-05-20 by Stuart Ainsworth Conferences SQLServerPedia Syndication The Social Web 0

Couple of upcoming presentations

I know; I suck at blogging.

Anyway, I have a couple of upcoming presentations this month, so I figured I needed to get back and gear and at least post a notice about them. First, I’ll be presenting at A Bunch of Devs (http://www.meetup.com/A-Bunch-of-Devs/) on the Red Gate development suite of tools. Funny story; I actually work in that building. The organizers reached out to Red Gate to see if they had a Friend nearby. I guess I qualified.

Next, I’ll be back at SQL Saturday Atlanta to present on Biggish Data; this is the first Atlanta SQL Saturday that I actually had almost nothing to do with (as a chapter leader, I helped with some basic decision, but very little). I’m excited that it’s continuing to thrive. Says a lot about the infrastructure that PASS puts behind these events; they just need a little help from the local chapters, but they don’t rely on the same person getting burned out year after year.

2013-05-07 by Stuart Ainsworth Conferences PASS SQLServerPedia Syndication The Social Web 0

#SQLSatATL SQLSaturday Atlanta 220–May 18, 2013

Just a quick note; SQLSaturday is coming back to Atlanta on May 18, 2013. This free (lunch is optional) event usually sells out way in advance, so you may want to go ahead and sign up. Also, the speaker lineup tends to fill up pretty quickly (call for speakers closes 2/24), so if you’re a SQL Rock Star or just want to share your knowledge, go ahead and submit your session as well!

2013-02-07 by Stuart Ainsworth Conferences SQLServerPedia Syndication 0

MaTT: You have to start somewhere

So, January came and went, and no post from me. I continue to suffer from writer’s block, but I’ve finally cobbled together enough ideas that I feel like I can put a couple of quick posts out there. I’m still struggling with learning how to manage a technical team (MaTT) as opposed to being a technical person, but here’s another concept I’m beginning to grasp.

You have to start somewhere.

When I began managing my team, I was tempted to rush in and save the day. I know where a lot of the problems are, and I know which ones are big, and which ones are not. I kept thinking that if I could just motivate the team to start attacking the problem, then we’d be sitting pretty in a year.

I was wrong.

Most people are already motivated to do their jobs; if they’re not, then they’re in the wrong position, and it’s hurting them as well as hurting the company. What a team is usually looking for in a manager is to help them make priority decisions, and to back them up when they need things to change. Here’s the conundrum: Technical people often (logically) focus on changing the things that hurt them the most. Your DBA may say “I keep having to babysit this query for the boss because it’s slow, and it’s slow because the developers don’t know how to normalize a database; can we refactor everything?”. Your developers may say “If we had more time to rebuild everything, we could fix that query; can we hire another person or put these features on hold so we could tune everything?”. Your boss may say “We need to make a profit; what’s the most cost-effective way to manage our database architecture moving forward?”

If you focus your energy on addressing the technical issues without giving consideration to the profitability of your company, you’re spinning your wheels. However, you MUST figure out a way to ease the pain of your technical team by addressing their concerns. It’s no secret that I’m a Lean/Agile kind of guy; I truly believe that these philosophies can help balance the scales between support/development/operations, but you have to start somewhere.

“Somewhere” for me is the small fixes; don’t tackle the big projects head-on. Focus on being successful at a couple of small things (e.g., reduce the number of support calls in a measurable fashion, or refactor some small but essential bit of code). Make sure that these changes are measureable, and make sure that you report the changes to your boss AND to your team. This accomplishes two things:

It shows measurable progress (in my experience, bosses love metrics), and
It encourages your team by giving them a couple of quick successes.

If your team doesn’t see progress, then in their mind, “nothing is being done”. Your boss wants to know how you’re improving efficiency and effectiveness in terms of dollar signs; your team wants to know that they’re solving a technical problem. Make sure you can address both of those needs.

More to come.

2013-02-06 by Stuart Ainsworth Professional Development SQLServerPedia Syndication 0

Transitioning into Management; lessons learned…

This has been a busy year for me; apart from all of the personal news, I’ve spent the last year feeling out my new position as a manager. While is not my first job as a manager (I spent about 3 months as a manager working for a department in a death spiral in 2002), it’s been a bit of a bumpy ride. I’m learning as I go, which is a great skill to have as a technical person, but tricky in a management position. Here’s a short list of things I’ve learned over the last year, and I hope it’ll be useful to those of you that are making that transition yourself:

1. These ain’t the problems you’re used to solving.

As a database person (DBA, Developer, Architect), I was very accustomed to tackling technical challenges on a daily basis. Disk running out of space? Check for unexpected log growth. Bug in a procedure? Rewrite it and test. I just assumed that as a technical manager (managing a group of DBA’s) that I would continue in that vein, just with more help and more paperwork. I was wrong (so wrong).

You see, most of the problems I face today aren’t technical; they’re people and processes. I’ll talk more about the people in a bit, but the process issues stem from things like "we can’t get this bug fixed because the devs are busy writing new features” or “I have 35 things to do on my top priority list; which one do I do next”. As a manager, my primary obligation is not to make sure that technical problems are addressed, but rather to make sure that there are no impediments for my team to implement technical solutions.

2. The people in your department aren’t you.

And they don’t solve problems like you do; they have their own systems, answers, and methods to get to those answers. While it may sound conceited, I think I was a damn good database person as an employee, and I think it was those skills which ultimately led to this opportunity. However, I have quickly learned that not everyone approaches problems the way I do, and I can’t simply jump in and solve the technical problem for somebody. It’s tough, especially when I see that one of my employees took a few hours to google something I knew how to do blindfolded, but I have to let them learn at their pace (and encourage them to learn faster).

One thing I have learned is that I have to make time to pass on my skillset, and that’s not a one-time thing. I need to spend some recurring time collaborating with my team so that I can step away from solving the technical problem and focus on the impediments (see rule 1 above).

3. Don’t let the drama get to you.

As an employee, it was fun to be snarky. When something (or someone) ticked you off, you could go ahead and rant about how stupid the decision was or how bad the system sucked, all the while knowing that you would be doing your best to fix the problem the next day. Your words didn’t really mean much; it was just a way of blowing off steam. As a manager, other people are watching you, and you can’t blow off steam (in front of them). Compounding the problem is the fact that as a manager, people come to YOU to rant. All you can do is listen and try to get to the core of the problem; again, if there’s a real issue where it looks like the train is going off the tracks or your team can’t accomplish it’s objectives, then you need to be able to delve into that issue and resolve it. But you can’t participate in their rant session; listen, decipher the issue, and then take action.

4. Find time to stay technical.

As an employee, I can tell you that it’s tough to work for a manager that doesn’t understand what you do; don’t become that manager. I can’t stay abreast of every specialized aspect of SQL Server, but I can keep up with the gist of things. I may not be up to speed on the latest and greatest design patterns, but I need to be able to have a conversation with a developer and understand what they’re trying to do. I’ve recently started studying for the MCSA on SQL Server 2012; I haven’t been certified on a platform since SQL Server 7. I feel it’s important to show my guys that I’m trying to stay a technical resource for them, and also encourage them to pursue more knowledge. The expectation in our department is that research is part of our jobs.

I used to dream about being an Microsoft Certified Solutions Master; I put that dream on hold so I could take care of my personal life, and now it looks like I’m walking another path. It doesn’t mean that I can’t be a technical person; I just will have a broader repertoire.

5. Change is hard; get over it.

I just got back from the first week of Model-Netics training which is offered by my employer in a four week series (over four months); one of the first models we talked about was the change curve, which looks something like this:

The idea is that everybody is plodding through life at a certain level of productivity and morale, and then a change occurs (a promotion, a formation of a new department, a new process gets implemented, etc…); suddenly everything is askew, and productivity and morale go sliding down the mountain toward the valley of despair. At some point, acceptance will set it, and if the change is a positive one, productivity and morale should settle in at a new high.

Scholars will note that the change curve is widely used in terms of the 5 stages of grief, but the point of it is that normal acceptance to a change takes time, and a realization that things are different and you’ve got to adjust to the new normal. I went into this management position thinking that I could quickly bounce into doing the things I wanted to do, without realizing that it was going to take some time for me to adjust to my new responsibilities. I also had to balance the productivity and morale issues in my new department (as well as my old one). That realization is the basis for transitioning from a slide into a climb.

It’s been an interesting first year, and I’m looking forward to the new challenges ahead. I’ve never worked so hard in my life, but yet I feel like my job is important to me, and at the end of the day I can put it down and focus on other things. Life is about more than a career.

2012-11-20 by Stuart Ainsworth Professional Development SQLServerPedia Syndication 5

post #summit12 write-up

Everyone does one, so I figured I should Smile Unfortunately, I barely recovered from my trip to Seattle, and am now rushing out the door to Dallas for company training, so I’m afraid this will be brief. I do intend to blog more in the future (promises, promises), but for now, here’s the highlights:

Day 1 Keynote rocked; it looks like SQL Server vNext will be an awesome release for DBA’s. 2008 and 2012 brought a lot of cool things to the table development/BI – wise, but less love for administrators (IMHO). Hekaton will change that.
I was really burning out on the whole chapter leader/SQL Saturday/community activist thing; three days in Seattle changed that. We’re now planning Atlanta SQLSaturday 2013 (woo-hoo!!)
The Chapter Leader meeting format was very effective, and a big difference from previous years.
Session planning seemed a little “off” this year; too many people trying to cram into too small a space. I missed some good sessions because the rooms were too full.
I got bit by the cert bug, and passed exam 70-461 Querying Microsoft SQL Server 2012 on the second try. I flunked it on Wednesday, got pissed off about it, crammed that night, and passed it on Thursday. More on that later.

All in all, it was awesome. I had a great time reconnecting with people, and I’m looking forward to the year to come. Gotta run; the Atlanta airport is an hour away. I have no clue how you road warriors do this….

2012-11-12 by Stuart Ainsworth PASS SQLServerPedia Syndication The Social Web 0

Finally putting together my #SQLPASS Summit schedule

It’s hard to believe that the Summit is next week; I’m just now pulling together my schedule, and I’ve got some tough choices to make. I have noticed a pattern, however; this year, I’m going to sessions for new features and possible solutions as opposed to “OMG, I have to solve this problem NOW” kinds of sessions. I’m also going to sessions where I think my team should get more knowledge, not just the things that interest me. I guess I’m finally growing into this management role.

Below is my tentative schedule; see all the red? That’s double-bookings, or even triple booking. Sigh. I’m also planning on getting enough liquid courage this year to SQLKaraoke (although many of you may wish I don’t).

Wednesday, November 07, 2012
10:15 AM – 11:30 AM	[BID-100] PowerPivot 101	[RM:608]
10:15 AM – 11:45 AM	[DBA-321-S] Extended Events: Work Smarter Not Harder	[RM:615-617]
1:30 PM – 2:45 PM	[LT-100] Regular Expressions in SSMS in 10 Minutes or Less	[RM:307-308]
3:00 PM – 4:15 PM	[BID-203] Developers Are from Mars, Report Servers Are from Venus	[RM:608]
4:45 PM – 6:00 PM	[AD-305-M] Practical Uses and Optimization of New T-SQL Features in SQL Server 2012	[RM:602-604]
4:45 PM – 6:00 PM	[BID-205-M] Building Self-Service BI Apps Using PowerPivot and Excel	[RM:609]
4:45 PM – 6:00 PM	[BIA-208] Building a Tabular Model Database	[RM:613-614]
4:45 PM – 6:15 PM	[DBA-408-S] Choose Your Own Adventure: Performance Tuning	[RM:615-617]
Thursday, November 08, 2012
10:15 AM – 11:45 AM	[AD-321-S] Storing Columnstore Indexes	[RM:615-617]
1:30 PM – 2:45 PM	[DBA-201] How to Defragment Indexes for Peak Performance	[RM:618-620]
3:00 PM – 4:15 PM	[BID-210] Bad Reports: Fixing Their Mistakes	[RM:613-614]
3:00 PM – 4:30 PM	[BID-308-S] Exploring PowerPivot and Power View in Excel 2013	[RM:606-607]
5:00 PM – 6:15 PM	[BID-101] Creating a Power View Reporting Solution	[RM:606-607]
5:00 PM – 6:30 PM	[DBA-320-S] Diagnose T-SQL Performance Problems Fast with sp_Blitz	[RM:618-620]
5:00 PM – 6:15 PM	[DBA-304-M] Load Millions of Records in a Split-Second with Partition Switching	[RM:609]
5:00 PM – 6:15 PM	[PD-300] Providing Effective Feedback	[RM:307-308]
Friday, November 09, 2012
8:00 AM – 9:15 AM	[BIA-313] Power (View)ful Tabular BI Semantic Model Development	[RM:611]
9:45 AM – 11:00 AM	[AD-308] Geospatial Data Types in SQL Server 2012	[RM:302-TCC]

2012-11-01 by Stuart Ainsworth Conferences PASS SQL Server SQLServerPedia Syndication 0

en·tro·py