Voice of the DBA

Category: Editorial

  • Where Do You Run Your R Scripts?

    I know most of you don’t work with the R language. In fact, plenty of you might not know anything about R other than a cursory understand of this as some sort of data analysis language. If you want to know more, here’s what the R Project is.

    Microsoft wants you to use R Services in SQL Server, or the R Server product available as a standalone system. However, I saw someone ask the question why would someone run their R scripts inside SQL Server, because these are expensive CPU cycles to burn on analysis. Someone else noted that Microsoft loves your licensing dollars, so their push to use R Services is perhaps a little self serving.

    Push the intellignce to the data makes sense. Isn’t that what we do with large data warehousing queries or SSAS cubes? We’re trying to get the analysis done at scale without having to move the data elsewhere, especially considering we’ve (usually) already moved the data in some sort of ETL (or ELT) process. Gaining insights from our ever increasing scales of data requires some computational cycles somewhere.

    What’s the alternative? Large queries that pull data to some client? I think that’s fine, and that might be a better alternative since simple queries to pull data don’t burn as many CPU cycles as those that might perform analysis. I certainly understand that the licensed CPU cycles for a SQL Server instance are expensive, and we want to be careful how they are used. Adding complex R scripts might not be the best use of our licensing dollars. On the other hand, if I can perform analysis quicker, that is more useful, than perhaps I can eliminate other random queries analysts want to run on my database?

    Ultimately I think that R Services make some sense in SQL Server, but not as some experiment. I would suggest that the R client is the way to experiment, preferably on a copy of data that allows someone to build scripts and determine if there is insight to be gained from a particular set of data. Build a Proof of Concept (POC), and only deploy it to a SQL Server if you find it provides value.

    And if you do so, continue to experiment. That R script you run today might not be as useful in six months as your application, database, and business evolve. Data analysis isn’t a set-it-and-forget-it, but rather an ongoing, iterative process.

    Steve Jones

    The Voice of the DBA Podcast

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

  • One Million

    Is a million writes/sec a large number? I guess it’s all relative, but I’d consider it a fairly large number. Our peak transfers for SQLServerCentral are in the hundreds/sec, though we’re not really a busy database server. Some of you might have much higher numbers, and if you’re in the 100,000/sec and can let us know, I’m sure others would be interested in hearing about your experiences.

    I ran across a piece on the Uber database infrastructure that I found impressive. No, Uber doesn’t use SQL Server (they use Cassandra), but they have worked to build a large scale infrastructure. Their goals:

    • 1 in 100,000 requests can fail
    • 1,000,000 writes/sec
    • > 100,000 reads/sec

    Those are quite impressive. While I’m not sure they’ve ever achieved these levels in production, I’m glad they’re testing at these points. I think far too many people forget to test the limits of where there systems might grow and only stick with where they are today. Or where they were a month ago when they refreshed a test environment from production. Test at larger than production levels, at least once in awhile.

    There’s something impressive with one million. Getting to a MB, roughly 1mm bytes, was impressive to me. Not such a big deal now (with pictures requiring > 1MB), but 1mm MB is a terabyte, and while I carry that in my pocket, it’s still an impressive size. Crossing one million members at SQLServerCentral was impressive. I think $1mm is a lot of money. One in a million still seems like a very small chance of an event. At the recent Data Science Summit, we see SQL Server scoring over 1mm fraud predictions/sec.

    Achieving 1mm of anything in a database system is still a large number. I know many people have tables with over a billion rows, but I’d still say a million is large. Perhaps you disagree, but I’m still a little awed at seeing SQL Server process a query of 1mm rows in less than a second.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Archaic Commands

    I was watching the San Diego Technology Immersion Group meeting in September, and found myself transported back into the past. This month was the first meeting of the Linux chapter, spending a few months working through The Linux Command Line.

    Watching from home, I watched the hosts walking through the command line in Linux, showcasing various basic file comments. Seeing them explain and execute commands like ls, rm, alias, cat, tail, less, and other commands in a text session reminded me of learning Unix at university on SunOS. Slowly various bits of knowledge came back to me from my own experiments learning how to work with files, full of text data, interspaced with my regular visits to the “man” pages when something didn’t work. In fact, seeing the man pages come up on the YouTube session made me smile.

    It’s funny to think how much the world has changed. Even now, I have an Ubuntu VM that I run at times, but it’s mostly a GUI shell, and my interactions with the command line are fairly minimal. That’s completely the opposite of my time at university and my first job where I pretty much lived in the command line. My code editors were vi and emacs that I ran across a dozen X Windows sessions open on various machines in the college computer lab. My interactions were really working with text and text commands. Even now, I might use SSMS and Visual Studio, but I’ll pop into a command line to check my git repo or spend time playing with PowerShell and executing commands in text sessions. While working with Python I’m as likely to use the REPL from a command window as I am to use an editor like VS or PyCharm. I really just like the interaction of the command line.

    Not everyone feels that way, and that’s fine. I agree that GUI based systems are more convenient at times. In fact, I often use various GUI items in SSMS build up a task, such as an Extended Events session, and then script the result to give me code that I can execute. GUIs do some things extremely well, but allowing for repeatability is not one of them. They can also easily hide the actual commands and defaults being executed.

    I think everyone working in technology needs to have some skill in working with a command line. Even if you want to use T-SQL to script all your tasks, you should be familiar enough with something like SQLCMD to execute a script in case you run into a situation where SSMS might not work. This might be especially true as you learn to automate more of the work your systems require and perhaps execute these scripts through other tools, like an Enterprise scheduler.

    If nothing else, the command line teaches you how to work in a methodical, step by step process. However, I’d argue it develops new skills that might lead you to do solve a problem in more creative ways. Perhaps you’ll even grow to use a command line language like PowerShell to accomplish some repeatable task. You might find it works better than the old way you used to work.

    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.

  • Yet Another Database

    The “yet another” moniker is used throughout technology. YAAF, YARN, YAML, and more ways that people talk about yet another way to solve some problem or build a platform. That’s one of the amazing things about technology is that we, as a group, are not satisfied with the way things are done. Someone is constantly looking to find a better solution and advance our technology choices in a new direction.

    In the database world, we’ve seen changes as well. Relational databases were the obvious, or perhaps just dominant, choice for most of my career. At some point I saw object databases, XML databases, then streaming databases, none of which gained much popularity. The last few years have seen document databases, graph databases, key value databases, and more. We categorize databases as row-store or column-stores now. There are probably other types I’m forgetting.

    There are also categorizations of OLTP, OLAP, operational, ODS, data warehouses (DW), etc. that people use. I ran across a piece called “Why you need another database“, whose opening line is “Do you really need yet another database?” The answer is, sure, you need an analytical database, but I think the piece is a bit behind the times. Lots of people have built an analytic database in the form of a relational DW, an SSAS cube, or perhaps some other type of NoSQL store (Hadoop is popular) that allows complex querying of data away from the operational, every day OLTP system.

    I would guess many of you have some separate reporting copy of data that allows analysis separately from your online system. This could be a copy of your production database, a data warehouse receiving regular loads, a read-only AG replica, or something else. Some of you might perform analysis in your primary OLTP system, with enough spare capacity to handle the reporting load.

    There are multiple ways in which you may decide to implement an analytical database, but I do agree that most businesses ought to have some way of analyzing the data they collect. The questions in the piece (structure, size, analytics), are good ones, and no matter what your platform, architecture, or technology, make sure that you build something that meets the needs of your organization as a whole, not just the technologists that prefer one platform over another.

    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.