SQLServerPedia

I was at SQL Saturday in Phoenix last week and had a number of sidebar discussions with a solider that is transferring out of the Army lifestyle and over to the civilian world. This is a transition that I have done before, and I recall it well. I have not thought about it much over the last 15 years or so, but I do recall the nervousness that I had when I made the jump from the Marine Corps to the workforce as I know it today. When I made the transition I was lucky enough to have the opportunity to take a class my last week in the corps to help me with the many changes I was about to experience. I debated not taking the class because I didn’t think it was going to be that different, but I sure am glad I did. So as I reflect on this experience I thought I would share a few tips that might make it a bit easier. If you have any that you think would fit well send them over to me and I will see what I can do to add them to the list.

1. Stay away from acronyms on your resume – There is a good chance that when someone is reviewing your resume that they did not serve in the military. With this being a fact, consider how many terms you use on a day to day basis that you consider normal knowledge. If someone is vetting your resume and does not understand all the terms you are using they may disqualify you simply because they don’t understand what you are trying to relay. My tip here is find a friend or a relative who does not know the military life and have them review your resume, if you use terms such as MOS or OD duty chances are they will catch it and help you explain more.
2. Chain of command – This was a difficult one for me. The chain of command is pretty simple to understand; even people in the civilian world use it. However, in the military I was taught that anything and everything in your life was important to your chain of command. In a time of high stress such as a situation where you are in battle and bullets are being fired at you this is completely understandable. Assume you are in a fighting position, and one of your troops had a pretty serious breakup via a “Dear John” letter. This is something you would want to know, but in the civilian world many supervisors get a little creped out when they know so much about you.
3. Your opinion – Many think that the military is a group of young people that are trained to not have and opinion. When the commanding officer says take that hill, he does not need to hear a bunch of people arguing about the right way to do it, or if you should even try to do it. Granted, these discussions on the best way may arise. In the civilian world, most employers want to know if you think that something is not being done in the best way. The secret here is understanding the line between open discussions on the best way to do things and just doing them with no opinion at all. With that being said, it is still critical to know that as a solider you don’t always know all the information that is relevant, in the civilian world that is the often the case as well.
4. Networking – In the military if you wanted to get the best equipment it never hurt to know someone in the supply area, well it’s true in the civilian world as well. Who you know can help you, use your contacts well, and as you make the transition understand you need to make as many contacts as you can.
5. YES SIR – A few years ago I had interviewed someone who was coming out of the Navy, when I talked with him the answer to everything was “Yes Sir“. When the candidate spoke it was not a yell, nor a bark but very direct. It is not critical to speak in such a formal way, however respect should always be paid.
6. Volunteer – I have heard the joke more times than I care to count, in the military you become a volunteer when everyone else steps back faster than you. I was told many times before boot camp that you should never volunteer for anything. In the civilian world, I cannot stress enough how important it is. Not only do you have the opportunity to stand out, but you have a chance to learn something new. Be the person your boss knows they can go to when the need to get something done. Be the person they can count on, the type of person you want on your team.

This is something I just heard about recently, but then experienced it for myself.

This story was told to me: A client is looking for a contractor and contacts a staffing firm to find candidates.  The staffing firms sends a resume that looks real good.  A technical interview is done via the phone, the candidate really knows his stuff and nails the interview, and he is hired.  A few weeks later the new hire arrives and begins work.  But something does not seem right.  The contractor does not seem to be that sharp, nothing like he was during the interview.  His coding skills are not that good.  After a few weeks, the client does some digging, and it is discovered this person is not the person who did the interview!

It’s called the “bait and switch”, and the hope is the client remains unaware it is a different person.  I’m guessing sometimes they get away with this switch, but you can imagine how upset the client is when they find out they have been tricked.  It seems the staffing firm is tricked also. This happened twice, and both cases involved sponsored candidates from India.  In one of the cases there was a group of seven who all lived together and one of them was really sharp (the “ring leader”) and would do all the interviews for the other six, who were junior-level.  The resumes had the real name of the person but the experience was the ring leaders.  Also, when the junior programmers were placed at a client the ring-leader would help out the junior programmers if they were struggling on the project they were on.  And the client was none the wiser.

