T-SQL Tuesday #081: Sharpen Something

Comments: No Comments
Published on: July 27, 2016

Sharpen Something

It has now been 30 months since the last time I hosted a TSQL Tuesday, that was TSQL Tuesday 51. I recapped that event here with the original invite here. I can’t believe it has been that long since I last hosted. It only seems like yesterday.

sqlskillsharpener_pigComing into the present day, we are now at TSQL Tuesday 81. For this month, I would like to try and up the ante a bit. Usually we only get about a weeks notice prior to the event to think about the article to write for the event.

This time, I want to invite everybody just a little bit sooner and will follow-up with a reminder seven days prior to the event. The reason I want to do this is because I think this may be a touch more difficult this time.

 

This month I am asking you to not only write a post but to do a little homework – first. In other words, plan to do something, carry out that plan, and then write about the experience. There is a lot going into that last sentence. Because of that, let me try to explain through a few examples of what I might like to see. Hopefully these examples will help you understand the intent and how this month the topic relates to “Sharpening Something“.

EXAMPLES

  1. You have learned about a really cool feature called Azure DevTest Lab. Having heard about it, you wish to implement this feature to solve some need in your personal development or corporate environment. Develop a plan to implement the feature and tell us the problem it solves and about your experiences in getting it to work from start to end. An example of how I might try to use this might involve the creation of a disposable and easy setup environment for Precons, Workshops, and various other types of training.
  2. There is a really awesome book about SQL Server you heard about and you decided to buy it. Plan to sit down and read the book. Take a nugget or two from the book and tell us how you can use that nugget of information within your personal or professional environment.
  3. You know you are extremely deficient at a certain SQL Skill. Tell me what that skill is and develop a plan to get better at that skill. Report on the implementation of this skill and how you are doing at improving. Maybe that skill is about Extended Events, PoSH or availability groups.
  4. Similar to the skill deficiency, you know you do not understand a certain concept within SQL Server as well as you feel you should. Maybe that concept is indexing or statistics (for example). Create a two week plan to become more proficient at that concept. Follow that plan and report on your progress.

In recap, this is an invite to make a short term goal covering the next two weeks. Tell everybody what that goal is (in your tsql tuesday post of course) and how you went about creating a plan for that goal and how you have progressed during the two week interval.

What is T-SQL Tuesday?

TSQL2sDay150x150T-SQL Tuesday is a monthly blog party hosted by a different blogger each month. This blog party was started by Adam Machanic (blog|twitter). You can take part by posting your own participating post that fits the topic of the month and follows the requirements below. Additionally, if you are interested in hosting a future T-SQL Tuesday, contact Adam Machanic on his blog.

How to Participate

  • Your post must be published between 00:00 GMT Tuesday, Août 9e, 2016, and 00:00 GMT Wednesday Août 10e, 2016.
  • Your post must contain the T-SQL Tuesday logo from above and the image should link back to this blog post.
  • Trackbacks should work. But, please do add a link to your post in the comments section below so everyone can see your work.
  • Tweet about your post using the hash tag #TSQL2sDay.

You Deserve to be an MVP

Categories: News, Professional, SSC
Comments: 2 Comments
Published on: July 25, 2016

I have been sitting on this article for a while now. I have been tossing around some Microsoft_MVP_logo_thumb
thoughts and finally it is time to share some of those thoughts with the masses. I hope to provoke further thought on the topic of being an MVP.

I want to preface all of these thoughts first by saying that I believe there are many great people out there who are not an MVP who deserve to be an MVP. These are the types of people that do a lot for the community and strive to bring training and increased knowledge to more people in various platforms under the Microsoft banner.

Now for some obligatory information. While it is true I am an MVP, I feel obligated to remind people that I have zero (yup that is a big fat zero) influence over the MVP program. I am extremely grateful for the opportunity to retain the position of MVP along with all of the rest of the MVP community (there are a few of us out there). Not only am I grateful to the program for allowing me in, I am also grateful to all of those that nominated me.

Work – and lots of it!

mvp_banner

One of the first things that strikes me is the nomination process for the MVP program. There are two parts to the process. The easy part comes from the person making the nomination. That said, if you are nominating somebody or if you are asking somebody to nominate you, read this guide from Jen Stirrup. Jen has listed a bunch of work that has to be done on the part of nominator. Or is it work for the person making the nomination?

When you really start thinking about it, the nominee is really the person that needs to do a fair amount of work. Yes, it is a good amount of work to do. Then again, maybe it is not very much work for you at all.

