Another quick post of simple changes you can make to your code to create more optimal execution plans. This one is on implicit conversions. An implicit conversion is when SQL Server must automatically convert a data type from one type to another when comparing values, moving data or combining values with other values. When these values are converted, during the query process, it adds additional overhead and impacts performance.

Here is a great chart by Microsoft that shows you conversions and which will cause an implicit or explicit conversion. In this post I will not go into explicit, just know that is what you explicitly tell SQL Server to CAST or CONVERT a value.

Image credit: https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-2017

Let’s look at a very simple but common implicit conversion scenario. Here we have a table Employee with a NationalIDNumber column defined with a NVARCHAR  data type. In the query we will use a WHERE clause to search for a specific ID.

In the query below, we have requested NationalIDNumber equal to the integer value 14417807.  For SQL Server to compare these two data types it must convert that NVARCHAR to a INT. Which means every value in the that column must go through a conversion process which causes a table scan.

USE AdventureWorks2016CTP3 
GO 
SET STATISTICS IO ON 
GO 
SELECT BusinessEntityID, NationalIDNumber, LoginID, HireDate,JobTitle
FROM HumanResources.Employee
WHERE NationalIDNumber = 14417807

In the execution plan you will see an exclamation point warning you that there is a potential issue with the query.  Hovering over the SELECT operator, you will see that a CONVERT_IMPLICIT is happening which may have affected the optimizer from using a SEEK.

(1 row affected)

Table ‘Employee’. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Now the question is how we fix it. It’s really simple but it does require a code change. Let’s look back at our query.

SELECT BusinessEntityID, NationalIDNumber, LoginID, HireDate,JobTitle
FROM HumanResources.Employee
WHERE NationalIDNumber = 14417807

Remember we asked for a integer value. Just by add single quotes to the value we can eliminate our issue. It’s important to always know what data types your columns are when querying them. In this case since it is a NVARCHAR all I need to do is supply a character value. This is accomplished by adding single quotes around the value.

SELECT BusinessEntityID, NationalIDNumber, LoginID, HireDate,JobTitle
FROM HumanResources.Employee
WHERE NationalIDNumber = '14417807'

It’s simple to see the results. Note above the Scan count 1, logical reads 9, physical reads 0. When we rerun it we get the below.

(1 row affected)

Table ‘Employee’. Scan count 0, logical reads 4, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

You can also wee in the graphical plan that the warning is now gone, and we have a SEEK instead of the SCAN which is much more efficient.

The only thing left to clean up in this plan is the key lookup. To fix that take a look at my blog from last week here.  There are many ways you can end up with  implicit or explicit conversion issues and the additional overhead they can create. In most cases they are extremely easy to fix with a little code change, this was just one example.  To help you find these in your environment Jonathan Kehayias has written a query to  find column side implicit conversions in your plan cache be sure to check it out.

Recently Intel announced some major upgrades to their Xeon CPU line. The long and short of the CPU announcement was that Intel was releasing their 56 Core CPUs for public release. That’s just a massive amount of CPU power that’s available in a very small package. A dual socket server, with two of these CPUs installed, would have 112 cores of CPU power, 224 with Hyper-Threading enabled. That’s a huge amount of CPU power.  And if 112 cores aren’t enough for you, these CPUs can scale up to an eight-socket server if needed.

With each one of the processors, you can install up to 4.5TB of RAM on the server, per socket.  So a dual socket server could have up to 9TB of RAM. (That’s 36TB of RAM for an eight-socket server if you’re keeping track.)

For something like a Hyper-V or a VMware host, these are going to be massive machines.

My guess is that we won’t see many of these machines are companies. Based on the companies that Intel had on stage at the keynote (Amazon, Microsoft, and Google) we’ll be seeing these chips showing up in the cloud platforms reasonably soon.  The reason that I’m thinking this way is two-fold; 1. the power behind these chips is massive, and it makes sense that these are for a cloud play; 2. the people who were on stage at the Intel launch were executives from AWS, Azure and GCP.  By using these chips in the cloud, the cloud providers will be able to get their cloud platforms probably twice as dense as they have them now. That leads to a lot of square feet being saved and reused for other servers.

As to how Intel was able to get 56 cores on a single CPU, is through the same technique that they’ve used in the past. They took two dies, each with 26 cores on them and made one socket out of that.  In the olden days, we’d say that they glued two 26 core CPUs together to make one 56 core CPU. The work that Intel had to do, to make this happen was definitely more complicated than this, but this thought exercise works for those of us not in the CPU industry.

