Voice of the DBA

Author: way0utwest

How Virtualized?

I went to a talk recently where I saw this statistic: “50% of all workloads were virtualized in 2009. That number is 72% today.”

That’s a really big number, at least in my mind. That implies the vast majority of all servers, file, print, database, email, etc. are virtualized. Inside of companies that have their own data centers and machines, they must be heavily virtualized. I’m sure that all those instances in the “cloud” also count, but still, 72%? That’s big.

However I’m sure that’s skewed towards those machines that don’t require a lot of resources, like file and print servers, DNS hosts, etc. This week, I thought I’d see what the percentage is inside of your organization.

What percentage of your SQL Servers are virtualized?

Give us numbers of physical v virtual if you can. I’d combine all instances, from development to test to production, not worrying about size or workload. If you have a single guest on a host, using almost all the resources, that’s a virtual server.

My suspicion is that the percentage of SQL Servers is much lower than that of other workloads, but I’m curious. With the low overhead of modern hypervisors, and the free (or low) cost, it makes sense to virtualize servers. If for no other reason than to remove any weird hardware dependencies for DR purposes. However I’m sure that there are large workloads that require more resources than the current hypervisors can expose, at least for some database instances, and those need to remain on physical machines, but my guess is more often than not, it’s the human concerns or lack of confidence that prevents virtualization.

Let us know this week how your organization is doing in the trend towards virtual servers.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and LibSyn.

May 8, 2015
PSA: Update Your PASS Profile

I got my annual two emails from the PASS organization with the notification to update my profile to vote. I’m not sure why I get two emails to two different email addresses when I’ve only logged into one profile account in the last six or seven years, but I’m sure the data pros at PASS will deupe their list one of these days.

Last year there were issues with voting and eligibility as many PASS members never logged into their profiles and weren’t seen as eligible to vote.

This is your Public Service Announcement.

If you didn’t get the email, or ignored it, go to sqlpass.org and log into your profile. I assume you can do that.

Once you do that, you should see this checkbox in your profile. If you don’t, contact PASS

This is what the email says.

If you didn’t get one and think you can vote, contact PASS and let them know.

May 7, 2015
Reframing to Overcome Filtered Index Limitations
I’m continuing on with the Blogger’s challenge in this post.

Turning the Problem Around

In the last post, I wrote about limitations in filtered indexes. I proposed that my table had lots of data with NULL or blank spaces in the gender column, and I wanted to avoid indexing those rows. I tried this code
```
CREATE INDEX Users_Gender_Filtered2
 ON Users (gender)
  WHERE (gender IS NOT NULL OR gender = ' ');
```
However, that failed. I can’t use two sets of criteria in the filtered index. But I can use one, so I need to re-frame the problem.

If I look at the data, I have four choices: ‘M’, ‘F’, NULL, ‘ ‘. I can group two of those choices together, looking for a positive (matching) set of data rather than a negative (non-matching set. The easy way to do that is with an IN clause.
```
CREATE INDEX Users_Gender_Filtered2
 ON Users (gender)
  WHERE gender IN ('M', 'F');
```
This works, and I’ve got a filtered index. In the real world, I’d actually drop the first index (gender is not null), and only go with this one.

References

A few places I used to research this post.
- https://msdn.microsoft.com/en-us/library/cc280372.aspx
- https://www.simple-talk.com/sql/performance/introduction-to-sql-server-filtered-indexes/
Quick and Easy Blogging

This post occurred to me as I was writing the other post. I almost added a note on turning the index around, but realized this is a separate topic, and it makes a nice, second post. This post required < 10 minutes.

This post continues the April Blogger Challenge is from Ed Leighton-Dick, aimed at new bloggers, but anyone is welcome. I’m trying to motivate and cheer people on.
May 7, 2015
vNext 2016

We have a name for the next version of SQL Server: SQL Server 2016. I suspect the internal build may be v14, which I guess makes sense if you’re superstitious about the number 13, or you think your customers are. There are plenty of v13s out there for various other software packages, so I’m not sure if this is an issue, but we’ll see when we get a public CTP for the next version of SQL Server.

vNext has been the way to refer to the next version of Microsoft products before a decision is announced about the official name. This convention seems to have taken over from code names, which makes sense, but it certainly is less fun than hearing Denali, Yukon, or some of the names that have been used for SQL Server.

Perhaps this is a sign of maturity within Microsoft? It certainly could be as releasing a new version of SQL Server every two years doesn’t happen without some discipline and rigorous engineering effort. Despite the issues with SQL Server 2014 SP1, I do think Microsoft has done a nice job taking a formal approach to software engineering with SQL Server.

I am a bit more interested in SQL Server 2016 than I was in SQL Server 2014. Perhaps that’s just the fact that some significant changes might take longer than two years to implement and a number of them are coming in SQL Server 2016. I certainly looked forward to SQL Server 2008 and SQL Server 2012 more than the “R2” and 2014 releases. It has seemed that every other release has more significant enhancements and includes more reasons for me to consider upgrading. I think SQL Server 2016 is one of those releases.

The datasheet shows a number of features that I Think make this a compelling release. I worry about security and the Always Encrypted idea seems very interesting. We’ll see how it looks once we get details. I like the improvements to AlwaysOn as well as the changes to include JSON and R support into the database engine. Row Level Security is in Azure now, but I think that’s important in the boxed product, and, of course, the Query Store is something I’ve been looking forward to for years. If you don’t know about that, read about it now. I was hoping to link to Conor Cunningham’s SQL Bits session, but I’m guessing recording wasn’t allowed.

There’s more coming, including the “Stretch to Azure” feature, which I’m not sure about. I’d feel better about “copy to Azure”, but perhaps I can keep local filegroup backups of this data, so that might make sense. I’m sure many of you won’t like some features, but this does seem like a compelling release. Depending on how pricing and licensing might change.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.5MB) podcast or subscribe to the feed at iTunes and LibSyn.

May 7, 2015