This then happened to me, and shows the extent some people will go to trick the client.  The client I was at did a phone interview on a candidate that I participated in.  The candidate did very well in the phone interview.  Since the client had experienced the same bait-and-switch as above, the next step in the interview process is a required a face-to-face meeting.  So they did a video interview on Skype, were we used Skype for the video and used a land-line for the voice.  Once again the candidate did very well but….it turns out, we were talking to a different person on the land-line than who was on the video!  We got suspicious when we saw how the video and voice were so out of sync, and doing some more digging turned up they were in fact different people.  The guy on the video was pretending to be the guy talking on the land-line.  Crazy!

I asked my recruiter friend about this, and he replied:

I saw it happen more often earlier in my career, not so much now (but it still does happen on rare occasions).  Detroit in particular had a ton of foreign national firms set up show here in the 90’s and when this “bait & switch” became a trend, the Big 3 put an end to it.  They’d make candidates give some form of ID (SS# or some other identifier).  There would also be harsh penalties such as removal from the vendor blanket for firms that repeatedly used this tactic.  The bait & switch is a rookie move.  The staffing world can be a greasy business – unfortunately nothing really surprises me these days.

I’ve finally gotten my gear together for (what I consider) the “speaking season”. Here are the SQL Saturdays that I’m speaking at, or that I’ve submitted to, or are considering submitting to! I’ll update this list as plans solidify:

• 5/18/2013 – Atlanta - Scheduled to speak.
• 8/3/2013 – Baton Rouge - Submitted. Call for speakers closes 6/1/2013
• 8/17/2013 – NYC - Considering. Call for speakers closes 6/18/2013
• 8/24/2013 – OKC - Submitted. Call for speakers closes 6/21/2013
• 9/14/2013 – KSC - Submitted. Call for speakers closes 7/16/2013
• 9/28/2013 – Denver - Considering. Call for speakers closes 7/30/2013
• 10/15/2013 – PASS Summit – Submitted. Call for speakers closed.
• 12/7/2013 – D.C. - Considering. Call for speakers closes 10/8/2013

Let me know where you’re going to be! Maybe we’ll cross paths, shake hands, share info, sing a song…

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

I’ve as I’ve mentioned many times on twitter, I’ve become quite obsessed with Powershell over the last year and a half.  I recently joined the O’Reilly Blogger Review Program and here is my first review.

Book Summary

Microsoft Certified Trainer, Ed Wilson, covers all of the facets of Powershell 3.0.  Not only does he teach the reader how to use Powershell, he also teaches them to teach themselves Powershell.  The book starts with the basics and builds to expert levels on many topics in Powershell.  There is a section in the introduction on what chapters to read based on your background and ability and hopes/wants/needs for Powershell.  This helped me pick and choose what to read and focus on.  At the end of each chapter, there are exercises that really test your knowledge and drill the information into your brain.  Also, throughout the book, there are Tips and Notes that help find the important information quickly.  Finally, each chapter has a Quick Reference at the end that summarizes the main points which is extremely helpful in getting the information you need.

My Reactions

Since I got this book, I have used it as a reference everyday.  In fact, I added a snippet to my $profile to open it when I open Powershell.  I’ve seen a few videos and demonstrations of the Get-Command and Get-Member functions, but having the hands on exercises along with the the full explanations helped me finally understand how to use these – and that was in the first chapter!  I love the "Hey, Scripting Guy! Blog" and this book uses a similar approach.  It keeps things simple and builds upon the simple things.  Due to the fact that this book uses a straightforward approach and makes the finding of information very easy, I recommend it to anyone interested in learning and/or improving their Powershell skills.

This is just a quick tip to help with folks who present SQL code at events such as SQL Saturday. While most presenters use tools like ZoomIt (which if you present, please please learn to use this wonderful, free tool) sometimes it can get nauseating for attendees to watch you constantly zooming in and out, especially on code.

A quick way around this is by using the magnification feature in SQL Server Management Studio 2012. To do this simply hold down the Ctrl button on your keyboard and with your mouse scroll the mouse wheel up to increase the magnification and scroll down to decrease it.  Alternatively you can simply click on the magnification dropdown, which is located at the bottom left of the query window (by default) and select your desired level of magnification.

That’s it! Now you can quickly magnify your code to make it easier for your audience to see and you can reserve the zooming to highlight other areas as needed.

Sponsor: C2IT Consulting

Date: May 21st, 2013

Time: 4:00 PM – 6:00 PM

Location: 3601 West 76th Street, Suite 600 Edina, MN  55437

Live Meeting:

• URL: https://www.livemeeting.com/cc/usergroups/join?id=SRR9DN&role=attend&pw=zd%3F9c.2KJ
• Meeting ID: SRR9DN
• Entry Code: zd?9c.2KJ

Agenda:

• 4:00-4:15 : Registration / hospitality / networking
• 4:15-4:30 : SSMS 2012 – Tips, Tricks and New Features, by Rizwan Hassan
• 4:30-5:30 : SQL Server Security and Data Security, by Ross McNeely
• 5:30-6:00 : Announcements and prize giveaways

Click here for all the details and to RSVP

Presentation:

SQL Server Security and Data Security, by Ross McNeely

The presentation will walk through the requirements to secure SQL Server, and how to implement data security requirements. Ross decided to discuss both SQL Server Security and Data Security due to the amount of confusion that often occurs during the security phase of a project. Some of the topics included are SQL Server configuration, table/execution permissions, OLAP security, and using a security data model. The presentation will cover a large amount of material in a short time span, but hopefully you will find it informative.

Ross McNeely is a BI Practice Manager at C2 IT Solutions. As a Business Intelligence consultant Ross have installed and secured SQL Server instances for various customers. Ross has presented at several SQL Saturday in Minnesota, Wisconsin, and Iowa. Regardless if you have a beginner, intermediate, or advanced skill set, Ross will provide a solid foundation for each topic. Please join Ross McNeely for a fun and informative meeting.

Presentation:

SSMS 2012 – Tips, Tricks and New Features, by Rizwan Hassan

There are certain features in SSMS 2012 that we are unaware of and new features that learned while using SSMS 2012. I would like to share those features with you!!!

Rizwan Hassan started as System Administrator and evolved into SQL DBA Administrator. Rizwan have worked at Virteva and Kroll Ontrack. He is currently working at General Mills as SQL DBA. Responsible for 600 SQL instances with 3000 databases

Ever jump on a machine to connect to a SQL Server and find out… whoops! No tools.  For some reason, the client machine doesn’t have SQL Server Management Studio (SSMS) installed.  Now I’m not talking about not having the SSMS installed on the server, but on the local workstation that you need to connect to the server with.

No tools has happened to me many times and, in fact, happened to me this past week.  In the past, this would mean running around trying to get find the client’s SQL Server installation media.  Maybe downloading a 4+ GB ISO of the entire SQL Server installation from TechNet.  Or, hopefully, making sure the client had USB ports open on their machines and that I had my installation media backup drive with me.  A lot of things that were more work than one would like.

With the release of SQL Server 2012 Service Pack 1, there is a new option on the table.  Instead of downloading everything, you can download just the management tools through the Microsoft® SQL Server® 2012 Service Pack 1 (SP1) Express download page.  Just select either SQLManagementStudio_x64_ENU.exe or SQLManagementStudio_x86_ENU.exe from the download options and you’ll have the full version of SSMS at your fingertips; in about a quarter the size of the full installation download.

With my Technitrain hat on I’m sitting in on Marco’s Advanced DAX course in London today, and the question of comments in DAX came up – which reminded me that this is something I’ve been meaning to blog about. DAX as a language supports comments, but unfortunately it’s not possible to add comments inside a DAX measure or calculated column expression in either PowerPivot or SSAS Tabular right now (which is where they’re most needed – I hope this changes in the future). That said, there are some other things you can do to add textual explanations and descriptions to your DAX measure code.

Before we get onto the workarounds, a quick word about comments in DAX. These can only be used in DAX queries, and the types of comment supported are the same as in MDX: double-dashes and double-forward-slashes for single line comments, and forward-slash-asterisk to start a multi-line comment and asterisk-forward-slash to close a multi-line comment. Here’s an example:

--single line comment

//another single line comment

/*a multi-line

comment*/

evaluate table1

What can be done with measures though? After all, that’s where the most complex DAX is usually written.

First of all, you can add a description to a measure by right-clicking on it in the measure grid and selecting Description:

Unfortunately this description is not easily accessible to end users anywhere (it would be great if it appeared as a tooltip in a PivotTable, for example) but it can be seen in an Excel worksheet by running a DMV query. DMV queries can be run in Excel 2013 in the same way as DAX queries, using a query table as described here; the DMV query to use is:

select 

measure_name as [Measure Name], [description], measure_is_visible 

from $system.mdschema_measures

Unfortunately all hidden and implicit measures are returned, and even when the table is filtered so that only measure_is_visible=true there are still a lot of measures that probably shouldn’t be shown.

Similarly, descriptions can be added to any column (calculated or not) in your model, again by right-clicking on it and selecting Description.

This description can be displayed in the worksheet using the following DMV query:

select

hierarchy_name as [Column Name], [description] as [Description] 

from $system.mdschema_hierarchies

where cube_name='model'

You can also write text direct to cells in the measure grid too. When I first saw a customer do this I was worried that it might not be supported, but I’ve been told that it is; so long as you don’t use the =: used for defining measures then you should be ok.

This is probably the best way to add comments to your code, if only because it’s the most visible to anyone looking at your PowerPivot/SSAS Tabular model. Of course, for it to be effective you’ll need to have a system for arranging your measures in the measure grid; in “SQL Server Analysis Services 2012: The BISM Tabular Model”, Marco, Alberto and I recommended that you arrange all your measures in the top-left hand corner of the measure grid and I think that’s still a good idea, but the use of text in cells to create headings for groups of measures as well as descriptions can help a lot too.

It’s time for T-SQL Tuesday the forty-second, Wendy Pastrick asked that we talk

T-SQL Tuesday!

about our experiences with change in our work lives. I’d like to share some exciting news about myself and the new adventure I am taking.  Earlier this month I started a new journey with Dell as an Database Administrator Sr. Advisor. I also am happy to share why I decided to go down this path. Some of the reasons include new technology, career planning, exciting challenges with a great team.

New Technology!

I love being around new technology. Personally, I like to find new innovative technologies that can be used to improve business processes while save money and time. During the interview process I learned that the SQL team at Dell fits the bill. In fact, I heard about so many great projects including some under NDA all I can say is I feel like a kid in the candy store!

Career Planning!

Not every employeer makes it a priority to have you map your aspirations and goals. Very few companies help you do a fit gap assessment and tries to find business opportunities to fill them.  Dell goes out of its ways to help you with your career planning. This might be the greatest perk of the job.

Exciting Challenges with  a Great Team!

Not many people get excited about the opportunity to work with chained transactional replication where a subscriber is a publisher in another publication but I do.  That’s right, I love to be challenged and I am excited to work with some talented people. I am going to get to do both in my new role. I will be working with multiple DBA’s who have been working with SQL Server for over ten years. I look forward to sharing my knowledge and soaking up everything I can from them.

One thing I have noticed in all my years as a data professional: few users understand (or care) how far away they are from their data. Quite often they expect instant results from their queries despite there currently being an upper bound due to things like network bandwidth, the speed of light, and the data sitting on a server on the other side of the world.

SQL Server makes it easy to connect to and query data from remote data sources. The common way of getting this done is through the use of a linked server, which is little more than an OLEDB data source.

The beauty of a linked server connection is that it allows an end user to write a query that looks like any other T-SQL query. For example, here is a query against a local table:

SELECT col1, col2 FROM [databasename].[schemaname].[tablename]

And here is a query that would be written that would utilize a linked server:

SELECT col1, col2 FROM [linkedservername].[databasename].[schemaname].[tablename]

To someone with an untrained eye they would think the data is as easily accessible as any other. You don’t have to do anything special to write a query against a remote data source other than include the name of the linked server.

But the dirty little secret here is that SQL Server is going to make decisions for you as to how to mash all the data together and return you a result set.

People love using linked servers. Because data can (and does) exist everywhere, users naturally want to write one query that joins as much data as possible with no regard if it is local or remote. Even a DBA with many servers to manage will be tempted to build out a series of linked servers in order to capture monitoring details in a central location.

There is also the case where data is going to be too big or cumbersome to move around easily. In that case you are going to want the query to be executed on the remote server and only return the data that is needed across the network. What this means that you should make an effort to help SQL Server make the right choices.

Here are the top three issues I have seen when it comes to linked server (AKA Distributed Query) performance:

1. Insufficient Permissions

Without a doubt this is the number one reason for why linked server query performance suffers. Historically in order for SQL Server to take advantage of using statistics on the remote server then the login used to make the connection on the remote servers needed sufficient rights. The role needed would have been one of the following:

• sysadmin
• db_owner
• db_ddladmin

If you don’t have sufficient permissions then you aren’t able to use stats, and this is killing your performance across that linked server connections. So for everyone that has been assigning the db_datareader role to remote logins you are sacrificing performance for security. While that may be an acceptable tradeoff in your shop, I am willing to wager that most admins have no idea about this silent performance killer.

A good example of identifying these symptoms are contained in this article: http://www.sql-server-performance.com/2006/api-server-cursors/

In SQL 2012 SP1 the permissions to view the statistics on an object have been modified so that a user with SELECT permission would be able to use the stats on the remote tables. Check this link for more details in the ‘Permissions’ section towards the bottom.

2. Query join syntax

Conor Cunningham gave a great talk at SQLBits X on Distributed Queries. In that talk he discussed some different join types and whether or not they were good candidates to be executed remotely. For example, a simple query that wants to pull data from just one remote table is likely to be executed remotely and only pull back the necessary rows. But what about a join between one small local table and one large remote table? What happens then?

You’ll need to watch the video to listen to Conor explain all the nuances of distributed queries. The lesson I learned from the talk is simple: when possible, give the optimizer some help. If you can rewrite your query to only pull back the smallest rowset possible from the remote server, the better chance you have of that query being executed remotely.

3. Functions

Some functions, like GETDATE(), won’t be executed remotely. This make complete sense to me because if I am trying to execute (and filter) a set of data on a remote server (where the time could be very different than the local server) then the concept of GETDATE is lost due to the fact that the servers could be in multiple timezones. SQL Server knows this and as a result it likely won’t execute the query remotely.

OK, let’s say you have identified a linked server query that is not performing well. What can you do?

The way I see it, you have two options available for helping to tune a linked server query.

The first option is to force the query to run remotely. This is done by using the OPENQUERY() function. This function will force the query to be run on the remote server and by default will use the permissions defined in the linked server. We’ve already talked about the potential issue with permissions up above, so I won’t do that again here.

The second option is to try rewriting the query. (I know many developers that just screamed at me to “STOP BLAMING THE CODE!”) Start with one table and the necessary predicates and add in additional tables and predicates until you find the one that changes the results from being executed remotely into ones that are done locally. That will help you identify which part(s) of the query are making SQL Server to switch to running locally. Once identified you can then go about exploring some rewrite options.

I’ve been writing queries for linked servers for about a dozen years now. These are the top three performance killers I have found to be common to many shops. Microsoft has done well to help remedy the permissions issue. However they are not so good as to write your queries for you (at least not yet). Until that day happens you are going to want to spend the extra time testing your queries to make certain that they are behaving as expected.

The post Top 3 Performance Killers For Linked Server Queries appeared first on SQLRockstar - Thomas LaRock.