One of the things that really bugs me about the process is all of this work. Not specifically that I get the opportunity to do it. No, more specifically that there seems to be a growing trend in the community of entitlement. I feel that far too many people, that do a lot within the community, feel they are entitled to being accepted into the MVP program. And of course there are others that do much less and also exhibit the same sentiment.

Entitled?

When you feel you deserve to be an MVP, are you prepared to do more work? I have heard from more than one source that they will not fill out all the extra information requested when they are nominated. The prevailing reason here being that they are entitled, because they do some bit of community work, to be automatically included. Another prevailing sentiment, around this extra work, is that Microsoft should already be tracking the individual and know everything there is to know about the contributions of said individual.

These sentiments couldn’t be further from the fact. If you are thinking along the lines of either of these sentiments, you are NOT an MVP. There are a ton of professionals in the world doing a lot of community activities who are just as deserving of becoming an MVP. It long_resumeis hardly plausible for Microsoft to track every candidate in the world. Why not tell them a bit about yourself?

RESUME / CV

When applying for a job, how do you go about applying for that job? Every job for which I have ever applied, I have needed to fill out an application as well as send a resume to the employer. I hardly think any employer would hire me without knowing that I am interested in the job.

That sounds fantastic for a job right? Being an MVP surely has no need to send a resume for that, is there? Well, technically no. However, if you treat your community work like you would treat any other experience you have, you may start to see the need for the resume just a touch more. When nominated, you are requested to provide a lot of information to Microsoft that essentially builds your resume to be reviewed for the MVP program.

One of the prevailing sentiments I have heard from more than one place is that filling out all of this information is just bragging on yourself. That sentiment is not too far from reality. Just like any resume, you have to highlight your experiences, your accomplishments and your skills. Without this kind of information, how could Microsoft possibly know anything about you? Do you have the paparazzi following you and sending the information along to Microsoft for you? If you do, then why even bother with the MVP program? Your popularity is probably on a bigger scale than the MVP program if you have your own paparazzi.

Invest in your Professional Self

resume_wordcloudThe more effort you put into your candidate details the better chance you have at standing out within the review process. Think about it this way, would you turn in a piece of paper with just your name on it for a job? Or…would you take hours to invest in your personal self and produce a good resume that will stand out in the sea of resumes that have been submitted?

If you ask me to submit you as an MVP and I do, I would hope that you complete your MVP resume (candidate profile) and submit it to Microsoft. If you don’t take the time to do that, then I would find it hard to ever submit you again. The refusal to fill out that information speaks volumes to me and says either you are not interested or think too much of yourself for the MVP program.

Leadership

One of the attributes of an MVP is that of leadership. A simple measure of leadership actually falls into the previous two sections we just covered. If you are contributing to the community, that would be one small form of leadership. If you are willing to follow, that is also a form of leadership. If you are able to complete your information and submit it, then that is also an attribute of leadership.

Leaders demonstrate their leadership by being able to take direction, teaching others (community work), completing tasks when necessary, and reporting back up to their superiors on successes and failures (the last two can be attached to the completion of the nomination data).

Don’t believe me about leadership being an attribute of an MVP? Take a gander at this snippet from my last renewal letter. Highlighted in red is the pertinent sentence.

MVPrenew15_leader

You can run the phrase through a translator or take my word for it that it pertains to exceptional leaders in the technical community.

It’s not a Job though

I am sure some of the pundits out there would be clamoring that if the MVP program were an actual job, then they would perform all of the extra work. I have two observations for this: 1) it speaks to the persons character and 2) MVP really is more like a job than you may think.

The MVP program is not a paid job and probably falls more into the realm of volunteering back2workthan a paid job. Despite that, if you treat it more like a job with full on responsibilities you will have greater success in getting accepted and you will have a greater sense of fulfillment. Additionally, you will get further along with more opportunities within the MVP program just like a traditional job.

Just like a traditional job, there are responsibilities, non-disclosures, internal communications, and annual reviews. Did any of those terms raise your eyebrow? The community contribution paperwork does not end with becoming an MVP – that is just the job application / resume. Every year, you have to provide an annual review. This review is a recap of the entire year with your personal accomplishments and is basically a self-review that would be provided to the manager. I am sure you are familiar with the process of providing a self-review to document reasons why you should remain employed or even get a raise.

Non-traditional Job

