August 2011 – Curious..about data

Book Review – SQL Server Execution Plans

Let me start by saying that I love books published by Red Gate software. They are concise, easy to read and address specific areas that are often buried in chapters of big books. This is the first book I have read on a much needed area of information – Execution Plans. I make notes as I read books, especially to understand new things and perhaps read more/experiment more..this time I tried to turn my notes into a book review. It is perhaps more of what I learnt from the book than a review per se but I felt it was worth recording.

The book is for beginner-intermediate level DBAs/database programmers who are attempting to understand how to read, interpret, and experiment with execution plans in SQL Server Version 5 and above.

The author is Grant Fritchey aka Scary DBA (http://scarydba.com).

Chapter 1 Execution Plan Basics
This chapter deals with processes that occur in relational and storage engine – parsing of the query followed by algerbrizer for name resolution.The output is called a query processor tree – and passed on to the query optimizer in binary form. The priority of the optimiser is the low cost plan that it can get to in the shortest time.The plan is stored in the plan cache and compared to previous plans, if it finds one similar it uses that, otherwise it keeps the new one. The reasons why actual and estimated plans are sometimes different is one of my favorite parts – and I was particularly enlightened by the fact that parellelism as used by the storage engine during actual execution can be one of the reasons for this.There is a lot of detail on reading graphical plans.In particular the information available on hovering over each operator was interesting and helps learn of two things, cached plan size (and how it can help with performance cache issues) and also that the costs are just numbers that indicate where the processing overload is (I/O or CPU). I also learnt that value of ‘Ordered’ (0 or 1) clause indicates if data is ordered for that operation or not(somehow thought it meant if data was ordered in general), and node id starting left to right gives the order of processing. I was a little confused around estimated rebinds and rewinds – which are explained as number of times init() operator is called (I did not quite understand what that meant until i got to page 82 where it is explained) The rest of the chapter covers familiar ground on capturing xml and text execution plans via profiler and query analyser.

2 Reading Graphical Execution Plans for Basic Queries
It was interesting to know of 79 types of operators and 4 kinds they fall into – DML based logical and physical, parellelism based, cursor operators and language elements. The most common ones are the DML based.There is also an interesting table with 20 types of common operators. I learnt also that there are two types of operators, blocking and non blocking – the first of which require all the data before execution, and second executes as it receives the data. Blocking operators lead to concurrency issues at times. Some examples of which operator is which would have helped a lot here.
The operators covered in this section are familiar for the most part. It was also interesting to learn of the reasons why a hash match join is selected – a missing or incorrect index and/or a missing/non sargeable where clause.A nested loop join may be the most efficient join for smaller to medium queries. Also a merge join may not be ideal if the data is not sorted before joining. I was momentarily misled by the sentence also that ‘if the order by clause does not specify an order the default order is ascending’..I thought it meant the rows would return in ascending order with no order by which is not true. I got it right on re reading it but perhaps this could have been stated as ‘if the order by clause does not specify order as ascending or descending the default order is ascending’. I have encountered the generation of sort warnings if data is too big to be sorted in memory as an indication of more RAM – was glad to find that confirmed.

Rebinds and Rewinds have always puzzled me and found the explanation very clear. Along with this is also a clear explanation of the init,getnext and close methods.Higher rebinds/rewinds pointing to potential high I/O is a very useful tip.

3 Text and XML Execution plans for Basic Queries
There is quite a bit of info on how to read a text based execution plan. I didn’t read this too much since it is going to be deprecated in future versions. On the XML Based execution plan – it was interesting to note the differences and additional information that an XML plan provides over graphical – such as estimated number of rewinds and rebinds.The actual xml plan also shows information such as DegreeofParellelism,MemoryGrant and Runtimeinformation.

4 Understanding more complex Query Plans
This chapter begins with analysis and explanation of a merge join – how it combines input from two clustered index scans and performs the operation, and why the optimiser picked merge join over hash join. There is also a great comparison of one query written in traditional sub query form and also using cross apply operator..with analysis of plans and why cross apply was more efficient. It helped me understand how to analyse comparative plans for efficiency in similar situations.It was also interesting to learn that queries that use aggregates make better candidates for indexed views, and that the optimiser can actually choose between using a plan with materialised view or using the indexed view as a regular view (=materialising during execution).

5 Controlling Execution Plans with Hints
Even before I write this must confess one thing, am not a huge fan of hints.I have dealt with several developers who insist on using hints as shortcut to fixing their query or looking into table design/indexing issues..and a DBA has to be very careful of some of them. But I do admit that hints have their place, especially when you have a lot of code that is not easily changeable. The hint I have found most useful by far is FORCE ORDER since we deal with lot of very uneven data distributions where I work..the way that hint sometimes cleans up an otherwise messy plan was reconfirmed.It was also interesting to note how the ‘keep plan’ hint works.It works by treating temporary tables as permanent tables to minimize recompiles on the query.

6 Cursor Operations
In this chapter I learnt about operators used in cursor operations. I found it extremely interesting and useful – to learn of purpose and difference between logical operators and physical operators as related to cursors. Logical operators are to do with creation, opening, closing and deallocation of cursors, while physical operators are involved with actual processing for cursor records.The generic ‘C’ operator displayed during an estimated execution plan involving cursors has often baffled me but now I clearly understood what it was and why actual plans looked much more informative when it came to execution. There is also an example of comparing the different cursor types – static,read only and keyset cursors for performance. This is definitely an experiment worth trying on cursor based code.

7 Using XML in execution plans
This chapter gives a general overview of xml based commands and how they work.

8 Advanced Topics
The first thing I liked about this chapter was the explanation on how to read large plans using the ‘+’ operator and zooming in specific areas. I have several large plans to deal with and usually opt for the XML version of the plan while reading them – so this was a very good tip to know. It was also interesting to know the optimizer’s decision process when it comes to using parellelism. Apparently
there are two plans created when it decides parellelism is usable – one with parellelism and one without. The usability is then checked every time the plan is used and the appropriate version is used depending on number of threads and system load. I found this a great tip to know. The rest of the chapter is overviews on Forced parameterization and plan guides

The only real criticism I would have of the book is that the screenshots of execution plans are tiny and very difficult to read.

All in all a great informative book and looking forward to read the next version.

This book is available on :

http://www.amazon.com/Server-Execution-Plans-Grant-Fritchey/dp/1906434026/ref=sr_1_1?s=books&ie=UTF8&qid=1314571704&sr=1-1

Working the net…

One of the key lessons I have learnt from PASS conference is the art of networking. I am not a shy person, but I normally need time to get to know people – given a setting with a lot of strangers I usually keep to myself and wait for someone else to break the ice or prefer to say nothing and get done. To be pro active and introduce myself is itself a big step forward for me, as it may be for several other people. My do’s, don’ts and icebreakers are as below –

Some of the ‘Do’s –

1 Always use meal times and coffee breaks as opportunites for conversation. In other words don’t try to dig hard into the schedule or read something else at this time.
2 Carry business card at all times..you never know when the opportunity may arise.
3 Visit vendor pavilions, talk to vendors if you have liked something they had, even a free tool.
4 Go to as many before and after events as possible. The schedule book you get upon registration has a list of them that happen during the conference..and more are available by following the right people on Twitter (@sqlpass for PASS, @sqlandy – Andy Warren).
5 Respect how you feel and don’t try too hard – some of us are introverts by nature and feel drained after some degree of socialising..if you feel the need to withdraw respect that and retire to the room or elsewhere, don’t force any conversation – networking is the last thing that works better with ‘harder’ effort, it has to be natural and simple, with some effort but not forced or difficult effort.

Lines to use..
1 Easiest one – ‘My name is …’

Follow this with an open question as much as possible – a question that leads to conversation, not a yes or no answer –

1 ‘How do you find the conference’
2 ‘Been great (or lousy) weather…’
3 ‘I like the fruit at breakfast…’
4 ‘I am looking forward to …..session today’
5 ‘I see you are from…(usually readable on person’s badge’)..

Some of the don’ts –
1 Being un intuitive – look if the person looks approachable or the situation suits a conversation.If someone is pre occupied,talking to other people,working on their laptop or just does not look approachable – respect how you feel and do not approach them. There are times when appearances are deceptive, but it always pays to respect how you feel first.

2 Interrupting -One of my favorite books on networking is Don Gabor’s ‘How to start conversation and make friends’ – I met Don at the conference last year and learnt a lot from his book and session. One of the things he says is about how to join a group. Stand at a noticeable distance but not too intrusively, for a few minutes – if the key speaker is open to including others he will automatically make eye contact and include you, if not it is probably a private conversation or the speaker is not interesting in expanding it.But avoid just getting into group conversations uninvited, especially if you do not know the key speaker and participants.

3 Avoid lame lines – this is my favorite and has been a repeated lesson to me..if there is something substantial to tell someone about their book, blog or talk then it very much helps to do so. Many people like simple compliments also that you liked their book or their talk, but that is not something one should over do..when you say you like a book people like to know on what you liked and why. Otherwise this can sometimes results in an embarassing silence with the speaker expecting you to say more and you not knowing what to say.

Useful book:

http://www.amazon.com/How-Start-Conversation-Make-Friends/dp/1451610998/ref=sr_1_1?ie=UTF8&qid=1314039640&sr=8-1

Right and wrong reasons to attend

‘This community thing is not for me’

I got an answer like this from a long time friend of mine. He was a regular attendee at our user group meets, attended the PASS conference a couple of times, and now was running his own consulting company and doing well. He had had some bad experiences – his speaker submission was turned down once, and there had been some altercation with some celebrities. More than all that his reason was that he was ‘not much of an extrovert’ and ‘he’s already checked out Seattle’.

His reasoning around being an introvert was a little like mine, several years ago. I got into computers for that exact reason – they were lot more easier and predictable than people!! But I didn’t get very far being that way. My friend Andy Warren says he is about being extroverted for professional reasons and a private person otherwise. I think that is the best combination of behaviors I would aim for. If I don’t get out of my comfort zone a little and get to know others, they don’t get to know me. I would lose opportunites that I do not even know existed, and I would not know whom to approach or how if I ever needed help. Now I think majority of us who have the ‘introvert’ reason are just too comfortable with our ‘comfort zones’ or devalue the benefits of getting out of it a little.

The reasons this person had led me to think about the right and wrong reasons to attend a professional conference like PASS community summit.

The right reasons according to me –

1 Learn more – hear what experts have to say, particularly on new/upcoming features,also on explorations into specifics of other features.

2 Make new friends – get to know more people like me, more dba’s, more user group leaders, understand their problems, challenges and successes.

3 Understand the direction the product and technology is evolving – particularly from keynotes and also from content of presentations.

The wrong reasons –

1 Expecting networking to work in magical ways – someone offers you work/a job, a celebrity becomes best friends…these things happen…but normal ways networking works is that it creates an initial familiarity and friendly exchange, that is all. Sometimes that can evolve into deeper friendships, sometimes it just stays as a business card exchange. But the returns are gradual, not instant or magical. To my introverted friend these initial steps itself are a huge effort and he feels disappointed that ‘it is not working out’.

2 Looking for instant answers to some kind of technical problem – it is possible to get solutions to lot of issues, by asking questions at the right session. The microsoft ‘clinic’ at the Summit even allows you to take your laptop in and they work with you through the problem, for free. I once got help on a somewhat difficult replication issue this way. But again, it is not always possible and some answers are really not instant, they require a paid consultant to look deeper into a problem. It is a good idea not to promise your boss or anyone that you will come away with instant answers to some difficult problem – that may or may not happen. You will, however, get plenty of guidelines and pointers to the possible solution(s).

3 Getting priorities in wrong order – I do all my holiday shopping in Seattle. In fact am so used to it that am not sure where to shop when the summit moves elsewhere. But my first priority is the conference, not shopping. I use that as an opportunity to do shopping ,which is fine – but like my friend said if checking out ‘cool’ places is your priority that makes the gains out of the conference secondary.

4 This might seem funny…but looking for information on a really old version…last year i was asked at the user group by someone if the summit would have content related to SQL 2000. We are 3 versions after SQL 2000 and this year into the 4th. Most speakers would be more than happy to say if what they are talking of is SQL 2000 compatable…but to expect content on a product that old is somewhat unreasonable..technology is about staying up to date and most information would be related to newer content, not old.

Check to see if your reasons are in line 🙂

Excuse me…I am new!

My first PASS summit was 7 years ago,at Gaylord,Texas. I was an accidental DBA of 3 years, back then. I was not in a happy place, career wise. I had worked very hard to develop good DBA skills and was doing well managing their database servers. They had assigned me another role that was unrelated to my skillsets… The writing on the wall was clear, for me and several others… ‘Leave if you don’t like it’…

I was not sure of finding a job too quickly, and did not want to end up fired..It was in this situation that I accidentally clicked on a link to PASS from the Microsoft technet website. I saw this conference happening near Dallas. I thought it would help me to atleast go and find out the type of careers and future available with SQL Server experience. Dallas was not expensive in terms of airfare. The entire conference fee was not affordable to me, but I could take a small loan from the bank and manage two days. So I took two days off and went to attend the conference, entirely on my own.

Most of the things I learnt were way above the basic features I used..but it encouraged me to know that there was so much to this product and I could get ahead if I put in effort and time into learning it. On the last day I was wandering the hallways, feeling tired and fed up at the thought of getting back to work. There were two people sitting at a table, a bald guy with a friendly smile and another lady. They had some fliers and information on volunteering for the community, that looked interesting. I picked up courage to walk up to them and said ‘Excuse me, am new, could tell me more on what you do’. They asked me where I was from, and I told them. The bald guy asked me if I knew of a user group for SQL Server in that area. I had done my research on this and knew for a fact that there was none. So I nodded ‘no’. ‘It might be a good idea for you to start one’, he said without hesitation. A big crowd of negative thoughts filled my mind…‘Me, start a user group? Why… I did not know anyone in town aside from the people I worked with, I was an immigrant, not terribly extroverted by nature…so I was not sure where to go with the idea. But atleast he seemed to think I could do it. At a time when my morale was particularly low that was in itself encouraging. He gave me an application to fill out, along with his business card and asked me to call if I had any questions. I shoved the papers into my bag and didn’t think about it until I got home.

When I got home I put together some of the things I knew and some of the things I had learnt…into a resume’. In 10 days I had landed a new job as a DBA at another place. In the gap between the two jobs I looked at the paperwork and the business card again. Purely out of instinct I filled in the information and faxed the paper work to PASS Office at Chicago. PASS offered to do the first mailing for me, and our first UG meet was set to happen at the local public library.I picked up courage to call the guy who had given me the business card – he gave me 3 MVP’s contacts. I wrote to them and one of them agreed to speak. So started the first UG meet – with about 10 people in the room. Then meetings started happening regularly, and we moved from the library into a conference room at an office. Membership slowly went up to 100 people or so.

To cut a long story short, I landed my present job with the aide of someone who came to my UG meeting.He was impressed by the questions I asked a certain speaker, and gave me his boss’s email to write to. I was asked to come in to talk to his boss and two days later, offered a job with considerably more money and allowances than what I had. I was not the speaker, I was not an MVP , or a blogger, any kind of celebrity – just an ordinary person in the audience who asked some smart questions, that is all. And a user group lead who organized some meetings and helped him find some community.

At every conference I end up meeting more smart people…and more people who inspire me to succeed with their humility, tenacity and hard work. Brent Ozar sat at a breakfast table with several others at the conference in Denver..introducing himself just as a ‘sql guy from Chicago’. Kathie Kellenberger did her first session on ‘how to do efficient meetings’ with only 10 people or so in the class room, was one of them…. Andy Warren took several hours of his time to inspire us to start SQL Saturdays..and now we are into SQL Saturday #3…Kevin Kline was the first ever celebrity who cared to speak for a small user group like ours, driving all the way from Nashville. And not enough can be said ’bout the smiling bald guy – Rushabh Mehta – because of whom we have a thriving user community where I live. The list is long..and never fails to inspire. At every conference I meet and come away with more contacts and more experiences to learn and grow from.

As Arnie Rowland rightly said the really important word is ‘Community’. There is a lot one can learn technically, there is also a lot of fun one can have in a city like Seattle. But the real benefit of the conference comes with community..the people you meet..who inspire you, support you and encourage you towards your goals. At every conference I look for people like me, standing by with the line ‘excuse me, I am new’…this time am really happy to be guiding new comers in a more organized way…and also share more on how to get the best, and learning more in the process myself. And am really happy to start my new blog with a post honoring the great community that am proud to be part of.