These new CPUs use a shockingly small amount of power to run. The chips can use as little as 27 Watts of power, which is amazingly low, especially when you consider the number of CPU cores that we are talking about. Just a few years ago, these power numbers would be unheard of.

Denny

The post Servers running too slow, just add all the cores! appeared first on SQL Server with Mr. Denny.

This thread on Twitter last night really piqued my interest:

Looking for your Ideas – The biggest challenge at the moment is explain to traditional #ITPro audiences why upskilling in the cloud is important. How are you communicating this message w/o alienating them? Discuss.

— Michael Bender (@MichaelBender) April 10, 2019

It really made me think of a conversation I had with a colleague in my last “regular” job. I’m not counting my time at Comcast, because we were effectively a technology firm. I mean a normal, regular company whose core business does not relate to computers or software. Scott, who was my colleague had just attended TechEd 2011, or maybe 2012–the years run together at this point. His comment was “with everything going to the cloud, it seems like all the jobs will be with Microsoft, or helping other customers implement cloud.” In 2011-12, the cloud was still really awful (remember the original SQL Azure? I do, and it was bad), but it was clear what the future would be.

The Future is Here What Do We Do Now?

So if you are working in a “traditional” firm, and you feel as though your skills are slipping away, as the rest of the technology world moves forward, what should you do? The first thing I’m going to say isn’t an option for everyone, because of challenges, and personal situations, but given the current state of economy and IT employment, I think it needs to be said. If you are in a job where you are only supporting legacy tech, of which I don’t really mean on-prem firms–some of the most cutting edge SQL Server orgs in the world are 100% on-premises, but if you are regularly supporting software whose version conforms to the regular expression ^(200)\d{2}$my best bit of advice to you would be to start the process of finding another job.

I know changing firms isn’t for everyone, and if you want to become a cloud engineer, you need to build your skills in that space. The crux of the twitter thread is how do you learn this things when you are in an organization that thinks that cloud computing has something to do with rain? The first thing I would recommend, if you are willing to spend a little money, is to use skillmeup.com (note: both DCAC and my company have business relationships with Opsgility, the parent company). I have taught classes using their labs–you get a real Azure subscription, with a production scenario, and you also get online training associated with the lab.

Other resources like Pluralsight or LinkedIn Learning (note: DCAC has a business relationship with LinkedIn Learning) offer online training, however I really feel like getting hands on with tech is the best way learn tech.

My Budget Isn’t Even That High

Both Amazon and Microsoft offer free trials–I know Azure a lot better, so I’m going to focus on that scenario. (BTW, this ties to another bit of advice I have, learn one cloud first. The concepts between them are pretty similar, and if you learn one cloud really well, transitioning to the other one will be much easier than trying to consume all of it at once). The Microsoft offer gives you $200 to use for 30 days, also if you have an MSDN subscription you also get somewhere between $50-150 month to use.

While those numbers are small, especially when talking about services, it can still easily get you started with the basics of cloud. Virtual machines (which also cost a lot) are for all intents and purposes very similar to your virtual machines on-prem. But if you want to learn how to extend an on-premises Active Directory to the cloud, you can do that by building a Windows Server VM on your laptop, and then connecting to Azure Active Directory. That has minimal cost (AAD is a free service). Also, learning things like networking and storage also have minimal cost.

One of the most important cloud skills you can have, Automation, just involves using PowerShell (or CLI, depending on what you like). If you haven’t learned a scripting language, you should invest more time into that. You can do this on any trial account, and with a minimal cost, especially when you learn how to clean up the resources you deployed as soon as your deployment script created.

As a SQL Server pro, if you want to start learning Azure SQL*, you should get started with Azure SQL Database. It’s cheap, and you can do everything you can do in the $15,000/month database with the $5/month database.

tl;dr

This was a long post. Here’s what you should start learning for cloud:

• networking
• storage
• security
• platform as a service offerings in your field and how they work with networking, storage and security

You can do all of these things with a minimal financial investment, or perhaps even for free.

Summary

You are in charge of your career, not your current employer. If you want to advance your skills you are going to have to work for it, and maybe spend some money, but definitely a big time investment. Also, consider going to some training–I just did a precon at SQL Saturday Chicago, and while the attendees aren’t going to be cloud experts after a day, they have a great basis on which to move forward. Books and reading are challenging in a cloud world–it moves quickly and changes fast.

