Voice of the DBA

Author: way0utwest

The Data Scribe

There’s an interesting piece in the New Yorker about technology in the medical field from Atul Gawande. It talks about the love hate relationship doctors have with medical systems, and ruminates a bit about whether these new systems are making medicine better or worse. I think Dr. Gawande is a very interesting individual, and whose Checklist Manifesto is a great look at improving processes.

The article talks about the struggles of doctors with using various systems, but there is one interesting solution that some companies have tried: medical scribes. For these companies, rather than teach a doctor everything and have them do their own work and spend less time with a patient, the meetings are either recorded or witnessed by a scribe. The scribe handles the computer work, writing down observations, notes, orders, etc. The doctor later approves things, but this allows the doctor to focus more on patient care and get less caught up in technology.

Years ago I worked in a company that had some extensive technology in some areas, but some older senior management. There were still secretaries that literally took dictation, handled schedules, and printed reports for the senior executives, who almost never used their computers. The might pull up some emails, but anything that required more than a few keystrokes was dictated to a secretary who wrote and sent the emails. At the time I thought this was wasteful, after all, couldn’t an executive type a few emails themselves?

I thought of that experience while reading the other story about doctors. Certainly the executives could type emails, but not as well as others, and was that a good use of their time? Certainly today, with far too much communication being sent, I might argue executives ought to have less access to email, not more. Certainly upper level execs do have assistants, but is it really a good use of time for directors and managers to be dealing with many of our computer applications?

What about report tools like Power BI and Tableau? Is that a good use of anyone’s time in finding data and assembling it into reports? The analysis is, but tracking down data, formatting it, and more feels like something that is a skill in and of itself.

Perhaps we need data scribes in more industries. Not a single employee dedicated to one person, but a specialist in the gathering and cleaning of data, preparing it in tools that business people then use to perform their analysis, working for multiple people. Need more data? Ping your data scribe, someone that knows the structure, meaning, and location of data.

Would you want that job? I know I’ve done this at times for people, finding the data they need and producing a report, often showing them how to do it themselves, but I wonder if that’s a good idea. With data changing, new sources appearing, stronger access controls and more, perhaps this is a role that more companies ought to embrace to ensure that those with experience analyzing data aren’t spending time fiddling with it.

Steve Jones

The Voice of the DBA Podcast

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

November 20, 2018
Webinar: What We Learned at the PASS Summit

Join me tomorrow for a webinar from Redgate Software.You can register for our webinar today at 10am EST here.

Kendra Little and I join Grant Fritchey, the President of PASS, to discuss some highlights from last week’s PASS Summit.

Register today and join us tomorrow.

November 19, 2018
New Database Options

I saw recently that Azure SQL Database is getting a few more Database Scoped Options for that platform. These are intended to give more control over the way in which the engine behaves, without requiring each database on a server to function the same way. I expect to get to the on-premises product at some point, where they’ll be even more useful as we often might want different behavior for different contexts on an instance.

While there are advantages to managing all databases in an instance in the same way, I do think that more and more we consolidate databases at times and it’s better to have additional control when needed at the database level. This week, I wonder if there are things that you wish you would have been able to specify for each individual database.

What options would you want to see added at the database level?

I think that many of the options we’ve been given in current versions, as well as the newer ones appearing in SQL Server 2019 are a good start. I don’t know which instance level settings I might want here, but I certainly would like to see newer capabilities at the database level. It would be nice to see the capabilities for jobs and alerts to be set at the database level. Even if this were a part of the Agent subsystem, having the ability to keep these jobs within a database and have the agent read them would be useful.

Moving more capabilities to the database level gives us more flexibility in separating the workloads for different applications. With the movement of the platform code, and many customers, to Azure SQL Database where the system requires less dependence on an instance, it makes sense to start including more options at the database level. I would guess that at some point most of the settings that we need for manage a system will be included and set at the database level.

Steve Jones

The Voice of the DBA Podcast

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

November 16, 2018
Fun with Savepoints–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I haven’t spent a lot of time with savepoints, but I did find a question recently and thought I’d take a moment to dig into how they work. They are interesting, and they can be useful for you in certain situations.

Warning: Anything involving transactions can be tricky, so be sure you test, test, test and check out how things work with a wide variety of situations, including some you might not expect.

Here’s a basic setup. I’ll create a table to log some actions.
```
CREATE TABLE TransLogger

(ID INT IDENTITY(1,1) NOT NULL CONSTRAINT TransLoggerPK PRIMARY KEY

, LogMessage VARCHAR(200)

)

GO
```
Now that I have a table, let’s do something in a transaction. I’ll start a transaction, make two inserts, but set a savepoint between them
```
BEGIN TRANSACTION
INSERT dbo.TransLogger (LogMessage) VALUES ('First insert inside transaction')
SAVE TRANSACTION Firstsave
ROLLBACK TRANSACTION Firstsave
COMMIT
SELECT top 10
  *
  FROM dbo.TransLogger AS tl
```
If I look at the results, I see this:

That makes sense. I inserted this row (I’ve been testing, so that’s why it’s 11), and marked a savepoint with the SAVE TRANSACTION Firstsave line. Then I rollback a transaction to this savepoint, which does nothing. Finally I commit. I see my one row.

Let’s add something. I’ll add a second item, and decide to roll it back.
```
DECLARE @rollback INT = 1
BEGIN TRANSACTION
  INSERT dbo.TransLogger (LogMessage) VALUES ('First insert inside transaction')
   SAVE TRANSACTION Firstsave
  INSERT dbo.TransLogger (LogMessage) VALUES ('Second insert inside transaction')
   IF @rollback = 1
     ROLLBACK TRANSACTION Firstsave
COMMIT
SELECT top 10
  tl.ID, tl.LogMessage
  FROM dbo.TransLogger AS tl
```
Note I’ve added a variable so I can decide to rollback or not. I’d often have some condition or error handling that might cause a rollback, so this simulates that. Note that work before the savepoint is committed, but work after is removed with the ROLLBACK TRANSACTION Firstsave.

My results are a single row. Note, I cleared the table between runs.

Savepoints give me a place to commit work if I need it before doing more. This potentially allows me to capture some changes and not others if I don’t want to fail my entire transaction.

Personally, if I’m doing this, I would likely just have two transactions if I can have one commit without the other.

SQLNewBlogger

A few minutes of experimenting gave me a quick post. I need to do more, and certainly test more, but this is a basic idea of what savepoints are. You can write something similar.
November 15, 2018