Voice of the DBA

Author: way0utwest

  • Prepping for Summit 2017

    It’s about time for the PASS 2017 Summit. The event essentially starts on Monday with pre-cons and the unofficial networking dinner. Be sure you RSVP and come to the dinner if you don’t have plans. The official event is

    Many people are already traveling and packing for the event. I feel a bit behind as I won’t leave until late Monday afternoon and arrive late Monday night. I’m sure I’m not alone, but it seems like everyone’s ready and I’ve still got a day of work Monday.

    Redgate Software has a booth and a few presentations next week on Wednesday. We’d love to chat with you about ways to make database development easier, especially if you’re thinking DevOps. We also have some contests, swag and prizes.

    I’ll be around Tues, Wed, and Friday, and of course, moving from Game Night to the Redgate Party Thursday night. I’ve got other commitments Thursday day, but hope to see lots of you there.

  • Physical or Virtual Storage

    When I started working with SQL Server, every server had what we’d call das-dee, or DASD (Direct Attached Storage Devices). These were hard drives inside the same physical case as the rest of the Windows computer. I’ve added lots of drives to various server systems over the years. As databases grew, we even had separate boxes in our racks that were attached to the main server, but only filled with drives.

    Technology has changed, and today most of us work with SAN or NAS devices, where the storage is addressed across some type of network. Either a private one (copper or fiber), or the same Ethernet that connects the various computers together. A few of us might even have cloud storage that is located at Microsoft, Amazon, or elsewhere. The Stretch Database feature takes advantage of this last configuration. In all these cases, the storage that our databases see is often cobbled together from other disks that hide the underlying organization from the system.

    Recently I read a piece from Randolph West that talked about recovering data from a RAID array. That reminded me of my early career, where I had to make decisions about how to structure storage. I’ve run RAID 1, 5, 10, 0+1, 6, and maybe more in my career to store data files. However, at some point I stopped worrying about the underlying configuration. I just expected, and trusted, the storage people to ensure that space was available. I even stopped thinking of the z: or y: drives on my database server as disks. Those drives were just storage that existed somewhere in the ether, just available for the database to use.

    In thinking about Randolph’s experiences, I wondered how many of you out there might still deal with physical drives. Do you still make decisions about RAID levels? Do you even know what RAID levels are being used by your databases? If you’re a storage admin, you might, but for those of you that aren’t, do you know anything about your storage configuration?

    Really, I’m speaking of production systems, not development ones. Certainly many of us might know there’s a development server with RAID 5 that holds a bunch of dev/test VMs, but I would expect that might even be rare. Outside of our own workstation, we likely don’t know the storage setup. Plenty of development systems these days probably even use a SAN, maybe even the same one as production, for storage.

    For me, I have no idea of our systems. I used to build the SQLServerCentral servers, and when Redgate took over that part of the business, I helped spec the initial machines we rented as physical hosts. At some point we moved to virtual machines, and while I was asked about the specifications, I didn’t care about any of the hardware. I just said that I wanted enough CPU, RAM, space, and IOPS to handle the load. Deciding what that was, and ensuring it was available, was someone else’s job.

    If you spec hardware, or pay attention, let me know. There certainly are plenty of hardware geeks, like Glenn Berry, that pay attention and prefer particular configurations. Those are the people I’m glad I can ask for advice if need it. I certainly ask for help with my personal systems, but for servers, I just need capacity. Do you feel the same way?

    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.

  • Unstructured Data

    Is unstructured data a bad term? I saw some data professionals complaining about this, saying all data is structured. That’s usually true. A CSV, even a ragged one has structure. XML and JSON have structure, even if it might vary node to node. Certainly our relational tables are structured and some formats can be rigidly mandated between organizations (like EDI). Even data in PDF, Word, MP3, MP4 or other audio/video mediums is structured in that we know the format.

    Given that, is it a misnomer to use the term, unstructured, when describing flexible formats, such as XML? Is it OK for a PDF? I have had a presentation called Unstructured Data in SQL Server. This is primarily about FileStream, FileTable, and searching those objects. In the talk, I classify data in known formats as structured. These would be SQL Server tables and similar objects. At any point in time, we know what all data in the table looks like, even though we can have NULLs or missing data in rows.

    I call XML and JSON semi-structured formats. We can certainly determine the format for any node or section, but we wouldn’t know without querying or examining the data. It’s semi-structured in that there is a hierarchy, but the structured from section to section (essentially row to row) can vary. There can even be depths to hierarchies that vary. In many ways, that makes these great formats for flexibility in data exchange.

    I tend to view data in Word, PDF, MP4s, as unstructured. We don’t necessarily know where the data is, or how to separate it. We can get pages in Word or PDF, but those can vary and don’t necessarily help us extract information. They are XML, but the XML tags don’t relate to the content, unlike many other XML documents. Scenes or tracks in audio/video files might be separators, but those aren’t necessarily helpful in gathering information. Instead, we need other tools that can help deal with that data, finding words, concepts, or more inside of the binary stream.

    I like the term unstructured data because it helps me understand where the information is. While the tables in a database might be full of nonsensical information in some rows, or be poorly designed with data combined into text fields, at least I know where the fields are. Actually, in that case, I’d argue the data in varchar(max) text fields is really unstructured. You might disagree, but give me a better term to describe there the information is stored in a data format.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Basic FORMATting– #SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I saw the addition of FORMAT() to the T-SQL language, but didn’t play with it much. Recently it appeared in some code, and decided to experiment a bit. I had assumed this was mainly for dates, but it’s a general format/culture function that handles numbers as well.

    On the doc’s page, there are the basic description of the parameters, which are NVARCHAR(), so passing in VARCHAR() causes an implicit conversion. It shouldn’t be much, but there are already performance penalties (see Aaron Bertrand’s piece), so don’t add to the overhead.

    One good thing to note is that if you pass in invalid formats or cultures, a NULL is returned. Since the format and culture strings aren’t completely intuitive, this might be a source of issues in your code.

    This is a neat function, relying on CLR formatting rules. That means I can do fun things like:

    DECLARE @i int = 5000;

SELECT FORMAT(@i, N'USD$#');

    Which returns:

    USD$5000

    Or even:

    DECLARE @i INT = 5000
;
SELECT  FORMAT(@i, N'# dahlahs')
;
GO

    Which gives me:

    5000 dahlahs

    There are lots of formats, and certainly lots of nuances to numeric formatting strings. It’s worth reading up if you plan to use this, but again, beware of performance. I’d avoid using this if the data size is large, maybe more than a few hundred rows.

    After all, the database server is a shared resource, and using this CPU to handle simple formatting may not be the best use of your system.