We are born helpless. Totally dependent upon others for our survival.

When my daughter was born she needed to have formula dripped into her mouth to avoid dehydration and death. This lasted for a few weeks until she could feed on her own. Will she remember my waking up every few hours to help feed her?

No, and not just because she was too young.

She is more than likely able to tell you about all the times I told her she could not have donuts for dinner, or ice cream for breakfast, or not play with shards of broken glass.

Like most humans my daughter is going to remember the times she was told “no” vividly, and less likely to remember all the times she was told “yes”.

In my book DBA Survivor I speak as to how this translates to our lives as adults:

“No one cares about effort, they only care about results.”

If someone needs a quick answer from you on an obscure topic and you deliver they are likely to walk away happy. If they come to you ten times in a row and you deliver 9 out of the 10 times guess what sticks most in their mind? That’s right, the one time you couldn’t deliver is what sticks; those other nine don’t matter.

And that sucks.

And it is so very….human.

It is also human for us to not want to fail, to not disappoint others, so we get in the habit of over-promising. This often leads to under-delivering. It’s eerily similar to how virtualization works (which makes sense, since virtuallization systems were built by humans). It’s not the over-allocation of resources that is the issue, it’s when your resources are over-committed that performance suffers.

And that’s when people complain. They only remember the one failure, they never think about all the times their applications ran smoothly. When was the last time someone called and thanked you for an overnight batch process running fast?

I touched upon this in my Someday talk. People never stop to think about all the Somedays that they have already accomplished. They tend to focus more on the things they haven’t obtained yet. Not enjoying what they have, when they have it. Instead they clamor for more, and for things to never go wrong.

Humans are needy. They have a default setting of “yes” for their needs. It starts at birth and never stops.

And that’s why we only remember the “no”.

At the end of the day the issue isn’t as much with you as it is with the expectations that others place upon you. This also means that you, as a human, are often placing expectations on others. It’s a vicious cycle.

That’s what needs to be managed.

The post They Only Remember The “No” appeared first on SQLRockstar - Thomas LaRock.

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.

It’s the question I get asked first whenever I am working as a booth babe. Attendees approach and ask “what do you do”? I’d estimate that 98% of people working in booths take that as an opportunity to talk about themselves for the next 3-5 minutes.

I can’t think of anything more dreadful. Here you are, presented with an opportunity to engage someone in a conversation that helps you learn more about THEM and you spend it talking about yourself.

“I love listening to vendors talk about themselves for the first fifteen minutes when we meet.” – Said no one, ever.

It’s similar to how most vendor presentations start with the same typical sentence:

“Our company was founded in…”

As soon as I hear that phrase my first thought is how Nintendo was founded in 1889 but didn’t start making video games until the 1970′s and NO ONE CARES WHEN YOUR COMPANY WAS FOUNDED. If I want to know about your past, I’ll ask. Until then, let’s talk about the now, and how your product is going to be of value to me, my project, and my business environment. In short, let’s talk more about ME, and less about your company.

I suppose I have an advantage over most in this area. Having been the person forced to sit through some of the most boring vendor presentations on Earth I know what resonates with me.

The best vendor sales presentations do not follow a script. They don’t force me to look at slides right from the start. Instead, they start with a conversation. The slides may come later, or they may not. The conversation will focus on my needs, what I want to get from the product, etc. Then the presenters will spend time talking about how they can help, and do so without tossing jelly all over the place in the hope that something will stick.

Now that I am the person giving the presentations I want to avoid delivering the same stock material that everyone else delivers.

Consider this example. Let’s say I meet someone at a networking event, or on a speed date, and I talk about myself in the same manner as if it were a standard vendor presentation. It would go something like this:

Thomas William LaRock was founded in 1970 and specializes in data management and design. His headquarters are located in the USA with offices in Massachusetts and Colorado. He has traveled North America and Canada as well as Europe. With 6,000 followers on Twitter and a Klout score of 64 he has a global presence. Thomas has spoken with thousands of people that work for companies you may know. Using a proven methodology called the Cardiovascular System, Thomas is able to operate independently and be a solution provider by using a wide range of products (such as hands).

At six feet four inches tall Thomas stands out from the competition as a trusted resource to reach onto high shelves in his household and most grocery stores. Thomas also helps his children solve their math homework, often with just a few calculations, in order to free up time for other activities such as watching Doctor Who or chasing rabid squirrels with sticks.

How dreadful that would be for anyone to listen to, either during a presentation or at a networking event.

Contrast that response with this one:

“I help people smile more by making their jobs easier.”

Which one is likely to engage the other person to want to know more about what I do? Which one is likely to lead to a better conversation?

Four Ways To Make Your Communications Stick

When I am writing and building a presentation here are the tips I follow:

1. Know Your Audience First

If you don’t know who is in the room then you aren’t likely to know what to say to them either. Take the time to know them first. Ask them where they are from, or what they do, or why they are attending. This will enable you to help them the most.

2. Know The Value You Offer

If you don’t know your value, then you’ll find yourself rambling on and on about anything and everything until something you say makes them stop yawning. It is best to understand what it is that you have to offer. You also need to understand the difference between having a valuable skill and being able to deliver value to someone else. This point deserves its own blog post at some point.

