Voice of the DBA

Category: Blog

Recharging

It’s that time of year when many people take vacation and get away from work for a bit. I’m going to join in, taking a few days off this week. I was off yesterday, with family in town for my middle son’s high school graduation.

The extended family is leaving, but this week my wife, kids, and I will take a few days in Steamboat Springs, looking to recharge and relax before everyone gets on with their busy summer.

I thought about taking a computer, maybe doing a little “fun” coding, but I think I’ve decided I’ll stick with a bike, a guitar, and just unwind in an unwired fashion this week.

As much as many of us like computers, it’s good to get away, and find something in your life that’s a change for a few days.

May 24, 2016
Changing a Computed Column–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was working with a computed column the other day, and realized I had the wrong definition. In this case, I was performing some large calculation, and the result was larger than an int. However the first part of the formula was an int, which resulted in an implicit conversion to an int.

I needed to change the formula, and then realized that plenty of people might not work with computed columns much, and not realize how you alter a computed column.

You don’t.

In fact, you need to drop the column and add it back. In my case, this was what I did. Here was my table:

CREATE TABLE SiteStats
(
StatID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED
, StateDate DATE DEFAULT SYSDATETIME()
, StatMonth TINYINT
, StatYear int
, PageVisits INT
, TimeOnSite TIME
, Engagement AS (PageVisits * DATEDIFF(SECOND, CAST(’00:00:00′ AS TIME), TimeOnSite))
)
I wanted to cast the PageVisits part of the column to a bigint to solve the issue. I first needed to do this:

ALTER TABLE dbo.SiteStats
DROP COLUMN Engagement
Once that’s done, I can do this:

ALTER TABLE dbo.SiteStats
ADD Engagement AS (CAST(PageVisits AS BIGINT) * DATEDIFF(SECOND, CAST(’00:00:00′ AS TIME), TimeOnSite));
GO

Now I have a new definition that works great.

Some of you might realize that this could be an issue with columns in the middle of the table, and it is. However you shouldn’t worry about column order. Select the columns explicitly and you can order them anyway you want.

SQLNewBlogger

A quick post, five minutes. Even if you had to search for how this works, you could do this in 10-15 minutes, tops. Research, write why you did this and potential issues with your system.

May 18, 2016
The New Global Dashboard

Redgate recently released SQL Monitor 5.2, which is the latest upgrade to our monitoring/alerting/troubleshooting product for DBAs. This was the big change that the team has spent a lot of time developing and refining. It’s been available for a few weeks as a hidden URL, but with 5.2, this becomes the default main screen for SQL Monitor.

I think it’s a good move forward. In general, I don’t like things moving around physically in applications, as I get used to them being in a certain spot, or I expect them. However in this case, it makes sense.

In a dashboard for monitoring and alerting, you want to know what’s broken. Having a list of 20 servers at the top, and 1 broken one potentially “beneath the fold” (in newspaper parlance) and requiring scrolling would be bad. As a DBA, I’d want to see those items that are problematic. When I look at the monitor.red-gate.com site, I see:

The cluster has a long running query, which is an active, high priority alert. If I were to clear this, along with the other active alerts, this “card” would move to the end, and the sm-cluster2 item would take the top left spot.

Note there are options to configure what is a high or low priority, and even pin specific servers at the top, but the general behavior is to let you know what’s broken now.

There’s one other cool feature in this. If I have a high level alert, like a machine unreachable, and the alert clears itself (the machine reboots), I may see the machine as “green” on the dashboard when I login. This is because current alerts are shown, not historical ones. I can still get the historical data, but the intention is to make this a responsive tool for right now, not last night.

I think this is a great change, and I’m excited to see how well it works in practice as customers roll this out.

May 13, 2016
SQL Nexus
I was lucky enough to be accepted to speak at SQL Nexus in Copenhagen and I attended the event a few weeks ago. This is the new Nordic SQL conference that seems to be replacing the SQL Rally. I’d never been to Denmark, but the trip was easy, and I had no problem getting from the airport to the area downtown where the event was held. Surprisingly (for me) everyone I met spoke English, which was nice, considering I didn’t even know how to pronounce many words, including the train station I was trying to get to.

Being on the water, the speaker dinner was appropriate and quite enjoyable. I’ve had some good ones, but I think this was the best of them all.

The event was held in a movie theater, which I think is very interesting. The spaces were large, theater style room with big screens. Our computers were hooked to the projectors, which worked well. You can see the keynote in the IMAX room below.

The keynote was interesting, from Joseph Sirosh (Microsoft) and Troels Peterson (physicist at the Neils Bohr Institute). The same keynote from Joseph was at SQL Bits, but I don’t think Dr. Peterson went over to the UK. I want to write on Joseph’s a little later, so I’ll just take a few moments and show some highlights of how data is managed at the Cern Hadron collider.

Dr. Peterson works with the ATLAS detector. He had a few nice stats on the hardware.

If you do some math here, you’ll see that when they run the detector and conduct an experiment they produce a lot of data. In fact, this was the next slide:

That’s serious data. It was interesting that he said that’s an unmanageable amount of data. In fact, they need sensors to make decisions on the raw data because they can’t even use computers to analyze this level of information. However, they do have computers. In fact, they have:
- 1127 racks
- 10,070 servers
- 17, 259 processors
- 90, 948 cores
- 75,718 disks
- 113, 852 TiB raw disk
- 312 TiB of memory capacity.
- 120 tape drives
- 52000 tape catridges
- 75 PiB data on tape.
In all their analysis, searching for the secrets of the universe, they’ve learned a lot and gotten better at finding anomalies and problems with data. They know all their data is flawed, so they must use algorithms to try and find the data they can rely on in the entire lake of bits that is captured and stored. They use a lot of machine learning to comb through data.

In fact, he said their research actually showed that there was a reason certain data was altered in line with the phase of the moon. In fact, Dr. Peterson said that they determined that the length of the collider tunnel actually lengthened by 1mm because of the moon.

There were lots of other interesting SQL Server 2016 talks, including the ones you’d expect on machine learning, R, one on IoT (a bit of a wreck of a talk) and a great one showing MitM and other attacks against a SQL Server from a Linux machine.

The event was two days long. I spoke on encryption and security changes in SQL Server 2016 that went well. I’ll do some writing on my demos, showing more Always Encrypted, RLS, Dynamic Data Masking, and more.

The theater was right next to the river, with a nice walkway. A few of us were able to run alongside in the morning. Plenty of people walked or biked along the river each day, and the weather was amazing.

I think this was a really nice conference, at a good cost for those of you in Europe. If you can get to Copenhagen, it might be worth the two (or three days with a pre-con) to try SQL Nexus next year. I’m hoping they do the event again next year and I’d certainly like to go if I can.
May 12, 2016