As with a regular job, you must continue to accomplish something in order to maintain the position. The accomplishments can come in any form of community contribution such as blogs, speaking, mentoring, or podcasts (as examples). What is not often realized is that this takes time. Sometimes, it takes a lot of time. When you consider the time as a part of your effort, I hope you start to realize that being an MVP really is a lot like a part time job (and a full time job in some cases).

When we start talking about being an MVP in quantity of hours contributed and tasks accomplished, it is not hard to see it as a job. So if it really is just like a job, how much time are you willing to invest in the documentation for this award? Is it at least comparable to the time you invest in documenting your professional career when applying for a paying job? If you don’t take that kind of pride or effort in documenting your worth to your personal career development, then I dare say you need to rethink your approach and re-evaluate whether you should be an MVP candidate.

Being an MVP is not just an award – it is a commitment to do more for the community!

Another Query to Help Find the Right Event

Finding the right event or combination of events to monitor may seem like quite a daunting task with so many events to explore and xvnt_crossing(frequently) too little familiarity with Extended Events. In this follow-up article to Extended Event Help Queries, I will share another means to quickly explore the Extended Events metadata in the effort to find the precise event to fit your needs.

Today I will be sharing a new query or two that I have used on more than one occasion to help track down the event(s) that I wanted to, at least, try while troubleshooting specific problems within the SQL Server Instance. I found these queries useful once again in a recent opportunity to help out some friends from the SQL Community. (Maybe, just maybe, these are really just a spin of other previously shared queries.) I will share the most recent experience in another follow-up post.

For the sake of posterity, I am also adding this to the 60 Days of Extended Events Table of Contents.

Is There an Event that Contains pertinent Data?

In my previous article I demonstrated how to find an event based solely on the name or description of the event. This is fantastic if the event name (or description) contains one of the magical words you have used. What if the event name or description has nothing to do with the terms you selected? Or, what if the data you seek may be attached to the event but wouldn’t necessarily stand out as a description for that event (by name or description details for that event)?

Now comes the more difficult task right? If the name or description of the event doesn’t relate to the search terms then you just might overlook a few events and be stuck trying to troubleshoot a problem. An equally big problem this could cause is yet another invisible barrier to using Extended Events. It would be easy to slide down the slippery slope and not transition to Extended Events just because an event, applicable to the problem at hand, could not be found.

This is where searching on the payload comes in handy. Remember from a previous article that the payload is the event data that is attached to each event. Imagine how much more accurate we can become by extending our search from the name and description of the event object to next search on the payload names and descriptions? That is exactly what I will now show.

Basically, I am searching against the object_columns metadata dmv for Extended Events just to find any columns that match my theoretical needs. In this case, I am trying to find anything that may help me find events related to the terms “select” or “statement”. When run, there will be quite a few results for these terms. That may actually be a bit of a problem (just the same as if nothing were returned in the results). There is a fix for that (well probably several fixes as you begin to tweak the query).

One possible fix is to also include map_values to refine the search. Another fix might be to change the search terms. And still other fixes might include tweaking the predicate. For today, I am going to demonstrate the inclusion of map_values as a secondary tweak to help isolate the needed event.

In this iteration, my result-set is trimmed significantly due to the predicate and the requirement for a match based on the map_value name. Ultimately, this trimmed my results down to precisely the events that I needed in this case. With this slightly more advanced version, I have greater visibility into the Extended Event realm to help me find just the right event to match my needs.

Are there other Events related to the new found Event?

 

This is some pretty awesome stuff so far. But it doesn’t end right there. Many events are tied together to help provide a more holistic view of the problem. These events are tied together via keyword. You can read more about keywords here. By searching for events based on a keyword, we can get a lot more intelligent about the sessions we create with Extended Events.

Taking the single result from the last query and then using the SearchKeyword value, for that event, in this next query – I can potentially go from a Great DBA to a Super DBA.

I intentionally included the columns from each of the matching events. Doing this, I can also see what potential payload may be trapped when I attempt to trap any of the events from this result set.

Conclusion

There is great power in Extended Events. There is more power in being able to query the metadata for Extended Events in order to trap the appropriate information to properly troubleshoot a problem.

Have fun with these scripts and I hope you enjoy.

Extended Event Help Queries

Enjoy this article re-publication from my original work at SQL Solutions Group.

When working with Extended Events, there are times when a little more information is, well, helpful.  You know you want to use extended events to try and monitor for a specific thing xvnt_crossingto happen.  But sometimes you don’t know if there is an event for that “thing”, or maybe you don’t know if there is a session already in place to do that specific task (or if it is even running), or maybe you just need better information about the event to see what kind of data it captures when it is fired.

