Voice of the DBA

Author: way0utwest

  • Where’s the schema?

    Across the last few years, I’ve read many articles and listened to quite a few talks that discuss the advantages of NoSQL databases. I’ll admit that I’m often skeptical of the advantages of other datastores overcoming the disadvantages with a relational system, but I try to keep an open mind. I do appreciate that there are some benefits to using another data store in certain situations.

    One of the talks I heard recently discussed the fact that in many of these stores, we can add data in a “schemaless” fashion, and it’s stored in a flexible format that allows the developer to quickly capture the data they are using and retrieve it without requiring up front design work to build a particular format.

    That had me pondering the question of whether or not here really are schemaless data structures. If a developer  (or whatever SDK or framework they use) looks to persist come data, clearly there is a format of sorts, which means there is a schema. That schema might not be transferred or persisted in the data store, but there is some schema they expect, both on storage and retrieval. Whether this is a JSON, XML, some proprietary structure, or something else, there’s an known structure that the developer uses to work with the data.

    Is there really schema-less data? I tend to think no. All of the data we have contains some schema. That schema might vary from row to row, which is often what developers like when building applications. There is, however, a structure. The developer knows it, and must serialize and deserialize the data, or depend on some library like ADO.NET to do so. This often appears to a developer to be a lower barrier to entry. There’s less complexity and often no need to map the objecct-like structure of properties to some relational schema and make decisions on sizes.

    That’s not completely true, as the schema of the data still exists and must be persisted in the application. There is code that must handle the various values stored in some hierarchical fashion. If this changes over time, as values are added, the the application must deal with the missing values in older properties or arrays. If items are removed in the application, then would older sets of data just disappear? Perhaps, but the developer must make a decision, which may have implications for users of their application. This doesn’t even deal with the issues of aggregation and reporting, which might force other systems to implement the same schemas and business logic. Those rules and specifications don’t easily transfer from one application to another, especially when different teams or developers are involved.

    There’s always a schema, and the rules have to be implemented up front, or later on. Whether you use a RDBMS or a NoSQL store, you are going to be dealing with a schema. The question is do you want to deal with it in a central location or in every application? I lean towards the former, but you might prefer the latter. Neither is wrong, but you should be sure you understand all the advantages and disadvantages of your choice.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Building Better Test Data on the Redgate Hub

    I have a new piece published over at the Redgate Hub: Building Better Test Data with SQL Provision. Part of my job is helping people learn to use our products better, and provide not only solutions to issues, but also ideas that might help them think about new ways to use products.

    I’m a big believer in testing and having good test cases in your data. This piece gives a technique I’ve used in the past to ensure we test everything we need.

  • Mastering Index Tuning–Day 3

    This is a short series of posts on the courses I took with Brent Ozar. I actually completed the courses in the past, but I wrote notes and wanted to revisit the way things went.

    This post looks at the Mastering Index Tuning class. Other  posts are:

    Day 3

    As with Day 2, we begin with reviewing the labs from yesterday. These were harder labs, and Brent spent time looking at how he solved the labs, referencing parts of solutions some people had. This took awhile, with a break in the middle.

    As usual, we can ask questions and discuss the solutions in Slack, which Brent keeps an eye on.

    We start the lectures with artisanal food, which Brent does enjoy. Hand crafted items from the chef, which felt funny since my car killed something and left an organ of some sort in the bathroom.

    The analogy is that there are artisanal indexes, like those on computed columns, indexed views, and filtered indexes. These are items that can help in specific situations, but in general we don’t want to use them.

    I like that Brent brings in the experience they’ve had with clients, noting that some of these features don’t work well.

    The afternoon lab is fix some really bad reporting procedures with indexes (regular or artisanal) or changing code. I know I can’t always change code in databases, but this gives us a chance to try things. I ended up changing some code, but not much. The lab review after lunch was interesting, as Brent had a strange result with the last proc. Looking forward to seeing his debugging of this later.

    The lecture after lunch moves to the end of D.E.A.T.H, heaps. I hate heaps, so this was interesting. Brent agrees with me, you really need a CI on the table. Maybe there are some reasons to not use one in a situation, but most of you need to just add a key.

    The last part of the afternoon looks at the impact of CIs and then constraints and FKs. The CI part is interesting. I see lots of people talking about how to decide on this. I tend to lean towards Brent’s view, which he’s presented on and it’s in the class. Take the class if you want to learn (I don’t want to republish here).

    For FKs/constraints, the module had lots of discussion. People think about FKs in interesting ways. I’ll have to re-watch this as I got busy in the middle with other stuff and missed some lecture.

    The final lab is a big one. Use all the skills from the three days of the class. Restore the db, run a setup that messes up indexes, then fix things. It was a challenge, and I burned about 12 minutes deduping and eliminating indexes, then about 20 coming up with more to add. The creation took quite some time, so I never really got around to tuning, and since this is only part of my day, I had to stop. I do have some real work to do.

    The final lab solution goes up the day after, and what Brent came up with was interesting. I liked watching the videos later to see how he approached the issues and solved them. I like that there wasn’t “one” solution, and he talks about how we might solve the lab that would be different than production.

    That’s important, and it’s something that I appreciated in this class. I know better, but it’s always good to be reminded that the class is a game, a model of what could happen, but in the real world, these are just tools that might help, but could hurt. Judgment is still needed.

    The Aftermath

    One thing I like about this class, which I’ve missed in some live classes, is that I can re watch sections of the class later. The class page has a list of all the lectures and labs, with each containing a video. Some might be from my class, some from previous ones. Since this is delivered and recorded in a modular fashion, Brent can update sections over time.

    I went back to watch the first Artisanal index module, as I was distracted that morning by something at work. That was a nice benefit.

    The Final Word

    This was a great class. I haven’t been to a real class across multiple days in awhile, and I think the format of some lecture, a lab (with interactivity), and then a review of the lab, was great.

    The lectures were interesting, and I learned a few things. The labs were challenging, designed to force you to work within constraints to tune something. Indexing is often a place where you can make changes and rapidly affect your system. The effects could be good or bad, so you need to be sure you are proceeding in a methodical fashion and also capturing metrics on the changes.

    If you’re interested in the class, you can visit the Mastering Index Tuning page to learn more and purchase the class.

  • DevOps is Really Helpful

    A few weeks ago I gave a presentation at the 24 Hours of Pass Summit Preview. During my session, a demo broke and I had to ignore it. Later I found the issue and blogged about it. What was neat for me is that I didn’t need to keep my code up or apps open, but I still found the issue quickly. I found it because I have a DevOps process that instruments and tracks everything.

    This happened to me a few years ago. I was at a SQL Saturday and demoing one of the Redgate tools. There was an error and I couldn’t move changes to a downstream database, which threw me off. That was the point of the talk. I decided to debug on stage (or behind the podium in this case), looking through the logs. Within a few minutes, I had found the issue and solved it. A real life DevOps story in action.

    The idea of DevOps is what most talented developers and DBAs do. They ensure that they don’t just make changes on a whim, and they don’t depend on SSMS to be kept open. If they find an error, they save it, or they ensure their process captures all relevant logging. I’ve built systems like this, but many of the modern tools we use in DevOps automatically do this. Build servers and release servers make it a point of capturing all logging, so when that PoSh or CLI system runs, the output is saved and available for solving problems. And the data is available for improving the process, which is the goal.

    At various times in my career, I haven’t followed a set process, I’ve “tried” things in SSMS, a configuration dialog, or a command window that haven’t worked. I’ve sometimes remembered to undo them, sometimes not. I’ve lost track of what I’ve run often, just because I was working in an ad hoc manner. That led me to learn to do better, which led me to embrace and follow many of the principles and ideas that people call DevOps today. These are things I learned to do those things 15 years ago.

    Lots of DevOps is about using automation and tools, and those really do help. Having a pipeline for software changes, and one for configuration changes, reduces the chance of mistakes and problems. It also lets you instrument and track everything that happens. However, these are the easy things. These are the things that really talented professionals already do in their daily work.

    The hard part of building software is building a culture that learns to work together instead of separately, or worse, adversarially. It is truly hard to work together and concern yourself with the customer first, and your job last. Or put the QA person, the DBA, the developer, or someone else ahead of what makes your day easier. It’s also hard for management to build a framework where we don’t have incentives to put our own interests ahead of others. If we can do that, and build a DevOps style process, things work smoother, and our software will be better. Maybe more importantly, we’ll enjoy our time at work.

    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.