Voice of the DBA

Category: Editorial

  • More Query Tuning?

    This is probably a topic near and dear to the hearts of Grant, Brent, Paul #2 (White), and plenty other more well known speakers in the #sqlfamily community that often present on the topic of writing more efficient code. They do a fantastic job and if you get the chance to see any of these three, take it.

    Recently I saw someone on Twitter ask for more query tuning sessions at SQL Saturday and larger conferences. These seem to be very popular sessions, usually well very attended. Despite this, I don’t see a lot of these sessions compared to the popularity. I sometimes wonder if this is because relatively few speakers want to tackle complex challenges? Or maybe many don’t feel confident portraying themselves as experts in this subject? Is query tuning 101, or even 201, boring and less interesting for speakers?

    I don’t know, and I’ve avoided the topic myself. Part of this is to not conflict with friends, but also it’s a complex topic to try and cover. Despite that, I keep thinking that some more basic concepts would be welcome by many that attend SQL Saturday events. I expect that a there could probably be some sort of performance or tuning talk every hour at a conference and plenty of people would attend. That makes me think I ought to do a tuning session of some sort, just to help ensure this topis is covered more often.

    Picking a mix of topics and levels is often a difficult task for many organizers. As this year’s Summit sessions were released, I’ve seen a number of speakers bemoan that their favorite topic has few, often just one or two, talks scheduled. I think this is somewhat inevitable as new technologies get folded into the Microsoft Data Platform. I know that many of us are excited about one thing, the item that we use most often, or that we’d like to use more. I think the addition of Python is great, but it’s a small part of the platform, it’s new, and I don’t know how many other people want to use it. The same thing could be said for containers, for Query Store, and more.

    Building a schedule for a conference is about making choices and decisions that give a variety of topics, but also include some depth and detail in those areas that are popular. If you’re attending (or attended) a conference this year, the Summit, SQL Bits, a SQL Saturday, etc., what do you want to see on the program? Should there be more performance tuning sessions or do you like a wide variety of topics that let you choose what might suit you?

    Steve Jones

    The Voice of the DBA Podcast

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

  • Building Test Data

    One of the debates I’ve seen over the last few months is about test data in development environments. As I’ve been preparing for and learning more about the GDPR, it seems that many companies are concerned about holding sensitive data in their development systems. I think it’s a valid concern, and I’ve often had to deal with this issue in the past, before any regulation impacted my work.

    In one of my early jobs, we stored emails from customers in a table. We also had an email feature for our application. Needless to say, we needed to test that, though we didn’t want to obviously send emails to real customers when we were testing some sort of feature. I’ve done that and usually it results in a complaint and some scolding of the development staff. As a result, I learned to ensure that anytime we restored production to our QA system, we ran a script that would either change all emails to invalid values, or reset them to something we could use in a test system. In some cases, we’d reset them all to a specific address that we could check to see if the emails actually were sent.

    In talking to many people, they often don’t build test data for development systems because the data isn’t valid. What a developer thinks about, or what might be randomly generated by some utility often isn’t seen as valuable. Most developers want to see real data, perhaps because they can then better relate their work or a specific feature to the actual live system. Maybe it’s easier to see actual customers, products, accounts, etc. when working with clients or testers, but I do think that certainly in light of the GDPR and other regulation, there are risks here.

    While many people want to just restore production to refresh environments, I do think it’s a poor idea to use actual sensitive data. Even if you trust your developers, there have been no shortage of attacks against development systems, loss of laptops or other files with production data that were intended for developers. We just don’t secure test and development environments like production, and that means we are making a fundamental error in how work habits.

    I’ve gone through different views on this topic across the last couple decades, and now I want some real data, some not real data. What I’d really want is a bunch of random data that is close to production, mimicking the shape and skew of production, but without any sensitive data. Then what I’d like is a set of known cases of data that are the types of data that we need to ensure works in our system. Various cases of transactions and values designed to cover the functional edges that we support.

    Of course building these sets isn’t’ always easy, and it’s never going to be done. As long as we write software, we will need to maintain tests and data alongside the code. I do think this is a worthwhile investment in regulated industries, and likely worth doing in all industries. The thing is, it’s not interesting or fun, and likely not to be ever be done in most organizations. I’d like to change that, and I hope you do as well.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Data Submarine

    For some reason, this came to mind: under the sea, under the sea. That’s what I thought of as I was reading about Microsoft sinking a data center. There are probably jokes to be made about Microsoft and sinking, though they’re less humorous as the company’s stock price has risen quite a bit in the last few years.

    The is a research project from Microsoft on future data center design. Using modular devices that can be submerged for years at a time, only having a connection for power and data back to the surface. These are designed as sealed environments, without the creature comforts needed in data centers for human technicians. The systems inside are built to live on their own, using the water for cooling the heat generated by computations.

    There won’t be any repairs or replacements for failures. With the equivalent of twelve racks of servers in the system, I wonder how long they will list. I find it interesting that the experiment is designed for a year, though the device should have a timeline of 5 years. Does that mean that Microsoft expects current hardware to last for five years? Is that the new lifecycle of modern chips and storage? Or perhaps they find the lifecycle is shorter, but this is more a test of the extreme lifetime that they expect and they’ll track and chart the failures across time of components? I expect they already know some of the expected lifetime of hardware from their massive Azure data centers, which they can compare to this environment.

    It’s an interesting idea, and one that might see smaller, modular data centers spread around bodies of water where there is enough movement to carry heat away. This should reduce power consumption, as less is needed for cooling. This can also reduce latency, with devices perhaps located closer to clients for heavy compute capabilities or even content delivery.

    I’m not sure these will work at larger scales, as heat attracts life, with plant and animals potentially migrating to be near the submersibles. Who knows if were would be interference with operations, but I wouldn’t be surprised to find that some level of maintenance is needed. We might see the rise of a new type of job, like undersea gardener or window washer that keeps the submarines clear of encroaching biology.

    Of course, I wouldn’t be surprised to see Roomba-like automated devices that put many of these humans out of work.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Machine Learning in the Database

    When SQL Server added the ability to execute R code, the decision seemed to split the customer base into two groups. One group was impressed and thought the idea of executing R code to analyze data in the database was a good idea. They were excited and impressed by the loan classification demo. If you haven’t read about this or seen the demo, it’s very interesting, and it’s something you might take a few minutes to read or watch it.

    The other group of customers felt this was a poor use of CPU cycles for a very expensive SQL Server CPU license. Running a complex analysis, training models, and other functions commonly associated with R scripts aren’t a good use of scarce resources. They would rather have R code execute on a separate server, much like any large messaging workload might be better served by a service such as AWS’ Simple Queue Service rather than Service Broker.

    I tend to be in the first group, as is Dr. Low. He writes that there is a place where Machine Learning Services (MLS), with both R and Python, are a good use of resources. Not in all cases, and certainly not for all work. The difficult parts of training models and doing the hard work of coming up with new ways to perform an analysis is definitely better left to workstations and data scientists. Those actions might not be worth the resources they take.

    Once the models are trained, however, the executable load of submitting parameters to a model and getting a prediction is small. SQL Server allows us to load pre-trained models into the database and just call them as needed. Plus, the R models run in a multi-threaded fashion, unlike the single threaded execution in clients such as R Studio.

    As with any feature of SQL Server, it’s important to test and evaluate the real world impact of new code on production sized workloads. Not only will you want to measure the load of your model execution, but you should also measure any changes in your existing workload with the additional R or Python code load. While I wouldn’t prevent the use of MLS in SQL Server, just like SQL CLR code, I would be careful about introducing without extensive testing, including dark deployments and simulated loads.

    Steve Jones

    The Voice of the DBA Podcast

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