Sometimes, this information is easy enough to find.  Sometimes, it is less than evident how to find the wanted information.  Some of the information may be visible through the GUI (if you are using SQL Server 2012 or later), and sometimes it is just flat out easier to run a quick script.  Today, I would like to share a few quick scripts to help find some of this information that can make the life of the DBA just a bit easier.

Does an Event Exist?

First let’s tackle the problem of discovery.  When we want to use extended events to try and troubleshoot a problem or to capture more information, it is really good to know if such an event exists.  There are many events that capture data for various different things within SQL Server.  More and more events are being added with each release.  More and more data is being made available to the DBA to help perform a better job and to help the DBA better understand what is really happening within the database environment.

In order to determine if there might be an event, that can provide the data for that one “thing” that may be happening within your environment, we could start by querying the SQL Server Internals.  This next query will do just that for us.

This will query the DMVs related to Extended Events and provide us with the event names that contain the terms used in the filter / predicate.  In this case, I am looking for any events that contain the terms “growth” or “file_size”.  This is in an effort to try and find anything that might be related to database files changing sizes.  You may have seen one of the previous articles on tracking file size changes here or here.

The use of the right term to try and trap the right data for the problem that is happening can greatly decrease the time required to find the correct event.  There may be a bit of an art involved in finding the correct search term.  A little practice can help improve the ability to find the appropriate event more quickly.

Is the Data any Good?

Knowing if an Extended Event exists for the desired problem is one thing.  Knowing what data can be parsed from that event is another thing.  Many times, we may not know what kind of data can be trapped with each of the events.  Many times, we may just not be trapping enough data.  And of course, knowing what kind of data can be trapped by the event may help us in filtering that data down to what is usable for efficient troubleshooting.

Finding that data is just a matter of another simple query against the DMVs.  Let’s take a look at the query that can help us find the data we need:

In this case, I have taken the name of the event about which I want to learn more details.  In addition, I only want to retrieve the data columns that retrieve data that could change.  As you can see, I am investigating “trace_flag_changed”.  You may recall that particular event from a previous article and you can read more about it here.

You may also be looking at the query and thinking it seems a little overly complex for what we want.  Well, it is.  Here is an alternate version:

The discovery of the columns available in each event will give us some options to filter as well as some options in what kind of data we parse from the resulting event.

Did I Already Create that Session?

This is arguably less critical than the others.  After all, some will just drop and recreate the session.  But why drop the session if you don’t need to?  On the other hand, a quick glimpse in the GUI could also display that information.  Sadly that doesn’t work for all versions of SQL Server (e.g. SQL Server 2008).  Maybe you have the session running, or maybe it is simply created and not in the running state.

Finding these sessions and whether they are running or not is rather simple.  Just run this next query and it will help you figure out if the session is there or not.  All you need to do is plug in the name of the session and away you go.

This query has been confirmed to work in SQL 2008 and up through SQL 2014.  It should simplify some of the attempts to better understand what is happening with any sessions that might be out there.

These are a few quick and simple queries to help you along your way to becoming better acquainted with Extended Events.  I hope these will be useful to you and that you will get to know and use extended events regularly.

SQL Server CTE – Back to Basics

Categories: News, Professional, SSC
Comments: 2 Comments
Published on: July 14, 2016

Quick Flashback

sqlbasic_sargeBack in late December of 2015, a challenge of sorts was issued by Tim Ford (twitter) to write a blog post each month on a SQL Server Basic. Some have hash-tagged this as #backtobasics. Here is the link to that challenge sent via tweet.

I did not officially accept the challenge. Was an official acceptance required? I don’t know. I do know that I think it is a good challenge and that I intend to participate in the challenge. I hope I can meet the requirements and keep the posts to “basics”. Let’s just call this first post in the challenge to be my official acceptance.

With this being another installment in a monthly series, here is a link to review the other posts in the series – back to basics. Reviewing that link, you can probably tell I am a bit behind in the monthly series.

SQL Server CTE

kapowOf all the fundamental concepts within SQL Server, this one drives me a bit batty from time to time. Think about that statement for just a moment. I am not going to go in depth about the syntax of a CTE.

I am not really interested in breaking down the semantics and uses of the CTE. Rather, I want to break down something commonly associated to the CTE that is far more basic. I would classify this as more of a myth and misconception than anything else.

