Voice of the DBA

Author: way0utwest

Quick Schema Auditing
I was working on a demo recently and needed to show that a little monitoring can help you catch schema changes. At first I looked at SQL Audit and DDL Triggers, but then I ran across a short custom metric on the SQL Monitor Metrics site that my company, Red Gate Software, put up to help people share their custom monitoring metrics and alerts.

The metric is called Schema Modified, and it uses a really simple query. This is all is does:
```
SELECT DATEDIFF(ss, '1970', MAX([modify_date]))
 FROM [sys].[objects];
```
It calls this query every minute for each database on which you have it enabled. This gives you a count of the number of seconds between 1970 and the latest schema modification in your database.

Now that’s not terribly useful, but if you look for changes in this metric, then it becomes interesting. For example, in one of my tests, I got this value

1413904788

If I continued to run the query, the same value was returned if nothing changed in the database. However once I added a new object, then the value changed to

1413905295

That’s an increase, and my alerting was looking for changes in the value, so when this new count of seconds appeared, an alert was raised.

Using the Information

What good does it do you to know that something changed? Admittedly, this may or may not be useful. This doesn’t tell you what changed, and certainly help you determine who changed things.

However, in more than a few of my development jobs, we knew people would change things. That wasn’t the issue. Really we wanted to know that something changed, and if so, we would investigate further. Often we could easily determine who made the change, based on what it was.

This is really a trigger more for something like production, where I don’t expect changes, except when I deploy things. Any other change is cause for concern, and I might have alerts set to ping people when there’s a change. If we’re making the change, then we ignore the alert, because we’re aware of it.

If we aren’t deploying changes, then we start investigating immediately.
October 29, 2014
Triple Digits

The salary surveys for 2015 look good for data professionals. The Robert Half results that appear in this piece show lots of salaries creeping well into the triple digits for annual salary in the US. Not only are the upper bounds over $100k for many data professional areas, but so are many starting salaries.

The values show a good increase from 2014, so if you aren’t thrilled with your job and are thinking of changing positions, you might land yourself a nice raise. I wouldn’t encourage anyone to change jobs for just money, and certainly the problems at your employer might be worse at others, but if you have other reasons for leaving, certainly look for a raise if you can.

I’m also pleased to see a wider variety of positions appearing in the survey results. To me that points to a maturing of our industry and the recognition that we do a variety of jobs, all of which are different. And based on the salary responses, all of these are also important to companies. I’m have confidence that as the data we manage becomes more important, and many of us continue to work on improving your skills, we’ll continue to see greater rises in salaries in the future.

The one thing in the piece that caught my eye was the “add 7% to salaries for Oracle database skills.” I get that Oracle costs more, but do we think Oracle data professionals are worth more for that reason? Or because Oracle is a bigger pain in the rear to deal with?

Steve Jones

The Voice of the DBA Podcast

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

October 28, 2014
Default Framing–Window Functions
I’ve been playing with the window functions in T-SQL a bit, and I find them very interesting. They certainly solve some problems very well, in a way that’s much easier than has been available in T-SQL.

However there are some things you need to understand. One of these things is the framing of the window, and the data processed. A quick example will show some of this.

Imagine that I create a table and insert some data:
```
CREATE TABLE HomeRuns
( hrid INT IDENTITY(1,1)
, player VARCHAR(200)
, team VARCHAR(200)
, hrdate DATE
, HRcount TINYINT
CONSTRAINT hr_IDX PRIMARY KEY (hrid)
);
GO
```
We add a few row, which I’ll keep short.
```
INSERT HomeRuns (player, team, hrdate, HRcount)
 VALUES ('Troy', 'COL', '4/7/2013', 1)
      , ('Troy', 'COL', '4/18/2013', 1)
      , ('Troy', 'COL', '4/22/2013', 1)
      , ('Derek', 'NYY', '5/7/2013', 1)
      , ('Derek', 'NYY', '6/24/2013', 1)
      , ('Nelson', 'BAL', '3/31/2013', 1)
      , ('Nelson', 'BAL', '4/2/2013', 1)
      , ('Nelson', 'BAL', '4/20/2013', 1)
      , ('Lonnie', 'CLE', '5/9/2013', 3)
;
GO
```
This is a small set of data. Let’s imagine that I want to count the total home runs by team in each month. If I try to do this with windowing, I’ll get something like this:
```
select 
  team
  , datename( mm, hrdate)
  , HR.hrdate
  , sum(HR.HRcount) over (partition by month(HR.hrdate), team)
 from dbo.HomeRuns HR
```
When I run that, I see all the rows returned, which isn’t what we expect from aggregates. However it’s also not any kind or running total or examination of the data in a row by row processing that many window functions perform.

That’s not completely accurate, but it’s a feeling many people get when starting with these structures. However the results highlight something. Let’s look at them:

We can see that for April, for Baltimore, we see 2 rows, with a total of 2 for each row. There was one home run hit on each day, and the total is 2, but both rows are processed as one window.

Why?

That’s because the framing, the section of the partition that’s examined by the window aggregate, is the “”RANGE” of the partition by default. The entire partition, so all rows in front of, and behind, the current row, are used for the results.

This is a simple example, but it does show that you need to be aware of the default, which I don’t love. I wish the default were ROWS, and I’ll talk about that another time.
October 27, 2014
CI and CD Aren’t Magic

I’ve been studying and talking with lots of developers and DBAs about Continuous Integration and Continuous Delivery for a few years. Many technical people are excited about the possibilities and look forward to trying to automate their builds, their testing, and improving their software. However there are no shortage of concerns about the cultural problems of getting both technical staff and management to change the way they perform development.

These are very valid, and very real concerns.

CI and CD aren’t magic. Just like Agile, Pair Programming, or any other methodology, these development techniques don’t produce better software by themselves. There need to be cultural changes in how an organization views software and an investment in learning to adhere to the process and apply solid software engineering processes throughout a project. Merely adopting new process without changing your view on testing, and on accepting and using the feedback, will not create better software.

It’s a bit of a leap of faith to agree to implement testing earlier in the development process. The results of that additional work don’t readily appear, and certainly code will be written slower. It’s a stretch for many people to think that fixing problems returned by a CI process and maintaining a clean build will pay off over time. I agree, and I’ve questioned the value myself. However I also see that companies implementing these changes produce better software over time, and more reliably. If they really change their culture to believe in CI and CD.

However the better evidence, to me, is that companies that continue to produce software the way they have for years, continue to produce software with lots of bugs, that’s also over budget and late. Perhaps that’s good enough for many companies, and that’s sad. I hold out hope that anyone producing software, from managers to developers, and everyone in between, would want to do a better job with their next project.

Steve Jones

The Voice of the DBA Podcast

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

October 27, 2014