3. Tailor Your Message

Once you know a bit about them, and assuming you also know your value, you should be able to help connect the dots from there. You can craft your message to make certain it focuses on their needs. Remember, this should be about them, not you.

4. Practice, Practice, Practice

I cannot emphasize this point enough. You need to have confidence and deliver your message in a natural way, as if you were having a conversation. Because you *are* having a conversation.

Earlier I mentioned the term “jelly”. The term is from the book The Jelly Effect: How to Make Your Communication Stick, one of my favorite books on effective communication. Author Andy Bounds talks about how he learned to communicate efficiently with others as a result of helping his blind mother since he was a boy. I re-read the book at least once a year, and leaf through it often.

I recommend that book to anyone that asks me for help on speaking, building presentations, and writing. It helps you understand that when someone asks “what do you do” your answer needs to be something of value for THEM, and not about your resume.

The post Are You Making Your Communications Stick? appeared first on SQLRockstar - Thomas LaRock.

WASD does not support the WITH ENCRYPTION option for objects such as stored procedures, user defined functions, triggers, or views. So the idea of migrating an object that was compiled with that option is simply not feasible.

If you have a system with encrypted objects and you try migrating to WASD you will get “Validation of the schema model for data package failed. Error SQL71564: The element…cannot be deployed as the script body is encrypted.”

To unencrypt this proc you have three options: you can use a 3rd party tool, find various scripts through a quick internet search, or take the source code and issue an ALTER command without the encryption option.

I prefer the latter option. Just take the source code and remove the WITH ENCRYPTION statement.

I would also like to remind you that WITH ENCRYPTION does not encrypt anything. It obfuscates the object code, nothing more. It really should be named WITH OBFUSCATION, but that probably wouldn’t be clear for most end users. If you have a requirement to encrypt your source code you should go back and ask for more information as to why it is necessary. Chances are the WITH ENCRYPTION option is not meeting the original requirements.

After you update the object you’ll be ready to complete your migration to WASD. At the very least, you’ll get past that error message!

The post How To: Migrate Encrypted Procedures To Windows Azure SQL Database (WASD) appeared first on SQLRockstar - Thomas LaRock.

Imagine my surprise when I went through my RSS feeds today and found this blog post from Chris Skorlinski, written just last week.

Great minds, right?

I decided that instead of tossing my post altogether, or waiting a few weeks to publish an updated version, that I would just go ahead and share with you the one thing that Chris forgot to mention in his post.

First let’s talk about why I felt this topic was important enough for a post: because people fear change.

I mean…I *hate* it when someone moves my cheese or my wine. Especially when one or both of those items are just out of my reach.

When the topic of WASD is mentioned in certain circles I often hear comments about how WASD is lacking the features and functionality that exist in the on-premises version of SQL Server.

Two of the most common examples that get mentioned to me are the lack of Extended Events and/or SQL Profiler. It doesn’t matter if the person raising the objection uses those tools, they just want them available if needed. When I ask what they want to do with those tools that they cannot do with DMVs currently I get back a fairly common response:

Deadlocks.

While I highly doubt these same people are going to start using WASD even if those xEvents or Profiler were available I thought it was worth sharing that WASD makes it EASIER to troubleshoot deadlocks that a traditional on-prem instance of SQL Server.

That’s what Chris has done for the most part, except for one thing.

What Chris Is Missing

As much as people fear change, people love to look at pictures even more.

Chris shows how to view the XML for the deadlock event. If you want to see the graph then you just need to save that file as an .rdl file and then re-open. It’s so easy I decided to make a video to show you how it is done.

The end result will be this:

Consider how you have to capture and review deadlock details in on-prem SQL Server 2012 today and I think you’ll find WASD to be much simpler. You don’t need to do anything in advance to capture deadlock information, or historical deadlock information. For more information on how to resolve deadlocking I will point you to Bart Duncan’s blog posts as a great first step (and likely the only step you will need).

Reducing the amount of administrative overhead for tracking and analyzing deadlocks is one example of how Microsoft is making an effort to reduce the total cost of ownership (TCO) with WASD.

The post How To: Troubleshoot Deadlocks In Windows Azure SQL Database (WASD) appeared first on SQLRockstar - Thomas LaRock.

I like to call this list my “facepalm” special, as any one of these essentially triggers a facepalm reaction. They have helped my customers and clients in the past and I am certain they will help you.

1. Build Your Own

Don’t build a host – especially a PRODUCTION host – out of spare parts leftover from servers that are near the end of their life. If you want to go on the cheap and use spare parts, do it for a development host and get ready to spend extra time keeping that bucket of bolts together. If you are going virtual, you will want to buy new hardware to use for your hosts, and hardware that is more powerful than the servers you already have deployed. There is also licensing considerations here. It could be the case that it is cheaper to buy new hardware and have less to license overall.

2. No Performance Expectations