There are many great resources out there for data visualization. Some of my favorite data viz people are Storytelling With Data (b|t), Alberto Cairo (b|t), and Andy Kirk (b|t). I often reference their work when I present on data visualization in the context of the Microsoft Data Platform. Their work has helped me choose the right chart types for my data and format it so it communicates the right message and looks good. But one topic I have noticed that most data visualization experts rarely address is the question I get in almost every presentation I give:

How do I tell a story with data when my data is always changing?

If you are a BI/report developer, you know this challenge well. You may follow all the guidelines: choose a good color palette, make visuals that highlight the important data points, get rid of clutter. But what happens when your data refreshes tomorrow or next month or next year? It’s much easier to make a chart with static data. You can format it so it communicates exactly the right message. But out here in Automated Reporting Land, that is not the end of our duties. We have to make some effort to accommodate future data values. Refreshing data creates issues such as:

• We can’t put a lot of static explanatory text on the page to help our audience understand the trends because the trends will change as the data is refreshed. Example: “Sales are up year over year, and the East region is the top contributor to current quarter profit” is true today, but may not be true next month.
• My chart may look good today, but new values may come in that change the scale and make it difficult to see small numbers compared to a very large number. Example: A bar chart showing inventory levels by product looks reasonable today because all products have a stock level between 1 and 50. But next month, a popular new product comes in, and you have 500 of them, which changes the scale of your bar chart and makes every other product’s inventory super small and not easy to compare.
• I can’t statically highlight outliers because my outliers will change over time. Example: I have a chart that shows manufacturing defects by line, and I want to highlight that the dog treats line has too many defects. I can’t just select that data point on the chart and change the color because next month the dog treats line might be doing fine.

How do we form a message with known metrics and data structure but without specific data values?

When people have asked me about this in the past, I gave an answer similar to the following:

Instead of a message about specific data values, I consider my audience and the metrics they care about and come up with the top 2 or 3 questions they would want to answer from my report. Then I build charts that address those questions and put them in an order that matches the way my intended audience would analyze that information. This might include ordering the visuals appropriately on a summary page as well as creating drillthrough paths to more information based upon items and filters selected to help my user understand the reasons for their current values.

While this isn’t horrible advice, I felt I needed a better answer on this issue. So I sought advice from Andy Kirk, and he responded brilliantly!

To the issue of situations where data is periodically refreshed, I see most encounters (ie. the relationship between reader and content) characterised less by storytelling (the act of the creator) and more by storyforming (the act of the reader). 

Andy Kirk

Storyforming

Andy went on to explain, “What I mean by this is that usually the meaning of the data is unique to each reader and their own knowledge, their own needs, their own decision-contexts. So rather than the creator ‘saying something’ about such frequently changing data in the form of messages or headlines, often it might be more critical to provide visual context in the form of signals (like colours or markers/bandings) that indicate to the reader that what they are looking at is significantly large/small/above average/below average/off-target/on-target/etc. but leave it up to THEM to arrive at their own story.

Then he gave an example: “I find this context a lot working with a football club here in the UK. Their data is changing every 3-7 days as new matches are played. So the notion of a story is absent from the visuals that I’m creating for their players/management/coaches. They know the subject (indeed better than me, it’s their job!), they don’t need me to create any display that ’spells out’ for them the story/meaning, rather they need – like the classic notion of a dashboard – clear signals about what the data shows in the sense of normal/exceptional/improving/worsening.”

Andy also agreed that a key aspect of storyforming is that interactive controls (slicers, filters, cross-filtering capabilities) in your report consumption tools give the reader the means to overcome the visual chaos that different data shapes may cause through natural variation over time.

Less Eye Rolling, More Storyforming

If you are a BI developer and have rolled your eyes or sighed in frustration when someone mentioned storytelling in data visualization, try thinking about it as storyforming. Make sure you have the right characters (categories and metrics) and major plot points (indicators of size, trend, or variance from target). You are still responsible for choosing appropriate chart types and colors to show the trends and comparisons, but don’t be so focused on the exact data points.

Many reporting tools (including Power BI) allow you to perform relative calculations (comparison to a previous period, variance from goal, variance from average) to dynamically create helpful context and identify trends and outliers. In Power BI, there are custom visuals that allow you to add dynamically generated natural language explanations if you feel you need more explanatory text (ex 1, ex 2, ex 3). And Power BI will soon be getting expression-based formatting for title text in visuals, which can also help with providing insights in the midst of changing values.

But mostly, try to design your report so that users can slice and filter to get to what matters to them. Then let your users fill in the details and meaning for themselves.