The topic du jour happens to be around punctuation. More specifically, do we use terminators or do we use beginninators? Yes that is a made-up term, but you get the drift. Where exactly should the punctuation be placed? Better yet, is it mandatory to include that punctuation every single time?

Those are the items I wish to discuss and hopefully demystify with a few examples (if that many are even necessary).

Punctuation

Punctuation shouldn’t be too big of a deal right? There is so little punctuation within t-sql that it really should be very basic and easy to understand. That said, there is this perpetual myth that a semi-colon is required at the beginning of a CTE. Furthermore, and somewhat more accurate, is that a semi-colon is a requirement in order for a CTE to work. Let’s take a look at both of those scenarios.

Case the First

Let’s just take a look at the most basic of scripts. A simple select statement without a CTE.

Straight away, we can see that I am accessing the AdventureWorks2014 Database. This happens to be on my 2014 instance as well. I have not terminated the select statement with a semi-colon. It really is somewhat a preference here since the statement will work with or without the semi-colon just the same (and despite the previous notion that it was going to become mandatory to terminate all statements with a semi-colon, don’t believe it until it actually happens).

Here is the SQL Server 2016 version:

Now let’s go with a basic CTE example written the same way that we commonly see CTEs written on the internet.

Notice that I am using the semi-colon as a statement beginninator and terminator all in the same statement. The statement runs perfectly fine – that much is true. The usual reason for the leading semi-colon is that the cte has to be preceded by a semi-colon. Let me see how true that is with a minor tweak!

What do you expect to happen from the execution of this statement? Well, as it stands the statement will execute without error just the same as it did in the previous example.

basicctetweaked

What? That worked? Yes. Yes it did. This is quite frankly due to the reality that a semi-colon is not required to precede a CTE. This statement was the only statement in the batch.

Case the Second

Based on those results, that must mean that a semi-colon is absolutely required when there is a statement preceding the CTE. Therefore, what is really required is the statement that immediately precedes the CTE must be terminated by a semi-colon. Not so fast there. Let’s test this one too!

Let’s use the following basic example.

That query works perfectly fine again. That must be due to the preceding semi-colon, right? Hold on to that thought. How about a rewrite as follows:

Here we tweaked the query to represent the notion that all previous statements should be terminated with a semi-colon. What do you think will happen? This will result in an error. GO is a batch terminator and therefore does not require the semi-colon. Surely this reinforces the theory that the semi-colon should come preceding to the CTE though, right? Let’s rewrite that query again.

I removed the semi-colon and now the code segment will execute without error. Wait there is more!

I want to continue tweaking this query. Let’s try multiple selects along with this CTE.

mind_blownNow I have a query that has a preceding select that is not terminated with a semi-colon. I have a CTE that is created right after that select and then I select from that CTE. What could ever possibly happen with this particular query?

The short of that story is that the query will execute without error and I will end up with two result sets.

Wait! Hold on two seconds there! Surely the semi-colon is an absolute requirement because we see it everywhere that it is a mandatory requirement.

The reality is that the semi-colon requirement is not really entirely accurate. If the CTE happens to be in the same batch, then the previous statement in the batch must be terminated by the semi-colon. Take the following change for example:

If I remove the semi-colon from after the “END” statement, then my query will terminate in an error. If I remove the BEGIN and END from around the first select statement, then the query will fail with an error. The error that I receive is part of the reason for the huge misconception about when a semi-colon is required:

Msg 319, Level 15, State 1, Line 38
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

As I have already shown though, this is not accurate. Start thinking on a bigger scale than just semi-colons. Personally, I would prefer to see CTEs that are used in examples on the internet to be encapsulated with a BEGIN…END instead of using a preceding semi-colon (beginninator).

Has this behavior changed with SQL Server 2016? That is easily shown by the following example:

Recap

These examples clearly demonstrate the inaccuracy of the myth that a semi-colon is required. Despite the statement made in the error message, it just is not entirely the full story. A semi-colon to terminate the preceding statement within a batch is necessary but it is not required every time. If there is a separation between batches and the CTE, then a semi-colon is not required. And never is a semi-colon required to precede a CTE. It is required to terminate a previous statement within the same batch.

Please don’t be led astray by some notions on the web that dictate a CTE must be preceded by a semi-colon.

«page 1 of 104








Calendar
July 2016
M T W T F S S
« Apr    
 123
45678910
11121314151617
18192021222324
25262728293031
Content
SQLHelp

SQLHelp


Welcome , today is Saturday, July 30, 2016