You cannot go virtual without having any idea as to what is an acceptable performance level. VMWare has a whitepaper that essentially says they can offer you 98% of the same performance as a current physical implementation of SQL Server.  Note that doesn’t mean you will get better performance by moving to VMWare itself. Often times you get better performance because you have moved to better hardware (see the first item in this list). But if you don’t know what your current performance SLAs are then you won’t have any idea if you have still met the SLAs once you have converted to virtual. Get your expectations set now so you can track them going forward.

3. Select Wrong Disk Choice

You have two main options here: raw device mappings (RDM) and virtual machine disk format (VMDK). Which one do you want to use, and when? VMWare says that in terms of performance the difference is minimal. The real difference is functional, or architectural (I know I just scared away some DBAs because I used the word ‘architecture’, but yeah I went there). VMWare has published a list of scenarios where RDMs would be a better solution for your shop. You need to know these differences before you start deploying a solution that fails to meet some critical business requirement.

4. Thin Provisioning

Thin provisioning is one of those bad ideas that sounds good and often produces the same results as do-it-yourself dentistry. It starts out innocently enough: someone wants to save space and only allocate storage as needed. The end result is that no one keeps efficient track of what VMs have been thin provisioned and eventually as the files grow in size they fill up all the available storage until all activity stops because the disk is full. VMWare has a recommendation for you listed here: use vMotion to migrate the guests to new hosts where they will fit. Great advice, but I’m guessing you didn’t have enough room to start with, otherwise you wouldn’t be using thin provisioning.

5. Over-Overallocation Of Memory/CPU

It’s okay to want to over allocate your memory and CPU resources. Want you don’t want to have happen is to have them over committed, as that’s where performance issues manifest themselves. When I am reviewing a customer’s configuration I tell them the line in the sand I draw is 1.5:1 ratio as an upper bound default for CPU resources (so, 16 logical cores means you can allocate 24 CPU as a baseline and adjust up or down as needed based upon workload and load balancing allows). You can find a good description on allocating vCPU at this blog post. For memory settings I follow what is outlined in the VMWare Performance Best Practices guide which states “…avoid over-allocating memory”. In other words, I’m much more conservative with memory over allocation than with CPU over allocation.

6. Trusting O/S Counters

When you go virtual that means you have an additional layer of abstraction (i.e., the hypervisor) between you and your data. I usually just say “there are many layers of delicious cake between you and your data”. The trouble is that you need to know which layer is causing you performance issues. Is it the host? Is it the guest? As such, you need to rely on the VM performance counters in order to get a complete picture of what is happening. You can see the counters explained in more detail from this page. If you are still relying on standard O/S metrics for a virtualized server then you are doing it wrong. (And if you need a tool to show you all those layers of cake, I can help.)

7. Running It All At Once

Remember how I said that you want to avoid over committing all your resources at once? That’s where load balancing and knowing your workloads are key. You cannot carve out a dozen guests to be used as production database servers to be run during regular business hours and expect that performance will remain at that 98% mark that VMWare suggests is attainable. You have to balance your workload otherwise you are going to find that your over allocation of resources is now an over commit of resources. Yet I still see customers stretching their hosts way too thin.

These are the seven items that I see hurting a majority of virtualization efforts. They result on bad performance that leaves users and administrators frustrated. They are also easily avoidable with just a bit of up front knowledge and requirements gathering.

The post Doing It Wrong: Virtualizing SQL Server appeared first on SQLRockstar - Thomas LaRock.

Of course I had him mention bacon in my copy.

Did you even need to ask?

Last week while on vacation I took the time to read Super Freakonomics and found it to be better than the first Freakonomics. I don’t want to spoil it for you completely, but here are some of the highlights in Super Freakonomics for me:

1. Prostitutes in Chicago are more likely to have sex with an officer than be arrested by one
2. Livestock are responsible for greenhouse gases that are 25 times more potent then the entire transportation sector
3. On a per-mile basis it is safer to drive drunk than to walk drunk
4. How pimps are similar to realtors
5. Why suicide bombers should purchase life insurance

What I love most about this book is that the authors know how to ask the right questions in order to get the right data to help them answer questions. More importantly, they let the data answer questions they didn’t even know they were looking for.

That being said, this is not an exact science. There are areas in the book where it seems the authors have not gotten all the data they need.

For example, they have no way of knowing how many miles are walked by drunks each year. It is rather difficult to say with certainly that on a per mile basis it is safer to drive drunk than to walk. But with Levitt himself admitting that math isn’t his strongest suit perhaps we can understand how a few leaps are made here and there.

But the stories and anecdotes do make for an entertaining read nonetheless.

I highly recommend both Freakonomics and Super Freakonomics for everyone to read especially data professionals. I believe these books help us to understand the value and power that exists within data analytics. There’s also fun, too.

I’ve added them to my library page, under the “Data Analytics and Insights” section.

P. S. While in Chicago that week I also attended my first Chicago Cubs game. It was also my first ever major league baseball home opener. If you know anything about the Cubs you know about the tradition of playing only daytime baseball games until lights were installed 1988. Being able to attend a daytime Cubs game and have it be the home opener was a thrill for me.

The post Book Recommendation: Super Freakonomics appeared first on SQLRockstar - Thomas LaRock.